Single quote in text box problem 3

[mattbold]

hello,

if when i fill in a text box on my page i put a single quote in, when it comes to running the SQL to update my database with the value of the text box i get an error because of the single quote, how do i go about fixing this?

my SQL curently looks like this:
"UPDATE tblDJ SET Area = '" & ddlArea.SelectedIndex & "', Telephone = '" & txtTelephone.Text & "', Years = '" & txtYears.Text & "', ProffYears = '" & txtProffYears.Text & "', CV = '" & txtCV.Text & "', Updated = #" & Now() & "#, MinPay = '" & txtMinWage.Text & "', Available = 1 where userID = " & Session("userid&quot

and if for example i have a ' in the txtCV text box i get the error.

any help would be greatly appreciated!

cheers ,
matt

 

[blueark]

Replace (txtCV, "'", "''&quot

 

[mattbold]

cool thanks, next question is what do i do if i want to enter a word with a single quote in it, or a name that has one in it?

- matt

 

[blueark]

Same thing. Basically what you're doing is replacing ' with '', which SQL interprets as a single quote within a string, rather that the start or end of a string.

 

[chiph]

mattbold -

This is a common problem when concatenating SQL together. Try changing your code to this:

StringBuilder MySQL = new StringBuilder();

MySQL.Append(" UPDATE tblDJ");
MySQL.Append("   SET Area = @Area,");
MySQL.Append("   Telephone = @Telephone,");
MySQL.Append("   Years = @Years,");
MySQL.Append("   ProffYears = &ProffYears,");
MySQL.Append("   CV = @CV,");
MySQL.Append("   Updated = @Updated,");
MySQL.Append("   MinPay = @MinPay,");
MySQL.Append("   Available = @Available,");
MySQL.Append(" WHERE userID = @UserID");

SqlConnection MyConn = new SqlConnection("connect string here");
SqlCommand MyComm = new SqlCommand();
MyComm.CommandType = CommandType.Text;
MyComm.CommandText = MySQL.ToString();
MyComm.Parameters.Add("@Area", SqlDbType.NVarChar, 50).Value = ddlArea.SelectedIndex;
MyComm.Parameters.Add("@Telephone", SqlDbType.NVarChar, 20).Value = txtTelephone.Text;
MyComm.Parameters.Add("@Years", SqlDbType.TinyInt).Value = Convert.ToByte(txtYears.Text);
MyComm.Parameters.Add("@ProffYears", SqlDbType.TinyInt).Value = Convert.ToByte(txtProffYears.Text );
MyComm.Parameters.Add("@CV", SqlDbType.NVarChar, 2000).Value = txtCV.Text;
MyComm.Parameters.Add("@Updated", SqlDbType.DateTime).Value = DateTime.Now();
MyComm.Parameters.Add("@MinPay", SqlDbType.NVarChar, 50).Value = txtMinWage.Text;
MyComm.Parameters.Add("@Available", SqlDbType.TinyInt).Value = 1;
MyComm.Parameters.Add("@UserID", SqlDbType.NVarChar, 50).Value = Session("userid");

MyComm.Connection = MyConn;
MyConn.Open();
MyComm.ExecuteNonQuery();
MyConn.Close();
MyConn.Dispose();

Note that you'll need to change the database datatypes & columne lengths to match your specific database.

While this looks like a lot of code, what it does for you is:
1) No more string replacements for quote characters - the ADO.NET Parameter obect takes care of that for you
2) By using replacable parameters, the database is able to place this query into it's procedure cache without having to go through the auto-parameterization process, which means that not only will this query run faster, it'll run even faster the second time you call it.
3) The CPU load will be lower on your database server because it has less work to do.
4) This allows you to isolate your database code from your user interface code by allowing you to create a method that accepts parameters - which helps in code reuse.

Hope this helps.

Chip H.

 

[Isadore]

chip: I thought that was well put - quiet an encouraging approach - will definitly take a look, thanks.