Solved

SQL Insert Parameters.AddWithValue  Error: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ' '.

Posted on 2014-03-11
7
2,742 Views
Last Modified: 2014-03-11
Hello,
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"

Open in new window

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

Open in new window

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

Open in new window

0
Comment
Question by:jshesek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39921008
From what I can see there, everything seems to be exactly as it should be ...
it must be something "stupid", a small detail.
looking & listening
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39921036
Put in a breakpoint and verify the strSQL value that you are sending.

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)"
0
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 250 total points
ID: 39921048
Also put a break on line 36 and hover over all of you variables and make sure their values are what you expect before you continue.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:jshesek
ID: 39921261
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(GetConnectionString())
            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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39921406
Is there a trigger on that table?
0
 
LVL 1

Author Comment

by:jshesek
ID: 39921475
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
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39921612
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_Data.NetAmount) Then
                SQLCmd.Parameters.AddWithValue("NetAmount", SqlDecimal.Parse(MB_Data.NetAmount))
            Else
                SQLCmd.Parameters.AddWithValue("NetAmount", DBNull.Value)
            End If

But I have seen it both ways.
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question