SQL/VB.NET Error

  • Thread starter Thread starter scorpion53061
  • Start date Start date
S

scorpion53061

This is a windows vb.net app that is using SQL Server.

I have 1(one) customer who receives this error out of the 400 or so that use
this software. The error and statement are below. I also know this user has
a very slow internet connection but I dont think it is a result of this. Any
ideas?

sql2 = "SELECT GETDATE() AS TIMEOFENTRY,custno, DATEOR, ORDERNO, LN, custpo,
ITEMNO, QTY, PRICE, UM, totprice, QTYALL, QTYBACK, SLSMNO, INIT FROM jjk044
where custno = " + custno + " and " + Selection + " order by " + S + ""

S is
If ComboBox2.Text = "Ticket Number" Then
S = "orderno"
End If
more choices like this availabile.

Error is:

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'by'.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at jjk_Software.fmopen.Button1_Click(Object
sender, EventArgs e)
 
sql2 = "SELECT GETDATE() AS TIMEOFENTRY,custno, DATEOR, ORDERNO, LN,
custpo,
ITEMNO, QTY, PRICE, UM, totprice, QTYALL, QTYBACK, SLSMNO, INIT FROM jjk044
where custno = " + custno + " and " + Selection + " order by " + S + ""
Click to expand...

What field are you trying to set Selection = to? Look:

WHERE [custno] = 03219243 AND 329818 ORDER BY [field_name]
-------------------------------------^^^^^^^^

You see?

Running your queries through Query Analyzer (or similar tool) will kill
these types bugs before they can cause you a problem.
Also, the [&] is preferred for concatenation over the [+] symbol.

HTH,
Jeremy
 
scorpion53061 said:
This is a windows vb.net app that is using SQL Server.

I have 1(one) customer who receives this error out of the 400 or so
that use this software. The error and statement are below. I also
know this user has a very slow internet connection but I dont think
it is a result of this. Any ideas?

sql2 = "SELECT GETDATE() AS TIMEOFENTRY,custno, DATEOR, ORDERNO, LN,
custpo, ITEMNO, QTY, PRICE, UM, totprice, QTYALL, QTYBACK, SLSMNO,
INIT FROM jjk044 where custno = " + custno + " and " + Selection + "
order by " + S + ""

S is
If ComboBox2.Text = "Ticket Number" Then
S = "orderno"
End If
more choices like this availabile.

Error is:

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'by'.
at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
Click to expand...

The syntax of the where clause is wrong. The resulting SQL is for example:

....where custno = 11 and 12 order by orderno

It probably hast to be
....where custno = 11 and ANOTHERFIELD=12 order by orderno
 
Hi Scorp,

Is the customer leaving the combobox empty? If so, the by clause reads
'order by '. Could that be the problem? Also, have you tested all
selections in the combobox? Finally, do a try catch and print out the ex
message to see what sql2 looks like when it fails.

HTH,

Bernie Yaeger
 
You were right.......I forgot to put in a catch for the empty combo box.
Thank you.......
 
Back
Top