Stored Procedure

[Gary75]

Hi

The code below seems to fail on Executing the query!

Any ideas?

Gary


SP:
CREATE PROCEDURE [spInsertOrder]

(@customerID int,
@theidentity int OUTPUT
)

AS
INSERT INTO [costingSystem].[dbo].[Orders]
( [customerID],
[dateOrdered])

VALUES
( @customerID,
getdate())

Select theidentity = @@identity
GO


CODE:
Dim prmCustomerID As New SqlParameter()
Dim prmTheIdentity As New SqlParameter()

Me.Cursor = System.Windows.Forms.Cursors.WaitCursor()

objConn = New SqlConnection(myGlobals.Name())
objConn.Open()
Try
With prmCustomerID
.Direction = ParameterDirection.Input
.SqlDbType = SqlDbType.Int
.ParameterName = "@customerID"
.Value = cbCustomerID.Text
End With

With prmTheIdentity
.Direction = ParameterDirection.Output
.SqlDbType = SqlDbType.Int
.ParameterName = "@theIdentity"
End With

objCmd = New SqlCommand("spInsertOrder", objConn)

With objCmd
.Parameters.Add(prmCustomerID)
.Parameters.Add(prmTheIdentity)
.ExecuteNonQuery()
End With
MessageBox.Show("The newest Identity value is: " & prmTheIdentity.Value.ToString)
Catch sqlEx As SqlException
MessageBox.Show(sqlEx.ToString)
Finally
objConn.Close()
objConn.Dispose()
End Try

Me.Cursor = System.Windows.Forms.Cursors.Default()

[Borry]

What error do you get ?

[Gary75]

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'spInsertOrder' at System.Data.SqlClient.SqlCommand.ExecuteReader(Command Behavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
line 525!

There is a wee bit more but i think I captured the best bits!

Thanks

Gary

[Borry]

What returns cbCustomerID.Text

I suppose this is a combobox ? Does it return a value ?

[Gary75]

Yes I tested it, it returns either 1, 2 or 3 dependant on selection!

Thanks

Gary

[Borry]

Leave out once the output parameter in you SP and in your code... what happens then ?

[Gary75]

I get the same error?!?!?

[Borry]

you're sure you did comment out this : .Parameters.Add(prmTheIdentity)
?

[Gary75]

yip!

[Borry]

Maybe you can your sqlprofiler then you can check which query's been given towards sqlserver

HTH