"" in sql statemtent

[sjn78]

I used to just make my query like this:

 

sql= "SELECT * FROM Contacts WHERE FirstName = '" & txtFirstName.Text & "'"

 

unitl I discovered that is the persons name had a ' in it, it didnt work.

 

I then changed it to

 

sql= "SELECT * FROM Contacts WHERE FirstName = """ & txtFirstName.Text & """

 

and that works.....for VB anyway.

 

How do I get this to work in C#? The first one works with the = '", but how do I use " in the string.

 

Im using an Access database by the way.

[Joe Mamma]

People. . . use parameters and all your problems go away!!!

 

your code is (for the most part) sql flavor agnostic.

Immune to wierd characters in the strings.

Immune to different date text formats.

 

 

this query will work against SQL, ORACLE, ACCESS, Sybase, DB2, Informix, Interbase:

 

OleDBConnection conn = new OleDBConnection( [some connection string] );
conn.Open();

// note, no need to worry as to how the DB delimits strings or formats dates!!!
OleDbCommand cmd = new OleDbCommand("Select * from MyTable where Column1 = ? and Column2 = ? and aDateColumn = ?", conn);

// note, these two parameters have the same value. . . 
cmd.Parameters[0].value = "any old value you want whether it has a ' in it or a \\ or \"");
cmd.Parameters[1].value = @"any old value you want whether it has a ' in it or a \ or """);

// note, this parameter has a date value, no need to format 
cmd.Parameters[2].value = DateTime.Now

 

Let the OLEDB Provider do the work for you!!!

 

with apologies to Socrates,

 

'the unparameterized query is not worth preparing'

[Mothra]

People. . . use parameters and all your problems go away!!!

 

I second that!! POWER TO THE THE PARAMETERS!!!!

[sjn78]

sql = "SELECT * FROM Contacts WHERE " + 
@"FirstName = """ + e.Node.Text.Substring(0, e.Node.Text.IndexOf(" ")) + @""" AND " +
@"LastName = """ + e.Node.Text.Substring(e.Node.Text.IndexOf(" ") + 1) + @"""";

 

Dont need parameters.....although I did look into them.

 

I put in odd characters into the records and it works fine.

[Joe Mamma]

Dont need parameters.....although I did look into them.

 

I put in odd characters into the records and it works fine.

Yes but it is extrememly poor practice!

It could be the difference bwtween getting a job and not.

 

Did I mention it is a security risk???

read the section labeled 'SQL Injection' in this link.

Secure Multi-tier Deployment

 

as a matter of fact, this whitepaper is a must read:

SQL Server 2000 SP3 Security Features and Best Practices

[sjn78]

Nice links.

 

Although I didn't doubt what you had said, for the purpose of what I was doing, it was overkill.

 

But I will look into this a lot deeper when the need arises.

[VBAHole22]

Not too muck up old news but....

 

I realize parameters are important but look at what you are doing in your command

 

OleDbCommand cmd = new OleDbCommand("Select * from MyTable where Column1 = ? and Column2 = ? and aDateColumn = ?", conn);

// note, these two parameters have the same value. . . 
cmd.Parameters[0].value = "any old value you want whether it has a ' in it or a \\ or \"");
cmd.Parameters[1].value = @"any old value you want whether it has a ' in it or a \ or """);

 

You have 2 different parameters that you are refering to as '?' and '?'. Doesn't that seem odd to anyone else?

 

What order are these parameters placed into the query? How do you know which will be in what slot? Is it the order you add them? The order you set them?

 

For the record I haven't had any luck using the '?' in Access queries. All I get is a '?' where the parameter is supposed to be.

Must be missing something here.....

[VBAHole22]

Here is an example about what I am talking about.

 

Dim objconn As New OleDb.OleDbConnection(strConn)
       Dim strSQL As String = ""
       Dim objcmd As New OleDb.OleDbCommand(strSQL, objconn)
       Dim P_path As New OleDbParameter
       Dim P_file As New OleDbParameter

With objcmd
               .CommandType = CommandType.Text
               .Parameters.Add(P_path)
               .Parameters.Add(P_file)
End With

PName = File.FullName 'path name
FName = File.Name     'file name

               'Create Parameters
               P_path.ParameterName = "@PName"
               P_path.DbType = DbType.String
               P_path.Direction = ParameterDirection.Input
               P_path.Value = PName

               'Create Parameters
               P_file.ParameterName = "@FName"
               P_file.DbType = DbType.String
               P_file.Direction = ParameterDirection.Input
               P_file.Value = FName

'This parameter is meaningless------------
objcmd.Parameters.Add("@CustomerID", DbType.String, 5).Value = "8"

'The following 3 commands have the exact same result on the db

'objcmd.CommandText = "INSERT INTO " & strTable & " (MAP_PATH, MAP_NUMBER) VALUES (? , @CustomerID)"

'objcmd.CommandText = "INSERT INTO " & strTable & " (MAP_PATH, MAP_NUMBER) VALUES (? , ?)"

objcmd.CommandText = "INSERT INTO " & strTable & " (MAP_PATH, MAP_NUMBER) VALUES (@PName , @FName)"

'This will NEVER output your parameters for you to see
Console.WriteLine(objcmd.CommandText.ToString)

objcmd.ExecuteNonQuery()

 

I was so busy trying to see my parameters in the debug output that I didn't realize that the query was working. You can't get the param values at runtime.

Furthermore, it's misleading to name your parameters in the query string because the name has no impact at all. It appears that it only cares about the order they are added to the command.

 

This whole arrangement is a great arguement for using stored procedures, but is that wise at the Access level?

 

Couldn't they have come up with a better way to do this like when you use place holders like {1} and such.

 

Just my 2 cents :rolleyes:

[HJB417]

Nice links.

 

Although I didn't doubt what you had said, for the purpose of what I was doing, it was overkill.

 

But I will look into this a lot deeper when the need arises.

 

It's good practice. Only .net noobs create dynamic sql using string concatenation and I have no respect for those that do.

[HJB417]

Not too muck up old news but....

 

I realize parameters are important but look at what you are doing in your command

 

OleDbCommand cmd = new OleDbCommand("Select * from MyTable where Column1 = ? and Column2 = ? and aDateColumn = ?", conn);

// note, these two parameters have the same value. . . 
cmd.Parameters[0].value = "any old value you want whether it has a ' in it or a \\ or \"");
cmd.Parameters[1].value = @"any old value you want whether it has a ' in it or a \ or """);

 

You have 2 different parameters that you are refering to as '?' and '?'. Doesn't that seem odd to anyone else?

 

What order are these parameters placed into the query? How do you know which will be in what slot? Is it the order you add them? The order you set them?

 

For the record I haven't had any luck using the '?' in Access queries. All I get is a '?' where the parameter is supposed to be.

Must be missing something here.....

 

Then use named parameters.

[HJB417]

You can't get the param values at runtime.

 

Wrong.

 

foreach(System.Data.IDataParameter param in objcmd.Parameters)
Console.WriteLine("{0}='{1}'", param.ParameterName, param.Value);

[VBAHole22]

Then use named parameters.

 

This is the point I was trying to get across. You can name the parameters anything you want to. As far as I can tell, it DOES NOT matter.

 

If your SQL has 2 parameters

- you can add 3,000 params

-you can name them anything you want.

 

When you write that SQL it doesn't matter if you use ? or the parameter names

 

YOU WILL ALWAYS GET THE FIRST 2 PARAMETERS YOU ADDED.

 

 

By the way, i hope that I am dead wrong about this.