Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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?
0
CyberUnDead
Asked:
CyberUnDead
  • 4
  • 3
2 Solutions
 
YZlatCommented:
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
      Dim da as New OleDbDataAdapter
      Dim ds As New Dataset

        Try

              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()
       da.SelectCommand = cmd
                  ''make sure command does not timeout
                  da.SelectCommand.CommandTimeout = 0
                  da.Fill(ds)
              gv1.DataSource = ds.Table(0)
              gv1.DataBind()
         Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
0
 
CyberUnDeadAuthor 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.
0
 
YZlatCommented:
the problem is with your stored procedure. could you post the code here?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
YZlatCommented:
ID = GetUserID(User.Identity.Name)
        Dim cnStr As String = "your odbc name here"

        Dim cnn As New OdbcConnection(cnStr)
        Dim cmd As New OdbcCommand()
        Dim pm As OdbcParameter
      Dim da as New OdbcDataAdapter
      Dim ds As New Dataset

        Try

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

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

              cnn.Open()
       da.SelectCommand = cmd
                  ''make sure command does not timeout
                  da.SelectCommand.CommandTimeout = 0
                  da.Fill(ds)
              gv1.DataSource = ds.Table(0)
              gv1.DataBind()
         Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
0
 
CyberUnDeadAuthor 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.
0
 
CyberUnDeadAuthor 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?
0
 
CyberUnDeadAuthor 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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now