SQL Exception Error

Question

Post reply

SQL Exception Error

Sandra Larson

Old Hand

Points: 307
More actions
October 23, 2006 at 11:35 am

#91802

I'm having an issue when executing a stored procedure from a .Net Application. The sp inserts a record into a table on SQL Server.
This application has (and still does) work correctly for all other similar stored procedures. It seems as though on this new form, it doesn't want
to execute. I've since created a test table with only a single field, and an SP for the Select and the Insert for the table. No luck - I'm getting the same message.
I think I'm losing my mind....the SP executes perfectly fine via SQL Query Analyzer
Using VB.Net 2003 and SQL2K
Error Message for SQLException:
?ex.Message
"Line 1: Incorrect syntax near 'InsertMyTestTable'."
?ex.Number
170
?ex.State
1
?ex.Source
".Net SqlClient Data Provider"
?ex.StackTrace
"   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at DataAccess.SQLDataAccess.InsertDatabase(SqlCommand& InsertCommand, SqlCommand& SelectCommand, DataSet ds, String& tableName) in C:\Documents and Settings\Sandi.RPESPUD\My Documents\Visual Studio Projects\Farm_Working\DataAccess\SQLDataAccess.vb:line 72"
Select SP:
CREATE PROCEDURE GetMyTestTable
AS
SET NOCOUNT ON
SELECT RecordID FROM MyTestTable
GO
Insert SP:
CREATE PROCEDURE InsertMyTestTable
(
@RecordID int
)
AS
INSERT INTO MyTestTable (RecordID)
VALUES(@RecordID)
GO
Table Definition:
CREATE TABLE [dbo].[MyTestTable] (
[RecordID] [int] NOT NULL
) ON [PRIMARY]
Thanks in advance for any help.
Sandi Larson

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Your procedures look fine. Can you post your calling code in .NET? That might be where the problem is occuring.

I wasn't born stupid - I had to study.

Paul E SSC Enthusiast Points: 144 More actions · Answer 2

Run SQL Profiler and see what command is actually being executed (or attempted) on the Server. It's easy enough to mishandle a quote, comma, null, etc.

Sandra Larson Old Hand Points: 307 More actions · Answer 3

Thanks for your replies. I've actually solved the problem - I think I deserve the headbanging I was taking after this one...

In my .NET code I neglected to code the line that tells what commandtype is for the Select and Insert commands. Usually I cut and paste from another area, but this time I just coded it.

Thanks again for looking.... Sandi.