asked on

C# Not getting ID number of newly created SQL record

In the following code in my ASP.net C# web app I am trying to retrieve the ID number of a newly created SQL record. Unfortunately it returns a zero at the line I marked "ZERO RETURNED HERE"

try

{

connection.Open();

// Start a local transaction

transaction = connection.BeginTransaction("SampleTransaction");

// Assign the transaction object to the command

commandInsertSalesOrder.Transaction = transaction;

commandInsertSalesOrder.CommandText = oSQL;

DateTime dOrderDate;

if (DateTime.TryParseExact(Order_Date.Text, "yyyy-MM-dd", null, System.Globalization.DateTimeStyles.None, out dOrderDate))

{

commandInsertSalesOrder.Parameters.Add("@OrderDate", SqlDbType.DateTime2).Value = dOrderDate;

}

else

{

commandInsertSalesOrder.Parameters.Add("@OrderDate", SqlDbType.DateTime2).Value = DBNull.Value;

}

commandInsertSalesOrder.Parameters.AddWithValue("@CustomerID", oCustomerID);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipName", oShipName);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipAddress", oShipAddress);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipCity", oShipCity);

commandInsertSalesOrder.Parameters.AddWithValue("@StateProvince", oStateProvince);

commandInsertSalesOrder.Parameters.AddWithValue("@ZipPostal", oZipPostal);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipCountry", oShipCountry);

commandInsertSalesOrder.Parameters.AddWithValue("@Comment", oComment);

commandInsertSalesOrder.Parameters.AddWithValue("@UserEmail", oUserEmail);

int SalesOrderID = Convert.ToInt32(commandInsertSalesOrder.ExecuteScalar()); //ZERO RETURNED HERE

SOLUTION

ullfindsmit

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

David H.H.Lee

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Murray Brown

ASKER

Thanks David but I get the error "{"Must declare the scalar variable \"@INSERT\"."}"

Murray Brown

ASKER

Here is my code. I am sill getting a zero

string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

using (SqlConnection connection = new SqlConnection(cs))

{

SqlCommand commandInsertSalesOrder = new SqlCommand(oSQL, connection);

commandInsertSalesOrder.CommandType = CommandType.StoredProcedure;

commandInsertSalesOrder.CommandText = "InsertSalesOrder";

try

{

connection.Open();

// Start a local transaction

transaction = connection.BeginTransaction("SampleTransaction");

// Assign the transaction object to the command

commandInsertSalesOrder.Transaction = transaction;

DateTime dOrderDate;

if (DateTime.TryParseExact(Order_Date.Text, "yyyy-MM-dd", null, System.Globalization.DateTimeStyles.None, out dOrderDate))

{

commandInsertSalesOrder.Parameters.Add("@OrderDate", SqlDbType.DateTime2).Value = dOrderDate;

}

else

{

commandInsertSalesOrder.Parameters.Add("@OrderDate", SqlDbType.DateTime2).Value = DBNull.Value;

}

commandInsertSalesOrder.Parameters.AddWithValue("@CustomerID", oCustomerID);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipName", oShipName);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipAddress", oShipAddress);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipCity", oShipCity);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipStateProvince", oStateProvince);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipZipPostal", oZipPostal);

commandInsertSalesOrder.Parameters.AddWithValue("@ShipCountry", oShipCountry);

commandInsertSalesOrder.Parameters.AddWithValue("@SupplierID", 0);

commandInsertSalesOrder.Parameters.AddWithValue("@Comment", oComment);

commandInsertSalesOrder.Parameters.AddWithValue("@UserEmail", oUserEmail);

SqlParameter outputParam = commandInsertSalesOrder.Parameters.Add("@ID", SqlDbType.Int);

outputParam.Direction = ParameterDirection.Output;

commandInsertSalesOrder.ExecuteNonQuery();

string id = commandInsertSalesOrder.Parameters["@ID"].Value.ToString();

int SalesOrderID = Convert.ToInt32(commandInsertSalesOrder.ExecuteScalar());

