vb vs c# variables in sql query strings [reform my question]

[Rafone]

Greetings; What is the best way with C#.net 2005 or 2008 to ... I have a db that I only want to get info from (no updates) where the user's pick an ID number, start date and end date for the query. I would not know ahead of time what variables they choose. So what is the best way to programatically form the query? below is how we have always done it before. In VB I have some query's that use variables they are setup this way. I first added the single quotes i.e. I added sigle quotes i.e. ptID = "'" & ptID & "'" then added " & ptID & " this gave me dbl,single quote variablename singl,dbl quote. All worked well in VB. In C# I am trying the same thing SQL server requires the single quote so I am still getting the point with ptID = "'" + ptID + "'" then later " + ptID + ". All works fine on the development box but on the test box I get errors that indicate that it is this formatting that is the problem. So in C# what is the correct way to add in variables to the strSQL string. Am I doing it correctly now?? tia Rafone Statistics are like bikini's... What they reveal is astonishing ... But what they hide is vital ...

modified on Tuesday, September 23, 2008 12:42 PM

[N a v a n e e t h]

Use parametrized queries. Your code is open to SQL injection.

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia How to use google | Ask smart questions

[Nirandas]

What is the exact error you getting? Also constructing sql commands this way is not recommended. Instead use something like following: SqlCommand cmd =new SqlCommand("select * from tablename where id=@id",connection); cmd.Parameters.AddWithValue("@id",3); cmd.ExecuteReader(); //or whatever you want.

Checkout my blog at http://www.nirandas.com/blog/

[Rafone]

System.Exception: Incorrect syntax near '12'. Rafone

Statistics are like bikini's... What they reveal is astonishing ... But what they hide is vital ...

[Nirandas]

Paste the code you are using.

Nirandas, a developer from India. http://www.nirandas.com

[Rafone]

here ya go. As I said this all worked fine in VB.net. Here is a typical query string // strSQL = "SELECT dbo.tblFloat.PointID AS ID, dbo.tblFloat.UTCDateTime, dbo.tblFloat.ActualValue FROM dbo.tblFloat WHERE (dbo.tblFloat.PointID = " + ptID + ")" it is really just 1 line I have broken it up to make it easer to read. I have found this in the ms knowledgebase SqlConnection sqlConn; SqlCommand sqlCommand; String sQuery; sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (@p1, @p2, @p3)"; SqlParameter p1 = new SqlParameter("@p1", this.txtID.Text); SqlParameter p2 = new SqlParameter("@p2", this.cmbType.Text); SqlParameter p3 = new SqlParameter("@p3", this.txtName.Text); Is this the "new" way of doing this? Would I add, SqlParameter p1 = new SqlParameter("@ptID", ptID); Where ptID is the variable that I am trying to pass. and use that in my Where clause? if so is there any where you can point me to get more info on doing this? I have lots of queries in the old VB system that will have to be upgraded along with the upgrade to C#. thx in advance for any help Rafone Statistics are like bikini's... What they reveal is astonishing ... But what they hide is vital ...

modified on Tuesday, September 23, 2008 12:17 PM

[cpkilekofp]

Rafone wrote:

Is this the "new" way of doing this?

No, unless by new you mean "less than ten years old"...ADODB supports parameterized queries and parameters for stored procedures, and has ever since I started using it (around 1998). It's also been known for at least that long that building query strings using the method you've been working with is unsafe in Web applications due to the possibility of a SQL injection attack. Additionally, this technique is prone to the very error you are now experiencing. Except for an ad-hoc query engine, I would not use the old technique. Whoever the VB programmer was, he shouldn't have been allowed to keep his queries in this form. As to resources, try searching for "SQL parameters" or "parameterized queries" in Google or Live Search and see what comes back.

[Rafone]

Thanks to all who replied. I found the error. my bad the code in VB had dbl quote then single quote. Apparently that has worked fine for years. In fact it worked fine on my development box. However once the code was starting through the conversion and being run on a test box we starting seeing the error. I finally realized the the syntax should be single quote then dbl quote it all works fine. This is a large scale project and these apps that I am upgrading only query for data in the Intranet. There is no writing back to the database. Thanks for the info. I was really asking for some "best practices" info from you guys...thanks again Rafone

Statistics are like bikini's... What they reveal is astonishing ... But what they hide is vital ...