469,625 Members | 1,714 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

SQL Exception near keyword 'Procedure'

I am trying to call a Stored Prodecure in my code, and I keep getting
the same error...

system.data.sqlclient.sqlexception: incorrect syntax near the keyword
'Procedure'
I have added my stored procedure as a .sql file and have verified that
it is written correctly in SQL Sever 2005. Below is that stored
procedure...

CREATE PROCEDURE spInsertModels
@ModelName nvarchar(50),
@Photo image,
@PhotoPath nvarchar(255),
@Active bit
AS
INSERT INTO "dbo"."Models" (ModelName, Photo, PhotoPath, Active)
VALUES (@ModelName, @Photo, @PhotoPath, @Active)
The next couple of methods are written in VB.NET 2005 and are shown
below...

'This is the first method called
Private Sub UpdateOrInsertModelsInDB(ByVal changedData As DataTable)
Try
Dim oRow As DataRow
Dim smallIntActive As Int16

For Each oRow In changedData.Rows
smallIntActive = oRow("Active")
If smallIntActive = vbYes Then
smallIntActive = -1
End If
Dim cmdCommand As New SqlCommand
If oRow.RowState = DataRowState.Added Then
'THIS IS WHAT IS CALLED....
AddModelInsertUpdateParameters(cmdCommand, oRow,
smallIntActive, False)
ExecuteInsertSPWithParams(cmdCommand)
Else
AddModelInsertUpdateParameters(cmdCommand, oRow,
smallIntActive, True)
ExecuteUpdateSPWithParams(cmdCommand)
End If
Next
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub AddModelInsertUpdateParameters(ByRef cmdCommand As
SqlCommand, ByVal orow As DataRow, ByVal smallIntActive As Int16, ByVal
update As Boolean)
Try
Dim sqlParam As New SqlParameter

If update Then
sqlParam = cmdCommand.Parameters.Add("@ModelID",
SqlDbType.Int)
sqlParam.Value = orow("ModelID")
End If

sqlParam = cmdCommand.Parameters.Add("@ModelName",
SqlDbType.NVarChar, 50)
sqlParam.Value = orow("ModelName")

sqlParam = cmdCommand.Parameters.Add("@Photo",
SqlDbType.Image)
sqlParam.Value = orow("Photo")

sqlParam = cmdCommand.Parameters.Add("@PhotoPath",
SqlDbType.NVarChar, 255)
sqlParam.Value = orow("PhotoPath")

sqlParam = cmdCommand.Parameters.Add("@Active",
SqlDbType.Bit)
sqlParam.Value = smallIntActive
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub ExecuteInsertSPWithParams(ByVal cmdCommand As
SqlCommand)
Try
Using conn As New SqlConnection(dbClass.connectionString)
conn.Open()

cmdCommand.Connection = conn
cmdCommand.CommandText =
my.Resources.DatabaseCreationScripts.spInsertModel s
cmdCommand.ExecuteNonQuery()

conn.Close()
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Any Ideas on why I am getting this error?

Jul 5 '06 #1
1 1078
Have you added the stored procedure to the database?

What does my.Resources.DatabaseCreationScripts.spInsertModel s contain?
da*******@gmail.com wrote:
I am trying to call a Stored Prodecure in my code, and I keep getting
the same error...

system.data.sqlclient.sqlexception: incorrect syntax near the keyword
'Procedure'
I have added my stored procedure as a .sql file and have verified that
it is written correctly in SQL Sever 2005. Below is that stored
procedure...

CREATE PROCEDURE spInsertModels
@ModelName nvarchar(50),
@Photo image,
@PhotoPath nvarchar(255),
@Active bit
AS
INSERT INTO "dbo"."Models" (ModelName, Photo, PhotoPath, Active)
VALUES (@ModelName, @Photo, @PhotoPath, @Active)
The next couple of methods are written in VB.NET 2005 and are shown
below...

'This is the first method called
Private Sub UpdateOrInsertModelsInDB(ByVal changedData As DataTable)
Try
Dim oRow As DataRow
Dim smallIntActive As Int16

For Each oRow In changedData.Rows
smallIntActive = oRow("Active")
If smallIntActive = vbYes Then
smallIntActive = -1
End If
Dim cmdCommand As New SqlCommand
If oRow.RowState = DataRowState.Added Then
'THIS IS WHAT IS CALLED....
AddModelInsertUpdateParameters(cmdCommand, oRow,
smallIntActive, False)
ExecuteInsertSPWithParams(cmdCommand)
Else
AddModelInsertUpdateParameters(cmdCommand, oRow,
smallIntActive, True)
ExecuteUpdateSPWithParams(cmdCommand)
End If
Next
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub AddModelInsertUpdateParameters(ByRef cmdCommand As
SqlCommand, ByVal orow As DataRow, ByVal smallIntActive As Int16, ByVal
update As Boolean)
Try
Dim sqlParam As New SqlParameter

If update Then
sqlParam = cmdCommand.Parameters.Add("@ModelID",
SqlDbType.Int)
sqlParam.Value = orow("ModelID")
End If

sqlParam = cmdCommand.Parameters.Add("@ModelName",
SqlDbType.NVarChar, 50)
sqlParam.Value = orow("ModelName")

sqlParam = cmdCommand.Parameters.Add("@Photo",
SqlDbType.Image)
sqlParam.Value = orow("Photo")

sqlParam = cmdCommand.Parameters.Add("@PhotoPath",
SqlDbType.NVarChar, 255)
sqlParam.Value = orow("PhotoPath")

sqlParam = cmdCommand.Parameters.Add("@Active",
SqlDbType.Bit)
sqlParam.Value = smallIntActive
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub ExecuteInsertSPWithParams(ByVal cmdCommand As
SqlCommand)
Try
Using conn As New SqlConnection(dbClass.connectionString)
conn.Open()

cmdCommand.Connection = conn
cmdCommand.CommandText =
my.Resources.DatabaseCreationScripts.spInsertModel s
cmdCommand.ExecuteNonQuery()

conn.Close()
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Any Ideas on why I am getting this error?
Jul 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.