Occasional SQL Error

S

scorpion53061

sql2 = "SELECT custno, DATEOR, ORDERNO, LN, custpo, ITEMNO, QTY, PRICE, UM,
totprice, QTYALL, QTYBACK, SLSMNO, INIT FROM jjk044 where custno = '" &
custno & "' and " + OpenType + " order by " + sortterm + ""

For a local application I occasionally but not always get the below error
with this SQL statement.

System.Data.SqlClient.SqlException Incorrect Syntax near 'by' at
system.data.sqlclient.sqlcommand.executereader
 
W

William Ryan eMVP

Scorp:

First, kill the concatenated sql and use Params "WHERE custno = @OpenType
AND whatever else Order By @SortItem
then add the params to your command object's params collection. I can't be
sure what the problem is, but something, most likely a variable in OpenType
is causing a Syntax error .

If I'm reading this right, it looks like it will read "custno = SomeValue
and Someotherval order by SortItem" . I think the piece after the custno
needs another field name.

HTH,

Bill
 
K

Karl Gram

Hi,

Strange combination of & and + for concatenation.

My guess is that the sortterm variable will sometimes be empty...
 
S

scorpion53061

Hello again Bill,

custno is a a string variable within the vb.net application as well as
OpenType and sortterm that is used to dicate how and what to search for.

I thought the @ sign was only for columns?
 
S

scorpion53061

The sort term is determined by a combobox. It cannot be left blank.

How would you have written this statement?
 
J

Jay B. Harlow [MVP - Outlook]

Scorpion,
What does sql2 look like when you get the exception?

As the others have suggested, is sortterm empty?

Hope this helps
Jay
 
W

William \(Bill\) Vaughn

As Mr. Ryan says, you're asking for trouble with this approach through SQL
injection attacks--not to mention poorer performance and random errors (as
you've experienced). Switch to Command Parameters. I expect one of your
arguments has a keyword that's throwing the SQL syntax checker.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
S

scorpion53061

No it is assigned the value from the combobox choosing sort. It is designed
as such that the user cannot proceed without selecting a "sort" function.

Unfortunately I do not know what sql2 looks like becasue this error is being
reported to me by those outside my departement (accounting actually :) ).
They are the only ones having these errors as well.
Everybody else is fine.
 
W

William Ryan eMVP

Ok, but you're getting a syntax error and it's only occassional. That's
usually caused by an apostrophe in one of those fields (if it works most of
the time). But it looks like you are missing a field "where custno =
something and somethingelse" as opposed to "where custno = something and
whatever other condition" It's not going to equal two different fields and
you'll want to specify the fieldname again.
 
A

Alex B

Why not just post your whole project so we can rewrite that for you too.

At least two helpful folks have let you know that you should use command
parameters. Do you not know how to use them?
 
W

William Ryan eMVP

Scorp:

I rewrote it in my first post with actual parameters. I didn't add them to
the command object, but the syntax is

command.Parameters.Add("@VaribleName", SqlDbType.WhateverType, Size).Value =
WhateverParameter
 
S

scorpion53061

Alex,

While I mean no disrespect I assure you I work extremely hard on my own on
my projects. I simply wanted to see how others would have handled this
situation.

Asking for help occasionally is not weakness. You should have seen the
nightmare I was working on this weekend. Don't ever doubt I don't do my
share of legwork....
 
S

scorpion53061

thanks bill that was the part I was not sure about was how to handle the
variable...
 
W

William Ryan eMVP

Scorp:

There's like 6 overloads, so at the most basic....
cmd.Parameters.Add("@FirstParamname", ValueYouWantToSend) will work.

Probably use this first and let things happen implicitly until you get
confident with things. Afterward, add the type and size for more clarity
and ostensibly better performance.

Let me know if this works or not. i'm flying to San Fran for the Mobile Dev
Conference, but I'll be checking the NG Periodically.

BTW, did the other problem ever get resolved?
 
A

Alex B

I hear you - I apologize that I misunderstood your intentions... My 2c is
that Bill's advice is spot on - especially considering the security risk of
your original method... but my guess is that somehow that combobox is
returning an empty variable and that's why the other sql failed
intermittantly. After you switch to command parameters (if you're going
to go that route), if you'd be so kind, could you let us know what the
problem ended up being? If you need to stick with your earlier method for
other reasons, I'd recommend saving that sql statement to a text file when
it fails so you can go back and find out the parameters were.
 
C

Charlie Smith

