Select Statement with Variable not working C#

Question

I have done a bunch of Select statements with AddWithValue's and Add's but this one I can't seem to get to work. This works fine when I put the number in instead of the @orderNum. Tried with @orderNum and '@orderNum' when I put the '' nothing happens on button click but when I use @orderNum it says it can't find ORDERNUM in the table. The value in the SQL table is a CHAR FOR BIT DATA so maybe that has something to do with it? Ideas?

End of my select statement:

WHERE POITEM.ORDNO  = @orderNum

Add :

cmd.Parameters.Add(new OleDbParameter("@orderNum", poTextBox.Text));

The entire SELECT statement (the resulting big C# string concatenated from smaller chunks):

SELECT 
    CAST(POITEM.ITNBR as char(15) ccsid 37) as itemno, 
    CAST(POITEM.ITDSC as char(15) ccsid 37) as ITDSC, 
    CAST(POITEM.HOUSE as char(15) ccsid 37) as hou, 
    CAST(POITEM.REFNO as char(15) ccsid 37) as REF, 
    CAST(POITEM.STAIC as char(15) ccsid 37) as staic,
    POITEM.QTYOR,
    CAST(POITEM.UNMSR as char(15) ccsid 37) as unmsr,
    POITEM.UMCNV,
    POITEM.DKQTY,
    POITEM.STKQT,
    CAST(POITEM.JOBNO as char(15) ccsid 37) as job,
    CAST(POITEM.DPTNO as char(15) ccsid 37) as dept, 
    CAST(POITEM.VCLNB as char(15) ccsid 37) as vclnb, 
    CAST(POITEM.WHSLC as char(15) ccsid 37) as whsloc,
    POITEM.UCORQ, 
    CAST(POITEM.BLCOD as char(15) ccsid 37) as blcod, 
    CAST(POMAST.VNDNR as char(15) ccsid 37) as vendor, 
    CAST(POMAST.PSTTS as char(15) ccsid 37) as pstts,
    CAST(VENNAM.VNAME as char(15) ccsid 37) as vname, 
    CAST(OVERRD.BNAME as char(15) ccsid 37) as bname 
FROM POMAST 
LEFT OUTER JOIN POITEM ON POMAST.ORDNO = POITEM.ORDNO
LEFT OUTER JOIN OVERRD ON POMAST.ORDNO = OVERRD.ORDNO
LEFT OUTER JOIN VENNAM ON POMAST.VNDNR = VENNAM.VNDNR 
WHERE POITEM.ORDNO = @orderNum

Could you try setting the parameters first, then setting the CommandText? It usually doesn't matter, but this is an unusual case. Also, WHERE POITEM.ORDNO = @orderNum is correct syntax. — Keyur PATEL
– Keyur PATEL, Commented Sep 12, 2017 at 2:09
I have tried it like you requested as well... it's strange I can't seem to figure out why it wouldn't work?... I didn't know if the CHAR for BIT DATA had anything to do with it? — guitarProgrammer
– guitarProgrammer, Commented Sep 12, 2017 at 2:12
I'm just going to go out on a limb and suggest that one side of your SQL query might be null, or you have some padding on your @orderNum param. Another thing to consider is that you need to process poTextBox.Text before putting it in your query, because type mismatches will prevent your code from behaving as you expect it to (or just plain cause errors). — PSGuy
– PSGuy, Commented Sep 12, 2017 at 2:15
I tried doing this instead and still get the same error... string poNum = poTextBox.Text.Trim(); the used cmd.Parameters.Add(new OleDbParameter("@orderNum", poNum)); in case there was extra padding... still says no column found.. so strange. — guitarProgrammer
– guitarProgrammer, Commented Sep 12, 2017 at 2:22
Please do not post code in the comments. edit your instead. Also, it would help us to help you if you post not only the sql code buy also the c# code. One more thing, make sure you only tag the relevant database. It's unclear of it's MS-SQL-Server or AS400. — Zohar Peled
– Zohar Peled, Commented Sep 12, 2017 at 4:51

andrews · Accepted Answer · 2017-09-12 13:25:02Z

1

You said you were able to put a number instead of @orderNum, so does it mean you store integer order numbers in the CHAR FOR BIT DATA column type or can there be order numbers like: PO1234?

As others have suggested you need to convert this line:

WHERE POITEM.ORDNO  = @orderNum

to

WHERE POITEM.ORDNO = ?

and then set the parameter like this if your POITEM.ORDNO column contains only integers with no alpha characters:

int poNum = 0;

if (!string.IsNullOrWhiteSpace(poTextBox.Text))
    Int32.TryParse(poTextBox.Text.Trim(), out poNum);

cmd.Parameters.AddWithValue("?", poNum);

If your POITEM.ORDNO column contains alpha characters then just send the string instead of int as parameter:

string poNum = "";

if (!string.IsNullOrWhiteSpace(poTextBox.Text))
    poNum = poTextBox.Text.Trim();

cmd.Parameters.AddWithValue("?", poNum);

Try it out and let us know if it works.

answered Sep 12, 2017 at 13:25

andrews

2,1832 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

guitarProgrammer Over a year ago

This results in a System.InvalidOperationException: Command Parameter [0] data value could not be converted for easons other than sign mismatch or data overflow.

andrews Over a year ago

@guitarProgrammer so you have tried Int or String version? What was the poTextBox.Text value?

guitarProgrammer Over a year ago

Figured it out... if you put $ before the "SELECT then use this for your value '{poTextBox.Text}' it works fine.. not sure why that wouldn't work calling a parameter

andrews Over a year ago

@guitarProgrammer it sounds strange to me, really. Would you post the whole piece of C# code as a new answer?

DRapp · Accepted Answer · 2017-10-01 04:11:33Z

1

don't do string concatenation to build sql, expecially with fields that are string types.

When doing an Add Parameters with value, the "@" is recognized in sql-server as a parameter name. However, leave the "@" out of the parameter name reference. The whole point of the parameters method is it IS a parameter and sql-server will recognize it.

cmd.Parameters.Add(new OleDbParameter("@orderNum", poTextBox.Text));

change to

cmd.Parameters.Add(new OleDbParameter("orderNum", poTextBox.Text));

answered Oct 1, 2017 at 4:11

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2017-10-01 04:53:03Z

0

You're using OleDB. OleDB does not use named paramters like @orderNum. It uses a ? placeholder. So you need this:

WHERE POITEM.ORDNO  = ?

And :

cmd.Parameters.Add("?", OleDbType.Integer).Value = poTextBox.Text;

OleDb will match up which parameter to which placeholder in the query text based on the order in which you add parameters to the collection.

Or if you're talking to Sql Server, you can can use SqlClient instead of OleDb (System.Data.SqlClient, SqlConnection, and SqlCommand instead of System.Data.OleDb, OleDbConnection, and OleDbCommand). Then named paramters will work.

answered Oct 1, 2017 at 4:53

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Comments

guitarProgrammer · Accepted Answer · 2017-09-12 15:15:49Z

-1

Here is what works... strange that the parameter didn't. Another programmer here thought to try this and it worked!

OleDbCommand poSearch = new OleDbCommand("SELECT CAST(POITEM.ITNBR as char(15) ccsid 37) as itemno, CAST(POITEM.ITDSC  as char(30) ccsid 37) as ITDSC,"
      + "CAST(POITEM.HOUSE as char(15) ccsid 37) as hou,  CAST(POITEM.REFNO as char(15) ccsid 37) as REF, CAST(POITEM.STAIC as char(15) ccsid 37) as staic, "
      + "POITEM.QTYOR, CAST(POITEM.UNMSR as char(15) ccsid 37) as unmsr, POITEM.UMCNV, POITEM.DKQTY, POITEM.STKQT, CAST(POITEM.JOBNO as char(15) ccsid 37) as job,"
      + "CAST(POITEM.DPTNO as char(15) ccsid 37) as dept, CAST(POITEM.VCLNB as char(15) ccsid 37) as vclnb, CAST(POITEM.WHSLC as char(15) ccsid 37) as whsloc,"
      + "POITEM.UCORQ, CAST(POITEM.BLCOD as char(15) ccsid 37) as blcod, CAST(POMAST.VNDNR as char(30) ccsid 37) as vendor, CAST(POMAST.PSTTS as char(15) ccsid 37) as pstts,"
      + "CAST(VENNAM.VNAME as char(30) ccsid 37) as vname, CAST(OVERRD.BNAME as char(15) ccsid 37) as bname  FROM POMAST LEFT OUTER JOIN POITEM ON POMAST.ORDNO = POITEM.ORDNO "
      + $"LEFT OUTER JOIN OVERRD ON POMAST.ORDNO = OVERRD.ORDNO LEFT OUTER JOIN VENNAM ON POMAST.VNDNR = VENNAM.VNDNR  WHERE POITEM.ORDNO  = '{poTextBox.Text}'", cn);

answered Sep 12, 2017 at 15:15

guitarProgrammer

312 silver badges8 bronze badges

5 Comments

DRapp Over a year ago

AAhhhgggg... SQL-INJECTION EXPOSURE

Joel Coehoorn Over a year ago

Don't do this. It's vulnerable to sql injection attacks.

andrews Over a year ago

@JoelCoehoorn, @DRapp don't you find the $ sign at the beginning of the last line to be strange? I doubt it will ever compile. AFAIK only @ is allowed before strings in C#.

Joel Coehoorn Over a year ago

@andrew recent versions of c# use it to do string interpolation

andrews Over a year ago

@JoelCoehoorn ok, thanks for the note, will look up this in the docs.

Collectives™ on Stack Overflow

Select Statement with Variable not working C#

4 Answers 4

4 Comments

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related