none
MDF data File ERROR RRS feed

  • Question

  • I just previously created one project using .mdf file sql server on vs 2015.
    today I created the same project but with two tables. . in debug it shows Error. Please help me in how to do..

    code is below

    Imports System.Data.SqlClient
    Public Class Form1
        Dim cn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=E:\VS-2015\Projects\VB-2015\Customer\Customer\Database1.mdf;Integrated Security=True")
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            cmd.Connection = cn
            loadlistbox()
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If TxtId.Text <> "" And TxtName.Text <> "" And TxtPlace.Text <> "" And TxtWage.Text <> "" And TxtBonus.Text <> "" Then
                cn.Open()
                cmd.CommandText = "Insert into Info(CusId,CusName,CusPlace) values ('" & TxtId.Text & "','" & TxtName.Text & "','" & TxtPlace.Text & "')"
                cmd.CommandText = "Insert into Wages(CusWage,CusBonus) values ('" & TxtWage.Text & "', '" & TxtBonus.Text & "')"
                cmd.ExecuteNonQuery()
                cn.Close()
                TxtId.Text = ""
                TxtName.Text = ""
                TxtPlace.Text = ""
                TxtWage.Text = ""
                TxtBonus.Text = ""
                loadlistbox()
            End If
        End Sub
        Private Sub loadlistbox()
            ListBox1.Items.Clear()
            ListBox2.Items.Clear()
            ListBox3.Items.Clear()
            ListBox4.Items.Clear()
            ListBox5.Items.Clear()
            cn.Open()
            cmd.CommandText = "Select CusId,CusName,CusPlace from Info"
            cmd.CommandText = "Select CusWage,CusBonus from Wages"
            dr = cmd.ExecuteReader()
            If dr.HasRows Then
                While (dr.Read())
                    ListBox1.Items.Add(dr("CusId"))
                    ListBox2.Items.Add(dr("CusName"))
                    ListBox3.Items.Add(dr("CusPlace"))
                    ListBox4.Items.Add(dr("CusWage"))
                    ListBox5.Items.Add(dr("CusBonus"))
    
                End While
            End If
            cn.Close()
        End Sub
        Private Sub ListBox1_MouseClick(sender As Object, e As MouseEventArgs) Handles ListBox1.MouseClick
            Dim lb As New ListBox
            lb = sender
            If lb.SelectedIndex <> -1 Then
                ListBox1.SelectedIndex = lb.SelectedIndex
                ListBox2.SelectedIndex = lb.SelectedIndex
                ListBox3.SelectedIndex = lb.SelectedIndex
                ListBox4.SelectedIndex = lb.SelectedIndex
                ListBox5.SelectedIndex = lb.SelectedIndex
            End If
        End Sub
    End Class
    
    

    table Info

    Table Wages

    Debug Error

    Tuesday, January 19, 2016 4:04 PM

