Shortening long queries

[kenomote]

hey guys i need to know some of the techniques on how to shorten a very long query for example

Dim SqlStatement As String = "INSERT INTO tblcenterlane(DESCRIPTION, 
UNIT, 
BEG, 
OT_KITCHEN, 
OT_F&B, 
OT_F.O., 
OT_S&M, 
OT_H.K., 
ADMIN, 
DEL, 
PULLOUT, 
SPOILAGE, 
ENDING, 
UNIT_COST, 
AMOUNT, 
VARIANCE, 
REMARKS, 
DEPARTMENT, 
DATE, 
DATE_DURATION_FROM, 
DATE_DURATION_TO, 
SUPPLIER_ID, 
CATEGORY) 
VALUES('" & txtItemDesc.Text & "',
'" & txtUnit.Text & "',
'" & txtBeg.Text & "',
'" & txtDel.Text & "',
'" & txtSpoilDmg.Text & "',
'" & txtPout.Text & "',
'" & txtEnding.Text & "',
'" & txtUnitCost.Text & "',
'" & txtAmount.Text & "',
'" & txtVariance.Text & "',
'" & txtDate.Text & "',
'" & txtDurationF.Text & "',
'" & txtDurationT.Text & "',
'" & txtSupplier.Text & "',
'" & txtCategory.Text & "',
'" & rtxtRemarks.Text & "')"

its so hard to manage please i need to learn some proper clean coding and im really ashamed of this

[Ancient Dragon]

Is that inserting a value into every column of the table? If it is then you don't need to specify the column names.

[kenomote]

please help me and give me an example

yes that is inserting every value to all the column

[Ancient Dragon]

Just delete the column names in parantheses. Surely you don't want me to come to your house and do your typing??

Dim SqlStatement As String = "INSERT INTO tblcenterlane VALUES( /* blabla */)

[Reverend Jim]

Keep in mind that if you eliminate the column names then the values must be in the same order as the columns are defined in the table. If someone changes the column order then your query will puke or the data will go into the wrong fields. If someone adds a column (but not at the end of the table) then same problem. You are better off leaving the column names in. Also, you should be using parameterized queries to avoid SQL injection attacks.

[tinstaafl]

Wouldn't having the user input the data directly into a listview, or datagrid, bound to the database, allow passing all the data to the database with a much shorter string?

[Reverend Jim]

You can also "shorten" it by putting several items on each line instead of one per.

[kenomote]

i need examples im really slow at this...please

[Ancient Dragon]

You can also "shorten" it by putting several items on each line instead of one per.

Yes, but that would make it more difficult to change in the future.

[TnTinMN]

I'm not going to claim that this is a better way, but it is one way that I find easy to read and work with.

      ' This method uses a CopmmandBuilder to build the commands

      ' You need to provide a DataApater with the select command defined
      ' for the command builder to work from.

      ' Since you are using all columns, a simple select * works

      Dim conn As New SqlConnection(My.Settings.NorthwindConnectionString)

      Dim adapter As New SqlDataAdapter()
      adapter.SelectCommand = New SqlCommand("Select * From Employees", conn)

      ' feed the adapter into the builder
      Dim cmdbuilder As New SqlCommandBuilder(adapter)

      ' get the insert command. Tell it to use column names for paramters
      Dim inscmd As SqlCommand = cmdbuilder.GetInsertCommand(useColumnsForParameterNames:=True)
      ' now just fill the parameters
      ' the name format is: @ColumnName
      ' in the column name has a space(" ") in it, it is replaced with "_"

      With inscmd
         .Parameters("@Last_Name").Value = "Jones"
         .Parameters("@First_Name").Value = "William"
         .Parameters("@Title").Value = "CCABW"
         .Parameters("@TitleofCourtesy").Value = "Mr."
         .Parameters("@BirthDate").Value = #1/1/1955#
         .Parameters("@HireDate").Value = #6/23/1978#
         .Parameters("@Address").Value = "Somewhere"
         .Parameters("@City").Value = "MyTown"
         .Parameters("@Region").Value = "All"
         .Parameters("@PostalCode").Value = "24312"
         .Parameters("@Country").Value = "USA"
         .Parameters("@HomePhone").Value = "1234332345"
         .Parameters("@Extension").Value = ""
         .Parameters("@Photo").Value = DBNull.Value
         .Parameters("@Notes").Value = ""
         .Parameters("@ReportsTo").Value = 4
         .Parameters("@PhotoPath").Value = ""
      End With

      conn.Open()
      inscmd.ExecuteNonQuery()
      conn.Close()