By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,573 Members | 1,806 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,573 IT Pros & Developers. It's quick & easy.

SQL/VB.NET Error

P: n/a
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)
Nov 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
> 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

Nov 20 '05 #2

P: n/a
"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

Nov 20 '05 #3

P: n/a
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)

Nov 20 '05 #4

P: n/a
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)


Nov 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.