Question Spanning an insert statement on multiple line

O

Obeng

Member
Joined
Apr 12, 2012
Messages
5
Programming Experience
1-3
Can anyone help me with how to use the line continuation with an INSERT Command please. This is my code:
objCommand.CommandText = "INSERT INTO CollectionForm" & _
"(Region,District,Circuit,AcademicYear,Term,NameOfSchool,SchoolCode,Stream,Shift,DateOf,OpeningDays,InstructionalDays,EnrolKg1Boys)" & _
"(EnrolKg1Girls,TotalKg1Enrol,EnrolKg2Boys,EnrolKg2Girls,TotalKg2Enrol,EnrolP1Boys,EnrolP1Girls,TotalP1Enrol,EnrolP2Boys)" & _
"(EnrolP2Girls,TotalP2Enrol,EnrolP3Boys,EnrolP3Girls,TotalP3Enrol,EnrolP4Boys,EnrolP4Girls,TotalP4Enrol,EnrolP5Boys,EnrolP5Girls)" & _
"(TotalP5Enrol,EnrolP6Boys,EnrolP6Girls,TotalP6Enrol,EnrolJHS1Boys,EnrolJHS1Girls,TotalJHS1,EnrolJHS2Boys,EnrolJHS2Girls)" & _
"(TotalJHS2,EnrolJHS3Boys,EnrolJHS3Girls,TotalJHS3)" & _
"VALUES(@Region,@District,@Circuit,@AcademicYear,@term,@nameOf,@SchoolCode,@Stream,@shift,@Date,@OpeningDays,@InstructionalDays)" & _
"(@Kg1B,@Kg1G,@TotalKg1,@kg2b,@kg2g,@totalkg2,@p1b,@p1g,@totalp1,@p2b,@p2g,@totalp2,@p3b,@p3g,@totalp3,@p4b,@p4g,@totalp4,@p5b,@p5g,)" & _
"(@totalp5,@p6b,@p6g,@totalp6,@jhs1b,@jhs1g,@totaljhs1,@jhs2b,@jhs2g,@totaljhs2,@jhs3b,@jhs3g,@totaljhs3)"

'Inserts Data Parameters
objCommand.Parameters.AddWithValue("@Region", TextBox1.Text)
objCommand.Parameters.AddWithValue("@District", TextBox2.Text)
objCommand.Parameters.AddWithValue("@Circuit", CircuitTextbox.Text)
objCommand.Parameters.AddWithValue("@AcademicYear", ComboBox1.Text)
objCommand.Parameters.AddWithValue("@Term", TextBox48.Text)
objCommand.Parameters.AddWithValue("@nameOf", TextBox3.Text)
objCommand.Parameters.AddWithValue("@SchoolCode", TextBox4.Text)
objCommand.Parameters.AddWithValue("@Stream", TextBox50.Text)
objCommand.Parameters.AddWithValue("@Shift", TextBox49.Text)
objCommand.Parameters.AddWithValue("@Date", DateTimePicker1.Value.Date)
objCommand.Parameters.AddWithValue("@OpeningDays", TextBox51.Text)
objCommand.Parameters.AddWithValue("@InstructionalDays", TextBox52.Text)
objCommand.Parameters.AddWithValue("@Kg1b", TextBox5.Text)
objCommand.Parameters.AddWithValue("@Kg1g", TextBox28.Text)
objCommand.Parameters.AddWithValue("@totalkg1", TextBox40.Text)
objCommand.Parameters.AddWithValue("@Kg2b", TextBox6.Text)
objCommand.Parameters.AddWithValue("@Kg2g", TextBox27.Text)
objCommand.Parameters.AddWithValue("@TotalKg2", TextBox39.Text)
objCommand.Parameters.AddWithValue("@P1b", TextBox7.Text)
objCommand.Parameters.AddWithValue("@P1g", TextBox26.Text)
objCommand.Parameters.AddWithValue("@TotalP1", TextBox38.Text)
objCommand.Parameters.AddWithValue("@P2b", TextBox8.Text)
objCommand.Parameters.AddWithValue("@P2g", TextBox25.Text)
objCommand.Parameters.AddWithValue("@TotalP2", TextBox37.Text)
objCommand.Parameters.AddWithValue("@P3b", TextBox9.Text)
objCommand.Parameters.AddWithValue("@P3g", TextBox24.Text)
objCommand.Parameters.AddWithValue("@TotalP3", TextBox36.Text)
objCommand.Parameters.AddWithValue("@P4b", TextBox10.Text)
objCommand.Parameters.AddWithValue("@P4g", TextBox23.Text)
objCommand.Parameters.AddWithValue("@TotalP4", TextBox35.Text)
objCommand.Parameters.AddWithValue("@P5b", TextBox11.Text)
objCommand.Parameters.AddWithValue("@P5g", TextBox22.Text)
objCommand.Parameters.AddWithValue("@TotalP5", TextBox34.Text)
objCommand.Parameters.AddWithValue("@P6b", TextBox12.Text)
objCommand.Parameters.AddWithValue("@P6g", TextBox21.Text)
objCommand.Parameters.AddWithValue("@TotalP6", TextBox33.Text)
objCommand.Parameters.AddWithValue("@JHS1b", TextBox13.Text)
objCommand.Parameters.AddWithValue("@JHS1g", TextBox20.Text)
objCommand.Parameters.AddWithValue("@TotalJHS1", TextBox32.Text)
objCommand.Parameters.AddWithValue("@jhs2b", TextBox14.Text)
objCommand.Parameters.AddWithValue("@jhs2g", TextBox19.Text)
objCommand.Parameters.AddWithValue("@totaljhs2", TextBox31.Text)
objCommand.Parameters.AddWithValue("@jhs3b", TextBox15.Text)
objCommand.Parameters.AddWithValue("@jhs3g", TextBox18.Text)
objCommand.Parameters.AddWithValue("@totaljhs3", TextBox30.Text)

