Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO in VB.NET

Posted on 2002-05-20
9
Medium Priority
?
329 Views
Last Modified: 2013-11-23
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.OLEDB.4.0;" & _
        "Data Source=" & System.Windows.Forms.Application.StartupPath & _
        "\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.
0
Comment
Question by:xorcrack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:inthedark
ID: 7023332
what happens if you don't use a command object try:

conn.execute sql


0
 
LVL 1

Author Comment

by:xorcrack
ID: 7023339
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.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023368
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.

???????????

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:xorcrack
ID: 7023369
could you give me some sample code that you know works for you, that inserts a record into an access database??
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023401
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.


0
 
LVL 17

Accepted Solution

by:
inthedark earned 800 total points
ID: 7023413
So here is the solution Last is not allowed as a field name unless you use [Last]

Try this:

SQL = "Create Table [hotlist3] (" _
      + "MyID Int Identity NOT NULL," _
      + "[Last] VARCHAR(20) NULL)"
     
cn.Execute SQL

SQL = "INSERT INTO hotlist3 ([LAST]) VALUES ('SMITH')"

cn.Execute SQL

It works for me.....best of luck with ADO...
0
 
LVL 1

Author Comment

by:xorcrack
ID: 7023432
that did the trick! thank you so much for your time. i knew it had to be some dumb syntax quirk
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023438
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.
0
 
LVL 1

Author Comment

by:xorcrack
ID: 7023448
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question