VB Net - How to send the entire contents of a listbox to SQL database?

Question

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 4-Sep-14 4:59am

Łuki Gra

Add a Solution

2 solutions

Solution 1

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.

Posted 4-Sep-14 6:18am

PhilLenoir

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Łuki Gra · Accepted Answer · 2014-09-05T01:48:00

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()