objConnection.Open()
objCommand.ExecuteNonQuery()
FillDatagrid()
 
Sort by date Sort by votes
Those are just strings that are added together, it has no meaning until the string is complete, so your objective is to construct that single string as it should look when complete. Obviously you must create a string that represent a valid sql INSERT statement. You can look at the CommandText property in debugger after it is assigned and see if the string looks right to you. You can also put a breakpoint and enter ?objCommand.CommandText in Immediate Window to have it evaluate that expression.
Here's an example of how such a string might look like:
INSERT INTO table_name (column1, column2, column3) VALUES (@value1, @value2, @value3)
Click to expand...
As such I'd say your current string has way to many paratheses and is lacking commas.
 
Upvote 0 Downvote
You have to be careful with spaces. I normally just write it on one long line, then paste

" & _
"

Where appropriate, indenting as I go.

objCommand.CommandText = "INSERT INTO CollectionForm ( Region, District, Circuit, AcademicYear, Term, NameOfSchool, SchoolCode, Stream, Shift," & _
                                                     " DateOf, OpeningDays, InstructionalDays, EnrolKg1Boys, EnrolKg1Girls, TotalKg1Enrol," & _
                                                     " EnrolKg2Boys, EnrolKg2Girls, TotalKg2Enrol, EnrolP1Boys, EnrolP1Girls, TotalP1Enrol," & _
                                                     " EnrolP2Boys, EnrolP2Girls, TotalP2Enrol, EnrolP3Boys, EnrolP3Girls, TotalP3Enrol, EnrolP4Boys," & _
                                                     " EnrolP4Girls, TotalP4Enrol, EnrolP5Boys, EnrolP5Girls, TotalP5Enrol, EnrolP6Boys, EnrolP6Girls," & _
                                                     " TotalP6Enrol, EnrolJHS1Boys, EnrolJHS1Girls, TotalJHS1, EnrolJHS2Boys, EnrolJHS2Girls, TotalJHS2," & _
                                                     " EnrolJHS3Boys, EnrolJHS3Girls, TotalJHS3 ) " & _
                                             "VALUES ( @Region, @District, @Circuit, @AcademicYear, @term, @nameOf, @SchoolCode, @Stream, @shift," & _
                                                     " @Date,@Ope ningDays,@InstructionalDays, @Kg1B, @Kg1G, @TotalKg1, @kg2b, @kg2g, @totalkg2," & _
                                                     " @p1b, @p1g, @totalp1, @p2b, @p2g, @totalp2, @p3b, @p3g, @totalp3, @p4b, @p4g, @totalp4, @p5b," & _
                                                     " @p5g, @totalp5, @p6b, @p6g, @totalp6, @jhs1b, @jhs1g, @totaljhs1, @jhs2b, @jhs2g, @totaljhs2," & _
                                                     " @jhs3b, @jhs3g, @totaljhs3 )"


Don't know where all the extra parenthesis came from, but you're safe now they are all gone. Personally since the whole query is static I would just store it as a resource and be done with it. I would never have to see it again in my code.