scorpion53061 said:
sql2 = "SELECT custno, DATEOR, ORDERNO, LN, custpo, ITEMNO, QTY, PRICE, UM,
totprice, QTYALL, QTYBACK, SLSMNO, INIT FROM jjk044 where custno = '" &
custno & "' and " + OpenType + " order by " + sortterm + ""
Click to expand...

Scorpion,

I have seen this type of error when an apostrophe or quotation mark
gets entered with the data into one of the fields. This throws your
carefully crafted concat string off and causes an error. A distinct
possibility since you are getting an occasional error. This is
another reason to use command parameters because quotation marks are
handled by the parameter and you don't have to worry about it.

Charlie
 
S

scorpion53061

Yep it sure did!!

Boss was actually happy with my work for a change......Man he can be
impossible.

This is the text I posted in the Table.Compute thread. You must not have
seen it.

Jay and Bill,

Thank you very much for your help....

There was a lot of blood on the floor (mostly mine) on this one so to
speak....

Jay I am working with your solution.....I am running into some problems but
since this is working I need to get some reports done with it.

Again thank you very much and if you see anyting here to be alarmed about
please holler.....

For itemcheck = 0 To dstrancopy.Tables(0).Rows.Count - 1
For itemcheck1 = 0 To listds.Tables(0).Rows.Count - 1
If listds.Tables(0).Rows(itemcheck1).Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") Then
alreadyexists = True
Exit For
Else
alreadyexists = False
End If
Next
If alreadyexists = False Then
'Dim filter As String = String.Format("(Not TYPE in ('CASH
CREDIT','INVOICE CREDIT', 'RGA')) and (MONTH = '{0}')",
Month(r.Item("DATE")))
drnew1 = listds.Tables(0).NewRow
drnew1.Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM")

strexpression = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") & "' and MONTH = 'Jan' and
((TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'CASH CREDIT') OR (TYPE <> 'RGA'))"
foundrows = dstrancopy.Tables(0).Select(strexpression)
If foundrows.Length = 0 Then
drnew1.Item("JAN") = 0
drnew1.Item("JANQTY") = 0
Else
For Each r In foundrows
drnew1.Item("JAN") = r.Table.Compute(r.Item("UCOST")
* r.Item("QTY"), r.Item("MONTH") = "Jan")
drnew1.Item("JANQTY") =
r.Table.Compute(r.Item("QTY"), r.Item("MONTH") = "Jan")
Next
End If

strexpression = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") & "' and MONTH = 'Feb' and
((TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'CASH CREDIT') OR (TYPE <> 'RGA'))"
foundrows = dstrancopy.Tables(0).Select(strexpression)
If foundrows.Length = 0 Then
drnew1.Item("FEB") = 0
drnew1.Item("FEBQTY") = 0
Else
For Each r In foundrows
drnew1.Item("FEB") = r.Table.Compute(r.Item("UCOST")
* r.Item("QTY"), r.Item("MONTH") = "FEB")
'CStr(r.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r.Item("DATE")).IndexOf("01/") > -1)
drnew1.Item("FEBQTY") =
r.Table.Compute(r.Item("QTY"), r.Item("MONTH") = "FEB")
'CStr(r.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r.Item("DATE")).IndexOf("01/") > -1)
Next
End If

listds.Tables(0).Rows.Add(drnew1)
End If


Next
 
B

Bernie Yaeger

Hi Scorp,

Seems like a problem that should be solved a lot less contentiously than it
is.

Add a try catch block and print out sql2 in a messagebox when it throws the
error. Then you will see what's in sortterm and you'll solve the problem.

HTH,

Bernie Yaeger
 
W

William Ryan eMVP

Good deal. Let me ask you something OT. Is the thing the compute supposed
to solve a Report? If so, I'd really check out using Crystal Reports. I
love doing anything with ADO.NET, but I spent about an hour last week doing
some fairly complex OLAP reports. I was all proud of myself until I decided
to see how long it would take me to do it in Crystal. just under 20
minutes. Grouping, Running totals, Subtotals, and formatting is a total
breeze, so is custom formulas and exporting to Excel. Tools like that
really make things easy....

BTW, I'll be at MDC this week and blogging away. If you have any questions
or anything, post a reply http://www.msmvps.com/WilliamRyan/ or drop me an
email and I'll ask the MS dudes and post the discussion. Also, if you are
interested, I'm working on Part IV and V or my Efficient ADO.NET articles
and I'd like to write about the code you posted a while back with Excel. I
have the Word part done, but if you are interested in writing an article
with me, let me know. [We have a really simple to use Article generator
that makes it a breeze] However, I'd love to work on some stuff with you,
Miha, Cor , Skeet, or any of the ADO.NET group

Cheers,

Bill
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL/VB.NET Error 4

Top