SQL Database VB 2010 Please Help

Question

I am totally confused about the way SQL server Express works with my application, it seems like it is a hit and miss whether it works or not.

In my Server Explorer if I show data, I see data in my tables. 
Its path is:
C:\Visual Studio 2010\Projects\TenantTracker2012Sql\TenantTracker2012Sql\TenantTracker2012_be.mdf

Currently in Solution Explorer I have my mdf to "Do Not Copy" (I've actually tried all 3 settings) its path is:
C:\Visual Studio 2010\Projects\TenantTracker2012Sql\TenantTracker2012Sql\TenantTracker2012_be.mdf

Properties/Settings path is:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TenantTracker2012_be.mdf;Integrated Security=True;User Instance=True

My data just never shows up.

Is there anyone out there willing to discuss this on the phone? I am trying to get my demo up and running by the end of the month.

Thanks

MW2011

Answer 1

Hi.  So you are using a local .mdf file and trying to attach it to the local instance of SQL Express when the project starts up?  Below is what connectionstrings.com has to say about this (assuming SQL2005 but this is likely true for 2008 as well).  Are you meeting all the requirements that it says?

Using an User Instance on a local SQL Server Express instance

          The User Instance functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer.       

Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;

To use the User Instance functionality you need to enable it on the SQL Server. This is done by executing the following command: sp_configure 'user instances enabled', '1'. To disable the functionality execute sp_configure 'user instances enabled', '0'.

Answer 2

I am using the following code to try and populate the user info label on all my menu forms. I am trying to get the data when I load my mainmenu.

In server explorer I can show data for my ownerinfo table and see all the fields populated, however I never get any of my variables populated.

I am sure it has something to do with the mdf that is loaded at startup does not have any data in it, which I thought would be the same one that is in server explorer??

    Public Sub chkUserInfo()
        Dim strID As String = "1"
        Dim ConnString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TenantTracker2012_be.mdf;Integrated Security=True;User Instance=True"
        Dim SqlString As String = "Select * From [Owner Info] WHERE OwnerID = ? "
        Using conn As New SqlConnection(ConnString)

            Using cmd As New SqlCommand(SqlString, conn)

                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("OwnerID", strID)
                conn.Open()

                Dim reader As SqlDataReader = cmd.ExecuteReader()
                Dim str1 As String = ""
                If reader.HasRows Then
                    While reader.Read()
                        strOwnerInfo = reader.Item("Company Name").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("First Name").ToString() & " " & reader.Item("Last Name").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("Position").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("Address").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("City").ToString() & ", " & reader.Item("State").ToString() & " " & reader.Item("Zip").ToString()
                    End While
                    Me.lblOwnerInfo.Text = strOwnerInfo
                End If
                reader.Close()
                cmd.Parameters.Clear()
            End Using
        End Using
    End Sub

I am using the following pointing to a mdb file and it works fine, I want to switch over to all SQL Server not an access database.

    Public Sub ChkUserInfo()
        Dim strID As String = "1"
        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Rent Roll\Tenant Tracker 2011_be.mdb;Persist Security Info=False"
        Dim SqlString As String = "Select * From [Owner Info] WHERE OwnerID = ? "
        Using conn As New OleDbConnection(ConnString)

            Using cmd As New OleDbCommand(SqlString, conn)

                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("OwnerID", strID)
                conn.Open()

                Dim reader As OleDbDataReader = cmd.ExecuteReader()
                Dim str1 As String = ""
                If reader.HasRows Then
                    While reader.Read()
                        strOwnerInfo = reader.Item("Company Name").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("First Name").ToString() & " " & reader.Item("Last Name").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("Position").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("Address").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("City").ToString() & ", " & reader.Item("State").ToString() & " " & reader.Item("Zip").ToString()
                    End While
                    Me.lblOwnerInfo.Text = strOwnerInfo
                End If
                reader.Close()
                cmd.Parameters.Clear()
            End Using
        End Using
    End Sub

Thanks

WINN2012

Answer 3

With SQL Server, you would normally connect to the instance of the service (i.e. .\SQLEXPRESS in your case) and give it the name of the database you want to use, not a file. For example, if the database name is TenantTracker2012 then your connection string would be

Server=.\SQLEXPRESS;Database=TenantTracker2012;Trusted_Connection=True;

Of course, you would have to set up SQL Server on the end-user machine and install the database to it. Bear in mind that on Vista and later the user typically won't have write access to to anything in the Program Files directory structure.

HTH,

Andrew

Answer 4

I am having a whale of a time getting this right.

My plan was to load the owner information when my main menu starts up, it populates some variables (supposed to anyway) that would be used in my title label on ALL my menu forms.

This is the connection string I am trying to use atm (tried all different flavors)

        Dim ConnString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TenantTracker2012_be;Integrated Security=True;User Instance=True"

Dim SqlString As String = "Select * From [Owner Info] WHERE OwnerID = ? "

It never finds any data in the Owner Info table.

Currently in Solution Explorer my mdf is set to Copy If Newer.

In the Server Explorer, I can see my data in my tables, however I dont see any of that information when I run my project.

I also open SQL Server Management Studio, I shows a database there (must be an old one) the table names are different than the ones in Server Explorer for my project.

Really getting frustrated as I know this should be simple, but I am just having a hard time comprehending what I am doing wrong.

The idea was that I was going to have ALL my tables loaded with data for me to test with, then wipe out all the tables before I package it up.

I have Team Viewer if anyone would like to view my screen and possibly show me what I am doing wrong.

WINN2010

Answer 5

I have one single instance of a database (in D:\mssql databases). Nothing has to be copied, gets lost or is not found or not inserted. Maybe this could solve your problem, too.

---

Armin

Answer 6

Ok

I moved my mdf file to c:\Rent Roll\

I changed all my settings in my project to point to that file.

I set my db application to open the owner info for to make sure that its seeing the data, it is.

When i switch back to my main form that has my chkOwnerInfo routine, it never finds my data. using this connection string.

    Public Sub chkUserInfo()
        Dim intID As Integer = 1
        Dim ConnString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TenantTracker2012_be;Integrated Security=True;User Instance=True"
        'Dim ConnString As String = "Data Source=.\SQLEXPRESS;AttachDbFileName=C:\Rent Roll\TenantTracker2012_be.mdf;Integrated Security=True;User Instance=True"
        Dim SqlString As String = "Select * From OwnerInfo WHERE OwnerID = ? "
        Using conn As New SqlConnection(ConnString)

            Using cmd As New SqlCommand(SqlString, conn)

                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("OwnerID", intID)
                conn.Open()

                Dim reader As SqlDataReader = cmd.ExecuteReader()
                If reader.HasRows Then
                    While reader.Read()
                        strOwnerInfo = reader.Item("Company Name").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("First Name").ToString() & " " & reader.Item("Last Name").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("Position").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("Address").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("City").ToString() & ", " & reader.Item("State").ToString() & " " & reader.Item("Zip").ToString()
                    End While
                    Me.lblOwnerInfo.Text = strOwnerInfo
                End If
                reader.Close()
                cmd.Parameters.Clear()
            End Using
        End Using
    End Sub
As I said, if I default open my Owner Info for the data is there, so I has to be the connection string I am not getting correct.

WINN2012

Answer 7

You appear to be using the data objects in the System.Data.SqlClient namespace.  When using SqlClient you cannot use the "?" parameter placeholders.  You need to use named parameters instead.  Try this instead (where I replaced the "?" with "@OwnerId" in the query and where you add the parameter:

    Public Sub chkUserInfo()
         Dim intID As Integer = 1
         Dim ConnString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TenantTracker2012_be;Integrated Security=True;User Instance=True"
         'Dim ConnString As String = "Data Source=.\SQLEXPRESS;AttachDbFileName=C:\Rent Roll\TenantTracker2012_be.mdf;Integrated Security=True;User Instance=True"
         Dim SqlString As String = "Select * From OwnerInfo WHERE OwnerID = @OwnerID "
         Using conn As New SqlConnection(ConnString)
 
            Using cmd As New SqlCommand(SqlString, conn)
 
                cmd.CommandType = CommandType.Text
                 cmd.Parameters.AddWithValue("@OwnerID", intID)
                 conn.Open()
 
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                 If reader.HasRows Then
                     While reader.Read()
                         strOwnerInfo = reader.Item("Company Name").ToString() & vbCrLf
                         strOwnerInfo = strOwnerInfo & reader.Item("First Name").ToString() & " " & reader.Item("Last Name").ToString() & vbCrLf
                         strOwnerInfo = strOwnerInfo & reader.Item("Position").ToString() & vbCrLf
                         strOwnerInfo = strOwnerInfo & reader.Item("Address").ToString() & vbCrLf
                         strOwnerInfo = strOwnerInfo & reader.Item("City").ToString() & ", " & reader.Item("State").ToString() & " " & reader.Item("Zip").ToString()
                     End While
                     Me.lblOwnerInfo.Text = strOwnerInfo
                 End If
                 reader.Close()
                 cmd.Parameters.Clear()
             End Using
         End Using
     End Sub
 

Answer 8

In addition to dig boy, try also to avoid the asterix * if you are working with string identifier, those are case sensitive so a little mistake brings you in trouble

Therefore

"Select [First Name], Position, Address, City From OwnerInfo WHERE OwnerID = @OwnerID "

Better to replace that columname of First Name to FirstName

---

Success
Cor

Answer 9

Ok I have this now: However it does not return any data. See my second block of code I found on the web, its working however its double populating my variable.

    Public Sub chkUserInfo()
        Dim intID As Integer = 1
        Dim ConnString As String = "Data Source=WINN-DESKTOP\SQLEXPRESS;Initial Catalog=C:\RENT ROLL\TENANTTRACKER2012_BE.MDF;Integrated Security=True"
        'Dim ConnString As String = "Data Source=.\SQLEXPRESS;AttachDbFileName=C:\Rent Roll\TenantTracker2012_be.mdf;Integrated Security=True;User Instance=True"
        Dim SqlString As String = "Select CompanyName, FirstName, LastName, Position, Address, City, State, Zip From OwnerInfo WHERE OwnerID = @OwnerID "
        Using conn As New SqlConnection(ConnString)

            Using cmd As New SqlCommand(SqlString, conn)

                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@OwnerID", intID)
                conn.Open()

                Dim reader As SqlDataReader = cmd.ExecuteReader()
                If reader.HasRows Then
                    While reader.Read()
                        strOwnerInfo = reader.Item("CompanyName").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("FirstName").ToString() & " " & reader.Item("Last Name").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("Position").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("Address").ToString() & vbCrLf
                        strOwnerInfo = strOwnerInfo & reader.Item("City").ToString() & ", " & reader.Item("State").ToString() & " " & reader.Item("Zip").ToString()
                    End While
                    Me.lblOwnerInfo.Text = strOwnerInfo
                End If
                reader.Close()
                cmd.Parameters.Clear()
            End Using
        End Using
    End Sub

This works. I even copied the connection string to the code above, no joy :0(

    Public Sub chkUserInfo()
        da.Fill(ds, "OwnerInfo")
        dg.DataSource = ds.Tables("OwnerInfo")

    End Sub
    Dim cs As New SqlConnection("Data Source=WINN-DESKTOP\SQLEXPRESS;Initial Catalog=C:\RENT ROLL\TENANTTRACKER2012_BE.MDF;Integrated Security=True")
    Private da As New SqlDataAdapter("SELECT * FROM OwnerInfo", cs)
    Private ds As New DataSet

    Public Sub chkUserInfo()
        Dim str_connection As String = "Data Source=WINN-DESKTOP\SQLEXPRESS;Initial Catalog=C:\RENT ROLL\TENANTTRACKER2012_BE.MDF;Integrated Security=True"
        Dim mycon As SqlConnection
        Dim comUserSelect As SqlCommand
        Dim dr As SqlDataReader
        Dim ds As DataSet
        Dim da As SqlDataAdapter

        mycon = New SqlConnection(str_connection)
        comUserSelect = New SqlCommand("SELECT * FROM OwnerInfo", mycon)
        mycon.Open()
        dr = comUserSelect.ExecuteReader
        If dr.HasRows Then
            While dr.Read() 'While Data is Present
                strOwnerInfo = ""
                strOwnerInfo = dr.Item(3).ToString & vbCrLf ' Company
                strOwnerInfo = strOwnerInfo & dr.Item(1).ToString & " " & dr.Item(2).ToString & vbCrLf ' Name
                strOwnerInfo = strOwnerInfo & dr.Item(4).ToString & vbCrLf ' Position
                strOwnerInfo = strOwnerInfo & dr.Item(5).ToString & vbCrLf ' Address
                strOwnerInfo = strOwnerInfo & dr.Item(6).ToString & ", " & dr.Item(7).ToString & " " & strOwnerInfo & dr.Item(8).ToString 'City, St, Zip
                Me.lblOwnerInfo.Text = strOwnerInfo
            End While
        Else

        End If
    End Sub

Answer 10

Update to that, the original code works if I change: So why dont my Column Names work?? I have to use the column #. I had to change the SELECT to * as well.

strOwnerInfo = reader.Item("CompanyName").ToString() & vbCrLf
strOwnerInfo = strOwnerInfo & reader.Item("FirstName").ToString() & " " & reader.Item("Last Name").ToString() & vbCrLf
strOwnerInfo = strOwnerInfo & reader.Item("Position").ToString() & vbCrLf
strOwnerInfo = strOwnerInfo & reader.Item("Address").ToString() & vbCrLf
strOwnerInfo = strOwnerInfo & reader.Item("City").ToString() & ", " & reader.Item("State").ToString() & " " & reader.Item("Zip").ToString()

To

strOwnerInfo = reader.Item(3).ToString() & vbCrLf
strOwnerInfo = strOwnerInfo & reader.Item(1).ToString() & " " & reader.Item(2).ToString() & vbCrLf
strOwnerInfo = strOwnerInfo & reader.Item(4).ToString() & vbCrLf
strOwnerInfo = strOwnerInfo & reader.Item(5).ToString() & vbCrLf
strOwnerInfo = strOwnerInfo & reader.Item(6).ToString() & ", " & reader.Item(7).ToString() & " " & reader.Item(8).ToString()

I also had to change: 

Dim SqlString As String = "Select CompanyName, FirstName, LastName, Position, Address, City, State, Zip From OwnerInfo WHERE OwnerID = @OwnerID "

To

Dim SqlString As String = "Select * FROM OwnerInfo WHERE OwnerID = @OwnerID"

Answer 11

It seems you've declared strOwnerInfo outside the sub, so it's not empty starting with the second call. In the second code block, you delete it before usage.

If you expect only one record, there's no need to write a loop. Just write

     If dr.Read() Then    'found
          'concatenate strings
     else 'not found
          'handle it
     end if

In the second code block, if you retrieve multiple records, you overwrite the label's content each time, and you delete strOwnerInfo with each iteration. Doesn't seem to make sense. If you want all information of all records in the label in the end, you shouldn't set strOwnerInfo to "" each time. And it's not necessary to update the label with each iteration. The UI thread is locked anyway.

For quick concatenating larger Strings, the System.Text.Stringbuilder can (should) be used.

---

Armin

Answer 12

So why dont my Column Names work??

In what way? Do you get an exception? Which one in which line? Or wrong program behavior?

---

Armin

Answer 13

@MSW2011

By the way, why do you use such vintage style code, like you see it is cumbersome to use because all the chances on errors while especially around the SQL database are made so many solutions which should avoid that.

The old one is the DataSet with its DBDataAdapters (got a new live with the TableAdapterManager)
a kind of intermediate Linq to Sql which was created direct with Linq and uses mainly anonymous classes
the latest Linq to Entities. 

I've no preference, if you have a small database I would go probably for the TableAdapterManager which is the most easies to implement in a quick way. However if your solution is huge, then start thinking about Linq to entities.

You can try this sample on our website.

http://www.vb-tips.com/StronglyTypedDataSet.aspx

---

Success
Cor

Answer 14

Cor

I am using Vintage style code because I have been using VS 2010 for a grand total of about a month and dont know any better.

All I was trying to do was populate a title label on all my menu forms with the owner information.

Seemed to me like it was better to populate that global variable once when my main menu starts up than to check for the information every time a menu opens.

I would rather start off doing things the correct way, I followed your link above, however its populating a datagrid, i'm just populating a label.

After some tweaking the above code now works and I am populating my label on ALL my menu forms as desired.

MW2012