How to handle a value with an embedded single quote when INSERTing ? 1

[barden9]

I am developing a C# .Net Windows forms application. I execute a Sybase stored procedure from a 3rd party vendor and place the result set
into a SQL Server table.

I get the error "sqlException was unhandled" when the program executes the following statement: comm.ExecuteNonQuery();

Name Value
dataRow["ScannedField2"] "8-10 RUE D'ITALIE"

The problem appears to be a single quote appears between the D and I (as in D'ITALIE) in the value of ScannedField2. This is an address field of type string.

Do you know how I could revise my code below to handle this field with a single quote in it's value ?

_conn2.Open();

_conn.Open();

System.Data.SqlClient.SqlCommand comm = _conn.CreateCommand();

comm.CommandType = System.Data.CommandType.Text;

comm.CommandText = string.Format("DELETE FROM tblWOD");

comm.ExecuteNonQuery();

OdbcCommand command = new OdbcCommand("sp3D_GetDocumentAttributes", _conn2);

command.CommandType = CommandType.StoredProcedure;

OdbcDataReader reader = command.ExecuteReader();

DataTable tblAttributes = new DataTable();

tblAttributes.Load(reader);

foreach (DataRow dataRow in tblAttributes.Rows)

{

comm.CommandText = string.Format("INSERT INTO tblGetDocumentAttributes (MailID,PartyID,ACCOUNT,DocumentDate,scandate,DocumentType,Mailroom_Address1,Mailroom_Address2,
Mailroom_Address3,Mailroom_Address4,Mailroom_Address5) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", dataRow["MailID"], dataRow["Forwarding"], dataRow["AccountNumber"], dataRow["DocumentDate"], dataRow["ScanDate"], dataRow["DocumentType"], dataRow["ScannedField"], dataRow["ScannedField2"], dataRow["ScannedField3"], dataRow["ScannedField4"], dataRow["ScannedField5"]);

comm.ExecuteNonQuery();

}

 

[jmeckley]

the problem is your sql statement. you are using injected sql. use parameterized queries instead. this will negate the issue altogether.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190