Fixing: There is already an object named ‘sysnsobjs’ in the database

(I am not a DBA, I play one at work because I’ve been thrown into the position since I do all the .net programming, why shouldn’t I take care of the SQL server and web server also.. So this is what worked for me and probably are no where close to best practices)

My task: Move a database from SQL2000 to SQL2008 R2

Error:
System.Data.SqlClient.SqlError: There is already an object named ‘sysnsobjs’ in the database. (Microsoft.SqlServer.Smo)

Why I got this error:
I knew going into this that we’d have a problem. When I started at my company the SQL DB was already created for our website. Who ever it was, they just started throwing data into the master db and left it at that. Fast forward 10 years, I had made a copy of the db and made it, it’s own db but still I knew the copy of system files were still in there and when time came to do a update we were going to have problems. So it was no suprise when I received the error that a system file already existed.

What doesn’t work:
Looking online I’ve seen many people ask about this error and after about 2-3 pages of try this and try that, most just either give up or create some script to pull the data and create tables on a one off basis. So we know that a restore from backup doesn’t work, it gives our error of There is already an object named ‘sysnsobjs’ in the database.

So what about import data from one server to the other?
I created a new Database, named it the same as my sql2000 db and did an import from one server (2000) to the new server(2008 r2). At first glance all looked good, my tables were there and data and my stored procedures were all there but then when I checked on views, there was nothing. All the views were imported as tables (someone explained that it’s because that’s really what they are), so importing failed.

So what worked for me: A Walk through:

Importing from one db to the other but still on the SQL2000 server mostly worked.

1. Create your new db on the current (old) server

2. Right click new db and choose import

3. Choose your source and destination db’s

4. Instead of choosing “Copy tables and views from source database” choose “Copy objects and data between SQL Server databases”

5. On the select Ojects to Copy screen

uncheck Copy all objects and hit the button Select objects (choose everything, check everything)

uncheck use default objects, click Options button. Check everything except Generate Scripts in Unicode

6. Go have lunch this will take awhile

Mine actually failed (but not because of the system files) mine failed becuse of 2 views but everything else was copied over correctly.

7. Now make a backup of this new database

8. Copy bak file to new database server

9. Restore DB on new server

Make sure to check your permissions but all should be there and ready to use. Since you only need to make sure this DB is the same as your last (If you have both SQL servers still running) us something like the demo of http://www.sqlaccessories.com/SQL_Examiner_Suite/ to compare the table structure and it will also compare all of your stored procedures.

Cvent API: Multiple Searches

Narrowing Down a Search with Cvent API

In the past all of my searches in Cvent have been one search to find what I needed but today I ran into a problem. While doing a search for all users that have taken one of our surveys, the search returned 505 people. The next line does a Retrieve of those records, this is where my problem ended up. Cvent limits you to 200 Retrievals per call, so I either had to breakup the array or get less records.

My current code:

thesearch.Filter = New com.cvent.api.Filter(0) {}
 thesearch.Filter(0) = New com.cvent.api.Filter()

thesearch.Filter(0).Field = "Surveyid"

 thesearch.Filter(0).Value = "c382ea11-8825-4161-8513-########"
 thesearch.Filter(0).Operator = com.cvent.api.CvSearchOperatorType.Equals

ids = _ws.Search(com.cvent.api.CvObjectType.Respondent, thesearch)

There had to be away to do a deeper search, specially with the small retrieval requirements. I figured out that you can add more filters and require it to use AND or OR with them.

And or OR is controlled by:

thesearch.SearchType = com.cvent.api.CvSearchType.AndSearch

OR

thesearch.SearchType = com.cvent.api.CvSearchType.ORSearch

Adding more indepth searches (filters). This searches for the survey but also only those who have finished the survey which so far is only 10

'build array size
 thesearch.Filter = New com.cvent.api.Filter(1) {}
'new for array row 0
 thesearch.Filter(0) = New com.cvent.api.Filter()
'new for array row 1
 thesearch.Filter(1) = New com.cvent.api.Filter()
'type of search between filters
 thesearch.SearchType = com.cvent.api.CvSearchType.AndSearch
 thesearch.Filter(0).Field = "SurveyId"
 thesearch.Filter(0).Value = "c382ea11-8825-4161-8513-b977568b50f8"
 thesearch.Filter(0).Operator = com.cvent.api.CvSearchOperatorType.Equals

 thesearch.Filter(1).Field = "Status"
 thesearch.Filter(1).Value = "Complete Response" 'Real Survey
 thesearch.Filter(1).Operator = com.cvent.api.CvSearchOperatorType.Equals

