help needed with SQL

[purplehaze1]

Is it required to have quotes around field values while doing insert/update/delete?
e.g.
INSERT INTO t_customer (cust_cde, cust_name, m_ind, start_dte, lst_upd_id)
VALUES ('452','Tata Young','S','2/4/2005','sResh12')

I receive following err msg if there's no quotes around field values (in this case if there's no quotes around lst_upd_id):

The name 'sResh12' is illegal in this context.
Only constants, constant expressions, or variables allowed here. Column names are illegal


In the following program, when I try to put aposhtrophe in dr("m_ind") field which is char(1), get error when datarow is added to ds.tables(0).rows.add(dr),

err msg:

Cannot set column 'nmdp_ind' to "N".
The value violates the MaxLength limit of this column.

Also, none of the date fields has apostrophes.
Is there way round this problem?


Public Sub SaveCustomer()
...
...

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds, "t_customer")
...
...
dr("cust_cde") = PrepareStr(m_custCode)
dr("cust_name") = PrepareStr(IIf(m_custName = String.Empty, "", m_custName))
dr("m_ind") = PrepareStr(IIf(m_NMDPInd = String.Empty, "S", m_NMDPInd))
dr("start_dte") = PrepareStr(Date.Today)
dr("lst_updt_id") = IIf(Login.m_UserName = String.Empty, String.Empty, Login.m_UserName)

If IsNew Then
ds.Tables(0).Rows.Add(dr)
End If
End Sub


Public Function PrepareStr(ByVal strValue As String) As String
If InStr(strValue.Trim(), "'") Then
Dim ReplacedValue As String = Replace(strValue.Trim(), "'", "''")
Return "'" & ReplacedValue.Trim() & "'"
Else : Return "'" & strValue.Trim() & "'"
End If
End Function

 

[chiph]

You'll want to use database Parameter objects (like SqlParameter or OdbcParameter).

This has been covered numerous times here, so a search ought to turn up something fairly easily.

BTW: doubling up single-quotes leaves you open to a sql injection attack. Don't do it.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first