Answers

  • Hi Rakshithc,

    According to your code snippet, it seems that it has some problems. I modify your code as below, and it works fine on my side.

    Imports System.Data.SqlClient
    
    
    Public Class Form1
    
    
        Dim cn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=E:\VS-2015\Projects\VB-2015\Customer\Customer\Database1.mdf;Integrated Security=True")
    
        Dim cmd As New SqlCommand
    
        Dim dr As SqlDataReader
    
        Dim dtcustomers As New DataTable
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            cmd.Connection = cn
    
            loadlistbox()
    
        End Sub
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            If TxtId.Text <> "" And TxtName.Text <> "" And TxtPlace.Text <> "" And TxtWage.Text <> "" And TxtBonus.Text <> "" Then
    
                cn.Open()
    
                cmd.CommandText = "Insert into Info(CusId,CusName,CusPlace) values ('" & TxtId.Text & "','" & TxtName.Text & "', '" & TxtPlace.Text & "')"
    
                cmd.CommandText += ";Insert into Wages(CusId,CusWage,CusBonus) values ('" & TxtId.Text & "','" & TxtWage.Text & "', '" & TxtBonus.Text & "')"
    
                cmd.ExecuteNonQuery()
    
                cn.Close()
    
                TxtId.Text = ""
    
                TxtName.Text = ""
    
                TxtPlace.Text = ""
    
                TxtWage.Text = ""
    
                TxtBonus.Text = ""
    
                loadlistbox()
    
            End If
    
        End Sub
    
    
        Private Sub loadlistbox()
    
            dtcustomers = New DataTable()
    
    
            ListBox1.Refresh()
    
            ListBox2.Refresh()
    
            ListBox3.Refresh()
    
            ListBox4.Refresh()
    
            ListBox5.Refresh()
    
    
            cn.Open()
    
            Try
    
                Dim sqlString = "SELECT Info.CusId,Info.CusName,Info.CusPlace,Wages.CusWage,Wages.CusBonus FROM Info INNER JOIN Wages ON Wages.CusId = Info.CusId"
    
                Dim sda As SqlDataAdapter = New SqlDataAdapter(sqlString, cn)
    
                sda.Fill(dtcustomers)
    
                ListBox1.DataSource = dtcustomers
    
                ListBox1.DisplayMember = "CusId"
    
                ListBox2.DataSource = dtcustomers
    
                ListBox2.DisplayMember = "CusName"
    
                ListBox3.DataSource = dtcustomers
    
                ListBox3.DisplayMember = "CusPlace"
    
                ListBox4.DataSource = dtcustomers
    
                ListBox4.DisplayMember = "CusWage"
    
                ListBox5.DataSource = dtcustomers
    
                ListBox5.DisplayMember = "CusBonus"
    
    
            Catch ex As Exception
    
    
            End Try
    
            cn.Close()
    
        End Sub
    
    
    
        Private Sub ReadSingleRow(ByVal record As IDataRecord)
    
            Console.WriteLine(String.Format("{0}, {1}", record(0), record(1)))
    
        End Sub
    
    
    
    End Class
    

    I hope it’s helpful to you.

    Best regards,

    Cole Wu

    • Marked as answer by Rakshithc Friday, January 29, 2016 2:27 AM
    Tuesday, January 26, 2016 7:13 AM
    Moderator

