none
Adding a new row to a dataset RRS feed

  • Question

  •  

    hello,

    I am trying to add a new row to a dataset. I then write this changes to a xml file for local storage. However, I have noticed that when I read the xml file back into a dataset and display in the datagrid, the incidentID (PK) is NULL. The incidentID is auto incremented in the sql server database.

    Am I doing something wrong?


    Code Snippet

    Dim dr As DataRow
    dr = dsIncidents.Tables(0).NewRow()
    dr("Company") = Me.cboCustomer.Text
    dr("Contact") = Me.cboContact.Text
    dr("PhoneNo") = Me.txtPhone.Text
    dr("Priority") = Me.cboPrority.Text
    dr("Subject") = Me.txtSubject.Text
    dr("AssignedTo") = Me.cboUsers.Text
    dsIncidents.Tables(0).Rows.Add(dr)

    dsIncidents.AcceptChanges() 'I have called acceptChange to mark the rows unchanged, but sure if this does anything.

     

    dsIncidents.WriteXmlSchema(incidentXSD)
    dsIncidents.WriteXml(incidentXML, XmlWriteMode.DiffGram)

     

    Me.grdIncidents.Datasource = dsIncidents.Tables(0).DefaultView

     

     

    I want to save the updates in the xml file, than at the end of the day the user will update all the changes to the sql server database.

     

    Many thanks for any advice,

     

    Steve

    Friday, September 14, 2007 4:01 AM

Answers

  • I really don't know what to tell you.  I can only conclude that there's something you're doing in your program that's causing a side-effect.

     

    Here's my code, using the files you posted:

     

    Code Snippet

    DataSet ds = new DataSet();

    ds.ReadXmlSchema(@"c:\temp\ado\incident.xsd");

    ds.ReadXml(@"c:\temp\ado\incident.xml");

    DataTable dt = ds.Tables[0];

    dt.Columns["incidentID"].AutoIncrement = true;

    dt.Columns["incidentID"].AutoIncrementSeed = 0;

    dt.Columns["incidentID"].AutoIncrementStep = -1;

    DataRow r = dt.NewRow();

    dt.Rows.Add(r);

    Console.WriteLine("r[\"incidentID\"] = {0}", r["incidentID"]);

     

     

    Here's its output:

     

    r["incidentID"] = 0

     

     

    Saturday, September 15, 2007 8:01 PM