Luckly, This survey will only have around 100 respondents, but what if at some point I need to import 200+, I guess that will be another blog post.

Technorati Tags: API Search, CVent, cvent API, Cvent Filter, Indepth search, Searches

vb.net variable name within a variable

 Otherwise know as “variable string to variable” or “How to blow you mind thinking about variables”

Here’s the setup:

3 Variables

 Dim PTCompSalE As Int32 = 0
 Dim PTCompSalX As Int32 = 0
 Dim PTCompSalM As Int32 = 0

Through a series of  Select Case statements I end up with another variable (FullColumnName) that equals the name of one of the above:

Dim FullColumnName As String

Dim beginColumn As String  'PTComp
Dim middleColumn As String  'Sal
Dim endColumn As String  'X

FullColumnName = beginColumn & middleColumn & endColumn   'equals PTCompSalX

The question is, now that I know which variable I want to change values of (PTCompSalX) how do I tell asp.net that I want to change the value of PTCompSalX when the name is actually in another variable?
I could make a Select Case which would work if like in the example I only had 3 options but I have hundreds of options.
After hours of searching I found that Reflection is what I needed, Thanks to shaul_ahuva at Xtreme Visual Basic Talk.

Dim t As Type = Me.GetType()
Dim fi As FieldInfo = t.GetField(FullColumnName) 'get the value of FullColumnName's string reference
fi.SetValue(Me, 1) 'change the value of FullColumnName's string reference

Now PTCompSalX = 1

This allows me to use FullColumnName’s String to find the original variable and change the string for that original variable. Mind Blown!
While Reflection is something new to be, it looks like it will be on my To Read List.

[answer found]

Technorati Tags: .net, asp.net, reflection, variables, VB.NET

Adding a Web Reference in Visual Studio 2008

I’ve been doing most of my current projects in Visual Studio 2008, but when I was doing CVent API development I was using Visual Studio 2005. Today I needed to add the web reference for CVent’s API to a windows application I was creating.

First thing I noticed was the Add Web Reference option when I’d right click the project was missing. After a small amount of searching I was able to find the Add Web Reference option but it’s a bit hidden. Like in VS2005 right click the project but instead of Add Web Reference choose Add Service Reference

At the bottom of the Add Service Reference window, click the Advanced button

At the bottom of the Advanced window is the Add Web Reference button we’ve been looking for.

From here you’ll be able to add the Web Reference for your project. Here I’ve added CVents API, which is https:// so I had to click “allow the page” multiple times and also tell VS2008 not to block the page, thanks IE..

Technorati Tags: API, asp.net, code, CVent, Service Reference, VB.NET, Web Reference

Validating or Validation of a CheckBoxList

On my web form I came across an instance that I needed a user to either check a box from a checkboxlist or leave a comment, this then would be emailed to someone. So I needed to validate that a checkbox had been checked or at least a comment was added.

On my button I added onclick=”Control_Click”

<asp:Button id="btnSend" runat="server" CssClass="submitLarge" onclick="Control_Click" Text="SEND"></asp:Button>

Then in the codebehind I added the code to validate and then if validation is true, I send email.

Sub Control_Click(ByVal S As Object, ByVal E As EventArgs)
 validate_chkboxlist()
 Dim sb As New System.Text.StringBuilder

If valChkBoxList = True Then
 CreateEmail()
 Else
lblMessage.Text = "<font color=""#FF0000""><b>You must select at least one CheckBox or add a comment!</b></font>"
 End If

 End Sub

Sub validate_chkboxlist()
 For ii = 0 To (cblInterest.Items.Count - 1)
 If (cblInterest.Items(ii).Selected) Or txtComments.Text <> "" Then
 valChkBoxList = True
 Exit Sub
 End If
 Next
 End Sub

As you can see, from Control_Click I call the validate_chkboxlist. In validate_chkboxlist I loop through the checkboxes but also check that the comment.text isn’t blank. If a box is checked or the commentbox has text in it, valChkBoxList is marked as true and the email is sent.

Technorati Tags: asp.net, checkboxlist, code, code behind, validate, validation, VB.NET

vb.net RSS Feed Reader or Reading XML into Dataset

