adding parameters using DBCommand.Parameters.Add

I'm trying to configure some parameters for a data adapter that will connect to a MySQL data source.  I'm unfamiliar with how to do this using DBCommand.   I've included my sql query below.  The idea is query Table1 and then update STATUS and STATUSDATE as needed.

cmd.CommandText = "SELECT UserID, CheckListID, Status, STATUSDATE FROM Table1 WHERE UserID = @UserID AND CheckListID = @CheckListID"

Open in new window

PeterTrefrenAsked:
Who is Participating?
 
MogalManicCommented:
You also did not add the parameter to the cmd's ParameterCollection.  Each parameter group should look something like this:

Open in new window


Dim UserIDParam As DbParameter = cmd.CreateParameter
UserIDParam.ParameterName = "@UserID"
UserIDParam.Value = strUserId
cmd.Parameters.Add(UserIDParam)   'This is the part that is missing

Open in new window

0
 
daveamourCommented:
I would normally use parameters if calling a stored procedure.
Since you have just dyanmic sql in a strign then you could just add it in there as follows:
string userID  = 54
string checkListID  = 5
cmd.CommandText = "SELECT UserID, CheckListID, Status, STATUSDATE FROM Table1 WHERE UserID = " + userID.ToString() + " AND CheckListID = " + checkListID.ToString()  
 
 
0
 
CodeCruiserCommented:
Try

cmd.Parameters.AddWithValue("@UserID", theid)
cmd.Parameters.AddWithValue("@CheckListID", theid)


By the way, parameter names are usually in all lower case letters to avoid case sensitivity problems.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
daveamourCommented:
Actually this has got me thinking about SQL injection.  Of course with dynamic SQL you should santise data to prevent such attacks but does using parameters this way do any of this for you?
0
 
CodeCruiserCommented:
>but does using parameters this way do any of this for you?
YES. That is the reason parameters are recommended and using the values like you suggested is an invitation for a SQL Injection attack.
0
 
daveamourCommented:
Don't get me wrong, I'm a fan of stored procedures wherever possible but if for whatever reason you use dynamic sql then it is fine to use it the way I suggested as long as you santise your data.  Using parameters santises the data for you  - its just a question of whether you do it yourself or use a code feature that does it for you right.
Parameters are clearly better of course as you don't have to worry about forgetting to santise data but my way is not at all wrong - just more risky.
Just wanted to clarify this as your comment suggested that dynamic sql qithout parameters was wrong.
0
 
CodeCruiserCommented:
>your comment suggested that dynamic sql qithout parameters was wrong.
I said your code is wrong in the context of SQL Injection attacks(which you mentioned yourself). Otherwise, I use SQL queries without parameters in many places myself where the values come from within the program rather than through user input.
0
 
daveamourCommented:
Ok sorry - just wanted to clarify that's all!
0
 
MogalManicCommented:
Another reason to use parameters is that many databases use some sort of caching by SQL statement for the execution plan.  If you create your queries without parameters, then only the EXACT same query would use the cached execution plan.  A similar query with different where clause values would force the SQL query engine to generate a new execution plan for the statement.

This is the way to create parameters for ANY database (some database types do not have the AddWithValue() method):

System.Data.IDbDataParameter param = cmd.CreateParameter();
param.ParameterName="@UserID";
param.Value=theid;
cmd.Parameters.Add(param);

param = cmd.CreateParameter();
param.ParameterName="@CheckListID";
param.Value=theid;
cmd.Parameters.Add(param);

Open in new window

0
 
PeterTrefrenAuthor Commented:
Right now when I run the code below I get a fatal error.  I think the method I'm using is correct but maybe I need to change the value of the parameters or the SQL command text to match the type of data being passed.  Keep in mind UserID and CheckListID are both strings.
                Dim cmd As DbCommand = factory.CreateCommand
                cmd.Connection = cn
                strSQL = "UPDATE CheckListStatus SET STATUS = @Status, STATUSDATE = @StatusDate WHERE UserId=@UserID AND CheckListID=@CheckListID"

                Dim UserIDParam As DbParameter = cmd.CreateParameter
                UserIDParam.ParameterName = "@UserID"
                UserIDParam.Value = strUserId

                Dim CheckListIDParam As DbParameter = cmd.CreateParameter
                CheckListIDParam.ParameterName = "@CheckListID"
                CheckListIDParam.Value = Trim(strAssessId)

                Dim statusParam As DbParameter = cmd.CreateParameter
                statusParam.ParameterName = "@Status"
                statusParam.Value = "0"

                Dim statusDateParam As DbParameter = cmd.CreateParameter
                statusDateParam.ParameterName = "@StatusDate"
                statusDateParam.Value = "Null"

                cmd.CommandText = strSQL
                cn.Open()
                cmd.ExecuteNonQuery()
                cn.Close()

Open in new window

0
 
CodeCruiserCommented:
Which line generates the error? Add a try catch and get the exact exception details.
0
 
PeterTrefrenAuthor Commented:
The error is with the ExecuteNonQuery.  When I use a catch and look at err.description the text is blank.  The only thing I get is a windows message 'Fatal error encountered during command execution'.
0
 
CodeCruiserCommented:
Did you try my method?
0
 
MogalManicCommented:
The only thing that looks odd is line 19.  Did you want to set the StatusDate to a NULL value or to the string "Null"?

If you want to set it to the NULL value, then change line 19 to be:
               statusDateParam.Value = DbNull.Value;

0
 
PeterTrefrenAuthor Commented:
Adding the parameter to the collection worked.  However I'm still trying to understand how I can set the value of StatusDate to Null in the database.
0
 
PeterTrefrenAuthor Commented:
CodeCruiser:

With regards to using cmd.Parameters.AddWithValue("@CheckListID", theid),  DBCommand does not support AddWithValue.
0
 
PeterTrefrenAuthor Commented:
Thanks for all of the help I've learned a lot from everyone who responded.  If anyone figures out how to set the date value to Null just respond.  Otherwise at this point I have a work around for this issue for my code.
0
 
MogalManicCommented:
See my comment Before the accepted comment.
0
 
PeterTrefrenAuthor Commented:
MogalManic

Yes your solution about setting the date field to null is correct (statusDateParam.Value = DBNull.Value).  I coded it slightly wrong previously.

Thanks again,

Peter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.