Jet engine 4.0 and .Net framework

Discussion in 'ASP .Net' started by Troy, Nov 14, 2003.

  1. Troy

    Troy Guest

    We recently installed the .Net framework on a windows 2000 server.
    Shortly after that we experienced intermitant problems running a web
    based program that accesses an Access 2002 database.

    The intranet .asp program works, but as soon as it tries to access the
    database for normal users, it gives us an "unspecified error" and that
    it can't access the data base.

    As the administrator, I found my access was relatively stable. Anyone
    else though gets the errors.

    Are there known issues with .NET framework and the Jet engine?

    Is there a service patch I could run that may fix this or do I need to
    uninstall the .NET and hope things go back to normal?

    Thanks.
    T.
     
    Troy, Nov 14, 2003
    #1
    1. Advertising

  2. Troy

    Joe Guest

    I'm relatively new to .Net and I'm having similar problems - does restarting
    server solve correct the problem? I have another post to this forum looking
    for information on this issue and so far it's sitting there unanswered.

    I did find some improvement in my situation (error occurred more
    intermittently) when I added the iuser_machinename account in the security
    tab of the properties of the c:\documents and settings\machinename\local
    settings\temp folder - apparantly asp.net stores a lot of temporary files
    there. This files get cleared on a server restart so I'm thinking the issue
    is something to do with these.

    I've also read a lot of posts on the Brinkster help forum from users that
    experienced this issue - if there are any of those users reading this it
    would be great to hear your thoughts and if there has been any progress
    resolving the issue there.

    Regards,

    Joe
    "Troy" <> wrote in message
    news:...
    > We recently installed the .Net framework on a windows 2000 server.
    > Shortly after that we experienced intermitant problems running a web
    > based program that accesses an Access 2002 database.
    >
    > The intranet .asp program works, but as soon as it tries to access the
    > database for normal users, it gives us an "unspecified error" and that
    > it can't access the data base.
    >
    > As the administrator, I found my access was relatively stable. Anyone
    > else though gets the errors.
    >
    > Are there known issues with .NET framework and the Jet engine?
    >
    > Is there a service patch I could run that may fix this or do I need to
    > uninstall the .NET and hope things go back to normal?
    >
    > Thanks.
    > T.
     
    Joe, Nov 14, 2003
    #2
    1. Advertising

  3. unspecified error

    I use the Jet engine with the Net v1.0 and 1.1 framework in Win2k server
    without any problems.
    Where are you getting the "unspecified error" ? Or what is reporting such
    error? It's not much to go on.

    For starters, do have write access writes, virtual and NTFS for the access
    mdb file?
    --
    Peter O'Reilly
     
    Peter O'Reilly, Nov 14, 2003
    #3
  4. Troy

    Joe Guest

    Re: unspecified error

    Hi Peter,

    Thanks for taking the time to respond.

    If it was a permission issue on the database then surely it wouldn't be just
    an intermittent error. I'm using Jet 4.0, and can replicate the error using
    Access 2000 and Access 2002, win2k server and xp pro.

    As for when my error occurs, I've seperated out the data access tier to an
    assembly with a number of classes defining a number of functions returning
    datasets or datareaders depending on my needs. I am closing all connections
    at the end of each function/subroutine in my classes and explicitly
    disposing of the returned datareaders/datasets within my business logic
    tier.

    One thing I have noticed is that the majority of errors occur when I use
    forms authentication which uses a couple of tables from the database for
    users and their roles. The errors occur in random database transactions so
    it's difficult to be more specific. I do have impersonation set to false.

    Here's the connection string I'm using:
    Provider=Microsoft.Jet.OLEDB.4.0; Data
    Source=c:\inetpub\wwwroot\sitename\resources\data\data.mdb;Jet
    OLEDB:Database Password=password"

    and here's the data access assembly code:
    (the custusercontrol class simply grabs the connection string from the web
    config file)

    '##################### DLL to store data access classes for site
    ###################

    imports system
    imports system.data
    imports System.Data.OleDb
    imports hypertyper
    Namespace HyperTyper
    '###########################################################################
    ##########
    '############### Class to handle mailing list interactions with database
    #############
    '###########################################################################
    ##########
    public class handle_maillist : inherits custusercontrol
    '########## function to check if email address is already in the database
    ############
    public function doesMailExist(ByVal thisemail As String) As Boolean

    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)

    Dim queryString As String = "SELECT [mailing_list].* FROM
    [mailing_list] WHERE ([mailing_list]. = '" & thisemail & "')"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dbParam_email As System.Data.IDataParameter = New
    System.Data.OleDb.OleDbParameter
    dbParam_email.ParameterName = "@email"
    dbParam_email.Value = thisemail
    dbParam_email.DbType = System.Data.DbType.String
    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataSet As System.Data.DataSet = New System.Data.DataSet
    dataAdapter.Fill(dataSet)
    dim there as boolean
    If dataset.Tables(0).Rows.Count >0 then
    there = true
    else
    there = false
    end if
    return there
    end function

    '######################## function to join mailing list
    ####################################

    public sub joinMaillist(byVal mail_type_id as double, byval firstname as
    string, byval surname as string, byval mobile as string, byval email as
    string, byval ip as string, byval referrer as string)
    Dim dbcomm
    dim cusCtl as custusercontrol = new custusercontrol
    dim connstr as string = cusCtl.connection_string
    dim dbconn as New OleDbConnection(connstr)
    dbconn.Open()
    Dim ds1 as new System.Data.DataSet
    Dim da1 as New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM
    mailing_list", dbConn)
    Dim objCmdBld as New system.data.OleDb.OleDbCommandBuilder(da1)
    da1.Fill(ds1, "mailing_list")

    'we go ahead and add email to database

    dim myTable1
    dim mydatarow1
    myTable1 = ds1.tables(0)

    myDataRow1 = ds1.tables(0).newrow
    myDataRow1("date_time") = system.DateTime.Now
    myDataRow1("firstname") = firstname
    myDataRow1("surname") = surname
    myDataRow1("mobile") = mobile
    myDataRow1("email") = email
    myDataRow1("client_ip") = ip
    myDataRow1("refering_page") = referrer
    myDataRow1("mail_type_id") = mail_type_id
    myTable1.Rows.Add(myDataRow1)
    da1.Update(myTable1)
    dbconn.close()
    dbconn = nothing
    end sub

    '################### function to leave the mailing list

    public sub leaveMaillist(byval email as string)
    Dim dbcomm
    dim cusCtl as custusercontrol = new custusercontrol
    dim connstr as string = cusCtl.connection_string
    dim dbconn as New OleDbConnection(connstr)
    dbconn.Open()
    Dim objCmdBld as New system.data.OleDb.OleDbCommand()
    objCmdBld.connection = dbconn
    objCmdBld.commandtext = "delete * FROM mailing_list where email =
    '" & email & "'"
    objCmdBld.ExecuteNonQuery()
    dbConn.Close()
    dbconn = nothing
    end sub

    '################### function to return count of specific user groups
    ##########################
    public function getMaillistCount()
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT count(*) FROM
    [mailing_list]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dataReader
    datareader = Convert.ToInt32(dbCommand.ExecuteScalar().ToString())
    Return dataReader
    dbConnection.Close
    dbConnection = nothing
    end function

    '################### function to return lists of subscribers in different
    groups ##########################
    public function getMaillist(byval mail_type_id as integer)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT * FROM [mailing_list]"
    if mail_type_id <> 0 then
    querystring = querystring & "where mail_type_id = " & mail_type_id
    end if
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataSet As System.Data.DataSet = New System.Data.DataSet
    dataAdapter.Fill(dataSet)
    Return dataSet
    dbConnection.Close
    dbConnection = nothing
    end function

    '######################### function to update a subscriber in the maillist
    table ###################
    public function updateSubscriber(byVal oldmail as string, firstname as
    string, surname as string, email as string, mobile as string)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "Update [mailing_list] Set
    [date_time] = #" & system.datetime.now & "#, [firstname] = '" & firstname &
    "', [surname] = '" & surname & "', [email] = '" & email & "', [mobile] = '"
    & mobile & "' where email = '" & oldmail & "'"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    dbCommand.executenonquery()
    dbConnection.close()
    dbconnection = nothing
    end function


    end class

    '###########################################################################
    ###########
    '############### Class to handle user interactions with database
    ######################
    '###########################################################################
    ###########
    public class handle_users : inherits CustUserControl
    '##################### function to get active users for site login
    ###############

    public function authUser(ByVal email As String, ByVal Password As String)
    As System.Data.DataSet
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)

    Dim queryString As String = "SELECT [active_users_view].* FROM
    [active_users_view] WHERE (([active_users_view].[email] = @email) AND
    ([active_users_view].[pas"& _
    "sword] = @password))"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dbParam_email As System.Data.IDataParameter = New
    System.Data.OleDb.OleDbParameter
    dbParam_email.ParameterName = "@email"
    dbParam_email.Value = email
    dbParam_email.DbType = System.Data.DbType.String
    dbCommand.Parameters.Add(dbParam_email)
    Dim dbParam_password As System.Data.IDataParameter = New
    System.Data.OleDb.OleDbParameter
    dbParam_password.ParameterName = "@password"
    dbParam_password.Value = password
    dbParam_password.DbType = System.Data.DbType.String
    dbCommand.Parameters.Add(dbParam_password)
    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataSet As System.Data.DataSet = New System.Data.DataSet
    dataAdapter.Fill(dataSet)
    Return dataSet
    dbconnection.close
    dbconnection = nothing
    end function

    '##################### function to get active users details ###############

    public function getUserDetails(ByVal email As String) As
    System.Data.DataSet
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)

    Dim queryString As String = "SELECT [active_users_view].* FROM
    [active_users_view] WHERE (([active_users_view].[email] = @email))"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dbParam_email As System.Data.IDataParameter = New
    System.Data.OleDb.OleDbParameter
    dbParam_email.ParameterName = "@email"
    dbParam_email.Value = email
    dbParam_email.DbType = System.Data.DbType.String
    dbCommand.Parameters.Add(dbParam_email)
    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataSet As System.Data.DataSet = New System.Data.DataSet
    dataAdapter.Fill(dataSet)
    Return dataSet
    dbconnection.close
    dbconnection = nothing
    end function

    '##################### function to get all users' details ###############

    public function getallUsersDetails(ByVal email As String) As
    System.Data.DataSet
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)

    Dim queryString As String = "SELECT [all_admin_details_view].*
    FROM [all_admin_details_view] WHERE (([all_admin_details_view].[email] =
    @email))"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dbParam_email As System.Data.IDataParameter = New
    System.Data.OleDb.OleDbParameter
    dbParam_email.ParameterName = "@email"
    dbParam_email.Value = email
    dbParam_email.DbType = System.Data.DbType.String
    dbCommand.Parameters.Add(dbParam_email)
    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataSet As System.Data.DataSet = New System.Data.DataSet
    dataAdapter.Fill(dataSet)
    Return dataSet
    dbconnection.close
    dbconnection = nothing
    end function



    '################### function to return count of specific user groups
    ##########################
    public function getUserCount(byval usergroup as integer)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT count(*) FROM [users]
    where [users].[user_type_id] =" & usergroup
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dataReader
    datareader = Convert.ToInt32(dbCommand.ExecuteScalar().ToString())
    Return dataReader
    dbConnection.Close
    dbConnection = nothing
    end function

    '################### function to return list of users in specific groups, eg
    admin ##########################
    public function getUserList(byval usergroup as integer)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [sorted_user_list].* FROM
    [sorted_user_list] where [sorted_user_list].[user_type_id] =" & usergroup
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close
    dbConnection = nothing
    end function

    '################### function to return security role of user
    ##########################
    public function getRole(byval email as string)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [users].* FROM [users]
    where [users].[email] ='" & email & "'"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close
    dbConnection = nothing
    end function

    '########## function to check if email address is already in the users
    database ############
    public function doesUserExist(ByVal thisemail As String) As Boolean

    dim cusCtl as custusercontrol = new custusercontrol
    dim connectiontring As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)

    Dim queryString As String = "SELECT [users].* FROM [users]
    WHERE ([users].[email] = '" & thisemail & "')"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dbParam_email As System.Data.IDataParameter = New
    System.Data.OleDb.OleDbParameter
    dbParam_email.ParameterName = "@email"
    dbParam_email.Value = thisemail
    dbParam_email.DbType = System.Data.DbType.String
    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataSet As System.Data.DataSet = New System.Data.DataSet
    dataAdapter.Fill(dataSet)
    dim there as boolean
    If dataset.Tables(0).Rows.Count >0 then
    there = true
    else
    there = false
    end if
    return there
    end function



    '######################### function to add a new user to the users table
    ###################
    public function addUser(byVal user_type_id as integer, company as string,
    firstname as string, surname as string, card_type_id as integer, card_no as
    string, card_security_no as string, card_exp_mo as integer, card_exp_yr as
    integer, password as string, email as string, day_phone as string, eve_phone
    as string, mobile as string, fax as string, address1 as string, address2 as
    string, address3 as string, city as string, postcode as string, county_id as
    integer, country_id as integer, url as string, admin_level_id as integer,
    active as integer, registered as integer)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "Insert into users([user_type_id],
    [company], [firstname], [surname], [card_type_id], [card_no],
    [card_security_no], [card_exp_mo], [card_exp_yr], [password], [email],
    [day_phone], [eve_phone], [mobile], [fax], [address1], [address2],
    [address3], [city], [postcode], [county_id], [country_id], [url],
    [admin_level_id], [active], [registered]) values (" & user_type_id & ", '" &
    company & "', '" & firstname & "', '" & surname & "', " & card_type_id & ",
    '" & card_no & "', '"& card_security_no & "', " & card_exp_mo & ", " &
    card_exp_yr & ", '" & password & "', '" & email & "', '" & day_phone & "',
    '" & eve_phone & "', '" & mobile & "', '" & fax & "', '" & address1 & "', '"
    & address2 & "', '" & address3 & "', '" & city & "', '" & postcode & "', " &
    county_id & ", " & country_id & ", '" & url & "', " & admin_level_id & ", "
    & active & ", " & registered & ")"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    dbCommand.executenonquery()
    dbConnection.close()
    dbconnection = nothing
    end function

    '######################### function to update a user to the users in the
    users table ###################
    public function updateUser(byVal user_type_id as integer, company as string,
    firstname as string, surname as string, card_type_id as integer, card_no as
    string, card_security_no as string, card_exp_mo as integer, card_exp_yr as
    integer, password as string, email as string, day_phone as string, eve_phone
    as string, mobile as string, fax as string, address1 as string, address2 as
    string, address3 as string, city as string, postcode as string, county_id as
    integer, country_id as integer, url as string, admin_level_id as integer,
    active as integer, registered as integer)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "Update [users] Set [user_type_id]
    = " & user_type_id & ", [company] = '" & company & "', [firstname] = '" &
    firstname & "', [surname] = '" & surname & "', [card_type_id] = " &
    card_type_id & ", [card_no] = '" & card_no & "', [card_security_no] ='" &
    card_security_no & "', [card_exp_mo] =" & card_exp_mo & ", [card_exp_yr] = "
    & card_exp_yr & ", [password] = '" & password & "', [email] = '" & email &
    "', [day_phone] = '" & day_phone & "', [eve_phone] = '" & eve_phone & "',
    [mobile] = '" & mobile & "', [fax] = '" & fax & "', [address1] = '" &
    address1 & "', [address2] = '" & address2 & "', [address3] = '" & address3 &
    "', [city] = '" & city & "', [postcode] = '" & postcode & "', [county_id] =
    " & county_id & ", [country_id] = " & country_id & ", [url] = '" & url & "',
    [admin_level_id] = " & admin_level_id & ", [active] =" & active & ",
    [registered] =" & registered
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    dbCommand.executenonquery()
    dbConnection.close()
    dbconnection = nothing
    end function


    '######################### function to delete a user from the users table
    based on user_id_type ###################
    public sub deleteUser(byVal email as string)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "DELETE * FROM users where email =
    '" & email & "'"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    dbCommand.executenonquery()
    dbConnection.close()
    dbconnection = nothing

    end sub

    end class

    '###########################################################################
    #######
    '################### Class to handle products interaction
    #########################
    '###########################################################################
    #######

    Public class handle_products
    public function getProdTypes()
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [product_type].* FROM
    [product_type]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection
    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close
    dbConnection = nothing
    end function

    '##################### function to get product categories by product type
    ###############

    public function getProdCatsByType(ByVal prodType As integer)
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [product_cats].* FROM
    [product_cats] WHERE [product_cats].[prod_type_id] =" & prodType & ";"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close()
    dbConnection = nothing
    end function

    end class

    '###########################################################################
    ################
    '###################### Class with utility functions for database editing
    ##################
    '###########################################################################
    ################

    public class handle_db

    '###################### function to return the list of ALL countries from
    the database #####
    function getAllCountries
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [countries].* FROM
    [countries]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close()
    dbConnection = nothing
    end function

    '##################### function to return list of active countries
    #########################
    function getActiveCountries
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [active_countries_view].*
    FROM [active_countries_view]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close()
    dbConnection = nothing
    end function

    '###################### function to return the list of ALL counties from the
    database #####
    function getAllCounties
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [counties].* FROM
    [counties]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close()
    dbConnection = nothing
    end function

    '##################### function to return list of active countries
    #########################
    function getActiveCounties
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [active_counties_view].*
    FROM [active_counties_view]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close()
    dbConnection = nothing
    end function

    '##################### function to return list of user_types
    #########################
    function getUserTypes
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [user_type].* FROM
    [user_type]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close()
    dbConnection = nothing
    end function

    '##################### function to return list of admin_levels
    #########################
    function getAdminLevels
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [admin_levels].* FROM
    [admin_levels]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close()
    dbConnection = nothing
    end function

    '###################### function to return the list of mailing list
    subscriber types from the database #####
    function getMailTypes
    dim cusCtl as custusercontrol = new custusercontrol
    dim connectionString As String = cusCtl.connection_string

    Dim dbConnection As System.Data.IDbConnection = New
    System.Data.OleDb.OleDbConnection(connectionString)
    dbConnection.open()
    Dim queryString As String = "SELECT [mail_type].* FROM
    [mail_type]"
    Dim dbCommand As System.Data.IDbCommand = New
    System.Data.OleDb.OleDbCommand
    dbCommand.CommandText = queryString
    dbCommand.Connection = dbConnection

    Dim dataAdapter As System.Data.IDbDataAdapter = New
    System.Data.OleDb.OleDbDataAdapter
    dataAdapter.SelectCommand = dbCommand
    Dim dataReader
    datareader = dbcommand.executeReader()
    Return dataReader
    dbConnection.Close()
    dbConnection = nothing
    end function
    end class
    end namespace

    Is there something really obvious here I'm overlooking - as I mentioned in
    my previous message, I am a asp.net newbie.

    Regards,

    Joe




    "Peter O'Reilly" <!N!O!.S!P!AM!> wrote in message
    news:...[color=blue]
    > I use the Jet engine with the Net v1.0 and 1.1 framework in Win2k server
    > without any problems.
    > Where are you getting the "unspecified error" ? Or what is reporting such
    > error? It's not much to go on.
    >
    > For starters, do have write access writes, virtual and NTFS for the access
    > mdb file?
    > --
    > Peter O'Reilly
    >
    >[/color]
     
    Joe, Nov 15, 2003
    #4
  5. use structured exception handling

    Joe,

    My recommendation to you is to use structured exception handling present in
    VB.NET. I highly recommend using this language feature.

    I recommend writing code to dump the exception's text into a log file in the
    catch statement. From there you will be able to pin-point the error. It's
    definitely worth the time invested to implement, which is really not much in
    light of the time troubleshooting errors after they occur.

    Likewise, I would have the database connection closing and destruction
    performed in the finally statement to ensure that it does in fact close
    during normal operation or a run time error. Ensuring db connections,
    particularly for file based MS Access, is most important and now with
    structured exception handling being included with VB, to that end it is of
    great fail-safe benefit.

    Good luck.

    --
    Peter O'Reilly
     
    Peter O'Reilly, Nov 17, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. david
    Replies:
    1
    Views:
    397
    Saurabh Nandu
    Nov 17, 2003
  2. Joe Van Meer
    Replies:
    2
    Views:
    646
    Peter O'Reilly
    May 5, 2004
  3. =?Utf-8?B?RGF2ZQ==?=

    The Microsoft Jet database engine cannot open the file

    =?Utf-8?B?RGF2ZQ==?=, Aug 3, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    976
    =?Utf-8?B?RGF2ZQ==?=
    Aug 5, 2005
  4. Dan Matthews

    80004005 and Jet Engine errors

    Dan Matthews, Dec 10, 2003, in forum: ASP General
    Replies:
    3
    Views:
    134
    Ken Schaefer
    Dec 11, 2003
  5. aa
    Replies:
    6
    Views:
    347
    Aaron Bertrand - MVP
    Jan 26, 2004
Loading...

Share This Page