ASP.Net Access Db Connectivity Question 1

jessedh · Dec 1, 2003

Hello All,

I have written a few relatively simple classic ASP scripts that connect to an access db and return data, etc based on user input query strings.

I recently received a copy of VS.Net and have been playing with the ASP.net stuff and can't sort out how to replicate the functionality. I have seen tons of tutorials about how to write .Net w/o VS.Net, but I would really like to find a tutorial somewhere that describes how in detail to use the VS.Net environment for Db connectivity.

Thanks in Advance for you help....
Jesse

kd7yen · Dec 1, 2003

IF your using MS SQL server you need to put it as a system datatype in your OBDC Connections

heres some sample code from my own site
<%@ page aspcompat=true
<html>
<body>
<%
dim oConn
dim sConn
dim sCmd
dim rs
dim oField
'setup the connection
oConn = server.CreateObject("ADODB.Connection&quot

sConn = "Provider=MSDASQL.1;Persist Security Info=False;UID=sa;Password=;Inital Catalogue=math"
oConn.open(sConn)
sCmd = "your sql statement goes here"
... i dont know thee rest off the top of my head about executing the SQL statement but that will get you connected
if you need more help please contact me at KD7YEN@excite.com

jessedh · Dec 1, 2003

I am looking for something more along the lines of an Access string. Also something to show me how to use the datagrid, etc tools in VS.Net

DJCordel · Dec 1, 2003

Hello...

Here is one that I have used for an Access database connection....I have included some code for using a datagrid, and displaying the contents of the Access database inside of it. This is just a simple example, and a large amount of modifications can be done to it to achieve the results your looking for.

Code:

<%@ Page Language=&quot;vb&quot; %>
<%@ import Namespace=&quot;System.Data&quot; %>
<%@ import Namespace=&quot;System.Data.OleDb&quot; %>

<script runat=&quot;server&quot;>

    Sub Page_Load (Source As Object, E As EventArgs)

            Dim strConnection As String = &quot;Provider=Microsoft.Jet.OleDb.4.0;data source=&quot; & Server.Mappath(&quot;\Database\YourAccessFile.mdb&quot;)
            Dim ojbConnection As New OleDbConnection(strConnection)
            Dim strSQL As String = &quot;SELECT * FROM YourTable&quot;
            Dim ojbCommand As New OleDBCommand(strSQL, ojbConnection)

            ojbConnection.Open()
            dgNameList.DataSource = ojbCommand.ExecuteReader()
            dgNameList.DataBind()
            ojbConnection.Close()

    End Sub

</script>

<html>
<head>
</head>
<body>
	<asp:DataGrid id=&quot;dgNameList&quot; runat=&quot;server&quot; />
</body>
</html>

Also, this example uses a datareader, vs. the dataset object. To much to go into now, but if you are looking for advance functionality above and beyond just displaying the information, you will need to look into using the Dataset object instead.

Hope that helps!

-Jason

Isadore · Dec 1, 2003

One more Access connectivity schema:

This Sub populates a Listbox.

Private Sub listContactsList()
listContacts.Items.Clear()
Dim strAwwCode As String = Left(Request.QueryString("AwwSiteCode&quot

,5)
Dim dbconnCt As OleDbConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.MapPath("fpdb\Contacts.mdb;&quot

)
Dim DBCommand = New OleDbCommand ("SELECT WebMasterContacts.Contact_ID, WebMasterContacts.ContactName FROM (tblGC RIGHT JOIN WebMasterContacts ON tblGC.Contact_ID = WebMasterContacts.Contact_ID) LEFT JOIN tblAwwCode ON tblGC.GrpID = tblAwwCode.GrpID WHERE tblAWWCode.AwwCode='" & Left(Request.QueryString("AwwSiteCode&quot

,5) & "'" & " ORDER BY WebMasterContacts.Last", dbconnCt)
Dim reader As OleDbDataReader
Try
dbconnCt.Open()
reader = DBCommand.ExecuteReader()
Do While reader.Read()
Dim NewItem As New ListItem()
NewItem.Value = reader("Contact_ID&quot

NewItem.Text = reader("ContactName&quot

listContacts.Items.Add(NewItem)
Loop
reader.Close()
Catch err As Exception
lblResults.Text = "Error reading list of names. "
lblResults.Text &= err.Message
Finally
If (Not dbconnCt Is Nothing) Then
dbconnCt.Close()
End If
End Try
End Sub

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP.Net Access Db Connectivity Question 1

jessedh

MIS

kd7yen

Programmer

jessedh

MIS

DJCordel

IS-IT--Management

Isadore

Technical User

Similar threads

Part and Inventory Search

Sponsor