//int SalesOrderID = Convert.ToInt32(commandInsertSalesOrder.ExecuteScalar());

//commandInsertSalesOrder.CommandType = CommandType.StoredProcedure;

//commandInsertSalesOrder.ExecuteNonQuery();

//int SalesOrderID = (int)retval.Value;

//=================================================================================================

//Now read through the staging tables

DataTable SQLDataTable = GetDataTable_Sales_Order_Details_Staging();

foreach (DataRow row in SQLDataTable.Rows)

{

//string name = row["name"].ToString();

decimal quantityOrdered = row["Quantity Ordered"] == DBNull.Value ? 0 : Convert.ToDecimal(row["Quantity Ordered"]);

decimal quantityShipped = row["Quantity Shipped"] == DBNull.Value ? 0 : Convert.ToDecimal(row["Quantity Shipped"]);

string description = row["Description"] == DBNull.Value ? "" : new Conversions().String_to_SQL(row["Description"].ToString());

string partID = row["Part ID"] == DBNull.Value ? "" : row["Part ID"].ToString();

string partNo = row["Part No"] == DBNull.Value ? "" : row["Part No"].ToString();

string unitPrice = row["Unit Price"] == DBNull.Value ? "0" : row["Unit Price"].ToString();

decimal amount = row["Amount"] == DBNull.Value ? 0 : Convert.ToDecimal(row["Amount"]);

int currentStockLevel = row["Current Stock Level"] == DBNull.Value ? 0 : Convert.ToInt32(row["Current Stock Level"]);

int supplierID = row["Supplier ID"] == DBNull.Value ? 0 : Convert.ToInt32(row["Supplier ID"]);

int quantityBackOrder = row["Quantity Back Order"] == DBNull.Value ? 0 : Convert.ToInt32(row["Quantity Back Order"]);

string insertQuery = $@"INSERT INTO [Sales Order Details] ([Sales Order ID], [Part ID], [Quantity], [Unit Price], [Status ID], [Date Allocated], [Purchase Order ID], [Invoice Detail ID], [User Email])

VALUES (@SalesOrderID, @PartID, @QuantityShipped, @UnitPrice, 0, GETDATE(), 0, 0, @UserEmail)";

using (SqlCommand insertCommand = new SqlCommand(insertQuery, connection))

{

// Assign the transaction object to the insertCommand

insertCommand.Transaction = transaction;

insertCommand.Parameters.AddWithValue("@SalesOrderID", SalesOrderID);

insertCommand.Parameters.AddWithValue("@PartID", partID);

insertCommand.Parameters.AddWithValue("@QuantityShipped", quantityShipped);

insertCommand.Parameters.AddWithValue("@UnitPrice", unitPrice);

insertCommand.Parameters.AddWithValue("@UserEmail", oUserEmail);

insertCommand.ExecuteNonQuery();

}

// Commit the transaction if all operations are successful

transaction.Commit();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

Response.Write(ex.Message);

// Rollback the transaction if there's an exception

try

{

transaction.Rollback("SampleTransaction");

}

catch (SqlException ex2)

{

if (transaction.Connection != null)

{

Console.WriteLine("An exception of type " + ex2.GetType() +

" was encountered while attempting to roll back the transaction.");

}

SOLUTION

Kyle Abrahams, PMP

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Murray Brown

ASKER

H i Kyle. Thanks. I modified my code to that below and still get a zero returned

//TRANSACT ROLLBACK

string userEmail = Label_UserEmail.Text;

string insertSQL = $@"INSERT INTO [Sales Order Details] ([Sales Order ID], [Part ID], [Quantity], [Unit Price], [Status ID], [Date Allocated], [Purchase Order ID], [Invoice Detail ID], [User Email])

VALUES (@SalesOrderID, @PartID, @QuantityShipped, @UnitPrice, 0, GETDATE(), 0, 0, @UserEmail); select scope_identity()";

SqlTransaction transaction = null;

string cs = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

using (SqlConnection connection = new SqlConnection(cs))

{

SqlCommand commandInsertSalesOrder = new SqlCommand(insertSQL, connection);