none
C# and SQL RRS feed

  • Question

  • Hello,

    I am having trouble with SQL and C#, for some reason when i try to implement the button to update the list, or database, using the dataset and dataadaptor, it throws me this error message saying " Dynamic SQL generation for the updatecommand is not supported against a selectcommand that does not return any key column information", what could i be doing wrong, i implemented another method that when the user clicks on the add button, it adds new row in the database with the information in the selected textboxes, however for this problem, this is just implementing an update, such as a person changes different information etc. what could i be doing wrong, can someone please explain? here is the code below:

    SqlConnection

     

    newconnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;

     

     

    );

    DataSet

     

    ds1;

     

     

    int Maxrow = 0;

     

     

    int inc = 0;

     

     

    SqlDataAdapter sqlda;

    private

     

    void Form1_Load(object sender, EventArgs e)

     

    new DataSet();

    sqlda =

     

    new SqlDataAdapter(sql, newconnection);

    accessda =

     

    new OleDbDataAdapter(sql, accessconnection);

     

    "Workers");

    Maxrow = ds1.Tables[

     

    "Workers"].Rows.Count;

     

    NavigateRecords();

    }

    public

     

     

    void NavigateRecords()

     

    DataRow Drow = ds1.Tables["Workers"].Rows[inc];

     

    textBox1.Text = Drow.ItemArray.GetValue(1).ToString();

    textBox2.Text = Drow.ItemArray.GetValue(2).ToString();

    textBox3.Text = Drow.ItemArray.GetValue(3).ToString();

    }

    private

     

     

    void btnSave_Click(object sender, EventArgs e)

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

     

     

    DataRow drow = ds1.Tables["Workers"].NewRow();

     

    "Workers"].Rows.Add(drow);

     

    "Workers");

     

     

    MessageBox.Show("New Entry Added");

     


    // this is the problem below, method savebutton works fine, but the update does not, please let me know thank you :)

    private
    void btnupdate_Click(object sender, EventArgs e)

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

     

    DataRow drow2 = ds1.Tables["Workers"].Rows[inc];

     

    "Workers");

     

     

    MessageBox.Show("Data Updated");

     

    }

    drow2[1] = textBox1.Text;

    drow2[2] = textBox2.Text;

    drow2[3] = textBox3.Text;

    sqlda.Update(ds1,

     

     

    {

     


    }

     

    Maxrow = Maxrow + 1;

    inc = Maxrow - 1;

    sqlda.Update(ds1,

    drow[1] = textBox1.Text;

    drow[2] = textBox2.Text;

    drow[3] = textBox3.Text;

    ds1.Tables[

    {

     

    {

     

    newconnection.Open();

    sqlda.Fill(ds1,

    {

    ds1 =

     

    AttachDbFilename=C:\Database1\Database1.mdf;Integrated Security=True;Connect Timeout=30;

    User Instance=True"

    Tuesday, August 25, 2009 2:24 PM

Answers

  • Here you go ( you may have to tweak:

                // fill the table
                SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM tblWorkers", conn);
                dataAdapter.Fill(ds1, "Workers");
                // get the ID from the first record the variable "inc" is pointing 
                int WorkerID = (int)ds1.Tables["Workers"].Rows[inc]["worker_id"];
                // build your update statement
                dataAdapter.UpdateCommand = new SqlCommand("UPDATE tblWorkers SET [column1]= @p2, [column2] = @p3 WHERE [worker_id] = @p1", conn);
                // add parameters to prevent Sql Injection attacks
                dataAdapter.UpdateCommand.Parameters.Add("@p1", WorkerID);
                dataAdapter.UpdateCommand.Parameters.Add("@p2", textBox1.Text);
                dataAdapter.UpdateCommand.Parameters.Add("@p3", textBox2.Text);
    
                // Per MSDN: by default, AcceptChanges() is called implicitly after an update
                dataAdapter.AcceptChangesDuringUpdate = true;
                // update the record in the base table
                dataAdapter.Update(ds1,"Workers");
                
                // update your data table maually if the Update failed to do so
                DataRow dr = ds1.Tables["Workers"].Rows[inc];
                // index to whatever columns you are indexing
                // I would use actual column nmaes however since
                // that makes the code more readable and you 
                //cannot guarantee indexes will stay the same with each query
                dr[1] = textBox1.Text;  // dr["MyColumnName"] = textBox1.Text
                dr[2] = textBox2.Text;  // dr["MyOtherColumnName"] = textBox1.Text
                ds1.AcceptChanges();


    Thursday, August 27, 2009 12:40 PM

All replies

  • Does you table have a unique key column ( primary key )? The adapter is telling you it does not know what record to update.
    Tuesday, August 25, 2009 2:28 PM
  • No it does not have a key column, but the thing is, the savebutton method works? why cant the update one work as well? the error message points to:

    sqlda.Update(ds1,

    "Workers");

    Let me update the code, i looked above and some strange reason some of it is cut off, hopefully this should help,

    public

     

    Form1()

    {

    InitializeComponent();

    }

     

    SqlConnection newconnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;

    AttachDbFilename=C:\Database1\Database1.mdf;Integrated Security=True;Connect Timeout=30;

    User Instance=True"

     

    );

     

    DataSet ds1;

     

    int Maxrow = 0;

     

    int inc = 0;

     

    SqlDataAdapter sqlda;

     

    OleDbDataAdapter accessda;

     

    public string sql = "SELECT * from tblworkers";

     

    private void Form1_Load(object sender, EventArgs e)

    {

    ds1 =

    new DataSet();

    sqlda =

    new SqlDataAdapter(sql, newconnection);

    newconnection.Open();

    sqlda.Fill(ds1,

    "Workers");

    Maxrow = ds1.Tables[

    "Workers"].Rows.Count;

    NavigateRecords();

    }

    public

     

    void NavigateRecords()

    {

     

    DataRow Drow = ds1.Tables["Workers"].Rows[inc];

    textBox1.Text = Drow.ItemArray.GetValue(1).ToString();

    textBox2.Text = Drow.ItemArray.GetValue(2).ToString();

    textBox3.Text = Drow.ItemArray.GetValue(3).ToString();

    }

    private

     

    void btnSave_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

     

    DataRow drow = ds1.Tables["Workers"].NewRow();

    drow[1] = textBox1.Text;

    drow[2] = textBox2.Text;

    drow[3] = textBox3.Text;

    ds1.Tables[

    "Workers"].Rows.Add(drow);

    Maxrow = Maxrow + 1;

    inc = Maxrow - 1;

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("New Entry Added");

    }

     

    private void btnupdate_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

     

     

    DataRow drow2 = ds1.Tables["Workers"].Rows[inc];

    drow2[1] = textBox1.Text;

    drow2[2] = textBox2.Text;

    drow2[3] = textBox3.Text;

     

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("Data Updated");

    }

     

    private void btnDelete_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

    ds1.Tables[

    "Workers"].Rows[inc].Delete();

    Maxrow--;

    inc = 0;

    NavigateRecords();

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("Record deleted");

    }

    Also the Delete and the update button does not work, but the save button does... why??? it gets the error code at

    sqlda.Update(ds1,

    "Workers");

    thanks

    Tuesday, August 25, 2009 2:53 PM
  • You can always add new records to a table with no primary key, but to update or delete specific records you have to work off a unique record ID so the code knows which record you are working against.

    If you add 3 record to a table with the columns fname, mi, and lname:

    Add:

    "John" "Smith"
    "John" "Smith"
    "Bob" "Smith"
     
    Now you want to add a middle initial to one which one are you talking about. You can designate you want to update the one record where fname = "John" and lname = "Smith". You would update two records ( may or may not be what you want ).

    It is database design 101, every table should have a primary key column, especially if any combination of columns do not make a unique entry. This key also comes in to play later when or if you want to have children tables they need the keys to relate to each other.
    Tuesday, August 25, 2009 3:04 PM
  • I am sorry this is NOT ANSWERED AND NOT SOLVED, when i do put the key, it still shows that error message, now i am going to post this again since you think this is answered and i am going to link it to this one because you jumped the gun, so this is still not working any other ideas?
    Tuesday, August 25, 2009 6:49 PM
  • Post your updated code and indicate where it is failing. Post you table's schema also. Post the exception message if any or detail your desired result. We cannot help fix what we cannot see.
    Tuesday, August 25, 2009 6:53 PM
  • Michael, listen, if you see above i did show you the code, but again i will show it below the exact same code as above, if you look where it is commented at (//) it will show you my comment where its not working, and i even posted twice the exact spot that is showing me the error message including typing out the exact word for word what the error message is saying, yes i will try and post it with the tables schema, how do you do that?


    code below:

    public

     

    Form1()

    {

    InitializeComponent();

    }

     

    SqlConnection newconnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Workers\MyWorkers.mdf;

    Integrated Security=True;User Instance=True"

     

    );

    private

     

    void Form1_Load(object sender, EventArgs e)

    {

    ds1 =

    new DataSet();

    sqlda =

    new SqlDataAdapter(sql, newconnection);

    newconnection.Open();

    sqlda.Fill(ds1,

    "Workers");

    Maxrow = ds1.Tables[

    "Workers"].Rows.Count;

    NavigateRecords();

    }

    public

     

    void NavigateRecords()

    {

     

    DataRow Drow = ds1.Tables["Workers"].Rows[inc];

    textBox1.Text = Drow.ItemArray.GetValue(1).ToString();

    textBox2.Text = Drow.ItemArray.GetValue(2).ToString();

    textBox3.Text = Drow.ItemArray.GetValue(3).ToString();

    }

    private

     

    void btnSave_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

     

    DataRow drow = ds1.Tables["Workers"].NewRow();

    drow[1] = textBox1.Text;

    drow[2] = textBox2.Text;

    drow[3] = textBox3.Text;

    ds1.Tables[

    "Workers"].Rows.Add(drow);

    Maxrow = Maxrow + 1;

    inc = Maxrow - 1;

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("New Entry Added");

    }

     

    private void btnupdate_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

     

     

    DataRow drow2 = ds1.Tables["Workers"].Rows[inc];

    drow2[1] = textBox1.Text;

    drow2[2] = textBox2.Text;

    drow2[3] = textBox3.Text;

     

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("Data Updated");

    }

     

    private void btnDelete_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

    ds1.Tables[

    "Workers"].Rows[inc].Delete();

    Maxrow--;

    inc = 0;

    NavigateRecords();

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("Record deleted");

    }


    The error message comes up at:
    sqlda.Update(ds1, "Workers");

    For only the following methods below:

    private void btnupdate_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

     

     

    DataRow drow2 = ds1.Tables["Workers"].Rows[inc];

    drow2[1] = textBox1.Text;

    drow2[2] = textBox2.Text;

    drow2[3] = textBox3.Text;

     

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("Data Updated");

    }

     

    private void btnDelete_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

    ds1.Tables[

    "Workers"].Rows[inc].Delete();

    Maxrow--;

    inc = 0;

    NavigateRecords();

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("Record deleted");

    }



    as you can see this does not include the save button which makes sense since the save button we are adding a new row, these above:
    (the update button and the delete button) we are removing and updating previous rows, but still using the same method:
    sqlda.Update(ds1, "Workers");

    the table is called tblWorkers
    there are only 4 columns, the key column is Workers_ID, then there is first_name, Last_name, Job_Title,  the key column Workers_ID is the key
    and i set it to be, and also i made the change to be true or yes as identification information, or (indentity to), also increment 1 for workers_ID,

    I hope this clears any confusion.

    Thank you

    Tuesday, August 25, 2009 7:01 PM
  • There is absolutely no reason to mark Michael's post as abusive as he is trying to help you. That is uncalled for.

    Let's all take a breath and relax. If you feel your post hasn't been sufficiently answered, please respond with code that is well formed making it easier for us to understand and read. Articulate your issue with well documented code.

    Thank you Steven.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Tuesday, August 25, 2009 11:00 PM
  • Put a break right before the Update() and browse the data table "Workers" and make sure that the Worker_ID column is in the data table and it is populated. In your method where you create the record the worker_ID should be populated after you create the record in the table with the auto increment. The issue here maybe that you are creating records in your data table then sending those back to the database. The database is creating the record with a worker_ID but you are not requerying your data table to get that ID in your data table and/or you are not creating it in the data table when you make the record thus you are there is no ID in the data table to use.

    Let's also try this:

    ds1.Tables["Workers"].AcceptChanges();  right before the Update call.
    • Proposed as answer by JohnGrove Wednesday, August 26, 2009 1:47 PM
    Wednesday, August 26, 2009 1:39 PM
  • I think Johns right :( sorry Michael (dang i feel like i am in elementry again), anyways Mike, i think the ds1.Tables["Workers"].AcceptChanges(); was sorta working because now it did take the changes, however it did not update the sql database, so your code may be close to what I am looking for to solve the problem or something close to it, however the code that you gave me did remove the runtime error however when you close out of the program, and reopen it, it did not take the update changes, or save it to the database which sqlda.Update(ds1, "Workers"); is suppose to do, if you think of anything let me know but i will try on my part as well.
    Wednesday, August 26, 2009 2:42 PM
  • Okay, no biggie.

    If your database table has an ID column that is auto-increment you have to retrieve that value from the server to put into your data table as the ID. When you insert the record do not offer one in your data table let the server make the ID for you and retrieve it.

    If you do not want to do that then you create your new record and save it. Then you have to requery your data table back into memory so you can see that new record and its ID column. Then when you make changes to your data table and execute AcceptChanges() and then call Update() the data table has the valid ID to tell the server which record it wants to update.

    In our large system we do all inserts manually off a "Save" button and immediately requery any/all views that are open and need the new record. We make the new record with only the ID and maybe a few other columns ( if we know the values at t creation time ) and bring it back and then let the user modify the record and update it.

    We let the views handle all deletes and updates since they are correctly populated with primary key ID's and we cascade delete only in our RI code on the server.
    Wednesday, August 26, 2009 2:55 PM
  • "If your database table has an ID column that is auto-increment you have to retrieve that value from the server to put into your data table as the ID. When you insert the record do not offer one in your data table let the server make the ID for you and retrieve it."

    Thats what this line does:

    DataRow

     

    drow2 = ds1.Tables["Workers"].Rows[inc];

    inc is actually int inc = 0, so when the user press next button, it goes up 1, then in the database the next one after 0 is 1, so it moves up one row, pretty cool i think :) for a simple programming logic, but i guess the thing has to do with this trouble maker below:

    sqlda.Update(ds1,

    "Workers");

    why the sqladaptor is not working, i dont know, it has all the info, but i dont know, i wonder if i have to do a new sql command for the sql adaptor to tell the database to update rather than SELECT... what do you guys think?

    Wednesday, August 26, 2009 6:42 PM
  • Also wanted to ask, would this be a lot easier if done using LINQ? what is the difference and what would be easier, faster, and best practice, this method of what i am doing, or using LINQ, which would you use in real world environment?
    Wednesday, August 26, 2009 6:56 PM
  • Can you show us your query string to load your data table ( the command in the data adapter in your load )? To generate the UpdateCommand proerty with a SqlCommandBuilder you can have the command builder create the update statement for you but you have to have created a SelectCommand for it to work off of. I think it is in your variable sql in your Fill() command. You have to set this statement to the sqlda.SelectCommand property and the command builder should work for you then.


    This is from MSDN for UpdateCommand property:

    During Update , if this property is not set and primary key information is present in the DataSet , the UpdateCommand can be generated automatically if you set the SelectCommand property and use the SqlCommandBuilder .
    Wednesday, August 26, 2009 7:12 PM
  • sure its this below:

    SqlConnection

     

    newconnection = new SqlConnection(@"Data source=.\SQLEXPRESS;AttachDbFilename=C:\Workers\MyWorkers.mdf;Integrated Security=True;User Instance=True");

    DataSet

     

    ds1;

    SqlDataAdapter

     

    sqlda;

    public

     

    string sql = "SELECT * from tblWorkers";

    sqlda =

    new SqlDataAdapter(sql, newconnection);


    Let me know if you need anything else.

    Wednesday, August 26, 2009 7:16 PM
  • Change your update to sqlda.Update(ds1, "tblWorkers");
    Wednesday, August 26, 2009 7:23 PM
  • no that didnt work, i thought that was it too, but no :( if i change it to tblworkers, it says database not found, if i change it back, it just updates the dataset and no the database.
    Wednesday, August 26, 2009 7:26 PM
  • Forget that I typed in wrong table name ( dyslexic ). Try this code in your delete and update:

    SqlCommandBuilder cbUpdate = new SqlCommandBuilder(sqlda);
    sqlda.UpdateCommand = cbDelete.GetUpdateCommand();
    sqlda.Update(ds1, "workers");

    SqlCommandBuilder cbDelete = new SqlCommandBuilder(sqlda);
    sqlda.DeleteCommand = cbDelete.GetDeleteCommand();
    sqlda.Update(ds1, "workers");
    Wednesday, August 26, 2009 7:31 PM
  • no sadly the sqlda.UpdateCommand = cbDelete.GetUpdateCommand();
    and
    sqlda.DeleteCommand = cbDelete.GetDeleteCommand();
    sqlda.Update(ds1, "workers");

    gave me runtime errors when running the program said the following error message:

    Dynamic SQL generation for the DeleteCommand is not supported against a Selectcommand that does not return
    any key column information"

    somethings clicking in my head... it says deletecommand not supported against a selectcommand... does he mean the SQL = "SELECT..."??
    Wednesday, August 26, 2009 7:40 PM
  • Not to sound redundant but the exception is pointing out that your PK in tblWorkers is not being recognized so the update and delete commands cannot be created.

    I used the above code without error on a table from my database that has a structure of:

    column1 int PK, column2 c(30), column3 c(20), column4 datetime
    Wednesday, August 26, 2009 7:58 PM
  • hmm i am glad you tried it out, did you try my code or yours that you recently told me to try, either way atleast it narrows it down to something on my end, specifically my database, a setting or something i am missing, i know the key is there because i see a key when i connect to it using studio managment 2008 right next to the worker_ID, so the key is there and double check to make sure that its the indentifier is yes and its incremented by 1, is there anything else i could check on the database side that i may be missing?
    Wednesday, August 26, 2009 8:04 PM
  • Here is the code I used:


    DataSet ds = new DataSet(); 
    string ConnString = "Data Source=MLW-Desktop\\SqlExpress;Integrated Security=True";
    SqlConnection conn = new SqlConnection(ConnString);
    string QueryString = "SELECT * FROM mytable" 
                
    SqlCommand cmd = new SqlCommand(QueryString, conn);
    
    // fill the dataset     
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(ds, "mytable");
    // I got back 2,056 rows

    // now delete one row ds.Tables["mytable"].Rows[1].Delete(); SqlCommandBuilder cb = new SqlCommandBuilder(da); da.DeleteCommand = cb.GetDeleteCommand(); da.Update(ds, "mytable");


    Wednesday, August 26, 2009 8:19 PM
  • Michael, it is you that have the patiance of a saint.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, August 26, 2009 8:21 PM
  • Years of raising kids, scout leader, and baseball coaching has dulled my senses :)
    Wednesday, August 26, 2009 8:32 PM
  • if you called that dulling, then thats worth it to spend time with your kids... not coding :) i myself am going to be a father soon, but atleast what i want to do is learn as much as I can before he comes then i will devote much of my time to him, then go seek a programming job, my hat goes off to you for your efforts of spending time, the right way.

    but the sad part is... i have to figure out whats wrong with my database, i am using visual studio 2008 C#, and 2008 SQL express, just an FYI of course. but atleast my code works right, can you past this code below and see if it works fine for you, this is the original code and wondering if it works, if it does, its my database, and somethings wrong on that end, if not, then it can be both or something else, either way, can you tell me, is this way that we are doing the best way, or using LINQ to SQL is better? what is the best practice or real world scenario?

    Thank you
    Wednesday, August 26, 2009 8:41 PM
  • here is the code below:

    private

     

    void btnupdate_Click(object sender, EventArgs e)

    {

     

    SqlCommandBuilder cb = new SqlCommandBuilder(sqlda);

    sqlda.Update(ds1,

    "workers");

     

     

     

     

    DataRow drow2 = ds1.Tables["Workers"].Rows[inc];

    drow2[1] = textBox1.Text;

    drow2[2] = textBox2.Text;

    drow2[3] = textBox3.Text;

    sqlda.Update(ds1,

    "Workers");

     

    MessageBox.Show("Data Updated");

     

    }

    Wednesday, August 26, 2009 8:41 PM
  • If you are using .NET 3.5

    ds1.Tables["Workers"].Rows[inc].SetField("Value1", "Value2", "Value3");


    .NET 2.0
    ds1.Tables["Workers"].Rows[inc].BeginEdit();
    ds1.Tables["Workers"].Rows[inc][0] = "Value1";
    ds1.Tables["Workers"].Rows[inc][1] = "Value2";
    ds1.Tables["Workers"].Rows[inc][2] = "Value3";
    ds1.Tables["Workers"].Rows[inc].EndEdit();
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, August 26, 2009 9:05 PM
  • When this post is over, you need to be giving Michael some points merely for the patience shown

    :-)
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, August 26, 2009 9:10 PM
  • For the future I would focus on Entity Framework ( that is where Microsoft's money is going ) while learning basic ADO.Net classes and style.

    I was able to get an update to work but I had to create my updatecommand manually. The generator uses you select statement to create the update and that may/maynot be the update you want to fire.
    Wednesday, August 26, 2009 9:47 PM
  • Can you show me some examples such as adding, updating and deleting using the ADO.Net Classes and styles as well as what you said on Entity and framework?

    can you also show me the code that you said was able to get an update to work but you had to create updatecommand manually? can you show me all the code?

    Thanks
    Wednesday, August 26, 2009 9:51 PM
  • Please ask this in a new thread and close this one. [If any of the answers were helpful at all, that is]
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, August 26, 2009 9:54 PM
  • ok ok i will open a new thread for the entity framwork request but if michael can show me the code that he was talking about, that would be great.

    thanks

    Wednesday, August 26, 2009 9:56 PM
  • Hi all,

    First, I want to thank Michael and John for such patient replies to Steven’s problem. You are always enthusiastic and helpful in MSDN forums. I really appreciate that.

     

    To Steven, I’m glad to see that your problem is almost resolved in this thread.

    For the first reply from Michael, which is about the analysis of your problem, is not abusive actually. Abuse means that he uses bad words towards somebody or forum here. So we must be very carefully to use unless he or she posts something is harm to the members and forums. I’ll clear the Abuse Flag on the reply for you.

     

    If you have any problem about Entity Framework, welcome to ADO.NET Entity Framework forum.

    Here are some documents of the EF overview for your reference:

    http://msdn.microsoft.com/en-us/library/bb386876.aspx

    http://msdn.microsoft.com/en-us/library/bb738547.aspx

    http://msdn.microsoft.com/en-us/data/cc300162.aspx#entity

     

    I’m also looking forward to Michael’s code sample to see wonderful solution.

     

    Best Regards

    Yichun Feng

    Thursday, August 27, 2009 9:41 AM
  • Here you go ( you may have to tweak:

                // fill the table
                SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM tblWorkers", conn);
                dataAdapter.Fill(ds1, "Workers");
                // get the ID from the first record the variable "inc" is pointing 
                int WorkerID = (int)ds1.Tables["Workers"].Rows[inc]["worker_id"];
                // build your update statement
                dataAdapter.UpdateCommand = new SqlCommand("UPDATE tblWorkers SET [column1]= @p2, [column2] = @p3 WHERE [worker_id] = @p1", conn);
                // add parameters to prevent Sql Injection attacks
                dataAdapter.UpdateCommand.Parameters.Add("@p1", WorkerID);
                dataAdapter.UpdateCommand.Parameters.Add("@p2", textBox1.Text);
                dataAdapter.UpdateCommand.Parameters.Add("@p3", textBox2.Text);
    
                // Per MSDN: by default, AcceptChanges() is called implicitly after an update
                dataAdapter.AcceptChangesDuringUpdate = true;
                // update the record in the base table
                dataAdapter.Update(ds1,"Workers");
                
                // update your data table maually if the Update failed to do so
                DataRow dr = ds1.Tables["Workers"].Rows[inc];
                // index to whatever columns you are indexing
                // I would use actual column nmaes however since
                // that makes the code more readable and you 
                //cannot guarantee indexes will stay the same with each query
                dr[1] = textBox1.Text;  // dr["MyColumnName"] = textBox1.Text
                dr[2] = textBox2.Text;  // dr["MyOtherColumnName"] = textBox1.Text
                ds1.AcceptChanges();


    Thursday, August 27, 2009 12:40 PM