We help IT Professionals succeed at work.

asp.net MS Access ODBC query/stored procedure

CyberUnDead
CyberUnDead asked
on
400 Views
Last Modified: 2013-11-26
Can someone help me convert the code behind from Oledb to Odbc?  The code below works fine as is but when I attempt to changed all the references from "OleDB" to "Odbc" I receive the error:

--- Begin Error---

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

on the line: gv1.DataSource = cmd.ExecuteReader

--- END Error---

The [GetActivityTitle] query in MS Access 2000 is:

--- Begin Query---

SELECT Activity.title
FROM Activity
WHERE Activity.activity_id=[?];

--- END Query---

The code-behind on the Page_Load event is:

--- Begin Code---

        ID = GetUserID(User.Identity.Name)
        Dim cnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/myDB.mdb") & ";"

        Dim cnn As New OleDbConnection(cnStr)
        Dim cmd As New OleDbCommand()
        Dim pm As OleDbParameter

        With cmd
            .Connection = cnn
            .CommandText = "[GetActivityTitle]"
            .CommandType = CommandType.StoredProcedure
            pm = .Parameters.Add("ID", OleDbType.VarChar)
        End With

        pm.Direction = ParameterDirection.Input
        pm.Value = ID

        cnn.Open()
        gv1.DataSource = cmd.ExecuteReader
        gv1.DataBind()
        cnn.Close()

--- END Code---

Could I assign the result to a Dataset and then assign the Dataset to my Gridview Control?
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks YZlat I was able to use your code to include a Dataset so I could establish a Data Relation for my nested repeater control.  The code is working great.

However, my current project though uses ODBC for all of my database queries through a connectionstring in my web.config.  I am having trouble properly converting from OLEDb to ODBC.  When I change all of the references I receive the following error.

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

For version 2.0 I would like to change to a n-tier application and hide all the queries into a DAL but for the time being I am using ODBC for all my connections.
CERTIFIED EXPERT

Commented:
the problem is with your stored procedure. could you post the code here?
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
YZlat:

The MS Access query's title is GetActivityTitle and the sql is:

SELECT Activity_Titles.activity_title FROM Activity_Titles WHERE Activity_Titles.activity_id=?

The odd thing is this works:

            .CommandText = "SELECT Activity_Titles.activity_title FROM Activity_Titles WHERE Activity_Titles.activity_id=?"
            .CommandType = CommandType.Text

but this does not work
            .CommandText = "[GetActivityTitle]"
            .CommandType = CommandType.StoredProcedure

The rest of the code is the same as you provided above with oledb changed to odbc.

Author

Commented:
It is almost as if the CommandType is not being set to StoredProcedure but instead is being used as Text.  What I mean is that this code works though it should NOT.  This would explain why the name of StoredProcedure is throwing the error that is an invalid SQL statement.

 .CommandType = CommandType.StoredProcedure
 '.CommandText = "SELECT Activity_Titles.activity_title FROM Activity_Titles WHERE Activity_Titles.activity_id=?"

Any ideas?

Author

Commented:
I will leave this question open until I can execute this code YZlat provided at 10.02.2007 at 03:46PM EDT, ID: 20001459 on a different PC with a different install of VS.NET 2005.  This will help me eliminate if I have a busted VS.NET install.  

I will report back if I can get YZlat's code to work on the other PC.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.