ASP.NET and Access databases

[artsapimp]

I have taken the tutorials within vbworld regarding .NET and I love it so far. I would like to start using it today instead of the ASP I am currently using but I don't know how to use databases yet.

How would I write to a database (Access or SQL)? I know how to gather the data on the page but I don't know how to move it into a database. rst.Open....?

I never got into VB enough to get into databases either so sample code would be greatly appreciated.

Thank you.

[Cander]

See if this helps. Follow the Add_Click sub to see how to add to a databas using the Insert SQL command

' ---------------------------------
' Code by Chris Andersen(AKA Cander)
' This example shows how to show data from an access database in the DataGrid control
' and how to write a new record to the database.
' ---------------------------------
 
' Using Code Behind, we can fully seperate presentation from code.
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Web.UI
Imports System.Web.UI.WebControls
 
Public Class Cander1
    ' Declare the class and have it inherit the System.Web.UI.Page class. This is needed for 
    ' accessing the ASP.NET Page's Events/Properties/Methods
    Inherits Page
    
    ' We need to declare all web controls we use. Since the aspx page will inherit this class, they
    ' must be Public in order to be visible.
    Public lblName As Label
    Public lblAge As Label
    Public MyDataGrid As DataGrid
    Public tbName As TextBox
    Public tbAge As TextBox
    Public btnAdd As Button
    Public btnView As Button
    Dim myDS As New DataSet
    Dim myConn As OleDbConnection
    Dim myCommand As OleDbDataAdapter
    Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.Mappath("/candersen/db/test.mdb")
    Dim strSQL As String
 
    Sub Page_Load(Sender As Object, E As EventArgs)
        ' This sub is called everytime the Page Loads
    End Sub
 
    Sub Add_Click(Sender As Object, E As EventArgs)
        Dim strName As String = Name.Text
        Dim strAge As String = Age.Text
 
        ' Write posted data to the lables
        lblName.Text = strName
        lblAge.Text = strAge
 
        ' Insert Posted Data to database
        strSQL = "Insert Into Test(Name,Age) VALUES('" & strName & "','" & strAge & "')" 
 
        ' Populate DataSet
        FillDataSet()
 
        ' Update DataGrid
        UpdateGrid()
    End Sub
 
    Sub UpdateGrid()
        ' Updates the DataGrid
        strSQL = "Select * FROM Test"
 
        'Populate DataSet
        FillDataSet()
 
        ' Now set the DataGrid's source to the Dataset and bind it
        MyDataGrid.DataSource = myDS.Tables("Test").DefaultView
        MyDataGrid.DataBind()
 
    End Sub
 
    Sub FillDataSet()
        ' Recordset no longer exists for ADO.NET. Create DataSets and populate it from the data you get from
        ' a DataAdapter or other source(like XML). In this case we are getting ADO DataSet using the System.Data.OleDb.OleDbDataAdapter 
                
        ' Start Connection to ADO source
        myConn = New OleDbConnection (strConnString)
 
        ' Run the Sql statement and get the Data into the DataAdapter
        myCommand = New OleDbDataAdapter(strSQL, myConn)
 
        ' Fill the DataSet and name the table
        myCommand.Fill(myDS, "Test")
        myConn.Close()
    End Sub
End Class

[artsapimp]

That is what I was looking for. Thank you very much.

This is a totally different beast than ASP isn't it? This is a lot of coding from used to take about 5 lines.

[Cander]

Well im doing alot more there than just adding a record. So really its about the same amount of code to add.

In that example is a code behind component that is inheriting the calling asp page, declaring the objects, filling a datagrid, and adding new data. It was just the only example I had handy. Just concentrate on the filldataset and add_click routines. Oh and the Imports statements

Imports System.Data
Imports System.Data.OleDb

[artsapimp]

Alright, thank you.

I will give that a try.

[briancps]

Cander
Wonder if you can help
I browsed the forums to try to solve my problem and came across this thread which you contributed to which I thought might help me.
I have been programming in Vb for 10 years but VB.NET sometimes leaves me bewildered.

I have a web site I have developed using an Access 2002 DB. It all works fine if I hard code the actual database location from my Erive as below

Public dbcConnection As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = e:\irean\ireanweb\ireantest.MDB;"

as you can see the project and all the associated VB files are in e:\irean\ireanweb and that where the DB is.

I am preparing to upload it my clients Web Server and am a little unsure of where my DB is going to sit - I assume with all the HTML and ASPX files in my clients web site.

I changed the connection string as per your example - dont understand the system.mapppath ?? but now its looking in \inetpub\wwwroot for the DB

Is there some advice you can give me to make this uploading as painless as possible or do you know of any resources I could scoure to help me

thanks
BH