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.Na me)
Dim cnStr As String = "Provider=Microsoft.Jet.OL EDB.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.StoredProcedur e
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?
--- 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.Na
Dim cnStr As String = "Provider=Microsoft.Jet.OL
Dim cnn As New OleDbConnection(cnStr)
Dim cmd As New OleDbCommand()
Dim pm As OleDbParameter
With cmd
.Connection = cnn
.CommandText = "[GetActivityTitle]"
.CommandType = CommandType.StoredProcedur
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
the problem is with your stored procedure. could you post the code here?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
YZlat:
The MS Access query's title is GetActivityTitle and the sql is:
SELECT Activity_Titles.activity_t itle FROM Activity_Titles WHERE Activity_Titles.activity_i d=?
The odd thing is this works:
.CommandText = "SELECT Activity_Titles.activity_t itle FROM Activity_Titles WHERE Activity_Titles.activity_i d=?"
.CommandType = CommandType.Text
but this does not work
.CommandText = "[GetActivityTitle]"
.CommandType = CommandType.StoredProcedur e
The rest of the code is the same as you provided above with oledb changed to odbc.
The MS Access query's title is GetActivityTitle and the sql is:
SELECT Activity_Titles.activity_t
The odd thing is this works:
.CommandText = "SELECT Activity_Titles.activity_t
.CommandType = CommandType.Text
but this does not work
.CommandText = "[GetActivityTitle]"
.CommandType = CommandType.StoredProcedur
The rest of the code is the same as you provided above with oledb changed to odbc.
ASKER
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.StoredProcedur e
'.CommandText = "SELECT Activity_Titles.activity_t itle FROM Activity_Titles WHERE Activity_Titles.activity_i d=?"
Any ideas?
.CommandType = CommandType.StoredProcedur
'.CommandText = "SELECT Activity_Titles.activity_t
Any ideas?
ASKER
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.
I will report back if I can get YZlat's code to work on the other PC.
ASKER
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.