Add rows in MS Access with Autonumber + Primary key

[Mootser13]

Using VB.NET 2005, I am trying to update a primary key column that is set to AutoNumber.
I can use this code
Dim drNew As DataSet1.tablerow
drNew = Me.DataSet1.nameoftable.NewnameoftableRow
drNew.Item("NameofColumn") =
Me.DataSet1.nameoftable.nameofColumn.AutoIncrement


But this does not actually set the value of the field.

Also I want to make sure I can delete rows and then re-add new ones.

 

[PRPhx]

Autonumber fields in Access aren't meant to be modified. If you need an incrementing field for your table, add a new field and set it's type to number, and whatever you need for the field size, such as long integer. THEN you can code changes to this field in VB net when you need to make them.

Also, IF you REALLY want/need an autonumber field, you can drop the column, then add it again and the number sequence should be back, in order. I really don't recommend doing this however.

 

[Mootser13]

But why would I not want to have an autonumber on the primary key field ?
Isn't there any VB/net 2005 code I can use to "trigger" this autonumbering event for this field.
If I do nothing to update this field in my row addition, it will not add the row at all. "Field cannot be null".

 

[jebenson]

The way to get an autonumber to increment when adding new records is do to nothing with it in the insert statement. For example, say you have a table with 3 fields:

Field1 - Autonumber PK
Field2 - text
Field3 - number

If you build your insert command like this:

Insert into table (Field1, Field2, Field3) values ([red]???[/red], 'test data', 1234)

you will have the problem you are now experiencing - what to put in the place of the red question marks ([red]???[/red]). However, if you build the SQL like this:


Insert into table (Field2, Field3) values ('test data', 1234)

it will work, and the autonumber field (Field1) will increment.

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

 

[Mootser13]

I am actually adding a row by using the add row, then using the update method.
Can you explain further how to build the insert command ?

 

[onpnt]

You've missed the answer already Mootser13

jebenson has given you everything you need and just short of doing the work for you. If you need further help on building strings and or knowledge of SQL I would highly suggest using some of the self paced tutorials and references online. Like MSDN Home

General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[Mootser13]

I appreciate it.
Sometimes looking at your code helps me to understand the thought process. I can't imagine this is a unique situation I have run into.

Thanks for the help..

 

[onpnt]

I can't imagine this is a unique situation I have run into

This is as you said in no way a unique situation. In fact it is very basic database level knowledge along with programming essentials. That's why I gave you the link to the knowledge base along with the other members good tips. That's the benefit of Tek-Tips in the attempt to provide solutions in the form of teaching and not providing pure coded examples or fixes that will only prevent that important learning step.

Besides, my code is to messy for any other human to read

General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[Antithott]

The following code shows a way of adding a field wiht autoincrement and promarykey of a ID field.



Dim Cn As ADODB.Connection, Cat As ADOX.Catalog, _
objTable As ADOX.Table

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
objTabel = New ADOX.Table

Cn.Open("Provider=Microsoft.Jet.OLEDB.4.;Data Source=C:\test.mdb")

Cat.ActiveConnection = Cn

objTabel.Name = "test"
objTabel.ParentCatalog = Cat

objTabel.Columns.Append("Id", DataTypeEnum.adInteger)
With objTabel.Columns("Id")
.Properties("AutoIncrement").Value = True
End With

objTabel.Keys.Append("PrimaryKey", KeyTypeEnum.adKeyPrimary, "Id")

Cat.Tables.Append(objTable)

objTabel = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing