2

I used to work a lot with classic asp. I'm currently trying the ASP.NET MVC and like it so far, but I'm missing the simple usage of the ADODB Connection. I was searching the web, for any simple and clear solution like the code below, used on classic ASP, but everything I found was way more complicated and/or not with ADODB on C#. I want Recordsets, no "UPDATE.." SQLs, Column("Name") not Column[0], and so on... Is all of this gone? 

Set db = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
db.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("data.mdb")
rs.Open "select * from test;", db, 1, 3 
rs.AddNew
rs("text") = "Hello World!"
rs.Update
rs.Close
db.Close
Set rs = Nothing   
Set db = Nothing

I know there are ObjectMappers, etc... But I would like to use the ADODB Connection with Recordsets the old fashion way. So please no anwsers, dont use it, its old or to slow etc. I'm aware of these things. Is it still possible and is there any simple working example with code.

Thanks.

Improve this question
1
  • 3
    It may seem easier to just use the older APIs in the new environment but I thoroughly recommend taking the time to invest in learning the .NET framework equivalents of these operations. You will reap the rewards many times over. Commented Oct 5, 2009 at 6:21

6 Answers 6

Reset to default
3

What you are proposing to do is similar to walking into a Ferrari showroom and asking to buy a new car, and then promptly throwing the engine out and replacing it with an old heap from a 10 year old banger with 100,000 miles on the clock. This is (in my opinion) further exacerbated by the fact that you seem to prefer creating recordsets in classic ASP to perform a simple update. This practice was discouraged years ago even within classic ASP.

Working with Access in ASP.NET is actually quite easy if you forget about the RecordSet concept, and actually get the SQL to use "UPDATE MyTable SET x = etc...". Some clear examples of simple operations using ADO.NET and Access appear here: http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

ASP.NET MVC is a great choice for you, given your background. I urge you to put the little bit of extra effort in to learn ADO.NET and use that too instead of classic ADO. You've put your big toe in the new waters. Time to hold you breath and go in all the way ;o)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

You can fill a DataSet and work with that. See the following OleDB tutorial for an explanation:

http://msdn.microsoft.com/en-us/library/aa288452(VS.71).aspx

Improve this answer

4 Comments

Yes, I found this also. But its OLE DB. How is the update here? Is the Dataset still direct to the DB connected, or is it a copy in memory. And the DB in back may alter.. and if i do update its overwriten. This looks also more complicated, even without exception handling etc.
Well, there are reasons for the complexity. One of these reasons is the ability to target other databases besides Access with the same code. It might be worth your while to study it a bit further.
any example or link what is better to do the same?
Sorry, I didn't understand the question. The link I provided has example code for an Access database.
2

What you are trying to do is an acceptable feat for any programmer that wants to find out the "missing" elements of how to connect to a database in ASP.NET. What really is the problem is that ASP.NET 'hides' the implementation details and the details of the syntax and processes used. I found out (the hard way ...on my own, due to a lack of reasonably, absolutely correct methodology / processes of writing programming code in ASP.NET), by way of 'the hard way', which is through examination of (non-helpful) code examples in books and on the Internet, through critical thinking about the problem, and, fotunately, through my experience with C++, VB5, VB6, and VB.NET (Framework 1.0) programming languages.

The proper code for accessing a MS Access (2000) DB to interact with a ASP.NET 1.1 engine on IIS 6 is:

--In the pre-HTML (before the HTML Doctype decalration or element tag) / ASP.NET script section in the MAIN .aspx page (without a code behind file), you should have:

<%@ Page Language="VB" Debug="True" EnableViewState="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)

   If Not Page.IsPostBack
      BindData() ' Only binds the data on the first page load
   End If
  End Sub


'''''''******BEGIN OF DB DATA DISLAY IN DATAGRID
  Sub BindData()
    '1. Create a connection
    Const strConnStr as String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=c:\inetpub\protected\Comments.mdb"
    Dim objConn as New OleDbConnection(strConnStr)
    objConn.Open()

'2. Create a command object for the query
    Const strSQL as String = "SELECT ID, Ethnicity, Username, Comments FROM tblMsgNotes"
    Dim objCmd as New OleDbCommand(strSQL, objConn)

'3. Create/Populate the DataReader
    Dim objDR as OleDbDataReader
    objDR = objCmd.ExecuteReader()    

    dgComments.DataSource = objDR
    dgComments.DataBind()   
  End Sub
'''''''******END OF DB DATA DISLAY IN DATAGRID


