none
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

    Saturday, June 09, 2012 12:28 AM

Answers

  • 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
     

    Sunday, June 10, 2012 10:58 PM
  • 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"




    Monday, June 11, 2012 11:18 PM

All replies

  • 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'.

    Saturday, June 09, 2012 1:24 PM
  • 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

    Sunday, June 10, 2012 1:33 PM
  • 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


    Sunday, June 10, 2012 5:45 PM
  • 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

    Sunday, June 10, 2012 7:25 PM
  • 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

    Sunday, June 10, 2012 8:10 PM
  • 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

    Sunday, June 10, 2012 9:27 PM
  • 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
     

    Sunday, June 10, 2012 10:58 PM
  • 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

    Monday, June 11, 2012 5:12 AM
  • 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
    

    Monday, June 11, 2012 10:58 PM
  • 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"




    Monday, June 11, 2012 11:18 PM
  • 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



    Monday, June 11, 2012 11:21 PM
  • 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

    Monday, June 11, 2012 11:25 PM
  • @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

    Tuesday, June 12, 2012 4:22 AM
  • 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

    Tuesday, June 12, 2012 8:15 AM