Re: Problem returning identity from SQL Server when string contains semicolon

I'm writing a record from an asp.net page to SQL Server. After the insert
I'm selecting @@identity to return the ID of the record that I just wrote.
It worked fine until I typed a semicolon into one of the string fields to be
inserted. The string fields are inside single quotes in the INSERT command.
With the semicolon in the string, the record is written correctly including
the semicolon, but the identity is not returned. Without a semicolon in the
string, the identity is returned correctly. The code is below, any
suggestions would be appreciated. The field with the semicolon is
ContractNumber.  Thanks.

        lsSQL = "INSERT INTO tblVPContracts (NonContracted, ContractNumber,
POReqNumber, ForInfoOnly, ImmediateActionReq, ModifiedBy) VALUES (" _
        + NonContracted.ToString + ", '" + tbContractNum.Text + "', '" +
TextBox1.Text + "', " + ForInfoOnly.ToString + ", " +
ImmediateAction.ToString + ", " + UserID.ToString + " )"
        Dim MyCommand As SqlCommand = New SqlCommand(lsSQL, conn)
        MyCommand.ExecuteNonQuery()
        Dim sSelect As String = "SELECT @@IDENTITY as NewID"
        Dim DataSet As New DataSet
        Dim adapter As New SqlDataAdapter
        adapter.SelectCommand = New SqlCommand(sSelect, conn)
        adapter.Fill(DataSet, "Identity")
        cookie.Values.Add("VPContractID",
DataSet.Tables("Identity").Rows(0)(0))


If that Contract Number contained a single quote ('), then your sql statement would fail. Look up "SQL Injection Attack".
So use Parameters to pass those values! See MSDN for details.
This might also solve your semicolon problem.

Second remark: instead of filling an entire dataset with that single identity value, use ExecuteScalar (and cast the "object" result to an integer). This will return just the first column in the first row of the first resultset.

Hans Kesting


.

Relevant Pages

  • Re: EXCEL: How to scan text reversed (like ACCESS: InStrRev)?
    ... Not sure whether this will help, but you could use this array - ... to return each character as an array element. ... specific instance of a character in a string. ... this formula gives you the number of semicolon in the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Compensating for Bad Data
    ... I have all the string manipulation down. ... Dim rs As DAO.Recordset ... I need to leave the value alone if the two sides of the semicolon are ... compare the value before and after the semicolon-space ...
    (microsoft.public.access.externaldata)
  • Re: Compensating for Bad Data
    ... user started an answer with a "; ") where my string manipulation was flawed. ... dim v2 as variant ... I need to leave the value alone if the two sides of the semicolon ... compare the value before and after the ...
    (microsoft.public.access.externaldata)
  • Re: Semicolon problem
    ... Dim sStr As String ... For Each cell In Selection ... If ans = vbYes Then ... > Sorry guys, when i created the thread i wanted to make the wrong> semicolon BOLD, but instead it added a * at both sides of the ...
    (microsoft.public.excel.programming)
  • Re: Yet another syntax issue.
    ... Why are you enclosing in single quotes ' if you pass this string value as a ... Dim cnn As ADODB.Connection ... Dim strJcn As String ... Set prm = cmd.CreateParameter("@Jcn", adBSTR, adParamInput,, ...
    (microsoft.public.access.adp.sqlserver)