Link to home
Start Free TrialLog in
Avatar of wademi
wademi

asked on

In C#.net how do I use SQL parameters with an ODBC connection

I have an application that I have developed with C#.net and a SQL backend. I have a number of SQL update querys which requires input from users via a text box. I am using ODBC with a SQL driver to connect to my database. I am ODBC parameters in my query to handle user input via text box. This is to prevent database injection.

My parameters are like this.

string qryUpdateTitlte = @"update requests set requests.project_title =@updateTitle,change_user_ID = '" + user + "',change_date ='" + changedate() + "' where requests.id = '" + requestid + "'";
                        cmdUpdTitle = new OdbcCommand(qryUpdateTitlte, mysqlCon);
                        SqlParameter titleparam2 = new SqlParameter();
                        titleparam2.ParameterName = "@updateTitle";
                        titleparam2.Value = this.textBox1.Text;
                        cmdUpdTitle.Parameters.Add(titleparam2);
                        cmdUpdTitle.ExecuteNonQuery();

But everytime I execute this I get the following error.

The OdbcParameterCollection only accepts non-null OdbcParameter type objects, not SqlParameter objects.

Is there something wrong with they way I structure my parameters?
How do use SQL parameters if I am using ODBC with SQL driver.

if (pdid != 0)
                    {
                        string qryUpdateTitlte = @"update requests set requests.project_title =@updateTitle,change_user_ID = '" + user + "',change_date ='" + changedate() + "' where requests.id = '" + requestid + "'";
                        cmdUpdTitle = new OdbcCommand(qryUpdateTitlte, mysqlCon);
                        SqlParameter titleparam2 = new SqlParameter();
                        titleparam2.ParameterName = "@updateTitle";
                        titleparam2.Value = this.textBox1.Text;
                        cmdUpdTitle.Parameters.Add(titleparam2);
                        cmdUpdTitle.ExecuteNonQuery();
                        
                        qryUpdateDesc = @"update text_file set text_file.text_field =@projdescription,text_file.change_date = '" + changedate() + "', change_user_id ='" + user + "' where text_file.id = (Select requests.project_description_ID from requests where requests.id = '" + requestid + "')";
                        cmdUpddesc = new OdbcCommand(qryUpdateDesc, mysqlCon);
                        OdbcParameter descparam2 = new OdbcParameter();
                        descparam2.ParameterName = "@projdescription";
                        descparam2.Value = this.textBox2.Text;
                        cmdUpddesc.Parameters.Add(descparam2);
                        cmdUpddesc.ExecuteNonQuery();
                        
 
 
                    }

Open in new window

Avatar of rstomar
rstomar

Use ODBCParameter with ODBCCommand.

SQLParameter is for SQLCommand
You will get much better performance and control if you use the SQL specific data accessor classes located in System.Data.SqlClient then the ODBC ones.
Avatar of wademi

ASKER

HI RSTOMAR I am using ODBCCommand . I tried to use SqlParameter  before but I shanged it back to ODBC afterwards. I am still getting the error
ASKER CERTIFIED SOLUTION
Avatar of rstomar
rstomar

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wademi

ASKER

Thanks rstomar. You are the best.
You are welcome.
Please don't forget to accept the solution.
Thanks