Problem with Insert Into

[primagic]

I have the following stored procedure to insert records into two tables:

USE [PhoenixSQL]
GO
/****** Object:  StoredProcedure [dbo].[AddOrder]    Script Date: 04/01/2011 12:20:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddOrder]
(
@TraderID int,
@OrderNumber nvarchar(50),
@InvoiceNumber nvarchar(50),
@InvoiceDate datetime,
@Carriage decimal(18,2),
@OrderID int
)
AS
BEGIN

SET NOCOUNT ON;


INSERT INTO dbo.Orders (TraderID, OrderNumber, InvoiceNumber, InvoiceDate, Carriage)
VALUES (@TraderID, @OrderNumber, @InvoiceNumber, @InvoiceDate, @Carriage)
SELECT @OrderID = scope_identity()

INSERT INTO dbo.OrderDetails (OrderID)
VALUES (@OrderID)

END

I also have the following code on a codebehind page.

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("PhoenixSQLConnectionString").ConnectionString
        Dim con As New SqlConnection(ConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "AddOrder"
        cmd.Parameters.Add("@TraderID", SqlDbType.VarChar).Value = HiddenTraderID.Value.Trim()
        cmd.Parameters.Add("@OrderNumber", SqlDbType.VarChar).Value = txtOrderNumber.Text.Trim()
        cmd.Parameters.Add("@InvoiceNumber", SqlDbType.VarChar).Value = txtInvoiceNumber.Text.Trim()
        cmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = txtInvoiceDate.Text.Trim()
        cmd.Parameters.Add("@Carriage", SqlDbType.Decimal).Value = txtCarriage.Text.Trim()

        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteScalar()


        Catch ex As Exception
            Throw ex
        Finally
            con.Close()
            con.Dispose()
        End Try


    End Sub

I get the error:

Procedure or function 'AddOrder' expects parameter '@OrderID', which was not supplied.

I know I need to put the parameter @OrderID, how do I declare the parameter that will be the @@identity

cmd.Parameters.Add("@OrderID", SqlDbType.Int).Value = ??

 

[gmmastros]

Are you trying to return this value back to your front end?

According to the code, you appear to be using this value within the procedure and that's it.

I would suggest something like this:

LTER PROCEDURE [dbo].[AddOrder]
(
@TraderID int,
@OrderNumber nvarchar(50),
@InvoiceNumber nvarchar(50),
@InvoiceDate datetime,
@Carriage decimal(18,2)
)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @OrderID Int

INSERT INTO dbo.Orders (TraderID, OrderNumber, InvoiceNumber, InvoiceDate, Carriage)
VALUES (@TraderID, @OrderNumber, @InvoiceNumber, @InvoiceDate, @Carriage)
SELECT @OrderID = scope_identity()

INSERT INTO dbo.OrderDetails (OrderID)
VALUES (@OrderID)

Select @OrderId As OrderId

END

Basically, remove it from the parameter list and simply select it out.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[primagic]

I want to use the last inserted id to the dbrders table and insert that value into the dbrderDetails table.

 

[gmmastros]

I understand. The code I posted will do that.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[primagic]

Great. Works

 

[primagic]

I got one other question.

I have added a couple of other fields that need to be inserted into dbrderDetails

And one of them is a Product.

The user will select a product from a dropdownlist which has two columns. First column is the productid and the second column is the text value. I have it set so the productid is the value to parse.

However I am getting the error "Input string was not in a correct format". The datatype on the table is int. So i thought if it is pulling the productid which is a number then it should work? Do I need to convert it or something?

 

[primagic]

worked it out. selecteditem.value rather than .text