1,105,225 Community Members

Shortening long queries

Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Ancient Dragon
Achieved Level 70
27,587 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,031 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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

Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

please help me and give me an example

yes that is inserting every value to all the column

Member Avatar
Ancient Dragon
Achieved Level 70
27,587 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,031 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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 */)

Member Avatar
Reverend Jim
Noli mentula
5,402 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 646 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

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.

Member Avatar
tinstaafl
Postaholic
2,012 posts since Jun 2010
Reputation Points: 559 [?]
Q&As Helped to Solve: 402 [?]
Skill Endorsements: 35 [?]
 
0
 

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?

Member Avatar
Reverend Jim
Noli mentula
5,402 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 646 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

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

Member Avatar
kenomote
Light Poster
46 posts since Oct 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
Ancient Dragon
Achieved Level 70
27,587 posts since Aug 2005
Reputation Points: 5,232 [?]
Q&As Helped to Solve: 3,031 [?]
Skill Endorsements: 115 [?]
Team Colleague
Featured
Sponsor
 
0
 

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.

Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
3
 

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()
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article