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
– jmcilhinney, Commented Dec 10, 2014 at 8:11

Community · Accepted Answer · 2017-05-23 12:27:18Z

2

"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 '

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Dec 10, 2014 at 8:12

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3916571 Over a year ago

I am just new in programming mate so please be patient

Tim Schmelter Over a year ago

@user3916571: you're welcome. Note that i have edited my answer to provide an example.

Collectives™ on Stack Overflow

Insert Values Into Database using ListBox

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related