Link to home
Create AccountLog in
Avatar of CyberUnDead
CyberUnDead

asked on

asp.net MS Access ODBC query/stored procedure

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?
SOLUTION
Avatar of YZlat
YZlat
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of CyberUnDead
CyberUnDead

ASKER

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.
the problem is with your stored procedure. could you post the code here?
ASKER CERTIFIED SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
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?
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.