SQL/VB.NET Error

[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(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman
dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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)

[Jeremy Cowles]

> 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 + ""

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

[Armin Zingler]

"scorpion53061" <sc************@yahoo.com> schrieb

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(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

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
--
Armin

[Bernie Yaeger]

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

"scorpion53061" <sc************@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

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(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
at
System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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)

[scorpion53061]

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

"Bernie Yaeger" <be*****@cherwellinc.com> wrote in message
news:eL**************@TK2MSFTNGP10.phx.gbl...

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

"scorpion53061" <sc************@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

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(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
behavior)
at

System.Data.SqlClient.SqlCommand.System.Data.IDbCo mmand.ExecuteReader(Comman

dBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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)