To start, I'm using VB 2005 and SQL Server Express 2005.

This almost appears to be a bug, but I've yet to find an actual bug and
generally narrow my issues down to user-error on my part. I have the
following code snippet (LogFile is a class I've created simply to output to
a textfile):

---
        Dim ta As New mattersTableAdapter
        Dim dt As New mattersDataTable
        Dim dr As mattersRow

        LogFile.WriteLine("Adding new matter...")

        'Fill data table
        ta.Fill(dt)

        'Add new data row and update
        dr = dt.NewmattersRow
        dr.Item("file_no") = "New Matter"
        dr.Item("date_created") = Now
        dr.Item("date_modified") = Now
        dt.AddmattersRow(dr)
        ta.Update(dt)
        dt.AcceptChanges()

        LogFile.WriteLine("New matter added. Id: {0}.",
dr.Item("m_id").ToString)
---

As you can probably already tell I'm using a Strongly Typed Dataset. The
table schema includes "m_id" being the Primary Key and has the property
"Identity Specification" set to Yes and increments by 1.

My issue is that if the DataTable is empty after being filled from the
database (ie: the database table is empty) I get 0 (zero) returned to "m_id"
in the DataRow. If the DataTable already contains existing records and I run
this code, I get the appropriate, incremented value.

Is there something I'm missing?

Thanks in advance for any help...

Luke

Take a look at your data adapter and how the INSERT statement is configured.

It has to be:
INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id FROM 
Matter WHERE m_id = SCOPE_IDENTITY()

In order to get this to work with SQL Server, you also have to add code that 
looks like this to add an event handler for the row updated event of the 
underlying data adapter.

da.RowUpdated += new 
System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);

void da_RowUpdated(object sender, 
System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
  if (e.StatementType == System.Data.StatementType.Insert) e.Status = 
System.Data.UpdateStatus.SkipCurrentRow;
}

If you are using table adapters, you have to expose the underlying data 
adapter with the partial class.



"Lucas Bussey"  wrote in message 
news:8Joqi.7975$_d2.6876@pd7urf3no...

> To start, I'm using VB 2005 and SQL Server Express 2005.
>
> This almost appears to be a bug, but I've yet to find an actual bug and
> generally narrow my issues down to user-error on my part. I have the
> following code snippet (LogFile is a class I've created simply to output 
> to
> a textfile):
>
> ---
>        Dim ta As New mattersTableAdapter
>        Dim dt As New mattersDataTable
>        Dim dr As mattersRow
>
>        LogFile.WriteLine("Adding new matter...")
>
>        'Fill data table
>        ta.Fill(dt)
>
>        'Add new data row and update
>        dr = dt.NewmattersRow
>        dr.Item("file_no") = "New Matter"
>        dr.Item("date_created") = Now
>        dr.Item("date_modified") = Now
>        dt.AddmattersRow(dr)
>        ta.Update(dt)
>        dt.AcceptChanges()
>
>        LogFile.WriteLine("New matter added. Id: {0}.",
> dr.Item("m_id").ToString)
> ---
>
> As you can probably already tell I'm using a Strongly Typed Dataset. The
> table schema includes "m_id" being the Primary Key and has the property
> "Identity Specification" set to Yes and increments by 1.
>
> My issue is that if the DataTable is empty after being filled from the
> database (ie: the database table is empty) I get 0 (zero) returned to 
> "m_id"
> in the DataRow. If the DataTable already contains existing records and I 
> run
> this code, I get the appropriate, incremented value.
>
> Is there something I'm missing?
>
> Thanks in advance for any help...
>
> Luke
> 

Woops,

 INSERT INTO Matter file_no, date_created, date_modified VALUES(@file_no, 
@date_created, @date_modified); SELECT m_id FROM Matter WHERE m_id = 
SCOPE_IDENTITY()


"Jim Rand"  wrote in message 
news:OzkDVDH0HHA.464@TK2MSFTNGP02.phx.gbl...

