PDA

Click to See Complete Forum and Search --> : database update .asp

chrismartz
02-26-2004, 05:37 PM
I am making a webpage for teachers @ my school to put homework and other info on....I can get the info to show up through the access database but I am wondering how to update the info through using a online form instead of me going into the database and doing it....any help would be greatly appreciated!
buntine
02-26-2004, 07:14 PM
Ok, really briefly.

Set up a form with all of the relevent controls and then have that directed to an asp page like s.

<form target="process.asp" method="post">
...
</form>


Now, in your ASP page (process.asp) collect the data that has been passed to the server using the request.form() method as follows.

Dim firstname, surname

firstname = Request.form("yourFNameCtrl")
surname = Request.form("yourSNameCtrl")


To update records from a certain table we use SQL's UPDATE statement. Check out the following SQL query.

sql = "UPDATE tableName SET field_one=2, field_two=20"


You will need to use variables in this query, so take a look at the following which updates the first and last names of a user.

sql = "UPDATE tableName SET first_name = '" & firstname & "', surname = '" & sur_name & "'"


We can also add add a WHERE clause to the query so we only affect one record.

sql = "UPDATE tableName SET first_name = '" & firstname & "', surname = '" & sur_name & "' WHERE user_id = 3"


Depending on your level ok knowledge in ASP, you may need more help. Just post if so.

Regards,
Andrew Buntine.
chrismartz
02-27-2004, 09:02 AM
this is what i have on a page to show the results of the choice, do i need to create a new page for the update?

<html>
<head>
<title>Homework</title>
</head>
<body>
<%
Dim objDC, objRS

' Create and establish data connection
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30

