Simple SQL Server Puzzler

[BruceG]

I am in the process of teaching myself SQL Server, using the desktop edition of SQL Server 7 on a Win 98 platform. I am using the Wrox book "Beginning VB SQL Server 7" by Thearon Willis. I have been working thru the examples in the book and have had no problem whatsoever up to this point, and I am baffled by this problem.

I'm on Chapter 7, "Introduction to Stored Procedures", which has us writing basic stored procedures to insert, update, and delete data from a table of States. These SP's are then executed from a sample VB program. The insert and update procedures work beautifully, but the delete procedure produces the following error when executed from VB:

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '#'
('#' is the value of the parameter I'm passing it, which is the state_ID primary key field (an integer))

The State_T table has the following fields:
State_ID (int, PK, ID)
State_Name_VC (varchar(15))
State_Abbreviation_CH (char(2))
Last_Update_DT (datetime)

The contents of the stored procedure looks like this:

CREATE PROCEDURE up_parmdel_state_name (@State_ID Int) AS

DELETE FROM State_T
WHERE State_ID = @State_ID

This SP passes the syntax checks in Enterprise Manager and Query Analyzer, but the above-mentioned error occurs when VB executes the following statements:

' build the SQL string
strSQL = "BruceG.up_parmdel_state_name (" & lngStateID & ")"

'Delete the state data
objConn.Execute strSQL


lngStateID definitely has a good value coming into this function, and objConn is the same connection used for the insert and update procedures, which work fine. Also, the same permissions that were assigned to insert and update SPs were assigned to the delete SP.

What's more, I downloaded the author's code from the Wrox website (which is the same as what's in the book, which I am keying the code from; the only difference is the owner name ("willist" vs. "BruceG")) and it produces the same error.

This is what should be a simple example and I am totally stumped. Any ideas?

[Bigley]

I can't see anything that is obviously wrong with it. Why don't you try to execute the delete command without using the SP and then you will be able to see what end the error is at i.e.

Set your SqlStr to

DELETE FROM State_T WHERE State_ID = & lngStateId

As a matter of interest I have never used a prefix for a database object like you are doing with BruceG but I am sure Wrox knows what it is doing.

[Bigley]

And a further thought, I would not use the brackets either when passing a value to an SP, why not try leaving them out as another option.

[Bigley]

Yeh it's me again I just did a little test here on the brackets thinh and I recreated that error of yours so it's probably that....change your Strsql to

strSQL = "BruceG.up_parmdel_state_name " & lngStateID

We got there in the end

[BruceG]

It works!!! Thanks much, Bigley!

Now, I'm off to submit this problem to Wrox's Errata department ...

[BruceG]

Hold the phone. It would appear that the problem was actually caused by not having "DELETE" permission on State_T table. When I first removed the parentheses as Bigley suggested, the error message changed to indicate that I did not have DELETE permission on the State_T table. I fixed this in Enterprise Manager, then re-ran the VB program. At this point it worked and that's when I made my last post.

However, I was bothered by the fact that the other SPs had no problem when passing parameters in parentheses and this one supposedly did. So just for a gag, I put the parentheses back and re-ran - and - it worked!

So the real problem was that SQL Server threw me off by giving me an "incorrect syntax" error when it should have reported the "permission" problem in the first place.

Any ideas as to why SQL Server behaved as it did with regard to the error message?