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 E:Drive 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