101 ADO Help

  • Thread starter Thread starter CES
  • Start date Start date
C

CES

This is the first time I've tried to convert my .asp ado connection to the
..NET framework and I'm totally lost. Most of the examples I've seen are
incompliant or more complicated then I can comprehend. I was wondering If
someone could point me to a referance that showes an entire example of
connecting to a database and adding a record to a table within the database.

I'm pretty sure that the connection string below is correct:

Dim dbPath As String = "c:\database.mdb"
Dim cnString as String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
& dbPath
Dim cnObj as OleDbConnection = New OleDbConnection(cnString)
cnObj.Open()

Dim sql AS String = "SELECT * FROM Visitors"
Dim objCom as OleDbCommand = New OleDbCommand(sql, cnObj)

But here is where I get lost I'm not sure if I need to open a
OleDbDataReader in order to add a record to the database or what. The code
I'm trying to replicate is as follows:

dbPath = "c:\database.mdb"
Set cnLog = Server.CreateObject("ADODB.Connection")
cnStLog = "driver={Microsoft Access Driver (*.mdb)};" & '"dbq=" & dbPath
cnLog.Open cnStLog,"",""

Set rsLog = Server.CreateObject("ADODB.Recordset")
sql = "Select * From Visitors"

rsLog.Open sql, cnLog, 2, 3

rsLog.AddNew
rsLog("X") = "New Data"
rslog.Update

rsLog.Close
Set rsLog = Nothing
cnLog.Close
Set cnLog = Nothing

Thanks for any help.
CES
 
I don't know of any good code samples for converting classic asp/ado to .NET specifically, but you can download "101 VB Net Code Samples" from Microsoft and there's a lot of good examples for doing lots of stuff.

As far as adding records is concerned, you cannot do that with a Data Reader - it only reads.

The equivalent of an "AddNew" and "Update" in classic ADO would an OleDBCommand object where CommandText would be a SQL statement that INSERTs a new row into the table, with the right column values, etc. (Classic ADO has command objects, too.)
 
Hi Ces,

You try to mix up AdoNet and classic Ado.

When you use classic ADO (2.x etc) then you can use that in dotNet, you see
I wrote use, that is because it are than seperated DLL's and you do not use
the benefits from dotNet.

I advice you to use AdoNet. Try for that the resource kit, when there is
written in this SQLclient.SQL, than in this resource kit almost all samples
can be translated to OleDb.OleDb. You have than as well to use the right
connection string for what I saw you got a link for in this thread.

http://msdn.microsoft.com/asp.net/asprk/

I hope this helps?

Cor
 
¤ This is the first time I've tried to convert my .asp ado connection to the
¤ .NET framework and I'm totally lost. Most of the examples I've seen are
¤ incompliant or more complicated then I can comprehend. I was wondering If
¤ someone could point me to a referance that showes an entire example of
¤ connecting to a database and adding a record to a table within the database.
¤
¤ I'm pretty sure that the connection string below is correct:
¤
¤ Dim dbPath As String = "c:\database.mdb"
¤ Dim cnString as String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
¤ & dbPath
¤ Dim cnObj as OleDbConnection = New OleDbConnection(cnString)
¤ cnObj.Open()
¤
¤ Dim sql AS String = "SELECT * FROM Visitors"
¤ Dim objCom as OleDbCommand = New OleDbCommand(sql, cnObj)
¤
¤ But here is where I get lost I'm not sure if I need to open a
¤ OleDbDataReader in order to add a record to the database or what. The code
¤ I'm trying to replicate is as follows:
¤
¤ dbPath = "c:\database.mdb"
¤ Set cnLog = Server.CreateObject("ADODB.Connection")
¤ cnStLog = "driver={Microsoft Access Driver (*.mdb)};" & '"dbq=" & dbPath
¤ cnLog.Open cnStLog,"",""
¤
¤ Set rsLog = Server.CreateObject("ADODB.Recordset")
¤ sql = "Select * From Visitors"
¤
¤ rsLog.Open sql, cnLog, 2, 3
¤
¤ rsLog.AddNew
¤ rsLog("X") = "New Data"
¤ rslog.Update
¤
¤ rsLog.Close
¤ Set rsLog = Nothing
¤ cnLog.Close
¤ Set cnLog = Nothing

There isn't a true equivalent to this code, but the closest method would be to use a DataAdapter and
DataSet/DataTable. You can then add rows to the DataTable and perform batch Update using the
DataAdapter.

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb"

Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
AccessConnection.Open()

Dim AccessDataAdapter As New System.Data.OleDb.OleDbDataAdapter
AccessDataAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand("Select * from Table3",
AccessConnection)
Dim AccessCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(AccessDataAdapter)

Dim AccessDataTable As New DataTable("Table3")
AccessDataAdapter.Fill(AccessDataTable)

Dim DataTableRow As DataRow = AccessDataTable.NewRow
DataTableRow("ID") = "6"
DataTableRow("Name") = "Jim Jones"
AccessDataTable.Rows.Add(DataTableRow)

AccessDataAdapter.Update(AccessDataTable)
AccessConnection.Close()

You can also use Access QueryDefs (w/parameters) or SQL statements with the
System.Data.OleDb.OleDbCommand class.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top