> Take a look at your data adapter and how the INSERT statement is 
> configured.
>
> It has to be:
> INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id FROM 
> Matter WHERE m_id = SCOPE_IDENTITY()
>
> In order to get this to work with SQL Server, you also have to add code 
> that looks like this to add an event handler for the row updated event of 
> the underlying data adapter.
>
> da.RowUpdated += new 
> System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);
>
> void da_RowUpdated(object sender, 
> System.Data.SqlClient.SqlRowUpdatedEventArgs e)
> {
>  if (e.StatementType == System.Data.StatementType.Insert) e.Status = 
> System.Data.UpdateStatus.SkipCurrentRow;
> }
>
> If you are using table adapters, you have to expose the underlying data 
> adapter with the partial class.
>
>
>
> "Lucas Bussey"  wrote in message 
> news:8Joqi.7975$_d2.6876@pd7urf3no...
>> To start, I'm using VB 2005 and SQL Server Express 2005.
>>
>> This almost appears to be a bug, but I've yet to find an actual bug and
>> generally narrow my issues down to user-error on my part. I have the
>> following code snippet (LogFile is a class I've created simply to output 
>> to
>> a textfile):
>>
>> ---
>>        Dim ta As New mattersTableAdapter
>>        Dim dt As New mattersDataTable
>>        Dim dr As mattersRow
>>
>>        LogFile.WriteLine("Adding new matter...")
>>
>>        'Fill data table
>>        ta.Fill(dt)
>>
>>        'Add new data row and update
>>        dr = dt.NewmattersRow
>>        dr.Item("file_no") = "New Matter"
>>        dr.Item("date_created") = Now
>>        dr.Item("date_modified") = Now
>>        dt.AddmattersRow(dr)
>>        ta.Update(dt)
>>        dt.AcceptChanges()
>>
>>        LogFile.WriteLine("New matter added. Id: {0}.",
>> dr.Item("m_id").ToString)
>> ---
>>
>> As you can probably already tell I'm using a Strongly Typed Dataset. The
>> table schema includes "m_id" being the Primary Key and has the property
>> "Identity Specification" set to Yes and increments by 1.
>>
>> My issue is that if the DataTable is empty after being filled from the
>> database (ie: the database table is empty) I get 0 (zero) returned to 
>> "m_id"
>> in the DataRow. If the DataTable already contains existing records and I 
>> run
>> this code, I get the appropriate, incremented value.
>>
>> Is there something I'm missing?
>>
>> Thanks in advance for any help...
>>
>> Luke
>>
>
> 

Thanks for the tip, I will be looking into incorporating this. I've used 
SCOPE_IDENTITY() before...

However, why would the tableadapter return 0 when inserting a row into an 
empty table? Then, subsequent inserts return the properly incremented value?

Thanks again for the insight.

Luke

"Jim Rand"  wrote in message 
news:OFF4fyg0HHA.4816@TK2MSFTNGP04.phx.gbl...

> Woops,
>
> INSERT INTO Matter file_no, date_created, date_modified VALUES(@file_no, 
> @date_created, @date_modified); SELECT m_id FROM Matter WHERE m_id = 
> SCOPE_IDENTITY()
>
>
> "Jim Rand"  wrote in message 
> news:OzkDVDH0HHA.464@TK2MSFTNGP02.phx.gbl...
>> Take a look at your data adapter and how the INSERT statement is 
>> configured.
>>
>> It has to be:
>> INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id FROM 
>> Matter WHERE m_id = SCOPE_IDENTITY()
>>
>> In order to get this to work with SQL Server, you also have to add code 
>> that looks like this to add an event handler for the row updated event of 
>> the underlying data adapter.
>>
>> da.RowUpdated += new 
>> System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);
>>
>> void da_RowUpdated(object sender, 
>> System.Data.SqlClient.SqlRowUpdatedEventArgs e)
>> {
>>  if (e.StatementType == System.Data.StatementType.Insert) e.Status = 
>> System.Data.UpdateStatus.SkipCurrentRow;
>> }
>>
>> If you are using table adapters, you have to expose the underlying data 
>> adapter with the partial class.
>>
>>
>>
>> "Lucas Bussey"  wrote in message 
>> news:8Joqi.7975$_d2.6876@pd7urf3no...
>>> To start, I'm using VB 2005 and SQL Server Express 2005.
>>>
>>> This almost appears to be a bug, but I've yet to find an actual bug and
>>> generally narrow my issues down to user-error on my part. I have the
>>> following code snippet (LogFile is a class I've created simply to output 
>>> to
>>> a textfile):
>>>
>>> ---
>>>        Dim ta As New mattersTableAdapter
>>>        Dim dt As New mattersDataTable
>>>        Dim dr As mattersRow
>>>
>>>        LogFile.WriteLine("Adding new matter...")
>>>
>>>        'Fill data table
>>>        ta.Fill(dt)
>>>
>>>        'Add new data row and update
>>>        dr = dt.NewmattersRow
>>>        dr.Item("file_no") = "New Matter"
>>>        dr.Item("date_created") = Now
>>>        dr.Item("date_modified") = Now
>>>        dt.AddmattersRow(dr)
>>>        ta.Update(dt)
>>>        dt.AcceptChanges()
>>>
>>>        LogFile.WriteLine("New matter added. Id: {0}.",
>>> dr.Item("m_id").ToString)
>>> ---
>>>
>>> As you can probably already tell I'm using a Strongly Typed Dataset. The
>>> table schema includes "m_id" being the Primary Key and has the property
>>> "Identity Specification" set to Yes and increments by 1.
>>>
>>> My issue is that if the DataTable is empty after being filled from the
>>> database (ie: the database table is empty) I get 0 (zero) returned to 
>>> "m_id"
>>> in the DataRow. If the DataTable already contains existing records and I 
>>> run
>>> this code, I get the appropriate, incremented value.
>>>
>>> Is there something I'm missing?
>>>
>>> Thanks in advance for any help...
>>>
>>> Luke
>>>
>>
>>
>
> 