All replies

  • In order to insert into both of the tables, concatenate the INSERT commands:

    . . .
    cmd.CommandText = "Insert into Info(CusId,CusName,CusPlace) values ('" & TxtId.Text & "','" & TxtName.Text & "','" & TxtPlace.Text & "')"
    cmd.CommandText += "; Insert into Wages(CusWage,CusBonus) values ('" & TxtWage.Text & "', '" & TxtBonus.Text & "')"
    . . .

    In order to read from both of the tables, the rows have to be linked by some common value. Probably CusId can be used as a unique identification. Therefore, add CusId column to Wages table. Adjust the above INSERT statements to insert the CusId value too.

    Probably you will have to remove the existing data from these tables.

    Then replace your SELECT statements with this one:

    . . .
    cmd.CommandText = "SELECT Info.CusId, Info.CusName, Info.CusPlace, Wages.CusWage, Wages.CusBonus FROM Info INNER JOIN Wages ON Wages.CusId=Info.CusId"
    dr = cmd.ExecuteReader()
    . . .

    And use parameterised queries instead of string concatenations, which will not work in certain cases.




    • Edited by Viorel_MVP Tuesday, January 19, 2016 7:01 PM
    Tuesday, January 19, 2016 6:58 PM
  • Again it shows error when I click button. .mdf file wages column does not allows nulls. . Later I put check mark null in table for CusWage and CusBonus. .But when I click the button It shows the same Error
    Wednesday, January 20, 2016 3:56 PM
  • Again I changed null to CusId in wages column. . In debug I clicked the button it doesn't shows error but remains all the List Boxs itself Blank
    Wednesday, January 20, 2016 4:12 PM
  • Hi Rakshithc,

    Since you problem is more related to Data. I moved it to the ADO.NET forum for better support.

    Thanks for your understanding.

    Best Regards,

    Lake Xiao

    Thursday, January 21, 2016 2:40 AM
  • Thank you. .

    But still awaiting for solutions. .

    Thursday, January 21, 2016 11:35 AM
  • All the Re arranged contents are below.. please review

    Code

    Imports System.Data.SqlClient
    Public Class Form1
        Dim cn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=E:\VS-2015\Projects\VB-2015\Customer\Customer\Database1.mdf;Integrated Security=True")
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            cmd.Connection = cn
            loadlistbox()
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If TxtId.Text <> "" And TxtName.Text <> "" And TxtPlace.Text <> "" And TxtWage.Text <> "" And TxtBonus.Text <> "" Then
                cn.Open()
                cmd.CommandText = "Insert into Info(CusId,CusName,CusPlace) values ('" & TxtId.Text & "','" & TxtName.Text & "', '" & TxtPlace.Text & "')"
                cmd.CommandText += ";Insert into Wages(CusWage,CusBonus) values ('" & TxtWage.Text & "', '" & TxtBonus.Text & "')"
                cmd.ExecuteNonQuery()
                cn.Close()
                TxtId.Text = ""
                TxtName.Text = ""
                TxtPlace.Text = ""
                TxtWage.Text = ""
                TxtBonus.Text = ""
                loadlistbox()
            End If
        End Sub
        Private Sub loadlistbox()
            ListBox1.Items.Clear()
            ListBox2.Items.Clear()
            ListBox3.Items.Clear()
            ListBox4.Items.Clear()
            ListBox5.Items.Clear()
            cn.Open()
            cmd.CommandText = "SELECT Info.CusId,Info.CusName,Info.CusPlace,Wages.CusWage,Wages.CusBonus FROM Info INNER JOIN Wages ON Wages.CusId = Info.CusId"
    
            dr = cmd.ExecuteReader()
            If dr.HasRows Then
                While (dr.Read())
                    ListBox1.Items.Add(dr("CusId"))
                    ListBox2.Items.Add(dr("CusName"))
                    ListBox3.Items.Add(dr("CusPlace"))
                    ListBox4.Items.Add(dr("CusWage"))
                    ListBox5.Items.Add(dr("CusBonus"))
    
                End While
            End If
            cn.Close()
        End Sub
        Private Sub ListBox1_MouseClick(sender As Object, e As MouseEventArgs) Handles ListBox1.MouseClick
            Dim lb As New ListBox
            lb = sender
            If lb.SelectedIndex <> -1 Then
                ListBox1.SelectedIndex = lb.SelectedIndex
                ListBox2.SelectedIndex = lb.SelectedIndex
                ListBox3.SelectedIndex = lb.SelectedIndex
                ListBox4.SelectedIndex = lb.SelectedIndex
                ListBox5.SelectedIndex = lb.SelectedIndex
            End If
        End Sub
    End Class
    
    

    Info table

    Wages Table

    Error for the above statements..

    So I just changed the cusId column in wages and did allow Nulls by checkng the checkbox..

    But when I run the program it remains Blank without any errors message.. it doesn't gives output in the List boxes...


    Thursday, January 21, 2016 12:41 PM
  • Seems that you do not insert any value to new CusId column of Wages. Adjust the second INSERT statement, i.e. insert the value similarly to CusId of Info table.

    Thursday, January 21, 2016 12:48 PM
  • Partially it works.. but it needs to input same data in Id other wise won't works... I given the 1 value to cusId in both the table. In Running mode it requires Id as 1 compulsory. .. otherwise it remains Blank. .. ID =1 ,NAME =R ,PLACE= T , WAGE =3 , BONUS =2, ListBoxes fills correctly. . If ID = 2 or 3 all the List Boxes remains Blank
    Thursday, January 21, 2016 3:16 PM
  • Hi Rakshithc,

    Base on your code snippet, you want to insert some data into two tables, but you re-assignment of the variable commandtext. The same issue as select. You could modify code snippet as below and check if it works for you. And it works fine on my side.

    Private Sub loadlistbox()
    
            ListBox1.Items.Clear()
    
            ListBox2.Items.Clear()
    
            ListBox3.Items.Clear()
    
            ListBox4.Items.Clear()
    
            ListBox5.Items.Clear()
    
            cn.Open()
    
            Dim sqlString = "Select CusId,CusName,CusPlace from Info;Select CusWage,CusBonus from Wages"
    
            cmd.CommandText = sqlString
    
            dr = cmd.ExecuteReader()
    
            If dr.HasRows Then
    
                While (dr.Read())
    
                    ListBox1.Items.Add(dr("CusId"))
    
                    ListBox2.Items.Add(dr("CusName"))
    
                    ListBox3.Items.Add(dr("CusPlace"))
    
                    ListBox4.Items.Add(dr("CusWage"))
    
                    ListBox5.Items.Add(dr("CusBonus"))
    
                End While
    
            End If
    
            cn.Close()
    
        End Sub
    
    
        Private Sub ReadSingleRow(ByVal record As IDataRecord)
    
            Console.WriteLine(String.Format("{0}, {1}", record(0), record(1)))
    
    
        End Sub
    
    
    
        Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            If TxtId.Text <> "" And TxtName.Text <> "" And TxtPlace.Text <> "" And TxtWage.Text <> "" And TxtBonus.Text <> "" Then
    
               cn.Open()
    
    
                Dim sqlString = "Insert into Info(CusId,CusName,CusPlace) values ('" & TxtId.Text & "','" & TxtName.Text & "','" & TxtPlace.Text & "')"
    
                sqlString += ";Insert into Wages(CusWage,CusBonus) values ('" & TxtWage.Text & "', '" & TxtBonus.Text & "')"
    
                cmd.CommandText = sqlString
    
                cmd.ExecuteNonQuery()
    
                cn.Close()
    
                TxtId.Text = ""
    
                TxtName.Text = ""
    
                TxtPlace.Text = ""
    
                TxtWage.Text = ""
    
                TxtBonus.Text = ""
    
                loadlistbox()
    
            End If
    
    
    End Sub
    

    Best regards,

    Cole Wu

    Friday, January 22, 2016 1:42 AM
    Moderator
  • Thank you for your guid. . Still error..

    Code is..

    Imports System.Data.SqlClient
    Public Class Form1
        Dim cn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\Customer\Customer\Database1.mdf;Integrated Security=True")
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            cmd.Connection = cn
            loadlistbox()
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If TxtId.Text <> "" And TxtName.Text <> "" And TxtPlace.Text <> "" And TxtWage.Text <> "" And TxtBonus.Text <> "" Then
                cn.Open()
                Dim sqlstring = "Insert into Info(CusId,CusName,CusPlace) values ('" & TxtId.Text & "','" & TxtName.Text & "', '" & TxtPlace.Text & "')"
    
                sqlstring += ";Insert into Wages(CusWage,CusBonus) values ('" & TxtWage.Text & "', '" & TxtBonus.Text & "')"
                cmd.CommandText = sqlstring
                cmd.ExecuteNonQuery()
                cn.Close()
                TxtId.Text = ""
                TxtName.Text = ""
                TxtPlace.Text = ""
                TxtWage.Text = ""
                TxtBonus.Text = ""
    
                loadlistbox()
            End If
        End Sub
        Private Sub loadlistbox()
            ListBox1.Items.Clear()
            ListBox2.Items.Clear()
            ListBox3.Items.Clear()
            ListBox4.Items.Clear()
            ListBox5.Items.Clear()
            cn.Open()
            Dim sqlstring = "SELECT CusId,CusName,CusPlace from Info; Select CusWage,CusBonus from wages"
            cmd.CommandText = sqlstring
            dr = cmd.ExecuteReader()
            If dr.HasRows Then
                While (dr.Read())
                    ListBox1.Items.Add(dr("CusId"))
                    ListBox2.Items.Add(dr("CusName"))
                    ListBox3.Items.Add(dr("CusPlace"))
                    ListBox4.Items.Add(dr("CusWage"))
                    ListBox5.Items.Add(dr("CusBonus"))
    
                End While
            End If
            cn.Close()
        End Sub
    
        Private Sub ReadSingleRow(ByVal record As IDataRecord)
            Console.WriteLine(String.Format("{0}, {1}", record(0), record(1)))
        End Sub
        Private Sub ListBox1_MouseClick(sender As Object, e As MouseEventArgs) Handles ListBox1.MouseClick
            Dim lb As New ListBox
            lb = sender
            If lb.SelectedIndex <> 1 Then
                ListBox1.SelectedIndex = lb.SelectedIndex
                ListBox2.SelectedIndex = lb.SelectedIndex
                ListBox3.SelectedIndex = lb.SelectedIndex
                ListBox4.SelectedIndex = lb.SelectedIndex
                ListBox4.SelectedIndex = lb.SelectedIndex
            End If
        End Sub
    End Class
    
    

    Run error

    I think it fails in selecting row from second table I.e Wages.

    I tested by changing 

    ListBox4.Items.Add(dr(1))

    ListBox5.Items.Add(dr (2))

    It accepts and when I run the program in Listbox 4 and ListBox 5 items were filled as same as data in the Name fied and Place field respectively. .

    Friday, January 22, 2016 3:07 PM
  • I suggest that you DataBind your ListBoxes instead of adding Items manually:

    ' define your DataTable variable
    Dim dtCustomers As new DataTable
    
    ...
    
    ' Setup the DataBinding for the ListBoxes and TextBoxes in the Form1_Load event handler. 
    ' This only needs to be done once, but you'll need the columns in the DataTable defined first
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cmd.Connection = cn
    
        dtCustomers.Columns.Add("CusId" GetType(Integer)) ' not sure if the VB syntax is correct
        dtCustomers.Columns.Add("CusWage" GetType(Integer))
        dtCustomers.Columns.Add("CusBonus" GetType(Integer))
        dtCustomers.Columns.Add("CusName") ' defaults to string if type not specified
        dtCustomers.Columns.Add("CusPlace")
    
        ' Then databind each ListBox 
        ' (you can probably get by without setting the ValueMember):
        ListBox1.DataSource = dtCustomers
        ListBox1.DisplayMember = "CusId"
        ListBox2.DataSource = dtCustomers
        ListBox2.DisplayMember = "CusWage"
        ' ... same for the other 3
    
        ' You'll also need to DataBind your TextBoxes
        TextBox1.DataBindings.Add("Text", dtCustomers, "CusId")
        TextBox2.DataBindings.Add("Text", dtCustomers, "CusName")
        ' ... same for the other 3
    
        ' NOW, you can call the loadlistbox() method
        loadlistbox()
    End Sub
    
    
    ' and now your loadlistbox() simply needs to re-fill the DataTable
    ' the databinding takes care of re-displaying the new data
    Private Sub loadlistbox()
        cn.Open
        cmd.CommandText = "SELECT Info.CusId ... etc.etc."
        dr = cmd.ExecuteReader()
        dtCustomers.Clear()
        dtCustomers.Load(dr)
        cn.Close()
    End Sub
    

    See if that makes things work a little better ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, January 22, 2016 4:31 PM
  • Wel thi data binding is not working. ... Error msg Items collection cannot be modified when the Data Source property is set
    Saturday, January 23, 2016 1:21 PM
  • That's because you're probably removing items directly from a ListBox ... you should be using its DataSource. Could you post the part of the code that's giving you an error?

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, January 23, 2016 2:31 PM
  • I used The same code and not removing items from list box.. wen application starts it immediately shows the above error msg..
    Monday, January 25, 2016 2:17 AM
  • OK, fine, but still ... could you please post some code? And show which line causes the error?

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, January 25, 2016 2:20 AM
  • Imports System.Data.SqlClient
    Public Class Form1
        Dim cn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=E:\VS-2015\Projects\VB-2015\Customer\Customer\Database1.mdf;Integrated Security=True")
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
        Dim dtcustomers As New DataTable
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            cmd.Connection = cn
            dtcustomers.Columns.Add("CusId", GetType(Integer))
            dtcustomers.Columns.Add("CusName", GetType(String))
            dtcustomers.Columns.Add("CusPlace", GetType(String))
            dtcustomers.Columns.Add("CusWage", GetType(String))
            dtcustomers.Columns.Add("CusBonus", GetType(String))
    
            ListBox1.DataSource = dtcustomers
            ListBox1.DisplayMember = "CusId"
            ListBox2.DataSource = dtcustomers
            ListBox2.DisplayMember = "CusName"
            ListBox3.DataSource = dtcustomers
            ListBox3.DisplayMember = "CusPlace"
            ListBox4.DataSource = dtcustomers
            ListBox4.DisplayMember = "CusWage"
            ListBox5.DataSource = dtcustomers
            ListBox5.DisplayMember = "CusBonus"
    
            TxtId.DataBindings.Add("Text", dtcustomers, "CusId")
            TxtName.DataBindings.Add("Text", dtcustomers, "CusName")
            TxtPlace.DataBindings.Add("Text", dtcustomers, "CusPlace")
            TxtWage.DataBindings.Add("Text", dtcustomers, "CusWage")
            TxtBonus.DataBindings.Add("Text", dtcustomers, "CusBonus")
    
            loadlistbox()
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If TxtId.Text <> "" And TxtName.Text <> "" And TxtPlace.Text <> "" And TxtWage.Text <> "" And TxtBonus.Text <> "" Then
                cn.Open()
                cmd.CommandText = "Insert into Info(CusId,CusName,CusPlace) values ('" & TxtId.Text & "','" & TxtName.Text & "', '" & TxtPlace.Text & "')"
                cmd.CommandText += ";Insert into Wages(CusWage,CusBonus) values ('" & TxtWage.Text & "', '" & TxtBonus.Text & "')"
                cmd.ExecuteNonQuery()
                cn.Close()
                TxtId.Text = ""
                TxtName.Text = ""
                TxtPlace.Text = ""
                TxtWage.Text = ""
                TxtBonus.Text = ""
                loadlistbox()
            End If
        End Sub
        Private Sub loadlistbox()
            ListBox1.Items.Clear()
            ListBox2.Items.Clear()
            ListBox3.Items.Clear()
            ListBox4.Items.Clear()
            ListBox5.Items.Clear()
            cn.Open()
            cmd.CommandText = "SELECT Info.CusId,Info.CusName,Info.CusPlace,Wages.CusWage,Wages.CusBonus FROM Info INNER JOIN Wages ON Wages.CusId = Info.CusId"
    
            dr = cmd.ExecuteReader()
            If dr.HasRows Then
                While (dr.Read())
                    ListBox1.Items.Add(dr("CusId"))
                    ListBox2.Items.Add(dr("CusName"))
                    ListBox3.Items.Add(dr("CusPlace"))
                    ListBox4.Items.Add(dr("CusWage"))
                    ListBox5.Items.Add(dr("CusBonus"))
    
                End While
            End If
            cn.Close()
        End Sub
        Private Sub ListBox1_MouseClick(sender As Object, e As MouseEventArgs) Handles ListBox1.MouseClick
            Dim lb As New ListBox
            lb = sender
            If lb.SelectedIndex <> -1 Then
                ListBox1.SelectedIndex = lb.SelectedIndex
                ListBox2.SelectedIndex = lb.SelectedIndex
                ListBox3.SelectedIndex = lb.SelectedIndex
                ListBox4.SelectedIndex = lb.SelectedIndex
                ListBox5.SelectedIndex = lb.SelectedIndex
            End If
        End Sub
    End Class
    
    
    When I click  the start .. it shows the abpve error message. ..
    Monday, January 25, 2016 4:53 PM
  • Hi Rakshithc,

    According to your code snippet, it seems that it has some problems. I modify your code as below, and it works fine on my side.

    Imports System.Data.SqlClient
    
    
    Public Class Form1
    
    
        Dim cn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=E:\VS-2015\Projects\VB-2015\Customer\Customer\Database1.mdf;Integrated Security=True")
    
        Dim cmd As New SqlCommand
    
        Dim dr As SqlDataReader
    
        Dim dtcustomers As New DataTable
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            cmd.Connection = cn
    
            loadlistbox()
    
        End Sub
    
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            If TxtId.Text <> "" And TxtName.Text <> "" And TxtPlace.Text <> "" And TxtWage.Text <> "" And TxtBonus.Text <> "" Then
    
                cn.Open()
    
                cmd.CommandText = "Insert into Info(CusId,CusName,CusPlace) values ('" & TxtId.Text & "','" & TxtName.Text & "', '" & TxtPlace.Text & "')"
    
                cmd.CommandText += ";Insert into Wages(CusId,CusWage,CusBonus) values ('" & TxtId.Text & "','" & TxtWage.Text & "', '" & TxtBonus.Text & "')"
    
                cmd.ExecuteNonQuery()
    
                cn.Close()
    
                TxtId.Text = ""
    
                TxtName.Text = ""
    
                TxtPlace.Text = ""
    
                TxtWage.Text = ""
    
                TxtBonus.Text = ""
    
                loadlistbox()
    
            End If
    
        End Sub
    
    
        Private Sub loadlistbox()
    
            dtcustomers = New DataTable()
    
    
            ListBox1.Refresh()
    
            ListBox2.Refresh()
    
            ListBox3.Refresh()
    
            ListBox4.Refresh()
    
            ListBox5.Refresh()
    
    
            cn.Open()
    
            Try
    
                Dim sqlString = "SELECT Info.CusId,Info.CusName,Info.CusPlace,Wages.CusWage,Wages.CusBonus FROM Info INNER JOIN Wages ON Wages.CusId = Info.CusId"
    
                Dim sda As SqlDataAdapter = New SqlDataAdapter(sqlString, cn)
    
                sda.Fill(dtcustomers)
    
                ListBox1.DataSource = dtcustomers
    
                ListBox1.DisplayMember = "CusId"
    
                ListBox2.DataSource = dtcustomers
    
                ListBox2.DisplayMember = "CusName"
    
                ListBox3.DataSource = dtcustomers
    
                ListBox3.DisplayMember = "CusPlace"
    
                ListBox4.DataSource = dtcustomers
    
                ListBox4.DisplayMember = "CusWage"
    
                ListBox5.DataSource = dtcustomers
    
                ListBox5.DisplayMember = "CusBonus"
    
    
            Catch ex As Exception
    
    
            End Try
    
            cn.Close()
    
        End Sub
    
    
    
        Private Sub ReadSingleRow(ByVal record As IDataRecord)
    
            Console.WriteLine(String.Format("{0}, {1}", record(0), record(1)))
    
        End Sub
    
    
    
    End Class
    

    I hope it’s helpful to you.

    Best regards,

    Cole Wu

    • Marked as answer by Rakshithc Friday, January 29, 2016 2:27 AM
    Tuesday, January 26, 2016 7:13 AM
    Moderator
  • Thank you so much sirr.. you are really an amazing Engineer... it works works. ...it works..!!, Still I need your help in dataset which I posted in other forum...
    Tuesday, January 26, 2016 1:33 PM
  • But I have one doubt can it be updated and deleted?
    So I made one test by copying All the text boxes in rirgt side named as UpdatId UpdateName. Updateplace .UpdateWage . UpdateBonus..

    And wrote code in mouse click.

    ListBox1.SelectedIndex=lb.SelectedIndex
    ....
    ...
    ...
    UpdateId= ListBox1.selectedItem
    UpdateName= ListBox2.SelectedItem
    Updateplace=Listbox4.selectedItem
    .....
    .....
    ..

    In start mode ..it work welll for insert method..

    For update it shows an error message like

    On click on ID list items.

    " Conversation from type DataRowView to type String is not valid. 

    Any sollution?

    Tuesday, January 26, 2016 2:25 PM
  • I think that you're still missing the point about the use of DataBinding. Your TextBoxes are all databound to the same DataSource as the ListBoxes. If you Select an Item in a ListBox, the corresponding TextBox will show the same data and consequently the DataSet/DataTable will also have the correct data. You shouldn't have to do anything with ListBox.SelectedItem (which will always be DataRowView).

    However, I think that maybe we don't (or at least *I* don't) actually know what you're trying to accomplish ... so the way that we've suggested to databind your controls may not be right for what you're trying to do. Maybe you can elaborate on the purpose of the ListBoxes (and how this all relates to your other post, https://social.msdn.microsoft.com/Forums/en-US/08f64b4c-5ba9-4589-b717-891c658729a0/dataset-bug?forum=winformsdatacontrols,  where you're using a DataGridView instead of ListBoxes).


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, January 26, 2016 4:48 PM
  • Ok..Insert working I will check update command. .

    I Just posted another problem with vb database. I passed add save but failed in delete command. Please see..

    https://social.msdn.microsoft.com/Forums/en-US/a6f72919-5af4-4dd6-b7e6-3701f332f475/database-delete-command-error?forum=winformsdatacontrols

    Thursday, January 28, 2016 4:17 PM