'''*************BEGIN OF EDIT, UPDATE, CANCEL BUTTONS
'Sub dgComments_Edit(sender As Object, e As DataGridCommandEventArgs)
'    dgComments.EditItemIndex = e.Item.ItemIndex
'    BindData()
'End Sub

'Sub dgComments_Cancel(sender As Object, e As DataGridCommandEventArgs)
'    dgComments.EditItemIndex = -1
'    BindData()
'End Sub

'Sub dgComments_Update(sender As Object, e As DataGridCommandEventArgs)
'   'Read in the values of the updated row
'   Dim usrID00 as Integer = e.Item.Cells(1).Text
'   Dim strethnic00 as String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
'   Dim strusrname00 as String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
'   Dim strcommnts00 as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
''Construct the SQL statement using Parameters
'    Dim strSQL as String = _
'      "UPDATE [tblMsgNotes] SET [Ethnicity] = @ethnic00, " & _
'      "[Username] = @usrname00, [Comments] = @commnt00 " & _
'      "WHERE [ID] = @usrID"

'    Const strConnString as String = _
'       "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\protected\Comments.mdb"
'    Dim objConn as New OleDbConnection(strConnString)
'    objConn.Open()

'    Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
'    myCommand.CommandType = CommandType.Text'

'    ' Add Parameters to the SQL query
''    Dim parameterProdName as OleDbParameter = _
''               new OleDbParameter("@ProdName", OleDbType.VarWChar, 75)
''    parameterProdName.Value = strName
''    myCommand.Parameters.Add(parameterProdName)'


'    Dim parameterethnic00 as OleDbParameter = _
'               new OleDbParameter("@ethnic00", OleDbType.VarWChar, 75)
'    parameterethnic00.Value = strethnic00
'    myCommand.Parameters.Add(parameterethnic00)'


''    Dim parameterUnitPrice as OleDbParameter = _
''               new OleDbParameter("@UnitPrice", OleDbType.Currency)
''    parameterUnitPrice.Value = dblPrice
''    myCommand.Parameters.Add(parameterUnitPrice)


'    Dim parameterusrname00 as OleDbParameter = _
'               new OleDbParameter("@usrname00", OleDbType.VarWChar, 75)
'    parameterusrname00.Value = strusrname00
'    myCommand.Parameters.Add(parameterusrname00)


''    Dim parameterProdDesc as OleDbParameter = _
''               new OleDbParameter("@ProdDesc", OleDbType.VarWChar)
''    parameterProdDesc.Value = strDesc
''    myCommand.Parameters.Add(parameterProdDesc)


'    Dim parametercommnts00 as OleDbParameter = _
'               new OleDbParameter("@commnts00", OleDbType.VarWChar, 75)
'    parametercommnts00.Value = strcommnts00
'    myCommand.Parameters.Add(parametercommnts00)


''    Dim parameterProdID as OleDbParameter = _
''               new OleDbParameter("@ProductID", OleDbType.Integer)
''    parameterProdID.Value = iProductID
''    myCommand.Parameters.Add(parameterProdID)


'    Dim parameterusrID00 as OleDbParameter = _
'               new OleDbParameter("@usrID00", OleDbType.Integer)
'    parameterusrID00.Value = usrID00
'    myCommand.Parameters.Add(parameterusrID00)



'    myCommand.ExecuteNonQuery()   'Execute the UPDATE query








'    objConn.Close()   'Close the connection

'    'Finally, set the EditItemIndex to -1 and rebind the DataGrid
'    dgComments.EditItemIndex = -1
'    BindData()
'End Sub

'''*************END OF EDIT, UPDATE, CANCEL BUTTONS
</script>

--In the form section of the MAIN .aspx page (without a code behind file), you should have:

<%
 'removed from dgComments DataGrid :
'    EditItemStyle-BackColor="#faebd2"
'    OnEditCommand="dgComments_Edit"
'    OnUpdateCommand="dgComments_Update"
'    OnCancelCommand="dgComments_Cancel" %>

<% 'removed from the inner 'BoundColumns' section of the DataGrid
'     <asp:EditCommandColumn EditText="Edit Info"
'          ButtonType="PushButton"
'          UpdateText="Update" CancelText="Cancel" /> %>

<form id="csvinvA2" runat="server">
<asp:DataGrid id="dgComments" runat="server"
    AllowSorting="True"
    ItemStyle-BackColor="#22e4eb"
    AlternatingItemStyle-BackColor="#ffffff"
    AutoGenerateColumns="False" CellPadding="4"
    HeaderStyle-BackColor="Black"
    HeaderStyle-ForeColor="White"
    HeaderStyle-HorizontalAlign="Center"
    HeaderStyle-Font-Bold="True">
    <Columns>

        <asp:BoundColumn HeaderText="ID" DataField="ID" 
             ReadOnly="True"/>
        <asp:BoundColumn HeaderText="Ethnic Group" DataField="Ethnicity"
                ItemStyle-HorizontalAlign="Right"/>
        <asp:BoundColumn HeaderText="Name" DataField="Username" />
        <asp:BoundColumn HeaderText="Comment"
                DataField="Comments" />
    </Columns>
</asp:DataGrid>

</form>

I quoted out the Edit, Update, and Cancel functionality subroutine clauses so that you only have the basic ''VIEW'' interface implementation, If you are going to continue learning the inner workings of accessing and the manipulation of an MS Access 2000 database with ASP.NET 1.1 or higher, I suggest you take out the comment marks in the example above and dissect the code to better understand what is happening in the ASP.NET 1.x engine under IIS.

In ASP.NET MVC, I am not sure of the actual placement in the page document that will be constructed, but for the most part, this should help you 'get on the way' to understand what is happening with ASP.NET and MS Access (2000). If you are to use other Access DB versions other than Access 2000/2003, you will need to change the ''Jet call'' to the proper 'Jet versioning' so that the .aspx page can connect to the Access DB and parse/summon the innerds of that newer version of the Access DB file.

Improve this answer

Comments

0

No its all there, you'll just need to just use COM interop to get to it. See here for how

Improve this answer

1 Comment

Ok, this I already found. But how to have the example above in C#
0

I am not sure why you would want to use ADODB.Recordset, when things have moved into .net?

Having said that, you can wrap your operations into a VB based Activex DLL and with COM interop, make calls to your DLL.

e.g. The Update you mentioned can be wrapped into a method of your public class in VB6.
Make an activex DLL of it. Consume (Add Reference) to the Activex DLL from your .net code.

By doing this, COM specific things are handled in a boundary by itself and .net doesn't need to manage life of all COM class instances, that you create in your code.

Improve this answer

Comments

0

Your life would be vastly simplified by picking up new technologies here--such as Linq2Sql. Really isn't much point in writing ADO.NET code anymore, nevermind ADO.old code.

Improve this answer

1 Comment

Linq to SQL doesn't support connections to Access databases. SQL Server Express is a better choice than Access IMO (and Linq to SQL supports it), but that is a separate discussion.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.