SQL Statements 1

[hotcoals]

Hi I have a question about working with SQL Statements. I am using an 'INSERT INTO' statement in my VB.Net application. The problem i have is that it is extremely long. The question i have is, is there another way to do it, or if not can i put it on multiple lines?? Here is a copy of the code.

sql = "INSERT INTO Child (FamCode, ChildCode, ChildName, DOB, Allergies, BHMonOff1, BHMonOff1MN, BHMonUp1, BHMonUp1MN, BHTueOff1, BHTueOff1MN, BHTueUp1, BHTueUp1MN, BHWedOff1, BHWedOff1MN, BHWedUp1, BHWedUp1MN, BHThuOff1, BHThuOff1MN, BHThuUp1, BHThuUp1MN, BHFriOff1, BHFriOFf1MN, BHFriUp1, BHFriUp1MN,) VALUES ('" & txtFamCode.Text & "','" & txtChildCode.Text & "','" & txtChildName.Text & "','" & txtDOB.Text & "','" & txtAllergies.Text & "','" & txtMonOff1.Text & "','" & lstMonOff1.SelectedValue & "','" & txtMonUp1.Text & "','" & lstMonUp1.SelectedValue & "','" & txtTueOff1.Text & "','" & lstTueOff1.SelectedValue & "','" & txtTueUp1.Text & "','" & lstTueUp1.SelectedValue & "','" & txtWedOff1.Text & "','" & lstWedOff1.SelectedValue & "','" & txtWedUp1.Text & "','" & lstWedUp1.SelectedValue & "','" & txtThuOff1.Text & "','" & lstThuOff1.SelectedValue & "','" & txtThuUp1.Text & "','" & lstThuUp1.SelectedValue & "','" & txtFriOff1.Text & "','" & lstFriOff1.SelectedValue & "','" & txtFriUp1.Text & "','" & lstFriUp1.SelectedValue & "')"

Any help is greatly appreciated

 

[chrissie1]

To put it on multiple lines you can use " _" (space + underscore + Enter). Or you can leave it all on 1 line and go to Tools > Options > Text Editor > Basic > Editor > Interaction > Word wrap

BTW I really would recommend you using Parameters (look up sql injection on google).


Christiaan Baes
Belgium

"My new site" - Me

 

[djj55]

Here is an example of the underscore method:

sql = "INSERT INTO Child (FamCode, ChildCode, ChildName, DOB, Allergies, BHMonOff1, " _
    & "BHMonOff1MN, BHMonUp1, BHMonUp1MN, BHTueOff1, BHTueOff1MN, BHTueUp1, BHTueUp1MN, " _
    & "BHWedOff1, BHWedOff1MN, BHWedUp1, BHWedUp1MN, BHThuOff1, BHThuOff1MN, BHThuUp1, " _
    & "BHThuUp1MN, BHFriOff1, BHFriOFf1MN, BHFriUp1, BHFriUp1MN,) VALUES ('" _
    & txtFamCode.Text & "','" & txtChildCode.Text & "','" & txtChildName.Text & "','" _
    & txtDOB.Text & "','" & txtAllergies.Text & "','" & txtMonOff1.Text & "','" _
    & lstMonOff1.SelectedValue & "','" & txtMonUp1.Text & "','" & lstMonUp1.SelectedValue _
    & "','" & txtTueOff1.Text & "','" & lstTueOff1.SelectedValue & "','" & txtTueUp1.Text _
    & "','" & lstTueUp1.SelectedValue & "','" & txtWedOff1.Text & "','" _
    & lstWedOff1.SelectedValue & "','" & txtWedUp1.Text & "','" _
    & lstWedUp1.SelectedValue & "','" & txtThuOff1.Text & "','" _
    & lstThuOff1.SelectedValue & "','" & txtThuUp1.Text & "','" & lstThuUp1.SelectedValue _
    & "','" & txtFriOff1.Text & "','" & lstFriOff1.SelectedValue & "','" & txtFriUp1.Text _
    & "','" & lstFriUp1.SelectedValue & "')"

This is just one way if you do not use the wrap option.
Where to break is covered in several ongoing discussions throughout the web, but it comes down to personal preferance.
djj