connect to database

Discussion in 'C#' started by rojojat, Jul 16, 2009.

0
  1. #1
    Hi,
    I am a newbie to asp.net. I created a simple database in access
    database name=db2.mdb
    table name=table1
    fields= id title content

    does anyone have a script to select all records

    and a script to add records ?

    Thanks
     
    rojojat, Jul 16, 2009 IP
  2. camjohnson95

    camjohnson95 Active Member

    Messages:
    737
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #2
    I still use the old method used in Classic ASP. Here:
    

    Dim pth As String = Server.MapPath("db2.mdb")
    Dim objConn As Object

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        objConn = CreateObject("ADODB.Connection")
        objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pth & ";"
        objConn.Open()
        OpenDB()
        objConn.Close()
    End Sub

Sub OpenDB()
	Dim objRS As Object
	Dim sql As String

        objRS = CreateObject("ADODB.Recordset")
        sql = "SELECT * FROM table1"
        objRS.Open(sql, objConn, 1, 3)

	'read all values:
 	Do While Not objRS.EOF
		Response.Write(ObjRS("title").value) & "<br />"
		Response.Write(ObjRS("id").value) & "<br />"
		Response.Write(objRS("content").value) & "<br />"
		objRS.MoveNext()
	Loop

	objRS.Close()
End Sub
    Code (markup):
     
    camjohnson95, Jul 17, 2009 IP
  3. camjohnson95

    camjohnson95 Active Member

    Messages:
    737
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    60
    #3
    add records like this, i would say that ID is an AutoNumber so you don't need to add that:
    
objRs.AddNew()
objRS.Fields("title") = "test"
objRS.Fields("content") = "content"
objRS.Update()
    Code (markup):
     
    camjohnson95, Jul 17, 2009 IP
  4. vihutuo

    vihutuo Well-Known Member

    Messages:
    1,511
    Likes Received:
    34
    Best Answers:
    0
    Trophy Points:
    180
    #4
    just drag and drop the gridview control and follow the wizard
     
    vihutuo, Jul 19, 2009 IP
  5. ASPMachine

    ASPMachine Peon

    Messages:
    723
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #5
    First of all, create a myupdate.asp file with following given code.

    Code of myupdate.asp
    
<%
Function GetConnection()
	Dim databasePath, connectionString

'(STEP-1)------------------------------------------------------------
	databasePath = "[B]database\db2.mdb[/B]"
'--------------------------------------------------------------

'Connect to the Access database using a DSN-Less connection
	Set GetConnection = Server.CreateObject("ADODB.Connection")	
	connectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath(databasePath)	
	GetConnection.Open connectionString
End Function

'(STEP-2)------------------------------------------------------------
Function Getdata()
'---------------------------------------------------------------
	Dim con, rs, sql
	'Get the database connection
	Set con = GetConnection()
	
	Set rs = Server.CreateObject("ADODB.Recordset")

'(STEP-3)------------------------------------------------------------
	sql = "SELECT * FROM [B]table1[/B]"
'-----------------------------------------------------------

	rs.Open sql, con, 3, 3
	If (rs.EOF) Then
'(STEP-4)------------------------------------------------------------
		rs.AddNew()
		[B][COLOR="Blue"]rs("id")[/COLOR][/B] = [COLOR="Red"]request("id")[/COLOR]
		[COLOR="Blue"]rs("title")[/COLOR] = [COLOR="Red"]request("title")[/COLOR]
		[COLOR="Blue"]rs("content")[/COLOR] = [COLOR="Red"]request("content")[/COLOR]
		rs.Update()
'------------------------------------------------------
	
	else
		'nothing
	End If


	rs.Close()
	con.Close()
	
	Set rs = Nothing
	Set con = Nothing
[B]response.redirect("http://yoursite/anypage/")[/B]
End Function

%>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title></title>
</head>

<body>

[B]<%=Crawler()%>[/B]
</body>

</html>
    Code (markup):
    STEP1 = Database (db2.mdb) path. Here, the database db2.mdb is under the database folder.
    STEP2=Getdata() is the function name which will execute database update action.
    STEP3=table1 is the name of the table of database db2.mdb where the request data will be updated.
    STEP4=The rs("id"), rs("title") and rs("content") are the field name of the db2.mdb database and request("id"), request("title") and request("content") are the name of text boxes of the anyname.html.

    The response.redirect("http://yoursite/anypage/") code will redirect user after update the database. You can fix any page as you want.


    Secondly create a anyname.html file with following given code.

    
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Updating Database</title>
</head>

<body>

<form method="POST" action="myupdate.asp">
  <p><input type="text" name="id" size="20"></p>
  <p><input type="text" name="title" size="20"></p>
  <p><input type="text" name="content" size="20"></p>
  <p><input type="submit" value="Submit" name="submit"></p>
</form>

</body>

</html>
    Code (markup):
    keep both the file myupdate.asp and anyname.html file into the same folder. Run the anyname.html file from localhost or from your server. But don't forget to make write permission of the database db2.mdb if you want to test on your server. In the localhost, we don't need write permission for access database but in the server, you must need to make write permission otherwise no data will update. You can make write permission of your database file from your hosting control panel.

    Thanks ASPMachine
     
    ASPMachine, Jul 19, 2009 IP