Larry

Larry

  • NA
  • 6
  • 7.2k

How to pass a Guid parameter to Stored Proc

Mar 24 2013 5:52 PM
Hi I'm just learning C# and am struggling with a problem and would appreciate any help I can get.

My first problem is this, I have an aspx form that when you hit the save button it inserts data into 2 separate tables, [Transaction and TransactionDetails] both tables contain TransID fields defined as Guids. The Transaction tables Guid is also the primary key. My question is how do I insert the same guid (TransID) which is a uniqueidentifier into both tables.... then at the same time(that's my hope) I have a stored proc that needs to be run that requires that same Guid as a parameter. So in short I need the same GUID in both tables and have to pass the newly created Guid to my stored proc.

Here's my code code so far, can you help me with what I need to add or change to make that happen.

protected void Button1_Click(object sender, EventArgs e)
{

SqlConnection sc = new SqlConnection((System.Configuration.ConfigurationManager.ConnectionStrings["NovaConnectionString"].ConnectionString));

sc.Open();


string insertSQL;
insertSQL = "INSERT INTO [Transaction] (";
insertSQL += "TransactionID, FileNumber, TransactionDate, RegistryID, InstrumentID, TransactionTypeID, CompanyID, ";
insertSQL += "InvoiceNo, Unit, Price, DueDiligance, Legal)";
insertSQL += "VALUES (";
insertSQL += "@TransactionID, @FileNumber, @TransactionDate, @RegistryID, @InstrumentID, @TransactionTypeID, @CompanyID, ";
insertSQL += "@InvoiceNo, @Unit, @Price, @DueDiligance, @Legal)";

//string query1 = String.Format(@"Insert Into Transaction (TransactionID, FileNumber, TransactionDate, RegistryID, InstrumentID, TransactionTypeID, CompanyID, InvoiceNo, Unit, Price, DueDiligance, Legal)"
//+ " VALUES (@TransactionID, @FileNumber, @TransactionDate, @RegistryID, @InstrumentID, @TransactionTypeID, @CompanyID, @InvoiceNo, @Unit, @Price, @DueDiligance, @Legal)");
SqlCommand cmd = new SqlCommand(insertSQL, sc);
SqlCommand cmd3 = new SqlCommand("uspProcessPurchasedOffsets", sc);
cmd3.CommandType = CommandType.StoredProcedure;
//Add the parameters.
cmd.Parameters.AddWithValue("@TransactionID", Guid.NewGuid());
cmd.Parameters.AddWithValue("@FileNumber", txtFileNumber.Text);
cmd.Parameters.AddWithValue("@TransactionDate", txtTransactionCreated.Text);
cmd.Parameters.AddWithValue("@RegistryID", ddlRegistry.Text);
cmd.Parameters.AddWithValue("@InstrumentID", ddlInstrument.Text);
cmd.Parameters.AddWithValue("@TransactionTypeID", txtTransactionTypeID.Text);
cmd.Parameters.AddWithValue("@CompanyID", ddlCompany.Text);
cmd.Parameters.AddWithValue("@InvoiceNo", txtInvoiceNo.Text);
cmd.Parameters.AddWithValue("@Unit", txtTotalVolume.Text);
cmd.Parameters.AddWithValue("@Price", txtPrice.Text);
cmd.Parameters.AddWithValue("@DueDiligance", txtDueDiligance.Text);
cmd.Parameters.AddWithValue("@Legal", txtLegal.Text);

//Parameter for stored Proc

cmd3.Parameters.Add("@TransactionID", SqlDbType.UniqueIdentifier);


cmd.ExecuteNonQuery();

string insertSQL2;

insertSQL2 = "INSERT INTO [TransactionDetails] (StartSerialNumber,VintageYear,Units)";
insertSQL2 += "VALUES (@StartSerialNumber, @VintageYear, @Units)";

SqlCommand cmd1 = new SqlCommand(insertSQL2, sc);


//Add the parameters.
cmd1.Parameters.AddWithValue("@StartSerialNumber", txtStartSerial.Text);
cmd1.Parameters.AddWithValue("@VintageYear", txtVintage.Text);
cmd1.Parameters.AddWithValue("@Units", txtVolume.Text);


cmd1.ExecuteNonQuery();
cmd3.ExecuteNonQuery();

}

}