JS List
asked on
SQL Insert Parameters.AddWithValue Error: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ' '.
Hello,
Have a web page that will insert a new record into a SQL db. Coming up with this error:
I tried doing an insert with 1 parameter. Same error. It must be something else.
The aspx page has textboxes & 1 dropdown.
Here's the aspx.vb page
Have a web page that will insert a new record into a SQL db. Coming up with this error:
"System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ' '. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at line 73 ClientConnectionId:f07651ef-aa93-47f6-8461-10497529a466"
Line 73 is cmd.ExecuteNonQuery()I tried doing an insert with 1 parameter. Same error. It must be something else.
The aspx page has textboxes & 1 dropdown.
Here's the aspx.vb page
Imports ExecNonQuerySQL
Protected Sub btnNext_Click(sender As Object, e As System.EventArgs) Handles btnNext.Click
Dim today As Date
today = FormatDateTime(Now, DateFormat.ShortDate)
Dim strSQL As String
Dim thisManual As String = "Xmanual"
Dim quant As Integer
strSQL = "INSERT INTO ManualOrders "
strSQL = strSQL + "(FirstName, LastName, Business, Address1, Address2, City, Zip, [State], Phone, Email, Quantity, ManualType, OrderDate) "
strSQL = strSQL + "Values(@FirstName, @LastName, @Business, @Address1, @Address2, @City, @State, @Zip, @Phone, @Email, @Quantity, @ManualType, @OrderDate)"
Dim con As SqlConnection
con = addData()
Using con
Using cmd As New SqlCommand(strSQL, con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@FirstName", Trim(fname.Text))
cmd.Parameters.AddWithValue("@LastName", Trim(lname.Text))
cmd.Parameters.AddWithValue("@Business", Trim(business.Text))
cmd.Parameters.AddWithValue("@Address1", Trim(address1.Text))
cmd.Parameters.AddWithValue("@Address2", Trim(address2.Text))
cmd.Parameters.AddWithValue("@City", Trim(city.Text))
cmd.Parameters.AddWithValue("@State", Trim(state.Text))
cmd.Parameters.AddWithValue("@Zip", Trim(zip.Text))
cmd.Parameters.AddWithValue("@Phone", Trim(phone.Text))
cmd.Parameters.AddWithValue("@Email", Trim(email.Text))
cmd.Parameters.AddWithValue("@Quantity", quant)
cmd.Parameters.AddWithValue("@ManualType", thisManual)
cmd.Parameters.AddWithValue("@OrderDate", today)
Try
con.Open()
cmd.ExecuteNonQuery()
lbl1.Text = "<font color='red'>Success</font> <br>" & strSQL & "<p>"
Catch ex As SqlException
lbl2.Text = "<div class='text'>" & strSQL & "<br>" & ex.ToString & "</div>"
lbl1.Text = "<font color='red'>So Sorry.</font><p>"
Finally
con.Close()
End Try
End Using
End Using
Here's the class:Public Class ExecNonQuerySQL
Public Shared Function addData() As SqlConnection
Dim con As New SqlConnection(GetConnectionString())
Return con
End Function
Private Shared Function GetConnectionString() As String
Dim returnValue As String = Nothing
Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("thisconnection")
returnValue = settings.ConnectionString
Return returnValue
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The values are there - being submitted. I switched it around and tried inserting the values directly without AddWithValue
Pulled the textbox values before and assigned them to the variables in the string.
strSQL = "INSERT INTO Orders "
strSQL = strSQL + "(FirstName, LastName, Business, Address1, Address2, City, [State], Zip, Phone, Email, Quantity, ManualType, OrderDate) "
strSQL = strSQL + "VALUES ('" & fnamex & "', '" & lnamex & "', '" & busx & "', '" & addr1x & "', '" & addr2x & "', '" & cityx & "', '" & statex & "', '" & zipx & "', '" & phonex & "', '" & emailx & "', " & quant & ", '" & thisManual & "', '" & today & "' )"
recordsInserted = InsertRecord(strSQL)
InsertRecord class =
Public Shared Function InsertRecord(ByVal strSQL As String) As String
Try
Dim con As New SqlConnection(GetConnectio nString())
Dim cmd As New SqlCommand(strSQL, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
con = Nothing
Return 1
Catch ex As Exception
Return ex.ToString
End Try
End Function
Could there be a setting on the sql server?
Could it be that with the InsertRecord returns a string and that's making it blow?
I think it's in data connection class. ExecNonQuerySQL
I have to be able to get records in. Should I try a stored procedure?
Pulled the textbox values before and assigned them to the variables in the string.
strSQL = "INSERT INTO Orders "
strSQL = strSQL + "(FirstName, LastName, Business, Address1, Address2, City, [State], Zip, Phone, Email, Quantity, ManualType, OrderDate) "
strSQL = strSQL + "VALUES ('" & fnamex & "', '" & lnamex & "', '" & busx & "', '" & addr1x & "', '" & addr2x & "', '" & cityx & "', '" & statex & "', '" & zipx & "', '" & phonex & "', '" & emailx & "', " & quant & ", '" & thisManual & "', '" & today & "' )"
recordsInserted = InsertRecord(strSQL)
InsertRecord class =
Public Shared Function InsertRecord(ByVal strSQL As String) As String
Try
Dim con As New SqlConnection(GetConnectio
Dim cmd As New SqlCommand(strSQL, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
con = Nothing
Return 1
Catch ex As Exception
Return ex.ToString
End Try
End Function
Could there be a setting on the sql server?
Could it be that with the InsertRecord returns a string and that's making it blow?
I think it's in data connection class. ExecNonQuerySQL
I have to be able to get records in. Should I try a stored procedure?
Is there a trigger on that table?
ASKER
No
Did a test to do an insert with values defined in it.
INSERT INTO Orders Values ("JS", "LName", ....)
And it worked.
So as stated above it's something in the insert string. So going to insert not with addwithvalue but taking the values and testing them before insert.
Thanks for the help
JS
Did a test to do an insert with values defined in it.
INSERT INTO Orders Values ("JS", "LName", ....)
And it worked.
So as stated above it's something in the insert string. So going to insert not with addwithvalue but taking the values and testing them before insert.
Thanks for the help
JS
I normally use a stored procedure and when I use AddWithValue I don't use the @ sign in front of the variable name. I use it in the stored procedure. For example my stored procedure variable @NetAmount is inserted like this:
If Not String.IsNullOrEmpty(MB_Da ta.NetAmou nt) Then
SQLCmd.Parameters.AddWithV alue("NetA mount", SqlDecimal.Parse(MB_Data.N etAmount))
Else
SQLCmd.Parameters.AddWithV alue("NetA mount", DBNull.Value)
End If
But I have seen it both ways.
If Not String.IsNullOrEmpty(MB_Da
SQLCmd.Parameters.AddWithV
Else
SQLCmd.Parameters.AddWithV
End If
But I have seen it both ways.
One thing, you have your State & Fields in different orders between your insert columns and values. But that shouldn't be causing your error.
strSQL = strSQL + "(FirstName, LastName, Business, Address1, Address2, City, Zip, [State], Phone, Email, Quantity, ManualType, OrderDate) "
strSQL = strSQL + "Values(@FirstName, @LastName, @Business, @Address1, @Address2, @City, @State, @Zip, @Phone, @Email, @Quantity, @ManualType, @OrderDate)"