As for the second part, there's a great little idiom called With in VB, use it!

' Inserts Data Parameters
With objCommand.Parameters
    .AddWithValue("@Region", TextBox1.Text) : .AddWithValue("@District", TextBox2.Text) : .AddWithValue("@Circuit", CircuitTextbox.Text)
    .AddWithValue("@AcademicYear" , ComboBox1.Text) : .AddWithValue("@Term", TextBox48.Text) : .AddWithValue("@nameOf", TextBox3.Text)
    .AddWithValue("@SchoolCode", TextBox4.Text) : .AddWithValue("@Stream", TextBox50.Text) : .AddWithValue("@Shift", TextBox49.Text)
    .AddWithValue("@Date", DateTimePicker1.Value.Date) : .AddWithValue("@OpeningDays", TextBox51.Text) : .AddWithValue("@Instructional Days", TextBox52.Text)
    .AddWithValue("@Kg1b", TextBox5.Text) : .AddWithValue("@Kg1g", TextBox28.Text) : .AddWithValue("@totalkg1", TextBox40.Text)
    .AddWithValue("@Kg2b", TextBox6.Text) : .AddWithValue("@Kg2g", TextBox27.Text) : .AddWithValue("@TotalKg2", TextBox39.Text)
    .AddWithValue("@P1b", TextBox7.Text) : .AddWithValue("@P1g", TextBox26.Text) : .AddWithValue("@TotalP1", TextBox38.Text)
    .AddWithValue("@P2b", TextBox8.Text) : .AddWithValue("@P2g", TextBox25.Text) : .AddWithValue("@TotalP2", TextBox37.Text)
    .AddWithValue("@P3b", TextBox9.Text) : .AddWithValue("@P3g", TextBox24.Text) : .AddWithValue("@TotalP3", TextBox36.Text)
    .AddWithValue("@P4b", TextBox10.Text) : .AddWithValue("@P4g", TextBox23.Text) : .AddWithValue("@TotalP4", TextBox35.Text)
    .AddWithValue("@P5b", TextBox11.Text) : .AddWithValue("@P5g", TextBox22.Text) : .AddWithValue("@TotalP5", TextBox34.Text)
    .AddWithValue("@P6b", TextBox12.Text) : .AddWithValue("@P6g", TextBox21.Text) : .AddWithValue("@TotalP6", TextBox33.Text)
    .AddWithValue("@JHS1b", TextBox13.Text) : .AddWithValue("@JHS1g", TextBox20.Text) : .AddWithValue("@TotalJHS1", TextBox32.Text)
    .AddWithValue("@jhs2b", TextBox14.Text) : .AddWithValue("@jhs2g", TextBox19.Text) : .AddWithValue("@totaljhs2", TextBox31.Text)
    .AddWithValue("@jhs3b", TextBox15.Text) : .AddWithValue("@jhs3g", TextBox18.Text) : .AddWithValue("@totaljhs3", TextBox30.Text)
End With


Naming those textboxes more appropriately would also save you headaches.
 
Last edited:
Upvote 0 Downvote
Writing inline SQL is actually one area where XML literals in VB.NET come in handy: 
        Dim sql =
            <SQL>
INSERT INTO Table1 (Column1,
                    Column2,
                    Column3)
VALUES (@Column1,
        @Column2,
        @Column3)
            </SQL>

        MessageBox.Show(sql.Value)
 
Upvote 0 Downvote
Thanks

Thanks it has been a great help, I really appreciate it
Herman said:
You have to be careful with spaces. I normally just write it on one long line, then paste

" & _
"

Where appropriate, indenting as I go.

