473,372 Members | 925 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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
Nov 21 '05 #1
6 6211
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" <so*@slingshot.co.nz> wrote in message
news:1098149437.839697@ftpsrv1...
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

Nov 21 '05 #2
HS1
Thank you
But here is WindowsForms

"Brad Shook" <bs****@echd.org.removeme> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...
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" <so*@slingshot.co.nz> wrote in message
news:1098149437.839697@ftpsrv1...
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


Nov 21 '05 #3
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/de...ementtopic.asp

I hope this helps?

Cor

"HS1" <so*@slingshot.co.nz>
But here is WindowsForms

"Brad Shook" <bs****@echd.org.removeme> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...
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" <so*@slingshot.co.nz> wrote in message
news:1098149437.839697@ftpsrv1...
> 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
>
>



Nov 21 '05 #4
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" <no************@planet.nl> wrote in message
news:u7**************@TK2MSFTNGP15.phx.gbl...
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/de...ementtopic.asp
I hope this helps?

Cor

"HS1" <so*@slingshot.co.nz>
But here is WindowsForms

"Brad Shook" <bs****@echd.org.removeme> wrote in message
news:Ou**************@TK2MSFTNGP10.phx.gbl...
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" <so*@slingshot.co.nz> wrote in message
news:1098149437.839697@ftpsrv1...
> 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
>
>



Nov 21 '05 #5
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.OLED B.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
///
Nov 21 '05 #6
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

"Cor Ligthert" <no************@planet.nl> wrote in message
news:OG**************@tk2msftngp13.phx.gbl...
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.OLED B.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
///

Nov 21 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Frances | last post by:
Hi All, I'm having a problem trying to add a record to a simple Access 2000 db (db is very similar to an address book but with more info than the usual address, phone, etc.). The database is...
5
by: Ilan Sebba | last post by:
When it comes to adding records in related tables, Access is really smart. But when I try to do the same using ADO, I am really stupid. Say I have two parent tables (eg Course, Student) and one...
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
9
by: DS | last post by:
Whenever you use a continous form each row represents a record. Does this record have a number? If so how do you access it. Right now I have a field using auto number. But if access generates...
0
by: Scott269 | last post by:
So I've got an old MS Works database I imported into Access. I needed a primary key so I created a record number field that was just the record number I manually inserted when I entered it in the...
1
by: Scott269 | last post by:
So I've got an old MS Works database I imported into Access. I needed a primary key so I created a record number field that was just the record number I manually inserted when I entered it in the...
6
by: AA Arens | last post by:
Hi, I have a database with 2 main forms. Contacts and companies. I share the base with two others via LAN. On the companies form I have buttons to navigate throught the records (>400). We are...
10
by: MLH | last post by:
Suppose, in a multi-user environment, you have append query SQL in a VBA procedure that looks like INSERT INTO MyTable... and the next line reads MyVar=DMax("","MyTable... You can never be...
5
by: payffl | last post by:
My users are running Access '03. They have a database with a form that allows them to enter new records. Frequently they will move to a new record and not enter any information. This prevents...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.