Problems with inserting to the database (asp.net, vb.net)

Question

I have a problem with inserting the value of the dropdownlist in my mySql database. It says "There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement."

When I choose from one dropdownlist the value 2 and the second dropdownlist also the same number then it inserts without a problem. but when the values are different (not equal to each other) it gives me that problem.

Imports System.Data
Imports System.Data.SqlClient

Partial Class Bevestiging
Inherits System.Web.UI.Page
Dim con As New SqlConnection(" server=BOYAN\SQLEXPRESS; Initial Catalog=GipDatabase; User ID=sa; Password=DitIs1SuperGoedW8woord!")
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

LblFilmnaam.Text = Session("filmnaam")
LblDatum.Text = Session("datum")
Lbltijd.Text = Session("tijd")
LblAantalKin.Text = Session("Aantalkin")
LblAantalVol.Text = Session("AantalVol")
LblTypeZaal.Text = Session("Zaaltype")
LblPrijs.Text = Session("prijs")
End Sub

Protected Sub BtnBevestigen_Click(sender As Object, e As EventArgs) Handles  BtnBevestigen.Click
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
con.Open()
cmd.CommandText = "insert into TblReserveren(Filmnaam,datum, tijd, Aantalvolwassenen, Aantalkinderen, TypeZaal, Prijs, GebruikerID) Values('" + LblFilmnaam.Text.ToString + "','" + LblDatum.Text + "','" + Lbltijd.Text + "','" + LblAantalVol.Text + "','" + LblAantalKin.Text + "', '" + LblTypeZaal.Text + "', " + LblPrijs.Text + " ,  (Select ID from TblGebruiker Where Username = '" + Session("Username") + "'))"

cmd.ExecuteNonQuery()
'MessageBox("De film Is gereserveerd!")
'Response.Write("De film Is gereserveerd!")
con.Close()
End Sub
End Class

First, you should enable [Option Strict On](https://msdn.microsoft.com/en-us/library/zcd4xwzs.aspx) and correct any problems it points out for you. Then, you should look up how to use *SQL parameters* and use them for the query - it will be more secure against SQL injection attacks and it will work for data with apostrophes in them. — Andrew Morton, May 27 '16 at 18:48
You should really bind your variables instead of generating dynamic SQL. Also, are you maybe using decimal numbers and comma as the decimal separator? — James Z, May 27 '16 at 18:49

score 0 · Accepted Answer · edited May 23 '17 at 11:52

You should use SQL parameters for the query. It makes it easier to write, avoids problems with characters like apostrophes in data, helps to prevent SQL injection attacks, and can improve performance (this is not always a concern) by allowing SQL Server to re-use execution plans.

Also, using the Using construct makes sure that unmanaged resources (What is meant by “managed” vs “unmanaged” resources in .NET?) are disposed of correctly for you (if a class has a .Dispose() method then you should use Using or call .Dispose() on it).

So, your code could look like:

Using con As New SqlConnection("server=BOYAN\SQLEXPRESS; Initial Catalog=GipDatabase; Integrated Security=true;")
    Dim sql = "INSERT INTO TblReserveren(Filmnaam,datum, tijd, Aantalvolwassenen, Aantalkinderen, TypeZaal, Prijs, GebruikerID) Values(@Filmnaam, @datum, @tijd, @Aantalvolwassenen, @Aantalkinderen, @TypeZaal, @prijs, (SELECT ID FROM TblGebruiker WHERE Username = @Username))"
    Using cmd As New SqlCommand(sql, con)

        'TODO: Set the .SqlDbType and .Size to match the columns in the database. '
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Filmnaam", .SqlDbType = SqlDbType.NVarChar, .Size = 100, .Value = CStr(Session("filmnaam"))})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@datum", .SqlDbType = SqlDbType.DateTime, .Value = CDate(Session("datum"))})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@tijd", .SqlDbType = SqlDbType.NVarChar, .Size = 100, .Value = CStr(Session("tijd"))})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Aantalvolwassenen", .SqlDbType = SqlDbType.NVarChar, .Size = 100, .Value = CStr(Session("AantalVol"))})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Aantalkinderen", .SqlDbType = SqlDbType.NVarChar, .Size = 100, .Value = CStr(Session("Aantalkin"))})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@TypeZaal", .SqlDbType = SqlDbType.NVarChar, .Size = 100, .Value = CStr(Session("Zaaltype"))})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@prijs", .SqlDbType = SqlDbType.Decimal, .Value = CDec(Session("prijs"))})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Username", .SqlDbType = SqlDbType.NVarChar, .Size = 100, .Value = CStr(Session("Username"))})

        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Using
End Using

I assumed that "datum" refers to a date, and that "prijs" is a price. Prices should generally be stored as Decimal values.

As you appear to have the data in Session variables, I used those instead of retrieving data from textboxes - you want to minimise the chances which a malicious user has to change the data in an attempt to attack your website.

I have no knowledge of what the column types are defined as are in your database, so please adjust the parameters to match the database definitions.

Finally, you should never use the "sa" login for web-related work. I suggest using the "Integrated Security = true" setting. You may need to grant some permissions in SQL Server to the identity of the application pool used by your application.

Problems with inserting to the database (asp.net, vb.net)

1 Answers1