objCommand.CommandText = "INSERT INTO CollectionForm ( Region, District, Circuit, AcademicYear, Term, NameOfSchool, SchoolCode, Stream, Shift," & _
                                                     " DateOf, OpeningDays, InstructionalDays, EnrolKg1Boys, EnrolKg1Girls, TotalKg1Enrol," & _
                                                     " EnrolKg2Boys, EnrolKg2Girls, TotalKg2Enrol, EnrolP1Boys, EnrolP1Girls, TotalP1Enrol," & _
                                                     " EnrolP2Boys, EnrolP2Girls, TotalP2Enrol, EnrolP3Boys, EnrolP3Girls, TotalP3Enrol, EnrolP4Boys," & _
                                                     " EnrolP4Girls, TotalP4Enrol, EnrolP5Boys, EnrolP5Girls, TotalP5Enrol, EnrolP6Boys, EnrolP6Girls," & _
                                                     " TotalP6Enrol, EnrolJHS1Boys, EnrolJHS1Girls, TotalJHS1, EnrolJHS2Boys, EnrolJHS2Girls, TotalJHS2," & _
                                                     " EnrolJHS3Boys, EnrolJHS3Girls, TotalJHS3 ) " & _
                                             "VALUES ( @Region, @District, @Circuit, @AcademicYear, @term, @nameOf, @SchoolCode, @Stream, @shift," & _
                                                     " @Date,@Ope ningDays,@InstructionalDays, @Kg1B, @Kg1G, @TotalKg1, @kg2b, @kg2g, @totalkg2," & _
                                                     " @p1b, @p1g, @totalp1, @p2b, @p2g, @totalp2, @p3b, @p3g, @totalp3, @p4b, @p4g, @totalp4, @p5b," & _
                                                     " @p5g, @totalp5, @p6b, @p6g, @totalp6, @jhs1b, @jhs1g, @totaljhs1, @jhs2b, @jhs2g, @totaljhs2," & _
                                                     " @jhs3b, @jhs3g, @totaljhs3 )"


Don't know where all the extra parenthesis came from, but you're safe now they are all gone. Personally since the whole query is static I would just store it as a resource and be done with it. I would never have to see it again in my code.

As for the second part, there's a great little idiom called With in VB, use it!

' Inserts Data Parameters
With objCommand.Parameters
    .AddWithValue("@Region", TextBox1.Text) : .AddWithValue("@District", TextBox2.Text) : .AddWithValue("@Circuit", CircuitTextbox.Text)
    .AddWithValue("@AcademicYear" , ComboBox1.Text) : .AddWithValue("@Term", TextBox48.Text) : .AddWithValue("@nameOf", TextBox3.Text)
    .AddWithValue("@SchoolCode", TextBox4.Text) : .AddWithValue("@Stream", TextBox50.Text) : .AddWithValue("@Shift", TextBox49.Text)
    .AddWithValue("@Date", DateTimePicker1.Value.Date) : .AddWithValue("@OpeningDays", TextBox51.Text) : .AddWithValue("@Instructional Days", TextBox52.Text)
    .AddWithValue("@Kg1b", TextBox5.Text) : .AddWithValue("@Kg1g", TextBox28.Text) : .AddWithValue("@totalkg1", TextBox40.Text)
    .AddWithValue("@Kg2b", TextBox6.Text) : .AddWithValue("@Kg2g", TextBox27.Text) : .AddWithValue("@TotalKg2", TextBox39.Text)
    .AddWithValue("@P1b", TextBox7.Text) : .AddWithValue("@P1g", TextBox26.Text) : .AddWithValue("@TotalP1", TextBox38.Text)
    .AddWithValue("@P2b", TextBox8.Text) : .AddWithValue("@P2g", TextBox25.Text) : .AddWithValue("@TotalP2", TextBox37.Text)
    .AddWithValue("@P3b", TextBox9.Text) : .AddWithValue("@P3g", TextBox24.Text) : .AddWithValue("@TotalP3", TextBox36.Text)
    .AddWithValue("@P4b", TextBox10.Text) : .AddWithValue("@P4g", TextBox23.Text) : .AddWithValue("@TotalP4", TextBox35.Text)
    .AddWithValue("@P5b", TextBox11.Text) : .AddWithValue("@P5g", TextBox22.Text) : .AddWithValue("@TotalP5", TextBox34.Text)
    .AddWithValue("@P6b", TextBox12.Text) : .AddWithValue("@P6g", TextBox21.Text) : .AddWithValue("@TotalP6", TextBox33.Text)
    .AddWithValue("@JHS1b", TextBox13.Text) : .AddWithValue("@JHS1g", TextBox20.Text) : .AddWithValue("@TotalJHS1", TextBox32.Text)
    .AddWithValue("@jhs2b", TextBox14.Text) : .AddWithValue("@jhs2g", TextBox19.Text) : .AddWithValue("@totaljhs2", TextBox31.Text)
    .AddWithValue("@jhs3b", TextBox15.Text) : .AddWithValue("@jhs3g", TextBox18.Text) : .AddWithValue("@totaljhs3", TextBox30.Text)
End With


Naming those textboxes more appropriately would also save you headaches.
Click to expand...
 
Upvote 0 Downvote

Similar threads

O
  • Question
Datagrid keeps displaying previously entered data
Replies
1
Views
1K
Dunfiddlin
Dunfiddlin

Share this page

Latest posts

Back
Top