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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.