Updating SQL database, Please help
 

Can someone please help with the following problem I am trying to update a sql database through a command builder but I keep getting an sql exception (Additional information: System error.). I dont know where I am going wrong because I thought the command builder should carry out the update automatically.

the table on the sql database is called 'MovieTable1' and the dataset is called 'MovSet1'. please see the code I am using below....any help would be appreciated because this is now giving me a headache.

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;



namespace MovieDiary

{

        /// <summary>

        /// Summary description for Form1.

        /// </summary>

        public class Form1 : System.Windows.Forms.Form

        {

                private System.Windows.Forms.DataGrid dataGrid1;

                private System.Windows.Forms.Button button1;

                /// <summary>

                /// Required designer variable.

                /// </summary>

                private System.ComponentModel.Container components = null;

        

                public static SqlConnection Mcon = new SqlConnection("Server=SHERMAN2;Database =MDatabase; data source=\"SH" +

                        "ERMAN2\\SQLEXPRESS\"");



                        

                        public static SqlDataAdapter MovAdapt = new SqlDataAdapter("SELECT * FROM MovTable1", Mcon);

                        SqlCommandBuilder cb = new SqlCommandBuilder(MovAdapt);

                                         DataTableMapping myNewMapping =

                        new DataTableMapping("MovMap","MovTable1");

                public static DataSet Movset1 = new DataSet();

        

                public Form1()

                {

                        //

                        // Required for Windows Form Designer support

                        //

                        InitializeComponent();



                        //

                        // TODO: Add any constructor code after InitializeComponent call

                        //

                }



                /// <summary>

                /// Clean up any resources being used.

                /// </summary>

                protected override void Dispose( bool disposing )

                {

                        if( disposing )

                        {

                                if (components != null) 

                                {

                                        components.Dispose();

                                }

                        }

                        base.Dispose( disposing );

                }



                #region Windows Form Designer generated code

                /// <summary>

                /// Required method for Designer support - do not modify

                /// the contents of this method with the code editor.

                /// </summary>

                private void InitializeComponent()

                {

                        this.dataGrid1 = new System.Windows.Forms.DataGrid();

                        this.button1 = new System.Windows.Forms.Button();

                        ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();

                        this.SuspendLayout();

                        // 

                        // dataGrid1

                        // 

                        this.dataGrid1.DataMember = "";

                        this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;

                        this.dataGrid1.Location = new System.Drawing.Point(0, 0);

                        this.dataGrid1.Name = "dataGrid1";

                        this.dataGrid1.Size = new System.Drawing.Size(448, 128);

                        this.dataGrid1.TabIndex = 0;

                        // 

                        // button1

                        // 

                        this.button1.Location = new System.Drawing.Point(104, 168);

                        this.button1.Name = "button1";

                        this.button1.Size = new System.Drawing.Size(128, 23);

                        this.button1.TabIndex = 1;

                        this.button1.Text = "Update And Save";

                        this.button1.Click += new System.EventHandler(this.button1_Click);

                        // 

                        // Form1

                        // 

                        this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

                        this.ClientSize = new System.Drawing.Size(472, 266);

                        this.Controls.Add(this.button1);

                        this.Controls.Add(this.dataGrid1);

                        this.Name = "Form1";

                        this.Text = "Form1";

                        this.Load += new System.EventHandler(this.Form1_Load);

                        ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();

                        this.ResumeLayout(false);



                }

                #endregion



                /// <summary>

                /// The main entry point for the application.

                /// </summary>

                [STAThread]

                static void Main() 

                {

                        Application.Run(new Form1());

                }



                public void Form1_Load(object sender, System.EventArgs e)

                {        

                        

                        MovAdapt.Fill(Movset1,"MovTable1");

                        dataGrid1.DataSource = Movset1;

                        Show();

                        

                

                }



                public void button1_Click(object sender, System.EventArgs e)

                {

                                

                                

                                MovAdapt.Update(Movset1, "MovTable1");

                                Movset1.AcceptChanges();        

                        

                                

                }

        }

}

Re: Updating SQL database, Please help
 

Whats the actual error you get? (And do you have read/write permissions on the database)

Re: Updating SQL database, Please help
 

Well when I press the update button which has the update method I then get the following exception:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

How do I check if I have read/write permissions on the database?, because I assumed I have since I can read the data when I use the fill method.

thanks

Re: Updating SQL database, Please help
 

Looks like you're not generating the update command. It isn't actually created until you call SqlCommandBuilder.GetUpdateCommand(). You'll need something like this:

MovAdapt.UpdateCommand = cb.GetUpdateCommand

This article has a good explanation of how it works.

Re: Updating SQL database, Please help
 

I have tried using the following code: MovAdapt.UpdateCommand = cb.GetUpdateCommand but now I get the following exception:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.

Thanks for any further help.

Re: Updating SQL database, Please help
 

hmp

Re: Updating SQL database, Please help
 

Ah, yes. The command builder also requires that your

SELECT

statement return a primary key or unique index.

If your table has either of those, you may be able to get away with simply adding the appropriate column(s) to the

SELECT

statement.

If not, you'll have to write the update command yourself. Here's what it might look like:

using(SqlConnection connection = new SqlConnection("<connection string>"))

{

  SqlDataAdapter adapter = new SqlDataAdapter(

    "SELECT SomeField, SomeOtherField FROM Table"

  );



  adapter.UpdateCommand = new SqlCommand(

    "UPDATE Table SET SomeField = @SomeField WHERE TheKey = @TheKey"

  );



  adapter.UpdateCommand.Parameters.Add(

    "@TheKey", SqlDbType.Int).SourceColumn = "TheKey";



  adapter.UpdateCommand.Parameters.Add(

    "@SomeField", SqlDbType.Binary).SourceColumn = "SomeField";



  DataTable table = new DataTable();



  connection.Open();



  adapter.Fill(table);



  foreach(DataRow row in table.Rows)

  {

    row["SomeField"] = (int)row["SomeOtherField"] == 42;

  }



  adapter.Update(table);

}

Re: Updating SQL database, Please help
 

Thanks again but couldnt I just enter dynamic data direct through the datagrid and then just update the table(In other words actually click on the datagrid and then type data in the list and then click on update). The added data will be movie titles, director etc so that I could just add to the list when I want to update.

regards

Re: Updating SQL database, Please help
 

Short answer is no. How will your application know what SQL to send to your database to insert, update, or delete records?

I wish I had a better answer for you, but this is how it works. There are other ways to do what you want within ADO.NET, but they require more code and more manual management of the database. I believe that what I'm describing here is one of the simpler methods, and I think the most decoupled from the database itself.

Longer answer:

DataGrid

is there to provide a user interface. It can display and manage data based on a variety of underlying data-holding classes. We're looking at a

DataSet

or

DataTable

as the backing object for your

DataGrid

. Both of these are in-memory representations, i.e., they are a copy of your data, and they don't represent a direct connection to the database.

SqlDataAdapter

is probably the easiest way manage the link between these data objects and your database. At a minimum, you need to write the

SELECT

statement, and if you aren't able to use

SqlCommandBuilder

you'll also need to write appropriate

INSERT

,

UPDATE

, and

DELETE

statements for full database operation. Once that's done, though, the adapter's

Update

method takes care of all of the details.

If the .NET Framework 3.5 is available to you, there is also LINQ. It provides a language-integrated query facility that is much more intuitive than ADO.NET mechanisms and takes considerably fewer lines of code to do the same things. Using LINQ, however, is a whole different discussion--I only mention it in case you aren't familiar with it.