SqlDataAdapter Update is not working in C# wih Sql Server
- by Ahmed
I am trying to save data from C# form to Sql server Northwind Orders database, I am only using CustomerID, OrderDate and ShippedDate for data entry. Following is the code to Form load and save button:
private void Form1_Load(object sender, EventArgs e)
{
SetComb();
connectionString = ConfigurationManager.AppSettings["connectionString"];
sqlConnection = new SqlConnection(connectionString);
String sqlSelect = "Select OrderID, CustomerID, OrderDate, ShippedDate from Orders";
sqlDataMaster = new SqlDataAdapter(sqlSelect, sqlConnection);
sqlConnection.Open();
//===============================================================================
//--- Set up the INSERT Command
//===============================================================================
sInsProcName = "prInsert_Order";
insertcommand = new SqlCommand(sInsProcName, sqlConnection);
insertcommand.CommandType = CommandType.StoredProcedure;
insertcommand.Parameters.Add(new SqlParameter("@nNewID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "OrderID", DataRowVersion.Default, null));
insertcommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
insertcommand.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5,"CustomerID"));
insertcommand.Parameters["@sCustomerID"].Value = cmbCust.SelectedValue;
insertcommand.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8,"OrderDate"));
insertcommand.Parameters["@dtOrderDate"].Value = dtOrdDt.Text;
insertcommand.Parameters.Add(new SqlParameter("@dtShipDate", SqlDbType.DateTime, 8,"ShippedDate"));
insertcommand.Parameters["@dtShipDate"].Value = dtShipDt.Text;
sqlDataMaster.InsertCommand = insertcommand;
//===============================================================================
//--- Set up the UPDATE Command
//===============================================================================
sUpdProcName = "prUpdate_Order";
updatecommand = new SqlCommand(sUpdProcName, sqlConnection);
updatecommand.CommandType = CommandType.StoredProcedure;
updatecommand.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID"));
updatecommand.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8, "OrderDate"));
updatecommand.Parameters.Add(new SqlParameter("@dtShipDate", SqlDbType.DateTime, 8, "ShippedDate"));
sqlDataMaster.UpdateCommand = updatecommand;
//===============================================================================
//--- Set up the DELETE Command
//===============================================================================
sDelProcName = "prDelete_Order";
deletecommand = new SqlCommand(sDelProcName, sqlConnection);
deletecommand.CommandType = CommandType.StoredProcedure;
deletecommand.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID"));
sqlDataMaster.DeleteCommand = deletecommand;
dt = new DataTable();
sqlDataMaster.FillSchema(dt, SchemaType.Source);
ds = new DataSet();
ds.Tables.Add(dt);
bs = new BindingSource();
bs.DataSource = ds.Tables[0];
}
public void SetComb()
{
cmbCust.DataSource = dm.GetData("Select * from Customers order by CompanyName");
cmbCust.DisplayMember = "CompanyName";
cmbCust.ValueMember = "CustomerId";
cmbCust.Text = "";
}
private void btnSave_Click(object sender, EventArgs e)
{
sqlDataMaster.Update((DataTable) bs.DataSource);
}
and Stored Procedures for Insert/Update/Delete
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prInsert_Order]
-- ALTER PROCEDURE prInsert_Order
@sCustomerID CHAR(5),
@dtOrderDate DATETIME,
@dtShipDate DATETIME,
@nNewID INT OUTPUT
AS
SET NOCOUNT ON
INSERT INTO Orders (CustomerID, OrderDate, ShippedDate)
VALUES (@sCustomerID, @dtOrderDate, @dtShipDate)
SELECT @nNewID = SCOPE_IDENTITY()
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prUpdate_Order]
-- ALTER PROCEDURE prUpdate_Order
@nOrderID INT,
@dtOrderDate DATETIME,
@dtShipDate DATETIME
AS
UPDATE Orders
SET OrderDate = @dtOrderDate,
ShippedDate = @dtShipDate
WHERE OrderID = @nOrderID
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prDelete_Order]
-- ALTER PROCEDURE prDelete_Order
@nOrderID INT
AS
DELETE Orders
WHERE OrderID = @nOrderID
In the form CustomerID is selected via combobox which has Display property of CustomerName and Value property of CustomerID.
But when clicking save button it shows no error, but it also don't save anything in Orders Table of Northwind....dm.GetData is the method of my Data Access Layer class to just get the info and populate CustomerID combobox.
Any help with the code is highly appreciated...
Thanks
Ahmed
