Private Sub btnUserFeedbackDone_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUserFeedbackDone.Click
Dim cnn As SqlConnection = New SqlConnection(strConnection)
Dim strError As String
With cmdUserFeedbackAdd
.Connection = cnn
.Parameters("@UserEntry").Value = Me.txtUserFeedback.Text
.Parameters("@FirmID").Value = CInt(Session("FirmID"))
.Parameters("@UserName").Value = Session("Username")
End With
cnn.Open()
Try
cmdUserFeedbackAdd.ExecuteNonQuery()
Catch ex As Exception
strError = ex.Message
End Try
cnn.Close()
It's getting the following error: Incorrect syntax near '4'.
Here's the command object:
Me.cmdUserFeedbackAdd = New System.Data.SqlClient.SqlCommand
Protected WithEvents cmdUserFeedbackAdd As System.Data.SqlClient.SqlCommand
Here's the store procedure (it works in Sql Server):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[procUserFeedbackAdd]
@UserEntry nvarchar(500)
,@FirmID int
,@UserName nvarchar(20)
AS
INSERT INTO [dbo].[UserFeedback]
(
[UserEntry]
,[FirmID]
,[UserName]
)
VALUES
(
@UserEntry
,@FirmID
,@UserName
)
Also refer the following links: for the adding parameters with value
http://www.codeissue.com/issues/i24ddbf06082b65/difference-between-command-parameter-add-and-addwithvalue
I suggest you trying to another way——you don't need to specify the length for each of the Parameter here，and you can put Close in the Finally——that means no matter any exception is thrown out，you can always close the connection.
Private Sub btnUserFeedbackDone_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUserFeedbackDone.Click
Dim cnn As SqlConnection = New SqlConnection(strConnection)
Me.cmdUserFeedbackAdd.CommandText = "dbo.[procUserFeedbackAdd]"
Me.cmdUserFeedbackAdd.CommandText = System.Data.CommandType.StoredProcedure
Dim strError As String
With cmdUserFeedbackAdd
.Connection = cnn
.Parameters.AddWithValue("@UserEntry", Me.txtUserFeedback.Text)
.Parameters.AddWithValue("@FirmID", Session("FirmID").ToString())
.Parameters.AddWithValue("@UserName",Session("Username").ToString())
.Connection.Open()
End With
Try
cmdUserFeedbackAdd.ExecuteNonQuery()
Catch ex As Exception
strError = ex.Message
Finally
cnn.Close()
End Try
mmmtbig
Member
17 Points
23 Posts
ExecuteNonQuery() getting error: why?
Nov 24, 2012 05:12 PM|LINK
Private Sub btnUserFeedbackDone_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUserFeedbackDone.Click Dim cnn As SqlConnection = New SqlConnection(strConnection) Dim strError As String With cmdUserFeedbackAdd .Connection = cnn .Parameters("@UserEntry").Value = Me.txtUserFeedback.Text .Parameters("@FirmID").Value = CInt(Session("FirmID")) .Parameters("@UserName").Value = Session("Username") End With cnn.Open() Try cmdUserFeedbackAdd.ExecuteNonQuery() Catch ex As Exception strError = ex.Message End Try cnn.Close()It's getting the following error: Incorrect syntax near '4'.
Me.cmdUserFeedbackAdd.CommandText = "dbo.[procUserFeedbackAdd]" Me.cmdUserFeedbackAdd.CommandText = System.Data.CommandType.StoredProcedure Me.cmdUserFeedbackAdd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserEntry", System.Data.SqlDbType.NVarChar, 500)) Me.cmdUserFeedbackAdd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FirmID", System.Data.SqlDbType.Int, 4)) Me.cmdUserFeedbackAdd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserName", System.Data.SqlDbType.NVarChar, 20))Here's the store procedure (it works in Sql Server):
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[procUserFeedbackAdd] @UserEntry nvarchar(500) ,@FirmID int ,@UserName nvarchar(20) AS INSERT INTO [dbo].[UserFeedback] ( [UserEntry] ,[FirmID] ,[UserName] ) VALUES ( @UserEntry ,@FirmID ,@UserName )chaaraan
Contributor
2170 Points
484 Posts
Re: ExecuteNonQuery() getting error: why?
Nov 25, 2012 04:21 AM|LINK
Hi
Change the following lines and check :
Me.cmdUserFeedbackAdd.CommandText = "dbo.[procUserFeedbackAdd]"
Me.cmdUserFeedbackAdd.CommandText = System.Data.CommandType.StoredProcedure Me.cmdUserFeedbackAdd.Parameters.Add("@UserEntry", System.Data.SqlDbType.NVarChar) Me.cmdUserFeedbackAdd.Parameters.Add("@FirmID", System.Data.SqlDbType.Int) Me.cmdUserFeedbackAdd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar)
Also refer the following links: for the adding parameters with value
http://www.codeissue.com/issues/i24ddbf06082b65/difference-between-command-parameter-add-and-addwithvalue
Regards,
Charan
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: ExecuteNonQuery() getting error: why?
Nov 25, 2012 06:14 AM|LINK
Hello，
I suggest you trying to another way——you don't need to specify the length for each of the Parameter here，and you can put Close in the Finally——that means no matter any exception is thrown out，you can always close the connection.
Private Sub btnUserFeedbackDone_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUserFeedbackDone.Click Dim cnn As SqlConnection = New SqlConnection(strConnection) Me.cmdUserFeedbackAdd.CommandText = "dbo.[procUserFeedbackAdd]" Me.cmdUserFeedbackAdd.CommandText = System.Data.CommandType.StoredProcedure Dim strError As String With cmdUserFeedbackAdd .Connection = cnn .Parameters.AddWithValue("@UserEntry", Me.txtUserFeedback.Text) .Parameters.AddWithValue("@FirmID", Session("FirmID").ToString()) .Parameters.AddWithValue("@UserName",Session("Username").ToString()) .Connection.Open() End With Try cmdUserFeedbackAdd.ExecuteNonQuery() Catch ex As Exception strError = ex.Message Finally cnn.Close() End Trymmmtbig
Member
17 Points
23 Posts
Re: ExecuteNonQuery() getting error: why?
Nov 25, 2012 02:01 PM|LINK
Still getting the error. Here's the error message detail:
System.Data.SqlClient.SqlException was caught Class=15 ErrorCode=-2146232060 LineNumber=1 Message="Incorrect syntax near '4'." Number=102 Procedure="" Server="TBIG3" Source=".Net SqlClient Data Provider" State=1 StackTrace: 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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SpecBookProOnline2.Main.btnUserFeedbackDone_Click(Object sender, EventArgs e) in C:\Users\mmmyers3\Documents\Visual Studio 2005\Web Apps\SBPO2\SpecBookProOnline2\Main.aspx.vb:line 999mmmtbig
Member
17 Points
23 Posts
Re: ExecuteNonQuery() getting error: why?
Nov 25, 2012 04:53 PM|LINK
Chaaraan,
Your response led me to the problem.
This line
Me.cmdUserFeedbackAdd.CommandText = System.Data.CommandType.StoredProcedure
should be
Me.cmdUserFeedbackAdd.CommandType = System.Data.CommandType.StoredProcedure