Write to Access 2000

[Minneslucka]

I can open and read from my database, but I can't write to it, what could be wrong with this code:

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "/Allt I Allo.mdb;User ID=Admin;Mode=Share Deny None")

myConnection.Open()

Dim MyCommand As New OleDbCommand("INSERT INTO test (hej1, hej2) VALUES ('hej3', 'hej4')", MyConnection)

MyCommand.CommandText = "SELECT @@IDENTITY"

MsgBox("Last ID was : " & MyCommand.ExecuteScalar())

MyConnection.Close()

MyCommand.Dispose()

 

Greetings /Ale

[Robby]

this line MyCommand.CommandText = "SELECT @@IDENTITY"

is replacing the rprevious Insert line, remove it and it should work, however I don't think that you can use @@IDENTITY in Access.

You can do another Select to get TOP 1 from the table with Order By IDfield DESC.

[Minneslucka]

I think it's INPUT INTO that's wrong... The code doesn't report any error, but my database don't update either...

[a_jam_sandwich]

@@IDENTITY Works fine in access2000+

 

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "/Allt I Allo.mdb;User ID=Admin;Mode=Share Deny None") 
myConnection.Open() 
Dim MyCommand As New OleDbCommand("INSERT INTO test (hej1, hej2) VALUES ('hej3', 'hej4')", MyConnection) 
' This is your missing command your need to run the INSERT Query before getting your ID
MyCommand.ExecuteNonQuery()
' Get the Last INSERT ID
MyCommand.CommandText = "SELECT @@IDENTITY" 
MsgBox("Last ID was : " & MyCommand.ExecuteScalar()) 
MyConnection.Close() 
MyCommand.Dispose()

 

Andy