add a record for access with an AutoNumber field

[HS1]

Hello

I have a table in Access Database. This table has a AutoNumber field. I use
a DataGrid to show that table

When I insert a new record in for this table using a DataGrid, there is a
message that asks me to enter the value for this AutoNumber field. Why I
have to do that?

Could you please help
Thank you
S.Hoa

 

[Brad Shook]

Check the XSD file containing the table the AutoField should have the
attribute AutoIncrement="true"
EX.
<xs:element name="LocationId" msdata:AutoIncrement="true" type="xs:int" />

Brad Shook

 

[HS1]

Thank you
But here is WindowsForms

Check the XSD file containing the table the AutoField should have the
attribute AutoIncrement="true"
EX.
<xs:element name="LocationId" msdata:AutoIncrement="true" type="xs:int" />

Brad Shook

 

[Cor Ligthert]

S. Hoa,

Brad shows you the XSD generated when you use the designer and a strongly
typed dataset.

This kind of questions are almost impossible to answer withouth some code.
You see it yourself. Nobody know how you do it, there are so many
possibilities, so the least what you would show in my opinion is show some
code (10 rows) how you add that row.

Or is it done alone with the * than tell that.

Your problem is probably around the autoincrement and the three properties
from that.

http://msdn.microsoft.com/library/d...stemdatadatacolumnclassautoincrementtopic.asp

I hope this helps?

Cor

 

[HS1]

Thank you Cor
I will explain more details

I have a table of Access Database. There is an field ID with AutoNumber type
that is the primary key in this table.

I show the data of this table in a DataGrid using a dataset with a
connection.

I have some TextBox(es) that present data of the current record in the
DataGrid by using data Binding (of course, there is a textbox to present
ID).

You can see that it is ver common. Everthing works fine.

Now I want to add a new record into this table (or datagrid). When I click
add new button with the code

Me.BindingContext(DataGrid1.DataSource, "Clients").AddNew()

all TextBox(es) are empty then I can enter new data for the new record. I do
not enter value for the ID textbox field as it is AutoNumber. However, when
I click Update,

da1.Update(ds)

there is message that ask me the fiel ID shoud not be Null. Why I have to do
that when I already set that field as AutoNumber type (of course, I set when
I create that table in Access).

Is it clear???
Thanks

 

[Cor Ligthert]

S Hoa

I made a complete sample for you what should fit your problem completly

The first part is to make a minimum accessdatabase as you told, therefore
do not become affraid of that part. You can paste this in a form and need a
datagrid a textbox and a button on the form. As well do you have to set a
reference to reference to COM adox ext 2.x for dll and security (that is for
creating the testdatabase). The directory is in this case C:\test1. I hope
this helps, and expect an answer from you, that I am sure you saw this,
otherwise you know probably what I want to say.

I hope this helps?

Cor

\\\
Dim dv As DataView
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\test1\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test1\db1.mdb")
'To make tables we use Adonet
Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=C:\test1\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Close()
Dim da As New OleDb.OleDbDataAdapter("Select * from Persons", conn)
Dim ds As New DataSet
da.Fill(ds)
ds.Tables(0).Columns("AutoId").AutoIncrement = True
ds.Tables(0).Columns("AutoId").AutoIncrementSeed = -1
ds.Tables(0).Columns("AutoId").AutoIncrementStep = -1
dv = New DataView(ds.Tables(0))
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
ds.Tables(0).Rows(0)("Name") = "Cor"
dv.AllowNew = False
DataGrid1.DataSource = dv
TextBox1.DataBindings.Add("Text", dv, "Name")
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
dv.AllowNew = True
dv.AddNew()
dv.AllowNew = False
End Sub
///

 

[HS1]

Thank you very much for your help, Cor

I understand clearly your code. You create a table for a database db1.mdb
and then set AutoNumber for the Identity field

My table was ALREADY created in a database using Access Database. When I
created this table, I ALREADY set the field ID is AutoNumber. That means
the table with a field AutoNumber was created before I build a VB.Net
application. Now I have to create this VB.Net for entering new data (of
course, there is old data in this table).

I try a part of your code when the form is loaded:

ds.Tables(0).Columns("AutoId").AutoIncrement = True

It work OK now

S Hoa

I made a complete sample for you what should fit your problem completly

The first part is to make a minimum accessdatabase as you told, therefore
do not become affraid of that part. You can paste this in a form and need a
datagrid a textbox and a button on the form. As well do you have to set a
reference to reference to COM adox ext 2.x for dll and security (that is for
creating the testdatabase). The directory is in this case C:\test1. I hope
this helps, and expect an answer from you, that I am sure you saw this,
otherwise you know probably what I want to say.

I hope this helps?

Cor

\\\
Dim dv As DataView
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\test1\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test1\db1.mdb")
'To make tables we use Adonet
Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=C:\test1\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
conn.Close()
Dim da As New OleDb.OleDbDataAdapter("Select * from Persons", conn)
Dim ds As New DataSet
da.Fill(ds)
ds.Tables(0).Columns("AutoId").AutoIncrement = True
ds.Tables(0).Columns("AutoId").AutoIncrementSeed = -1
ds.Tables(0).Columns("AutoId").AutoIncrementStep = -1
dv = New DataView(ds.Tables(0))
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
ds.Tables(0).Rows(0)("Name") = "Cor"
dv.AllowNew = False
DataGrid1.DataSource = dv
TextBox1.DataBindings.Add("Text", dv, "Name")
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
dv.AllowNew = True
dv.AddNew()
dv.AllowNew = False
End Sub
///