CODEWITHSUNDEEP
sqlvb.net
Agamemnon
Agamemnon

Reputation: 607

Using variables to create SQL parameters

I have a loop that takes values from textboxes and dropdowns and uses them to update a an SQL table. I need to be able to rename the parameters and give them values based on the variable that the loop counts to. Example: if loop goes from 0 to 2 then textname_0 will go to @paramname_0 and textname_1 goes to @paramname_1 and so on.

I have written the code below but it generates an error which intimates the first parameter can't be found so the syntax is clearly wrong.

    Protected Sub Insert_To_Tables()

    Try


        Dim con As SqlConnection = New SqlConnection()
        Dim connectionString As String = "Data Source=wolf;Initial Catalog=Seat_Planner_2013;Integrated Security=True;Integrated Security=True"
        Using cn As New SqlConnection(connectionString)

            For i As Int32 = 0 To number_of_tickets_Ddl.SelectedValue

                cn.Open()
                Dim cmd As New SqlCommand()
                cmd.CommandText = "INSERT INTO tables ([table_num], [seat], [available], [ticket_num], [seat_title], [seat_firstname], [seat_surname], [booking_ref],[booked_by]) VALUES (@table_num_ & i, @seat_ & i, @available_ & i, @ticket_num_ & i, @seat_title_ & i, @seat_firstname_ & i, @seat_surname_ & i, @booking_ref, @booked_by) WHERE ([table_num] = @table_num) AND ([seat] = @seat_ & i)"

                cmd.Parameters.Add("@table_num", SqlDbType.SmallInt).Value = ddltable.SelectedValue
                cmd.Parameters.Add("@seat_" & i, SqlDbType.NChar).Value = CType(Me.FindControl(("DDLSeat_") & i), DropDownList).SelectedValue
                cmd.Parameters.Add("@available", SqlDbType.NChar).Value = "No"
                cmd.Parameters.Add("@ticket_num_" & i, SqlDbType.NChar).Value = CType(Me.FindControl(("TicketNo_TextBox_") & i), TextBox).Text.Trim
                cmd.Parameters.Add("@seat_title_" & i, SqlDbType.NChar).Value = CType(Me.FindControl(("Title_TextBox_") & i), TextBox).Text.Trim
                cmd.Parameters.Add("@seat_firstname_" & i, SqlDbType.NChar).Value = CType(Me.FindControl(("FirstName_TextBox_") & i), TextBox).Text.Trim
                cmd.Parameters.Add("@seat_surname_" & i, SqlDbType.NChar).Value = CType(Me.FindControl(("Surname_TextBox_") & i), TextBox).Text.Trim
                cmd.Parameters.Add("@booking_ref", SqlDbType.NChar).Value = booking_ref_LBL.Text
                cmd.Parameters.Add("@booked_by", SqlDbType.NChar).Value = CType(Me.FindControl(("TBoxFull_name_0")), TextBox).Text.Trim

                cmd.Connection = cn
                cmd.ExecuteNonQuery()
                cn.Close()

            Next

        End Using

        DB_error1.Text = " Insert to tables success. "

    Catch ex As Exception

        DB_error1.Text = "Fail "
        DB_error1.Text = DB_error1.Text + ex.Message.ToString
    Finally

    End Try

Upvotes: 0

Views: 2790

Answers (2)

SmT
SmT

Reputation: 335

  1. Check your cmd.CommandText, You should take the variable i to out of quotes.

  2. You also set the parameter name to @table_num but you are tying to use it like @table_num_1

    cmd.Parameters.Add("@table_num", SqlDbType.SmallInt).Value = ddltable.SelectedValue

    cmd.CommandText = ...VALUES (@table_num_ & i, ...

    Same with the @available parameter

You see the parameter names will not be same, so when you fix this two issues, other parts seem OK.

Upvotes: 0

Oded
Oded

Reputation: 499382

The SQL doesn't have dynamic parameters names - they are fixed. For example - you have a @table_num parameter in the SQL query (CommandText). Additionally, you have & i inside the string, making this invalid SQL.

Just use (@table_num, @seat, @available, ...

The added parameters must match these names, so concatenating is the wrong thing to do either in the SQL or when adding the parameters.

Just drop the concatenation and things should work as you expect.

For efficiency, I suggest you read up on table valued parameters.

Upvotes: 1

Related Questions