All replies

  • You're never sending the data to the database.  How could the database assign an ID to a row that it never knows existed?

     

    You can set the column's AutoIncrement property for the PK field in the DataSet designer.  That makes the DataTable assign a locally-unique ID to every new row you add.

     

    When you end up calling the Update method of your table adapter, the table adapter will insert the new rows into the database.  If you've set "Refresh the data table" under Advanced Options, and if you're either using the INSERT statement that the DataSet designer generated, or you've written one that returns @SCOPE_IDENTITY,  it will also get the ID that the database assigned on inserting the row, and update the row in the DataTable with this value. 

     

    It's generally a good idea to make sure that the IDs that ADO is assigning can't collide with the IDs that SQL Server is assigning.  A common way to do that - assuming that your SQL Server PK column is an int configured as IDENTITY(1,1) - is to configure the column so that the start value for AutoIncrement is 0 and the AutoIncrementStep is -1.  That way, all of the IDs assigned on the client are negative, and all the ones assigned in the database are positive, and there's no way that updating a row in a DataTable with the ID assigned by the database will cause a duplicate PK in the DataTable.

     

    And don't call AcceptChanges.  When you add, modify, or delete rows in a DataTable, their DataRowState gets set accordingly.  If you update a column's value, the original value is preserved.  (Look up HasVersion in the documentation and you'll find a discussion of how all this gets used.) 

     

    The table adapter's Update method relies on the DataRowState to know whether it should insert, update, or delete a given row.  If you're doing an update, it relies on the Original values in the row to check for concurrency exceptions.  (That is, if the Original value in the DataRow doesn't match what's in the database, someone or something has changed the row since you read it.) 

     

    If you call AcceptChanges, you're discarding all of this information.  When you run the Update method, it won't find any changes at all, and nothing will get sent to the database.  Generally, you should leave AcceptChanges alone and let the table adapter call it.

    Friday, September 14, 2007 9:00 AM
  • Hello Robert,

     

    Thanks for the great advice. I like the advice about setting the indentity to decement. That would solve the problem of having duplicate rows.

     

    However, I am not sure what I have done wrong, as everytime I have added a new row. All the rows have -1 for the incidentID (PK) column. So I am not getting the NULL but all -1. I am not sure why it doesn't decement so that I would get -1, -2 etc.

     

    Can you see any mistakes in my code, as to why I am getting -1s.

     

    Here is the code I am using.

     

    'Read the xsd file

    Code Snippet

    dsIncidents.ReadXmlSchema(incidentXSD) 'Do I always need to read in the xml schema file

    'Read in the xml file

    dsIncidents.ReadXml(incidentXML, XmlReadMode.DiffGram)

    Dim dr As DataRow

    'Set the auto increment columns

    dsIncidents.Tables(0).Columns("IncidentID").AutoIncrementSeed = -1

    dsIncidents.Tables(0).Columns("IncidentID").AutoIncrementStep = -1

    dsIncidents.Tables(0).Columns("IncidentID").AutoIncrement = True

    dr = dsIncidents.Tables(0).NewRow()

     

    dr("Company") = Me.cboCustomer.Text

    dr("Contact") = Me.cboContact.Text

    dr("PhoneNo") = Me.txtPhone.Text

    dr("Priority") = Me.cboPrority.Text

    dr("Subject") = Me.txtSubject.Text

    dr("AssignedTo") = Me.cboUsers.Text

     

    dsIncidents.Tables(0).Rows.Add(dr) 'Add the new row

     

    dsIncidents.WriteXmlSchema(incidentXSD) 'Do I always need to write the xsd schema when i have added a new row

    dsIncidents.WriteXml(incidentXML, XmlWriteMode.DiffGram)

     

     

    Many thanks for your help,

     

    Steve

    Friday, September 14, 2007 12:54 PM
  •  

    Hello,

    I read in the xml files and create a new dataset and set the autoIncrement properties. So each time a user wants to add a new row it will do the same thing again. So I will be setting the autoIncrement properties each time, that is why I keep getting the same number for the incidentID PK row for each row.

    The add form opens as a showDialog each time a user wants to add a new row. Is it possible to have a global dataset where it can be set only once?

    However, there is still a problem I noticed if you look at the code snippet below, I set the autoIncrement once, then add 3 rows. But row 1 always gives a NULL for the incidentID PK.

    example, why am I always getting a null for the first row?
    row 1  NULL
    row 2  0
    row 3  -1
    etc.

    Code Snippet
    Dim dr As DataRow
    'Set the auto increment columns
    dsIncidents.Tables(0).Columns("IncidentID").AutoIncrementSeed = 0
    dsIncidents.Tables(0).Columns("IncidentID").AutoIncrementStep = -1
    dsIncidents.Tables(0).Columns("IncidentID").AutoIncrement = True

    dr = dsIncidents.Tables(0).NewRow()
    dr("Company") = Me.cboCustomer.Text
    dr("Contact") = Me.cboContact.Text
    dr("PhoneNo") = Me.txtPhone.Text
    dr("Priority") = Me.cboPrority.Text
    dr("Subject") = Me.txtSubject.Text
    dr("AssignedTo") = Me.cboUsers.Text
    dsIncidents.Tables(0).Rows.Add(dr) 'Add the new row 1

    dr = dsIncidents.Tables(0).NewRow()
    dr("Company") = Me.cboCustomer.Text
    dr("Contact") = Me.cboContact.Text
    dr("PhoneNo") = Me.txtPhone.Text
    dr("Priority") = Me.cboPrority.Text
    dr("Subject") = Me.txtSubject.Text
    dr("AssignedTo") = Me.cboUsers.Text
    dsIncidents.Tables(0).Rows.Add(dr) 'Add the new row 2

    dr = dsIncidents.Tables(0).NewRow()
    dr("Company") = Me.cboCustomer.Text
    dr("Contact") = Me.cboContact.Text
    dr("PhoneNo") = Me.txtPhone.Text
    dr("Priority") = Me.cboPrority.Text
    dr("Subject") = Me.txtSubject.Text
    dr("AssignedTo") = Me.cboUsers.Text
    dsIncidents.Tables(0).Rows.Add(dr) 'Add the new row 3

     

     



    Many thanks for any extra help with this problem.

    Steve

    Friday, September 14, 2007 6:05 PM
  • I sure don't see anything obviously wrong with that code.  I rewrote it in C# (I don't use VB) and got the right results:

     

    Code Snippet

    DataSet ds = new DataSet();

    DataTable dt = ds.Tables.Add();

    dt.Columns.Add("ID", typeof(Int32));

    dt.Columns.Add("Desc", typeof(String));

    dt.Columns["ID"].AutoIncrementSeed = 0;

    dt.Columns["ID"].AutoIncrementStep = -1;

    dt.Columns["ID"].AutoIncrement = true;

    DataRow dr;

    dr = dt.NewRow();

    dr["Desc"] = "Row 1";

    dt.Rows.Add(dr);

    dr = dt.NewRow();

    dr["Desc"] = "Row 2";

    dt.Rows.Add(dr);

    dr = dt.NewRow();

    dr["Desc"] = "Row 3";

    dt.Rows.Add(dr);

     

    foreach (DataRow r in dt.Rows)

    {

    Console.WriteLine("{0}: {1}", r["ID"], r["Desc"]);

    }

     

     

    which produces:

     

    0: Row 1

    -1: Row 2

    -2: Row 3

    Friday, September 14, 2007 9:08 PM
  • Hello Robert,

     

    Here is my version of the code, And yes did produce the correct output. 0, -1, -2.

    Code Snippet

    Dim ds1 As New DataSet()

    Dim dt As DataTable = ds.Tables.Add()

    dt.Columns.Add("IncidentID", GetType(Int16))

    dt.Columns.Add("Description", GetType(String))

    dt.Columns("IncidentID").AutoIncrement = True

    dt.Columns("IncidentID").AutoIncrementSeed = 0

    dt.Columns("IncidentID").AutoIncrementStep = -1

    Dim dr1 As DataRow = dt.NewRow()

    dr1("Description") = "Row 1"

    dt.Rows.Add(dr1)

    dr1 = dt.NewRow()

    dr1("Description") = "Row 2"

    dt.Rows.Add(dr1)

    dr1 = dt.NewRow()

    dr1("Description") = "Row 3"

    dt.Rows.Add(dr1)

    For Each drs As DataRow In dt.Rows

    MsgBox(String.Format("{0} - {1}", drs("IncidentID").ToString(), drs("Description").ToString()))

    Next drs

     

     

    But what could be wrong with my code in my last post, that could cause a null in only the first row that was added. I can only conclude that it might have some thing to do with the xml file that is read in my the dsIncidents dataset.

     

    Here is the xml and xsd files that is being read in. Which you can download from my box.

    includes the xsd, and the xml.

    http://www.box.net/shared/k8o49bp1gy

     

    Many thanks for your patients,

     

    Steve

     

     

    Saturday, September 15, 2007 9:20 AM
  • I really don't know what to tell you.  I can only conclude that there's something you're doing in your program that's causing a side-effect.

     

    Here's my code, using the files you posted:

     

    Code Snippet

    DataSet ds = new DataSet();

    ds.ReadXmlSchema(@"c:\temp\ado\incident.xsd");

    ds.ReadXml(@"c:\temp\ado\incident.xml");

    DataTable dt = ds.Tables[0];

    dt.Columns["incidentID"].AutoIncrement = true;

    dt.Columns["incidentID"].AutoIncrementSeed = 0;

    dt.Columns["incidentID"].AutoIncrementStep = -1;

    DataRow r = dt.NewRow();

    dt.Rows.Add(r);

    Console.WriteLine("r[\"incidentID\"] = {0}", r["incidentID"]);

     

     

    Here's its output:

     

    r["incidentID"] = 0

     

     

    Saturday, September 15, 2007 8:01 PM
  •  

    Thanks for your help Robert,

     

    Its working ok now. Not sure what was wrong. Maybe just one of those things.

     

    Just a few questions, When i am going to update the incidents table, Do I always need to read and write the schema file? At the moment I read/write both the xml and schema file..

     

    Many thank,

     

    Steve

    Sunday, September 16, 2007 9:55 AM