Just an update:

I'm getting the same results when using OleDb and an MS Access database 
file. The first record added to a table returns 0 as the primary key value 
when the primary key field is an auto-incremented one.

Any thoughts?

Thanks again,
Luke

"Lucas Bussey"  wrote in message 
news:FBpri.16581$_d2.8541@pd7urf3no...

> Thanks for the tip, I will be looking into incorporating this. I've used 
> SCOPE_IDENTITY() before...
>
> However, why would the tableadapter return 0 when inserting a row into an 
> empty table? Then, subsequent inserts return the properly incremented 
> value?
>
> Thanks again for the insight.
>
> Luke
>
> "Jim Rand"  wrote in message 
> news:OFF4fyg0HHA.4816@TK2MSFTNGP04.phx.gbl...
>> Woops,
>>
>> INSERT INTO Matter file_no, date_created, date_modified VALUES(@file_no, 
>> @date_created, @date_modified); SELECT m_id FROM Matter WHERE m_id = 
>> SCOPE_IDENTITY()
>>
>>
>> "Jim Rand"  wrote in message 
>> news:OzkDVDH0HHA.464@TK2MSFTNGP02.phx.gbl...
>>> Take a look at your data adapter and how the INSERT statement is 
>>> configured.
>>>
>>> It has to be:
>>> INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id 
>>> FROM Matter WHERE m_id = SCOPE_IDENTITY()
>>>
>>> In order to get this to work with SQL Server, you also have to add code 
>>> that looks like this to add an event handler for the row updated event 
>>> of the underlying data adapter.
>>>
>>> da.RowUpdated += new 
>>> System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);
>>>
>>> void da_RowUpdated(object sender, 
>>> System.Data.SqlClient.SqlRowUpdatedEventArgs e)
>>> {
>>>  if (e.StatementType == System.Data.StatementType.Insert) e.Status = 
>>> System.Data.UpdateStatus.SkipCurrentRow;
>>> }
>>>
>>> If you are using table adapters, you have to expose the underlying data 
>>> adapter with the partial class.
>>>
>>>
>>>
>>> "Lucas Bussey"  wrote in message 
>>> news:8Joqi.7975$_d2.6876@pd7urf3no...
>>>> To start, I'm using VB 2005 and SQL Server Express 2005.
>>>>
>>>> This almost appears to be a bug, but I've yet to find an actual bug and
>>>> generally narrow my issues down to user-error on my part. I have the
>>>> following code snippet (LogFile is a class I've created simply to 
>>>> output to
>>>> a textfile):
>>>>
>>>> ---
>>>>        Dim ta As New mattersTableAdapter
>>>>        Dim dt As New mattersDataTable
>>>>        Dim dr As mattersRow
>>>>
>>>>        LogFile.WriteLine("Adding new matter...")
>>>>
>>>>        'Fill data table
>>>>        ta.Fill(dt)
>>>>
>>>>        'Add new data row and update
>>>>        dr = dt.NewmattersRow
>>>>        dr.Item("file_no") = "New Matter"
>>>>        dr.Item("date_created") = Now
>>>>        dr.Item("date_modified") = Now
>>>>        dt.AddmattersRow(dr)
>>>>        ta.Update(dt)
>>>>        dt.AcceptChanges()
>>>>
>>>>        LogFile.WriteLine("New matter added. Id: {0}.",
>>>> dr.Item("m_id").ToString)
>>>> ---
>>>>
>>>> As you can probably already tell I'm using a Strongly Typed Dataset. 
>>>> The
>>>> table schema includes "m_id" being the Primary Key and has the property
>>>> "Identity Specification" set to Yes and increments by 1.
>>>>
>>>> My issue is that if the DataTable is empty after being filled from the
>>>> database (ie: the database table is empty) I get 0 (zero) returned to 
>>>> "m_id"
>>>> in the DataRow. If the DataTable already contains existing records and 
>>>> I run
>>>> this code, I get the appropriate, incremented value.
>>>>
>>>> Is there something I'm missing?
>>>>
>>>> Thanks in advance for any help...
>>>>
>>>> Luke
>>>>
>>>
>>>
>>
>>
> 

1) Make sure that in your dataset, you have specified that the key field is 
an auto increment and both the seed and step are set at -1.

2) The hint that I gave you only applies to Sql Server.

