syntax error in t-sql stored procedure

Question

syntax error in t-sql stored procedure

Ben Bastiaensen

SSC Rookie

Points: 32
More actions
February 10, 2005 at 8:18 am

#162640

When i check the syntax off the following stored procedure all is well:
CREATE procedure sp_Insert
@gebruiker nvarchar(10),
@pwd nvarchar(15)
as
insert tblUsers(userid, pasword, FKGroupID, userCreation, timestampCreation)
values(@gebruiker, @pwd, 1, 'sa', getdate());
GO
but when i want to use this sp via a simple VB.Net app i get an error.
VB_code
Dim
objCn As SqlConnection = New SqlConnection(connStr)
Dim objCmd As SqlCommand = New SqlCommand("sp_Insert", objCn)
objCmd.Parameters.Add("@gebruiker", SqlDbType.NVarChar, 10)
objCmd.Parameters("@gebruiker").Value = txtUserid.Text
objCmd.Parameters.Add("@pwd", SqlDbType.VarChar, 15)
objCmd.Parameters("@pwd").Value = txtPwd.Text
objCn.Open()
Try
objCmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message & "-" & ex.StackTrace)
Finally
objCn.Close()
End Try
the error code says:
incorrect syntax near 'sp_Insert'
even though the syntax checker of sql server says everythings ok i get that error. Anyone any idea what the cause could be.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Charles Otten Default port Points: 1475 More actions · Answer 1

Without too much knowledge of VB.Net it looks like you might need to include the owner of sp_insert. But in VB.6 you need to identify the type of command the object is referencing. I believe it is SQL text so you would need to include the exec command. If you indicate it is a stored procedure this should work.

Just a guess.

If the phone doesn't ring...It's me.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

Also, reconsider the stored proc name, in particular the sp_ prefix.

See: http://www.sql-server-performance.com/vk_sql_best_practices.asp

Do not prefix your stored procedure names with "sp_". The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the "sp_" prefix.

John Jakob SSCommitted Points: 1720 More actions · Answer 3

To investigate the problem further, I would run SQL Profiler and see what syntax is being issued from VB.NET.

Extract the line with the Transact SQL command from profiler and try executing it manually in Query Analyzer.

Does it work?

- john

Ben Bastiaensen SSC Rookie Points: 32 More actions · Answer 4

Thankx for the great advices. As soon as i specifically put the commandtype of the command object to stored procedure the problem was solved.