using vb.net to insert into ms access

[Brian]

hello,
I am trying to insert into an access database using
vb.net. I tried to use DAO, but I found out that it is no
longer avaliable. Next I went to oledb and I am now
getting an error saying that one or more of the required
fields are missing. The only thing is, there is only one
required field and I am passing it, so there must be
something I am missing. I will use any db connection out
there I just need one that will work. If anyone has any
examples of one, I would appreciate it. Thanks.

 

[CJ Taylor]

Are you using a dataset, and if so, are you using an AutoNumbering system
for ID fields?

If so, VB.NEt doesn't handle those real well...

 

[Bernie Yaeger]

Hi Brian,

Oledb can handle what you are trying to do. Please send me a copy of the
code that is attempting the update, and I should be able to help. Include
the structure of the table (just give me all the column names and
datatypes). Also, are you using an oledb dataadapter built with the wizard?
It must include all cols, but, again, this should work just fine.

I have several Access apps out there that do this routinely, so I'm sure I
can help.

Bernie Yaeger

 

[Peter Huang]

Hi Brian,

[Test.mdb]
ID AutoNumber
Desc Text

[Insert Demo]
Imports System.Data.OleDb

Module Module1

Sub Main()
Dim conn As New OleDbConnection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Data Source=Test.mdb;"
Dim da As New OleDbDataAdapter("select * from testtable", conn)
Dim ds As New DataSet
Dim com As New OleDbCommand("INSERT INTO TESTTABLE([desc])
VALUES(@desc)", conn)
com.Parameters.Add("@desc", OleDbType.VarWChar, 50, "desc")
da.InsertCommand = com
da.Fill(ds)
Dim anyRow As DataRow = ds.Tables(0).NewRow
anyRow("Desc") = "hello"
ds.Tables(0).Rows.Add(anyRow)
da.Update(ds)
End Sub
End Module

If you have concern on this issue, please post here.

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[mike mccann]

It's possible one of your column names in the sql
statement doesn't match a column name in the database -
that will do it - but put up some more info if you want a
more exact answer

Cheers

mmc

 

[Guest]

Here is some of the code that I have in the program. I am
not sure what I am doing wrong. If anyone knows what I
did wrong I would appreciate any info. Thanks.


Dim strConnection As String
= "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data
Source=C:\inetpub\wwwroot\Inventory.mdb;"

Dim MyInsert As String = "INSERT INTO Inventory(EquipCat,
EquipType,LastName) Values(txtEquipCat.text,
txtEquipType.Text, txtLastName.text)"

Dim MyConnection As New OleDbConnection(strConnection)
Dim command1 As New OleDbCommand(MyInsert, MyConnection)
command1.Connection.Open()
command1.ExecuteNonQuery()
MyConnection.Close()

 

[CJ Taylor]

Ahh..

What are the data types of all those in the database... are they all
strings?

 

[Cor]

Hi anonymous,

I was waiting for a better answer for you because I am not sure of this
anwers works because I never use an Insert (like the other answers I use the
dataset).

Dim MyInsert As String = "INSERT INTO Inventory(EquipCat,
EquipType,LastName) Values(txtEquipCat.text,
txtEquipType.Text, txtLastName.text)"

But I think you want something as

Dim MyInsert As String = "INSERT INTO Inventory(EquipCat,
EquipType,LastName) Values(" & _
txtEquipCat.text & "," & _
txtEquipType.Text & "," & _
txtLastName.text & ")"

You can try it, although I think it is not the best solution and I even
don't know if it works.

Cor