AutoNumber Primary Key Field not updating

[aviles1973]

I have a basic 3 field database example that I have written in VB.NET 2005. I am creating my data connection,
data adapter, command builder and datatable at runtime.

I have 3 textboxes: FirstName, LastName, PhoneNumber
I have 1 label: EmloyeeID
All 4 are not binded to the datasource or table. I am using the PrivateSub ShowCurrentRecord below in code to assign the table values to the textboxes and labels as the records move to one from the other.

The PROBLEM: All field values update fine when I add a new record except for the EmployeeID. Its a primary key autonumber designed in MSAccess. When I add a new record every other field value in VB are updated fine but it seems that the EmployeeID (AutoNumber) is detected as a null until I reFILL the DataAdapter. This would be fine for this small example but it could be very time consuming in a large database project.

It seems to me that VB.NET isnt working together properly with MSAccess on the UPDATE command to update autonumbers as they are added.

PLEASE HELP?

Public Class Form1

Private m_cnADONetConnection As New OleDb.OleDbConnection()
Private m_daDataAdapter As OleDb.OleDbDataAdapter
Private m_cbCommandBuilder As OleDb.OleDbCommandBuilder
Private m_rowPosition As Integer = 0
Private m_dtContacts As New DataTable

Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
m_cnADONetConnection.Close()
m_cnADONetConnection.Dispose()
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
m_cnADONetConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DatabaseTest.net\Test.mdb"
m_cnADONetConnection.Open()

m_daDataAdapter = New OleDb.OleDbDataAdapter("Select * From Contacts", m_cnADONetConnection)

m_cbCommandBuilder = New OleDb.OleDbCommandBuilder(m_daDataAdapter)

m_daDataAdapter.Fill(m_dtContacts)

Me.ShowCurrentRecord()
End Sub

Private Sub ShowCurrentRecord()
If m_dtContacts.Rows.Count = 0 Then
FName.Text = ""
LName.Text = ""
PhoneNum.Text = ""
IDNum.Text = ""
Exit Sub
End If

IDNum.Text = m_dtContacts.Rows(m_rowPosition)("EmployeeID").ToString()
FName.Text = m_dtContacts.Rows(m_rowPosition)("FirstName").ToString()
LName.Text = m_dtContacts.Rows(m_rowPosition)("LastName").ToString()
PhoneNum.Text = m_dtContacts.Rows(m_rowPosition)("PhoneNumber").ToString()
End Sub

Private Sub ButtonFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonFirst.Click
m_rowPosition = 0
Me.ShowCurrentRecord()
End Sub

Private Sub ButtonPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonPrevious.Click
If m_rowPosition > 0 Then
m_rowPosition = m_rowPosition - 1
Me.ShowCurrentRecord()
Else
Beep()
End If
End Sub

Private Sub ButtonNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonNext.Click
If m_rowPosition < (m_dtContacts.Rows.Count - 1) Then
m_rowPosition = m_rowPosition + 1
Me.ShowCurrentRecord()
Else
Beep()
End If
End Sub

Private Sub ButtonLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonLast.Click
If m_dtContacts.Rows.Count > 0 Then
m_rowPosition = m_dtContacts.Rows.Count - 1
Me.ShowCurrentRecord()
End If
End Sub

Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
If m_dtContacts.Rows.Count <> 0 Then
m_dtContacts.Rows(m_rowPosition)("FirstName") = FName.Text
m_dtContacts.Rows(m_rowPosition)("LastName") = LName.Text
m_dtContacts.Rows(m_rowPosition)("PhoneNumber") = PhoneNum.Text

m_daDataAdapter.Update(m_dtContacts)
End If
End Sub

Private Sub ButtonAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonAdd.Click

Dim drNewRow As DataRow = m_dtContacts.NewRow()

drNewRow("FirstName") = FNameNEW.Text
drNewRow("LastName") = LNameNEW.Text
drNewRow("PhoneNumber") = PhoneNumNEW.Text

m_dtContacts.Rows.Add(drNewRow)

m_daDataAdapter.Update(m_dtContacts)

m_rowPosition = m_dtContacts.Rows.Count - 1
Me.ShowCurrentRecord()
End Sub

Private Sub ButtonCancel_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonCancel.Click
Me.ShowCurrentRecord()
End Sub
End Class

 

[jebenson]

Check out this MSDN article:

HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[aviles1973]

Thank You!

I had an alternative way of fixing this problem:

m_dtContacts.Reset()
m_daDataAdapter.Fill(m_dtContacts)

I just re-filled the DataTable and the primary key auto number updated itself but my question is:

1)Will this slow the program down noticeably as the Data in the database increases? I am writing a business program and I want to make sure that my method works.

2)I have been using MSAccess to create my databases but I think I'm ready to go with a better way:

Is there a free download for Microsoft SQL Server to write my Databases. And is it something like Access?

 

[jebenson]

Will this slow the program down noticeably as the Data in the database increases?

Yes, it will.

Is there a free download for Microsoft SQL Server to write my Databases?

Yes, it is called SQL Server 2005 Express Edition. You can get it at [URL unfurl="true"]http://msdn.microsoft.com/vstudio/express/sql/[/url]

And is it something like Access?

No. Surprisingly, it is like SQL Server.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[aviles1973]

I didnt get a chance to try this link yet:

HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET

because I went to Barnes & Nobles to find a good book on SQLServer2005 & when I got home I was fooling around with VB.net & found some info I didnt know existed:

VB.net has a wizard that opens SQLServer2005 in the background and you can actually create databases with ease right from VB.net. I'm sure everyone already knew that but Ive been away from programming quite a long time(was using the previous edition of VB.net and VB6.

I created the simple 4 field database In SQLServer2005 from VB.net with the same structure as the one that was written in MSAccess and just changed everything to SQL.Client and I still encountered the Autonumber PrimaryKey problem when added new records(rows).

But it also worked fine when I reset the dataTable and then refilled it right after the update.

Also I'm not to sure of what field types to use as MSAccess and SQLServer2005 field types have totally different names. Where can I find a quick example of when to use the right datatype in SQLServer?

I'm not even sure of what datatype everyone uses here for a PrimaryKey AutoNumber field. In MSAccess it was a LongInteger. I used numeric(18,0),not null

Is that right?