Todays task was to build an RSS reader for a template site. This template site would allow for any of our firms to put in their company info along with their RSS Feed and the website that is created will format all the info and also show their RSS Feed in a nice format.

My Search began and I soon came across Display RSS Feeds using ASP.Net. It worked great, this code grabs the XML with the XMLTextReader puts it into a DataSet and then binds it to a repeater. Exactly what I wanted. Using my Repeater I could pull only the pubDate and Title from the feed and format it how ever was needed. This worked great for the first 3 firms, then I ran into a problem.

As per the XML standards, you are allowed to have multiple tags for each item, so if your post had 2 links you’d see

<br> 'used to center code for blog
<link>http://tek-works.com</link>
<link>http://richardcurry.com</link>
<br>

The problem with this is that when you start adding the XML to your Dataset it tries to create 2 columns with the same name and you receive an error:

A column named ‘link’ already belongs to this DataTable: cannot set a nested table name to the same name.

I also had one blog that always showed up as 403 Forbidden. (also fixed by the code below)

I tried for a couple hours and while i’m sure there’s a way around this, I couldn’t find it so it was back to the searches.

I than ran across How To Create An RSS Reader in VB.NET on forums.asp.net

This code didn’t use the repeater but actually pulled each piece of the XML that you needed and then allowed you to format it.

I had to make a few changes, I needed the date and also had to add some formatting. After finishing my changes I tested the code with MANY RSS Feeds and it worked for all of them.

a couple of the gotcha’s:

1. In XML you have to call the tag exactly by it name (case sensitive)  i.e. pubDate
2. Like when pulling from SQL watch for Null, except in XML the tag may not even be there

I call and post the results with (feed1 is a label):

