Insert Values Into Database using ListBox

Question

I have the following code. My problem is I can't insert a record in my table and when I run the code there is no error. What is wrong with the code?

    Dim Conn As SqlConnection
    Dim cmd As SqlCommand
    Dim ConString As String


    ConString = "SERVER=MAXPAYNE-PC\DEVELOPER;DATABASE=sample;User=sa;Pwd=bwd"
    Conn = New SqlConnection(ConString)
    Try
        Conn.Open()
        For J As Integer = 0 To ListBox3.Items.Count - 1
            cmd = New SqlCommand("INSERT INTO players (name) VALUES (" & ListBox3.Items(J).ToString & ")")
            cmd.ExecuteNonQuery()
        Next
        Conn.Close()
    Catch ex As Exception
    End Try

How do you know there's no error? You have an empty `Catch` block so you are simply ignoring any exception that might be thrown. Put some code in that `Catch` block that will actually notify you if an exception is thrown and then you can actually say whether an error occurs or not. If you run the code receive no notification under those circumstances, THEN you can say that no error occurs. — jmcilhinney, Dec 10 '14 at 08:11

score 2 · Accepted Answer · edited May 23 '17 at 12:27

"when I run the code there is no error" there's no error because you have an empty catch. Why are empty catch blocks a bad idea?

The error is that the command has no connection assigned. This should work:

cmd = New SqlCommand("INSERT INTO players (name) VALUES (" & ListBox3.Items(J).ToString & ")")
cmd.Connection = Conn

You should also get familiar with the Using-statement and use that on all objects that implement IDisposable like SqlConnection which ensures that it gets dispoed/closed always(even on error).

Last but not least: always use SQL-Parameters instead of string concatenation to prevent sql-injection:

Using conn As New SqlConnection("SERVER=MAXPAYNE-PC\DEVELOPER;DATABASE=sample;User=sa;Pwd=bwd")
    Dim sql = "INSERT INTO players (name) VALUES (@name)"
    Using cmd As New SqlCommand(sql, conn)
        Dim nameParameter = New SqlParameter("@name", SqlDbType.NVarChar)
        cmd.Parameters.Add(nameParameter)
        conn.Open()
        For Each name As String In ListBox3.Items
            cmd.Parameters("@name").Value = name
            Try
                Dim inserted As Int32 = cmd.ExecuteNonQuery()
            Catch ex As Exception
                ' do something meaningful here (f.e. logging or at least output the error) '
                ' empty catches are evil in most cases since they conceal problems from you but not from the users '
                Throw
            End Try
        Next
    End Using
End Using ' conn.Close not needed due to the Using '

@user3916571: you're welcome. Note that i have edited my answer to provide an example. — Tim Schmelter, Dec 10 '14 at 08:20

Insert Values Into Database using ListBox

1 Answers1