An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Dat-CodePudding

If BNameTb.Text = "" Or BAuthorTb.Text = "" Or BQtyTb.Text = "" Or BPriceTb.Text = "" Or BPublisherTb.Text = "" Then
            MsgBox("Missing Information")
        Else
            Con.Open()
            Dim query = "Insert into BookTbl Value ('" & BNameTb.Text & "','" & BAuthorTb.Text & "','" & BPublisherTb.Text & "','" & BPriceTb.Text & "','" & BQtyTb.Text & "',)"
            Dim cmd As SqlCommand
            cmd = New SqlCommand(query, Con)
            cmd.ExecuteNonQuery()
            MsgBox("Book Saved")
            Con.Close()
            Displaybook()
            Reset()
        End If
    End Sub
If BNameTb.Text = "" Or BAuthorTb.Text = "" Or BQtyTb.Text = "" Or BPriceTb.Text = "" Or BPublisherTb.Text = "" Then
            MsgBox("Missing Information")
        Else
            Con.Open()
            Dim query = "Insert into BookTbl Value ('" & BNameTb.Text & "','" & BAuthorTb.Text & "','" & BPublisherTb.Text & "','" & BPriceTb.Text & "','" & BQtyTb.Text & "',)"
            Dim cmd As SqlCommand
            cmd = New SqlCommand(query, Con)
            cmd.ExecuteNonQuery()
            MsgBox("Book Saved")
            Con.Close()
            Displaybook()
            Reset()
        End If
    End Sub

CodePudding user response：

i think you need "values", not "value"

CodePudding user response：

The SQL string needs to have VALUES (plural), not VALUE (singular).

Once you fix that, you'll see a new error (still an SqlException) because of this part at the end of the queries:

& BQtyTb.Text & "',)"

The trailing comma at the end is not valid.

After you fix that, if Price and Qty are defined as numeric fields you'll also find the query fails because you cannot enclose numeric fields in quotes like you do text fields. You'll need to remove those single quotes.

A bonus issue that won't actually break the code here but is still not quite correct is the connection is not closed properly. In the case of an exception, the connection would be left hanging open. Do this often enough, and you can lock yourself out of your database completely, until some connections time out. Each query should have its own connection object (do NOT reuse the same connection throughout your app) and that connection should be enclosed with a Using block, to ensure it will disposed properly when finished.

Last but definitely not least, this is crazy-vulnerable to SQL injection issues. It is NEVER okay to use string concatenation to put data in an SQL statement like this. This is one of those things that's too important to do wrong even for practice/learning/proof-of-concept projects. Do a quick Google search for "parameterized queries vb.net". There are hundreds of examples on the web that will show you how to do this right, and it will improve performance as a bonus.