Sql – Getting odd error on .net ExecuteNonQuery

netsql server

I'm working in .NET with SQL server on the backend

I have a database that I create a record in using a web control – then I need to update some of the fields.
I can trap the sql statement and run it in sql server successfully – however, when I try to run execute non-query I get the following error:

Unhandled Execution Error
Incorrect syntax near '<'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at TestAPI.UpdateTicketValues(String srId) in D:\Webs\Internal\veritythree.com\SupportBeta\TestAPI.ascx.vb:line 216
at TestAPI.Submit_Click(Object sender, EventArgs e) in D:\Webs\Internal\veritythree.com\SupportBeta\TestAPI.ascx.vb:line 170
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) 0.517567748943243 0.511543

Here is my function:

Public Function UpdateTicketValues(ByVal srId As String) As Boolean
    Dim result As Boolean
    Dim myCDataReader As System.Data.SqlClient.SqlDataReader
    Dim myUConn As New System.Data.SqlClient.SqlConnection
    Dim myCCmd As New System.Data.SqlClient.SqlCommand
    Dim myUCmd As New System.Data.SqlClient.SqlCommand
    Dim strSQL As String

    strSQL = "SELECT Contact_RecId, First_Name, Last_Name, PhoneNbr, Extension, Email FROM vti_ContactInformation " & _
             "WHERE Company_RecId = " & CoId & " AND Email = '" & txtEmail.Text & "'"
    myCConn.Open()
    myUConn = New System.Data.SqlClient.SqlConnection("Data Source=x;Initial Catalog=x;User Id=x;Password=x;Trusted_Connection=False")
    myUConn.Open()
    myCCmd.Connection = myCConn
    myCCmd.CommandText = strSQL
    myCDataReader = myCCmd.ExecuteReader
    If myCDataReader.Read() Then
        'Run update with contact information
        strSQL = "UPDATE SR_Service " & _
                 "SET Contact_RecId = " & myCDataReader.GetValue(0) & ", " & _
                 "    Contact_Name = '" & myCDataReader.GetValue(1) & " " & myCDataReader.GetValue(2) & "', " & _
                 "    PhoneNbr = '" & myCDataReader.GetValue(3) & "', " & _
                 "    Extension = '" & myCDataReader.GetValue(4) & "', " & _
                 "    Email_Address = '" & myCDataReader.GetValue(5) & "' " & _
                 "WHERE SR_Service_RecId = " & srId & " "
        myUCmd.Connection = myUConn
        myUCmd.CommandText = strSQL
        'myCCmd.ExecuteNonQuery()
        lblServiceRequest.Text = myUCmd.CommandText
        result = True
    Else
        myUCmd.CommandText = ""
        result = False
    End If
    If myUCmd.CommandText <> "" Then
        myUCmd.ExecuteNonQuery()
    End If
    myCConn.Close()
    myUConn.Close()
    Return result
End Function

Any help is appreciated!

Best Answer

before i even look where the error might be i suggest you stop immediatly what your doing and first go change all sql code to use parameters. if you don't your site will be open to sql injection attacks that can destroy your database.

to find out where the problem is run profiler and check the stmt:starting and stmt:completed events.

Related Topic