xorcrack
asked on
ADO in VB.NET
im writing this very simple application in vb.net and i am trying to insert a record into an MS Access table using ADO.NET (2.7) and for the life of me can't figure out why this code gets an "unhandled error":
Dim conn As New OleDb.OleDbConnection()
Dim sql As String
conn.ConnectionString = "Provider=Microsoft.Jet.OL
"Data Source=" & System.Windows.Forms.Appli
"\hotlist.mdb;"
conn.Open()
sql = "INSERT INTO hotlist (LAST) VALUES ('SMITH')"
Dim cmd As New OleDb.OleDbCommand(sql, conn)
cmd.ExecuteNonQuery() '<----error occurs here
conn.Close()
i'm not sure im even coding this write. if anyone has a better solution to insert a record to an access database through code, let me know. thanks.
Dim conn As New OleDb.OleDbConnection()
Dim sql As String
conn.ConnectionString = "Provider=Microsoft.Jet.OL
"Data Source=" & System.Windows.Forms.Appli
"\hotlist.mdb;"
conn.Open()
sql = "INSERT INTO hotlist (LAST) VALUES ('SMITH')"
Dim cmd As New OleDb.OleDbCommand(sql, conn)
cmd.ExecuteNonQuery() '<----error occurs here
conn.Close()
i'm not sure im even coding this write. if anyone has a better solution to insert a record to an access database through code, let me know. thanks.
ASKER
the oleDBConnection object doesnt support an "Execute" method. and when i try this with the standard Connection object, i get a different error stating that the "INSERT" statement is wrong.
i usually have no trouble working with sql server databases using the standard Connection object. i dont know why it is proving so difficult to do the same thing with a ms acess database.
i usually have no trouble working with sql server databases using the standard Connection object. i dont know why it is proving so difficult to do the same thing with a ms acess database.
I don't know VB.NET but in VB with ADO you need to use the ADODB.Connection object. I have never seen OleDb. objects. I thought that ADO was a seemless wrapper for OleDb.
???????????
???????????
ASKER
could you give me some sample code that you know works for you, that inserts a record into an access database??
In VB 6 I would say:
dim CN as ADODB.Connection
CN.Connection string="...."
CN.Open
SQL = "Insert Into [MyTable] ([Field1], [Field2]) Values('Data1', 'Data2');"
CN.Execute SQL
CN.Close
Set CN = Nothing
1) Try using [Last] incase Last is a keyword or defined as a function, or something.
2) ADO only likes tables that have a primary key defined (or an Autonumber)
3) Make sure that you don't have any fields where "allow nulls" = no and have not been supplied with a value. Or a numeric field has been defined as required.
dim CN as ADODB.Connection
CN.Connection string="...."
CN.Open
SQL = "Insert Into [MyTable] ([Field1], [Field2]) Values('Data1', 'Data2');"
CN.Execute SQL
CN.Close
Set CN = Nothing
1) Try using [Last] incase Last is a keyword or defined as a function, or something.
2) ADO only likes tables that have a primary key defined (or an Autonumber)
3) Make sure that you don't have any fields where "allow nulls" = no and have not been supplied with a value. Or a numeric field has been defined as required.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that did the trick! thank you so much for your time. i knew it had to be some dumb syntax quirk
The problem with ADO and access I have never found some good documentation for this. I suspect that Microsoft created ADo without any spec. or documentation.....but this could be fantasy.
ASKER
i agree. microsoft's documentation for ADO that comes with vb.net contains sample code that uses illegal commands in vb.net and won't even run. it's very frustrating when they make *you* feel like the fool, when you cant figure out how to get there *advanced* technologies to do the simplest things.
conn.execute sql