<br>'used to center code for blog
Feed1.Text = ProcessRSS("<a href="http://feeds.feedburner.com/Tek-workscom">http://feeds.feedburner.com/Tek-workscom</a>?format=xml", "Tek-Works")

<br>

and the code with my changes

<br> 'used to center code for blog
Public Shared Function ProcessRSS(ByVal rssURL As String, ByVal feed As String) As String
 Dim request As WebRequest = WebRequest.Create(rssURL)
 Dim response As WebResponse = request.GetResponse()
 Dim sb As New StringBuilder("")
 Dim rssStream As Stream = response.GetResponseStream()
 Dim rssDoc As New XmlDocument()
 rssDoc.Load(rssStream)
 Dim rssItems As XmlNodeList = rssDoc.SelectNodes("rss/channel/item")

Dim title As String = ""
 Dim pubdate As String = ""
 Dim datepubdate As Date
 Dim format As String = "MMM d, yyyy"
 Dim link As String = ""
 Dim upperlimit As Integer = rssItems.Count
 If upperlimit > 4 Then
 upperlimit = 4
 End If
 If upperlimit > 0 Then
 sb.Append("<table style='padding-left: 10px; width: 290px; vertical-align: top;'><tr><td style='padding-left: 20px;' align='left'><br />")
 Dim i As Integer = 0
 While i < upperlimit
 Dim rssDetail As XmlNode
 rssDetail = rssItems.Item(i).SelectSingleNode("pubDate")
 If rssDetail IsNot Nothing Then
 datepubdate = CDate(rssDetail.InnerText)
 sb.Append("<div class='sectionTextsmallergrey'>")
 sb.Append(datepubdate.ToString(format) + "</div>")
 Else
 pubdate = ""
 sb.Append("<div class='sectionTextsmallergrey'>")
 sb.Append(pubdate + "</div>")
 End If

 rssDetail = rssItems.Item(i).SelectSingleNode("title")
 If rssDetail IsNot Nothing Then
 title = rssDetail.InnerText
 Else
 title = ""
 End If

 sb.Append("<div class='sectionText'>")
 sb.Append(title + "</div><hr id='lblhr1' runat='server' style='width: 100%;' />")
 i += 1
 End While
 sb.Append("</td></tr></table>")
 End If
 Return sb.ToString()
 End Function

<br>

Technorati Tags: asp.net, DataSet, RSS Feed, VB.NET, XML

Installing Visual Studio 2005 on Windows 7

Today I received a new computer at work, while that’s great news, the bad news is I now need to move everything from 4 years at this company to the new PC (including things that were on the PC before I started). We have some legacy programs that still are using Visual Studio 2005 (yeah i know, you don’t have to say update them!) My new computer is running Windows 7 and while installing VS 2005 I ran into a few problems that took me most the day to figure out.

Installing VS 2005 on Win 7

When you first put in the VS 2005 CD the install will begin, you are suddenly startled with a popup window telling you that there are known problems with VS 2005 and Win 7 and that you need to install “Visual Studio 2005 Service Pack 1 Update for Windows Vista”, this is misleading because you haven’t even installed VS 2005 yet so running the file they are pushing at you will not help. Instead, go ahead and install VS 2005 normaly from CD (or file). Next I had forgot that when VS 2005 was released, it didn’t support Web Applications and there was an update for that, so my next step is to install VS 2005 SP1 (this took me forever to find the correct download) http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5553 after installing SP1 you will then install the “for Vista update” located at http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=7524 and yes this is for Windows 7, even though it also says Vista.

One other thing I did install between the full version and SP1 was http://www.microsoft.com/download/en/details.aspx?id=6071 Microsoft Visual Studio 2005 – Update to Support Web Application Projects. The reason I left it out of the above list is beacuse I’m not sure if you need it, the Web Application updates maybe in SP1, but if you run into problems try installing this also.

Oh, and don’t forget to go to C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\ right click on devenv.exe and choose run as administrator.

Technorati Tags: installing, visual studio 2005, vs 2005, Win 7, Windows 7

Finding Network MAC Addresses with Advanced IP Scanner 2.0

Tek-Works was asked awhile back to review Advanced IP Scanner 2.0, not wanting to just review something with out actually needing it, would be useless for our readers, so I put it off till today. Today at work, my task was to add a wireless router to our network and to make it as secure as possible.

Locking Down the Router

The D-Link Router I chose had the option to restrict access by MAC address. This means that I can give the router a list of MAC addresses within our company and the router will only allow them to connect to the network and as an added layer of protection they also needed to have a 10 character password. So you ask what is a MAC Address?

A Media Access Control address (MAC address) is a unique identifier assigned to network interfaces for communications on the physical network segment. The standard format for printing MAC  addresses in human-friendly form is six groups of two hexadecimal digits, separated by hyphens (-) or colons (:)  (e.g. 01-23-45-67-89-ab   or   01:23:45:67:89:ab )

So my problem was how do I quickly and easily get a list of MAC Addresses for the router?

Finding MAC Addresses with Advanced IP Scanner 2.0

Advanced IP Scanner 2.0 (Download here) was the exact software I needed, it took seconds to download and install and I was ready to scan the network. I’ll walk through the steps so those of you wanting to lock down your home wireless router can easily do so.

The first thing Advanced IP Scanner 2.0 wants to know is what ip addresses it should scan. The default is 192.168.1.1 – 192.168.1.254 but to make sure you’re network is using these IP’s try this:

Click the start button and find RUN (on XP you click the RUN to start it, on Win7 you just start typing in the open text box). Once you have a box to type in, type CMD and hit enter. A Command Prompt will open up, it doesn’t matter what is on the line behind the > just type ipconfig and hit enter

For most networks (specially home networks) you can take the first 3 sections of  ”IP Address” and use them to create your scan IP’s. So we take 192.168.1 and start with .1 and end with .254 (Don’t ask about why 254.. just do it lol) If your IP Address was 10.10.0.134, your scan range would be 10.10.0.1 – 10.10.0.254

So we’ve got our range of IP Addresses to scan, so hit Scan..

Advanced IP Scanner 2.0 has now scanned my network and you see a list of names of computers/laptops on your network, their IP Address and their MAC Address. With this list I was then able to add the MAC addresses to my Router and secure our wireless network even more than just passwords.

Other uses for Advanced IP Scanner 2.0

While playing around with Advanced IP Scanner 2.0 for this post I wanted to see what else I could do with it. One of the cool features is if you click on the + for each computer you’ll see that computers shares, and even copy the path (\\192.168.1.136\Temp-folder) to that share, this makes for a simple way to add the share to your Mapped Network Drives.  Advanced IP Scanner 2.0 will also work with Radmin 3.4 which will allow you you access and control each computer remotely.

For a quick and simple scan of your network, that not only shows MAC & IP Address but gives you quick access to each computer on your network (including Shares,HTTP, HTTPs and FTP) Advanced IP Scanner 2.0 is the best product you’re going to find that is Freeware.

Technorati Tags: Advanced IP Scanner 2.0, freeware, FTP, HTTP, HTTPS, scan networks, shares

Adding Content to Accordion Pane in Code Behind

Adding Content to Accordion Pane in code behind and Creating an Accordion Control in Code Behind

Today I had to create an app the had a menu of countries and states but I only wanted countries or states that our company had businesses in. I’d pull them from the SQL server and have an accordion panel as the menu, one menu item States and when you clicked on it all the states would list below it as LinkButtons, The same setup was created for the Countries also. Using LinkButtons I would then open panels to the right depending on their menu choice.

There are 2 options for creating the Accordion and it’s Panels. You can either create them in the aspx page by dragging them onto the page and then add data to them from the code behind page like this:

Adding to an already created Accordion from Code Behind

<br>  'used to center code on blog

   Dim stateslink As New LinkButton
   stateslink.ID = "States" & dr("Country") 'set Linkbutton ID
   stateslink.Text = dr("Country") 'set Linkbutton text
   stateslink.CssClass = "LinkButtonSideindent" 'set Linkbutton CSS
   AddHandler stateslink.Click, AddressOf lnk_Click 'once clicked do what?
   AccordionPane0.ContentContainer.Controls.Add(stateslink) 'add to AccordionPane0

<br>

In the above code, AccordionPane0 was in the accordion we created by dragging and dropping.
But what if you wanted to create the entire accordion in the code behind?

Creating an Accordion in Code Behind

First on your aspx page you need to tell the code behind where this accordion is going to go. Add this to your aspx page:

<br/>

<asp:Panel ID="AccordionPanel" runat="server">

</asp:Panel>

<br/>

Then create the accordion in code behind

<br> 'to center on blog

Dim Accordion2 As Accordion = New Accordion
        Accordion2.ID = "States"
        'Accordion2.AutoSize = none
        Accordion2.ContentCssClass = "LinkButtonSideindent2"

        Accordion2.HeaderCssClass = "LinkButtonSide"
        Accordion2.HeaderSelectedCssClass = "LinkButtonSideactive"
        Accordion2.FadeTransitions = "True"
        Accordion2.SelectedIndex = "-1"
        Accordion2.RequireOpenedPane = "false"
        Accordion2.FramesPerSecond = 40
        Accordion2.TransitionDuration = 250
        Accordion2.RequireOpenedPane = False
        Accordion2.Panes.Add(newPaneStates("State / Province"))

        AccordionPanel.Controls.Add(Accordion2)

<br>

The above code creates an accordion named Accordion2 with an ID of States and a pane called State / Province. After creating the accordion we need to add content to it, just like above except this time we’ll create the pane and point the data to our new accordion

<br/> ' centering code for blog

Dim aPane As AccordionPane = New AccordionPane ' creates new pane
aPane.HeaderContainer.Controls.Add(New LiteralControl(header)) 'creates header, set from above code State/provinces

...Get data from sql...
...Push data into the accordion pane..

    Dim stateslink As New LinkButton
    stateslink.ID = "States" & dr("Country")
    stateslink.Text = dr("Country")
    stateslink.CssClass = "LinkButtonSideindent"
    AddHandler stateslink.Click, AddressOf lnk_Click
    AccordionPane0.ContentContainer.Controls.Add(stateslink)

<br/>

Last, if one of the linkButtons is clicked we need to create a function that will tell the page what to do:

<br/>

Protected Sub lnk_Click(ByVal sender As Object, ByVal e As EventArgs)
        ClientScript.RegisterClientScriptBlock(Me.[GetType](), "lnk", _
        "<script type = 'text/javascript'>alert('LinkButton Clicked');</script>")
    End Sub

<br/>

Technorati Tags: Accordion Control, code behind, LinkButton, VB.NET

.NET Regular Expression in Code Behind

Most the time when we’re using regular expressions we’re doing it with in our aspx page checking against a text box or some other control.

example

'

<asp:RegularExpressionValidator ID="regfirstName"
 runat="server" ErrorMessage="This expression does not validate."
  ControlToValidate="txtFirstName" 
ValidationExpression="^[a-zA-Z0-9'.\s]{1,40}$" />

'

When working with Query Strings you’ll need to compare the QueryString against a regular expression in the code behine, this is done by using the regex class from the System.Text.RegularExpressions namespace.

example

'

If Regex.IsMatch(Request.QueryString("region"), "^[a-zA-Z0-9\s\-]+$") Then

                Else

                End If

'

btw great site to help and/or get commonly user regular expressions is regexlib.com

Technorati Tags: .net, asp.net, code behind, regex, regular expression, VB.NET