3) For Microsoft Access, refer to page 171 of the book ADO.NET Cookbook 
published by O'Reilly.

Jim

"Lucas Bussey"  wrote in message 
news:dCrsi.23565$_d2.20513@pd7urf3no...

> Just an update:
>
> I'm getting the same results when using OleDb and an MS Access database 
> file. The first record added to a table returns 0 as the primary key value 
> when the primary key field is an auto-incremented one.
>
> Any thoughts?
>
> Thanks again,
> Luke
>
> "Lucas Bussey"  wrote in message 
> news:FBpri.16581$_d2.8541@pd7urf3no...
>> Thanks for the tip, I will be looking into incorporating this. I've used 
>> SCOPE_IDENTITY() before...
>>
>> However, why would the tableadapter return 0 when inserting a row into an 
>> empty table? Then, subsequent inserts return the properly incremented 
>> value?
>>
>> Thanks again for the insight.
>>
>> Luke
>>
>> "Jim Rand"  wrote in message 
>> news:OFF4fyg0HHA.4816@TK2MSFTNGP04.phx.gbl...
>>> Woops,
>>>
>>> INSERT INTO Matter file_no, date_created, date_modified VALUES(@file_no, 
>>> @date_created, @date_modified); SELECT m_id FROM Matter WHERE m_id = 
>>> SCOPE_IDENTITY()
>>>
>>>
>>> "Jim Rand"  wrote in message 
>>> news:OzkDVDH0HHA.464@TK2MSFTNGP02.phx.gbl...
>>>> Take a look at your data adapter and how the INSERT statement is 
>>>> configured.
>>>>
>>>> It has to be:
>>>> INSERT INTO Matter file_no, date_created, date_modified; SELECT m_id 
>>>> FROM Matter WHERE m_id = SCOPE_IDENTITY()
>>>>
>>>> In order to get this to work with SQL Server, you also have to add code 
>>>> that looks like this to add an event handler for the row updated event 
>>>> of the underlying data adapter.
>>>>
>>>> da.RowUpdated += new 
>>>> System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);
>>>>
>>>> void da_RowUpdated(object sender, 
>>>> System.Data.SqlClient.SqlRowUpdatedEventArgs e)
>>>> {
>>>>  if (e.StatementType == System.Data.StatementType.Insert) e.Status = 
>>>> System.Data.UpdateStatus.SkipCurrentRow;
>>>> }
>>>>
>>>> If you are using table adapters, you have to expose the underlying data 
>>>> adapter with the partial class.
>>>>
>>>>
>>>>
>>>> "Lucas Bussey"  wrote in message 
>>>> news:8Joqi.7975$_d2.6876@pd7urf3no...
>>>>> To start, I'm using VB 2005 and SQL Server Express 2005.
>>>>>
>>>>> This almost appears to be a bug, but I've yet to find an actual bug 
>>>>> and
>>>>> generally narrow my issues down to user-error on my part. I have the
>>>>> following code snippet (LogFile is a class I've created simply to 
>>>>> output to
>>>>> a textfile):
>>>>>
>>>>> ---
>>>>>        Dim ta As New mattersTableAdapter
>>>>>        Dim dt As New mattersDataTable
>>>>>        Dim dr As mattersRow
>>>>>
>>>>>        LogFile.WriteLine("Adding new matter...")
>>>>>
>>>>>        'Fill data table
>>>>>        ta.Fill(dt)
>>>>>
>>>>>        'Add new data row and update
>>>>>        dr = dt.NewmattersRow
>>>>>        dr.Item("file_no") = "New Matter"
>>>>>        dr.Item("date_created") = Now
>>>>>        dr.Item("date_modified") = Now
>>>>>        dt.AddmattersRow(dr)
>>>>>        ta.Update(dt)
>>>>>        dt.AcceptChanges()
>>>>>
>>>>>        LogFile.WriteLine("New matter added. Id: {0}.",
>>>>> dr.Item("m_id").ToString)
>>>>> ---
>>>>>
>>>>> As you can probably already tell I'm using a Strongly Typed Dataset. 
>>>>> The
>>>>> table schema includes "m_id" being the Primary Key and has the 
>>>>> property
>>>>> "Identity Specification" set to Yes and increments by 1.
>>>>>
>>>>> My issue is that if the DataTable is empty after being filled from the
>>>>> database (ie: the database table is empty) I get 0 (zero) returned to 
>>>>> "m_id"
>>>>> in the DataRow. If the DataTable already contains existing records and 
>>>>> I run
>>>>> this code, I get the appropriate, incremented value.
>>>>>
>>>>> Is there something I'm missing?
>>>>>
>>>>> Thanks in advance for any help...
>>>>>
>>>>> Luke
>>>>>
>>>>
>>>>
>>>
>>>
>>
>