Hi,

during a SQL Insert, how do I retrieve the value of the new columns ID? I've tried three methods and all come up with a value of zero. There must be somethign wrong with my code.... :S

Here is method 1)

SqlConnection dbCon = new SqlConnection(Config.ConnectionString);
            // Open connection
            dbCon.Open();

            // Build sql query.
            string strInsertQuery = "INSERT INTO shop_Order ";
            strInsertQuery += "(CustomerID, Status, OrderDate, CardNumber, ExpMonth, ExpYear, CVMValue, Comments) VALUES ";
            strInsertQuery += "(@CustomerID, @Status, @OrderDate, @CardNumber, @ExpMonth, @ExpYear, @CVMValue, @Comments); SELECT @@IDENTITY";


            SqlCommand oCmd = new SqlCommand(strInsertQuery, dbCon);

            orderID = Convert.ToInt32(oCmd.ExecuteScalar());

Method 2)

//create sql connections
            SqlConnection dbCon = new SqlConnection(Config.ConnectionString);

            SqlDataAdapter dbAdapt = new SqlDataAdapter("select * from " + thisTableName, dbCon);

            // We need this to get an ID back from the database
            dbAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            SqlCommandBuilder dbCB = new SqlCommandBuilder(dbAdapt);

            // Open Connection
            dbCon.Open();

            // New DataSet
            DataSet dbSet = new DataSet();

            // Populate DataSet with data
            dbAdapt.Fill(dbSet, thisTableName);

                // Get reference to our table
            DataTable dbTable = dbSet.Tables[thisTableName];

            // Create new row
            DataRow dbRow = dbTable.NewRow();

            // Store order data in the row
            dbRow["CustomerID"] = h["CustomerID"];

            // Add row back to table
            dbTable.Rows.Add(dbRow);

            // Update data source
            dbAdapt.Update(dbSet, thisTableName);

            // Get newFileID
            if(!dbRow.IsNull("ID") )
                orderID = (int)dbRow["ID"];

            // Return new order ID
            return orderID;

And method 3)

            //build sql query
            string SQLText = "INSERT INTO shop_Order ";
            SQLText += "(CustomerID, Status, OrderDate, CardNumber, ExpMonth, ExpYear, CVMValue, Comments) VALUES ";
            SQLText += "(@CustomerID, @Status, @OrderDate, @CardNumber, @ExpMonth, @ExpYear, @CVMValue, @Comments)";


            SqlCommand oCmd = new SqlCommand(SQLText, dbCon);


            //populate sql parameters
            oCmd.Parameters.Add("@CustomerID", h["CustomerID"]);
            oCmd.Parameters.Add("@Status", h["Status"]);
            oCmd.Parameters.Add("@OrderDate", DateTime.Now.ToShortDateString());
            oCmd.Parameters.Add("@CardNumber", h["CardNumber"]);
            oCmd.Parameters.Add("@ExpMonth", h["ExpMonth"]);
            oCmd.Parameters.Add("@ExpYear", h["ExpYear"]);
            oCmd.Parameters.Add("@CVMValue", h["CVMValue"]);
            oCmd.Parameters.Add("@Comments", h["Comments"]);

            SqlParameter sParam;
            sParam = oCmd.Parameters.Add(new SqlParameter("@productid",SqlDbType.Int));
            sParam.Direction = ParameterDirection.Output;



            //commit changes
            dbCon.Open();
            oCmd.ExecuteNonQuery();

Do you see any problems? Thanks!!!

To get the value, best is to use method two, as it syncs with the database. I haven't used the SQL command builder, but it should work... I've had to create table and column mappings though through the TableMappings and ColumnMappings objects. I don't know if that is the case...

Brian

Create A Strored procedure in SQL

Create Procedure ProcInsertOrder
@CustomerID int,
@Status varchar(20),
--Declare all of your parameters here
@OrderID int Output
As
Begin

INSERT INTO shop_Order (CustomerID, Status, OrderDate, CardNumber, ExpMonth, ExpYear, CVMValue, Comments) VALUES
(@CustomerID, @Status, @OrderDate, @CardNumber, @ExpMonth, @ExpYear, @CVMValue, @Comments)

Declare @OrderID int
SET @orderID = SCOPE_IDENTITY()


End


ASP.net code

Dim objConn AS NEw SQLConnection(ConfigurationSettings.Appsettings("C onnectionString")

Dim objCmd As New SqlCommand()
With objCmd
.Connection = objConn
.CommandText = "ProcInsertOrder"
.CommandType = CommandType.StroedProcedure
End With

'Add all of your input parameters here

MyParam = objCmd.Parameters.Add( New
SqlParameter( "@OrderID", SqlDbType.integer, 4 ))
myParam.Direction = ParameterDirection.Output

objCmd.ExecuteNonQuery()

Response.Write( "The last Order ID is " & ObjCmd.Parameters( "@OrderID" ).Value )

Brian, thanks again for your response. I agree; besides using a stored proc method 2 does seem the best option. It returns the a value of 0 (zero) even though the new row is added to the database and the function completes sucessfully. i put a watch on the varible orderID and it remains at 0 during debug.

shahchi1 - I ran your stored proc and its gave me this error:

Server: Msg 137, Level 15, State 2, Procedure ProcInsertOrder, Line 10
Must declare the variable '@OrderDate'.
Server: Msg 134, Level 15, State 1, Procedure ProcInsertOrder, Line 13
The variable name '@OrderID' has already been declared. Variable names must be unique within a query batch or stored procedure.

Have you declared all your parameters in stroed procedure ( I can't becasue I do not know what datatypes you are using) That's error message...read on creating stored procedure in SQL server books online......

Create Procedure ProcInsertOrder
@CustomerID int,
@Status varchar(20),
--Declare all of your parameters here
@OrderID int Output
As
Begin

INSERT INTO shop_Order (CustomerID, Status, OrderDate, CardNumber, ExpMonth, ExpYear, CVMValue, Comments) VALUES
(@CustomerID, @Status, @OrderDate, @CardNumber, @ExpMonth, @ExpYear, @CVMValue, @Comments)


SET @OrderID = SCOPE_IDENTITY()

End
Go

yeeees you're right. sometimes it pays to read comments.