Click here to Skip to main content
15,075,944 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
For a listbox loading txt file and now I want to send the contents to sql database in which there are two tables m.in user, comm
http://obrazki.elektroda.pl/8069892000_1409841532.png
How to do to add all items were recorded in the table at the same time with id user?
ex. http://obrazki.elektroda.pl/5048367700_1409841766.png


The following method works only when you select the item's.


Dim con As New MySqlConnection
         Dim cmd As New MySqlCommand
         Try
             con.ConnectionString = "Server = x; User ID = x; Password = x; Database = x;"
             con.Open ()
             cmd.Connection = con
             cmd.CommandText = "insert into ycomm (user, comm) values ​​('" & ListView2.Items (0) .SubItems (0) .Text & "', '" & ListBox1.SelectedItem & "')"
             cmd.ExecuteNonQuery ()
 
         Catch ex As Exception
 
         Finally
             con.Close ()
         End Try
         Call showData () 
Posted

You need to iterate through your list box items. Please note that using an unparameterized query opens your code to SQL injection attacks and issues if any of your text values include and apostrophe.

Replace:

cmd.CommandText = "insert into ycomm (user, comm) values ​​('" _
& ListView2.Items(0).SubItems(0).Text & "', '" & ListBox1.SelectedItem & "')"


With

VB
cmd.CommandText = "INSERT INTO ycomm (user, comm) VALUES(@user, @comm)"
cmd.Parameters.AddWithValue("@user", ListView2.Items(0).SubItems(0).Text)
cmd.Parameters.Add("@comm", SqlDbType.VarChar)

For i As Integer = 0 To lst.Items.Count
    cmd.Parameters("@comm").Value = lst.Items(i).Text
    cmd.ExecuteNonQuery()
Next


Please note that I don't know your database schema, so there may be primary key and data size issues which you would have to solve.
   
Comments
Łuki Gra 4-Sep-14 12:42pm
   
is a problem and does not work http://obrazki.elektroda.pl/9304125400_1409848834.png
i made a mistake?
PhilLenoir 4-Sep-14 14:14pm
   
From your screenshot I can see that you are using a class "MySQLCommand" as opposed to "SQLCommand", so I can't be certain of what changes you may have made.

What error is the compiler giving you? What is the column type in your schema?

To be safe, uou need to match the column type with the parameter type. Is it a char, nchar, nvarchar?

I have this code working using a SQLCommand object (with my own field names, of course), with no compile or run time errors.
PhilLenoir 4-Sep-14 15:34pm
   
OK, I see the issues:
1) You're using MySQL, not SQL Server, so the members are probably somewhat different
2) We may have different version of VS anyway

If you add the parameter with value (similar to the first parameter, you should still be able to change its value in the loop. I don't have MySQL or the libraries, so I can't test it thoroughly and the on-line documentation I had time to look at seems sketchy.
Łuki Gra 4-Sep-14 15:45pm
   
Ok thanks
PhilLenoir 4-Sep-14 15:46pm
   
Did you try the add with value, you should be able to use a blank string.
i did so


Dim con As New MySqlConnection
        Dim cmd As New MySqlCommand
        Try
            con.ConnectionString = "Server=mysql.x;User ID=x;Password=xDatabase=x;"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(0) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(1) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(2) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(3) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(4) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(5) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(6) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(7) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(8) & "')"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "insert into ycomm (IDvid,user,comm) values('" & lvDisplay.Items(0).SubItems(0).Text & "','" & ListView2.Items(0).SubItems(0).Text & "','" & ListBox1.Items(9) & "')"
            cmd.ExecuteNonQuery()
        Catch ex As Exception

        Finally
            con.Close()
        End Try
        Call showData()
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900