SqlStr

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dim strSQL As String = "SELECT CUSTNMBR, CNTCPRSN FROM Customers WHERE CUSTNAME = '" + cmbCustomer.Text + "'"

if cmbCustomer.Text is something like Marc's Computer Co. my SqlStr will not work. I tried Dim strSQL As String = "SELECT CUSTNMBR, CNTCPRSN FROM Customers WHERE CUSTNAME = ' + cmbCustomer.Text + '"

....but that didn't work.

Any ideas how to overcome or is a data cleansing exercise required.?
 
marcmc said:
Dim strSQL As String = "SELECT CUSTNMBR, CNTCPRSN FROM Customers
WHERE CUSTNAME = '" + cmbCustomer.Text + "'"

if cmbCustomer.Text is something like Marc's Computer Co. my SqlStr
will not work. I tried Dim strSQL As String = "SELECT CUSTNMBR,
CNTCPRSN FROM Customers WHERE CUSTNAME = ' + cmbCustomer.Text + '"

...but that didn't work.

Any ideas how to overcome or is a data cleansing exercise required.?
Click to expand...

Yes - use parameters instead, and set the values without trying to
escape them or anything. The driver will handle all the formatting.

See SqlCeParameter for more information.
 
You have to escape the single quote.

strSQL = strSQL.Replace("'", "''")

-Chris


marcmc said:
Dim strSQL As String = "SELECT CUSTNMBR, CNTCPRSN FROM Customers WHERE
Click to expand...
CUSTNAME = '" + cmbCustomer.Text + "'"
if cmbCustomer.Text is something like Marc's Computer Co. my SqlStr will
Click to expand...
not work. I tried Dim strSQL As String = "SELECT CUSTNMBR, CNTCPRSN FROM
Customers WHERE CUSTNAME = ' + cmbCustomer.Text + '"
 
marcmc said:
thx Jon tried but...

Dim myParameter As New SqlCeParameter
myParameter.ParameterName = cmbCustomer.Text
myParameter.IsNullable = True
myParameter.SqlDbType = SqlDbType.NVarChar
myParameter.Size = 10

MessageBox.Show(myParameter.ToString)

Dim strSQL As String = "SELECT CUSTNMBR, CNTCPRSN FROM Customers
WHERE CUSTNAME = '" + myParameter.ToString + "'"

no joy...what i doin wrong?
Click to expand...

You're completely bypassing the main purpose of parameters. The idea is
that you put something like

Dim strSQL as String =
"SELECT CUSTNMBR, CNTCPRSN FROM Customers WHERE CUSTNAME = ?"

and then add parameters to the command that you create from the SQL.

If you look at SqlCeCommand.Parameters in MSDN, there's a reasonable
example.
 
Back
Top