'Use this line to use Access
objDC.Open "DBQ=" & Server.MapPath("../db/zytre04.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;", "username", "password"

'Our SQL Server code - use above line to use sample on your server
'objDC.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
' & "Initial Catalog=samples;Connect Timeout=15;" _
' & "Network Library=dbmssocn;", "samples", "password"

' Create recordset and retrieve values using the open connection
Set objRS = Server.CreateObject("ADODB.Recordset")
' Opening record set with a forward-only cursor (the 0) and in read-only mode (the 1)

' If a request for a specific id comes in, then do it o/w just show pulldown
If Len(Request.QueryString("id")) <> 0 Then
' request record for requested id
objRS.Open "SELECT * FROM homework WHERE id = " & Replace(Request.QueryString("id"), "'", "''"), objDC, 0, 1
' Show selected record
If Not objRS.EOF Then
objRS.MoveFirst
%>
<!-- Teacher Name -->
<center>
<span style="position:absolute; top:10px; left:270px; z-index:1000; filter:glow('strength=1,color=black'); font-size:24pt; font-family:times; font-weight:bold; color:white; cursor:default; width:462px;">
&nbsp;<%= objRS.Fields("teachRealName") %>&nbsp;
</span>

<!-- /Teacher Name -->
<br><br><table border="2">
<tr>
<td><strong>Period 1</strong></td>
<td><strong>Period 2</strong></td>
<td><strong>Period 3</strong></td>
<td><strong>Period 4</strong></td>
<td><strong>Period 5</strong></td>
</tr>
<tr>
<td align="left"><%= objRS.Fields("teachPeriod1") %></td>
<td align="left"><%= objRS.Fields("teachPeriod2") %></td>
<td align="left"><%= objRS.Fields("teachPeriod3") %></td>
<td align="left"><%= objRS.Fields("teachPeriod4") %></td>
<td align="left"><%= objRS.Fields("teachPeriod5") %></td>
</tr>
</table>
<font size=1>**homework last updated on <%= objRS.Fields("date") %></font>
<%
End If
objRS.Close
End If

objRS.Open "homework", objDC, 0, 1
' Loop through recordset and display results
If Not objRS.EOF Then
objRS.MoveFirst
' the form below calls this file only this time with an id in the QueryString
%>
<form action="homework.asp" method="get">
<select name="id">
<option></option>
<%
' Continue until we get to the end of the recordset.
Do While Not objRS.EOF
' For each record we create a option tag and set it's value to the employee id
' The text we set to the employees first name combined with a space and then their last name
%>
<option value="<%= objRS.Fields("id") %>"><%= objRS.Fields("teachName") %></option>
<%
' Get next record
objRS.MoveNext
Loop
%>
</select>
<input type="submit" value="Submit" />
</form>
<%
End If

' Close Data Access Objects and free DB variables
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
%>
</body></html>
buntine
02-27-2004, 09:07 AM
No, i think it should work in the same page. The contents must besent to the server for processing though.
chrismartz
02-27-2004, 09:22 AM
not sure how to combine your code with the code I have.....can you give code please?
buntine
02-27-2004, 09:34 AM
Ok, so you want me to integrate the INSERT INTO SQL query into your page?
If so, where do you want to have it executed? At vthe bottom, top, middle?

Regards.
chrismartz
02-27-2004, 09:41 AM
i have the form action going to process.asp and all i really need is the code that would be on process.asp that will save the information to the database and send them back to the homepage.....I have the information go to the correct person by using the teachers id(id).....I don't use an sql database but i use an access database and I open the database by:

<%
Dim objDC, objRS

' Create and establish data connection
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30

'Use this line to use Access
objDC.Open "DBQ=" & Server.MapPath("../db/zytre04.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;", "username", "password"

'Our SQL Server code - use above line to use sample on your server
'objDC.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
' & "Initial Catalog=samples;Connect Timeout=15;" _
' & "Network Library=dbmssocn;", "samples", "password"

' Create recordset and retrieve values using the open connection
Set objRS = Server.CreateObject("ADODB.Recordset")
' Opening record set with a forward-only cursor (the 0) and in read-only mode (the 1)

' If a request for a specific id comes in, then do it o/w just show pulldown
If Len(Request.QueryString("id")) <> 0 Then
' request record for requested id
objRS.Open "SELECT * FROM homework WHERE id = " & Replace(Request.QueryString("id"), "'", "''"), objDC, 0, 1
' Show selected record
If Not objRS.EOF Then
objRS.MoveFirst
%>
buntine
02-27-2004, 09:53 AM
Ok, i dont know what else i can show you... I have already explained the UPDATE SQL statement in detail.

All you will have to do on process.asp is open the database with the preceding code you posted.
And then use the UPDATE statement in conjunction with the request.form("") values which have been sent to process.asp from the preveious page.
Finally, you will close the connection objects and redirect the user to a new page. Like so:

response.buffer = true 'Set the page buffer to allow server-side redirects.

'Open database...

sql = "UPDATE tableName SET fieldOne = '"&request.form("control_one")&"', fieldTwo = '"&request.form("control_two")&"' WHERE user_id = " & request.form("user_id")

conn.execute(sql)

set conn = nothing
conn.close

response.redirect("newpage.asp")
response.end



Regards,
Andrew Buntine.
chrismartz
02-28-2004, 09:24 AM
this is what i have and i'm getting the error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/homework/work/process.asp, line 32, column 48

when i try to use it

code:
<%
response.buffer = true 'Set the page buffer to allow server-side redirects
Dim objDC, objRS

' Create and establish data connection
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30

'Use this line to use Access
objDC.Open "DBQ=" & Server.MapPath("../db/zytre04.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;", "username", "password"

'Our SQL Server code - use above line to use sample on your server
'objDC.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
' & "Initial Catalog=samples;Connect Timeout=15;" _
' & "Network Library=dbmssocn;", "samples", "password"

' Create recordset and retrieve values using the open connection
Set objRS = Server.CreateObject("ADODB.Recordset")
' Opening record set with a forward-only cursor (the 0) and in read-only mode (the 1)

' If a request for a specific id comes in, then do it o/w just show pulldown
If Len(Request.QueryString("id")) <> 0 Then
' request record for requested id
objRS.Open "SELECT * FROM homework WHERE id = " & Replace(Request.QueryString("id"), "'", "''"), objDC, 0, 1
' Show selected record
If Not objRS.EOF Then
objRS.MoveFirst
%>

<%
sql = "UPDATE homework SET teachPeriod1 = '"&amp;request.form("Period1")&amp;"', teachPeriod2 = '"&amp;request.form("Period2")&amp;"', teachPeriod3 = '"&amp;request.form("Period3")&amp;"', teachPeriod4 = '"&amp;request.form("Period4")&amp;"', teachPeriod5 = '"&amp;request.form("Period5")&amp;"' WHERE id = " &amp; request.form("id")

conn.execute(sql)

set conn = nothing
conn.close

response.redirect("homework.asp")
response.end

%>
<%
End If
End If
End If
' Close Data Access Objects and free DB variables
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
%>
buntine
02-28-2004, 10:09 AM
You have startedf only two if statements, though, you have ended 3 of them..

Remove one of the end if statements.

Regards,
Andrew Buntine.
chrismartz
02-28-2004, 10:11 AM
i'm still getting the same error....do you have msn so i can talk to you in real time?

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/homework/work/process.asp, line 33, column 48
sql = "UPDATE homework SET teachPeriod1 = '"&amp;request.form("Period1")&amp;"', teachPeriod2 = '"&amp;request.form("Period2")&amp;"', teachPeriod3 = '"&amp;request.form("Period3")&amp;"', teachPeriod4 = '"&amp;request.form("Period4")&amp;"', teachPeriod5 = '"&amp;request.form("Period5")&amp;"' WHERE id = " &amp; request.form("id")
-----------------------------------------------^
buntine
02-28-2004, 11:01 AM
Yer, I will talk to you on MSN. I have sent you a PM with my email address.
chrismartz
02-28-2004, 11:20 AM
here is the txt file
chrismartz
02-28-2004, 11:27 AM
here is the beginning page homework.asp
chrismartz
02-28-2004, 11:28 AM
here is update.asp
buntine
02-28-2004, 11:50 AM
Try this out.