المساعد الشخصي الرقمي

مشاهدة النسخة كاملة : Modify a Dataset then apply dataset to sql server... What am I doing wrong Please?



C# Programming
09-30-2009, 12:32 PM
Can Someone please help me. I am trying to learn the use of datasets....
I can easily grab the data without a dataset from this table and apply the changes back to the sql server. But when I use the dataset I can only retrieve the data from sql server. Everytime I try doing the following C# Code to update the data in a sql server table stored in a dataset then apply the dataset back to sql server. The Environment comes back to me and complains.

Here Is the error I keep getting:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.







private void btnOptionSaveChanges_Click(object sender, EventArgs e)
{
//http://support.microsoft.com/kb/307587
//http://bytes.com/topic/c-sharp/answers/251248-reading-dataset
//http://support.microsoft.com/kb/307587
SQLObjects MySQLObjects = new SQLObjects();

if (StandardProcedures.SQL_TestConnection(WhatIsMyConnectionString) == false)
{
MessageBox.Show("There is an issue with the application's connection string or ability to communicate with SQL Server! No Changes will be made.");
//Environment.Exit(-1);
return;
}
if (!StandardProcedures.SQL_CheckToSeeIfTheObjectAlreadyExistsInSQLServer(WhatIsMyConnectionString, MySQLObjects.Table, "tblSoftwareControls"))
{
MessageBox.Show("tblSoftwareControls table does not exist in the Database. No Changes will be made");
//Environment.Exit(-1);
return;
}
//string connString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
//string sql = @"select top 1 * from tblSoftwareControls";
//string sql = @"SELECT CompanyName, CopyRight, ConnectionString, SplashScreenTimer, SplashScreenImage, VersionOverride FROM tblSoftwareControls";
string sql = @"SELECT * FROM tblSoftwareControls";

SqlConnection conn = new SqlConnection(WhatIsMyConnectionString);

try
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source, "tblSoftwareControls");
da.Fill(ds, "tblSoftwareControls");
//**************************************

//Update the dataset
DataTable dt = ds.Tables["tblSoftwareControls"];
DataRow dr;
if (dt.Rows.Count > 0)
{
dr = dt.Rows[0];
dr.BeginEdit();
dr["ConnectionString"] = txtApplicationConnectionString.Text;
dr["SplashScreenTimer"] = txtSplashScreenTimer.Text;
dr["VersionOverride"] = ckbxOverrideVersionByPass.Checked;
dr.EndEdit();
StandardProcedures.DebugLogWriter("Updated the record in tblSoftwareControls!");
}
else
{
dr = dt.NewRow();
dr["ConnectionString"] = txtApplicationConnectionString.Text;
dr["SplashScreenTimer"] = txtSplashScreenTimer.Text;
dr["VersionOverride"] = ckbxOverrideVersionByPass.Checked;

//Pass the new object into the add method of the datatable
dt.Rows.Add(dr);
StandardProcedures.DebugLogWriter("Wrote a new record to tblSoftwareControls!");
}
//**************************************

//Update the sql database by sending it the data in the dataset.
// BEGIN SEND CHANGES TO SQL SERVER

SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(da);
da.Update(ds, "tblSoftwareControls");
StandardProcedures.DebugLogWriter("DataSet modifications or additions or deletions has the modifications applied to the SQL Server Table.");
// END SEND CHANGES TO SQL SERVER




/*

foreach (DataRow dr in dt.Rows)
{
txtApplicationConnectionString.Text = dr["ConnectionString"].ToString();
txtSplashScreenTimer.Text = dr["SplashScreenTimer"].ToString();
ckbxOverrideVersionByPass.Checked = StandardProcedures.Convert_TrueFalse(dr["VersionOverride"].ToString());
}
DataTable dt = ds.Tables["tblSoftwareControls"];
txtApplicationConnectionString.Text = dt.Columns["ConnectionString"].;
txtSplashScreenTimer.Text = dt.Columns["SplashScreenTimer"].;
ckbxOverrideVersionByPass.Checked = StandardProcedures.Convert_TrueFalse(dt.Columns["VersionOverride"]);
*/
}
catch (Exception ex)
{
StandardProcedures.ErrorLogWriter("Trying to populate the Options Tab: " + ex.ToString());
}
finally
{
conn.Close();
}
}