ASP/SQL programming help

squashed_Frog · #1 May 12th, 2006, 21:58

Hey people i'm a third year uni student studying Internet & Multimedia computing, i've got an assignment due in in a couple of days but there's one part i just can't seem to get working!

I have created a grade keeping database that is accessed through active server pages and i cannot get the update grade function to work.

My tables are set up as follows:

STUDENT contains fields SID, SNAME, ADDRESS, POST_CODE
COURSE contains fields CID, CNAME, DEPARTMENT
STUDENTCOURSE contains fields SID(fk), CID(fk), GRADE

Below is the code i am using to update the values through a form but i just can't seem to get it working...any help would be much appreciated as i'm starting to panic a bit :oS...cheers!!

Page 1 : Lists all the relevant records (updategrade.asp)

<<A href="mailto:%@LANGUAGE="VBSCRIPT">%@LANGUAGE="VBS CRIPT" CODEPAGE="1252"%>
<html>
<head>
<title>Update Grade</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="362E7E" text="#FFFFFF">
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "e:\domains\s\squashed-frog.com\user\htdocs\webdevelopment\ryanlstudent.m db"
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT SID, CID, GRADE FROM STUDENTCOURSE",conn
%>
<font color="#FFFFFF" size="4" face="Arial, Helvetica, sans-serif"><strong>Update Grade</strong></font>
<br><br>
<font color="#FFFFFF" size="2" face="Arial, Helvetica, sans-serif">Update a Grade by clicking on the appropriate Student ID </font>
<br><br>
<table width="100%" border="1" bordercolor="#0066FF">
<tr align="center">
<td><strong><font size="2" face="Arial, Helvetica, sans-serif"> Student ID</font></strong></td>
<td><strong><font size="2" face="Arial, Helvetica, sans-serif"> Course ID</font></strong></td>
<td><strong><font size="2" face="Arial, Helvetica, sans-serif"> Grade</font></strong></td>
</tr>
<% do until rs.EOF %>
<tr>
<form method="post" action="updateGradeCode.asp">
<%
for each x in rs.Fields
if x.name="GRADE" then%>
<td> <input type="submit" name="GRADE" value="<%=x.value%>">
</td>
<%else%>
<td><font color="#FFFFFF" size="2" face="Arial, Helvetica, sans-serif">
<%Response.Write(x.value)%>
</font></td>
<%end if
next
%>
</form>
<%rs.MoveNext%>
</tr>
<%
loop
conn.close
%>
</table>
</body>
</html>

Page 2: updates the relevant record through a form "supposedly" (updategradecode.asp)

<<A href="mailto:%@LANGUAGE="VBSCRIPT">%@LANGUAGE="VBS CRIPT" CODEPAGE="1252"%>
<html>
<head>
<title>Update Grade Code</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="362E7E" text="#FFFFFF">
<h2><font size="4" face="Arial, Helvetica, sans-serif"><strong>Update Grade</strong></font></h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "e:\domains\s\squashed-frog.com\user\htdocs\webdevelopment\ryanlstudent.m db"
Dim grade
grade=Request.Form("GRADE")
if Request.form("confirmed")="" then
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM STUDENTCOURSE WHERE GRADE='" & grade & "'",conn
%>

<form method="post" action="updateGradeCode.asp">
<input type="hidden" name="confirmed" value="1" />
<table>
<%for each x in rs.Fields%>
<tr>
<td><%=x.name%></td>
<td><input name="<%=x.name%>" value="<%=x.value%>"></td>
<%next%>
</tr>
</table>
<br />
<input type="submit" value="Update">
</form>
<%
else
sql="UPDATE STUDENTCOURSE SET"
sql=sql & "SID='" & Request.Form("SID") & "',"
sql=sql & "CID='" & Request.Form("CID") & "',"
sql=sql & "GRADE='" & Request.Form("GRADE") & "'"
sql=sql & "WHERE GRADE='" & grade & "'"

on error resume next
conn.Execute sql
if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & grade & " was updated!")
end if
end if
conn.close
%>
</body>
</html>

I realise this is an uuuber long post with laods of pasted code but ANY light anybody could shed on the subject would be much appreciated! ...cheers!!

iZone · #2 May 13th, 2006, 13:05

From your update query

sql=sql & "SID='" & Request.Form("SID") & "',"
sql=sql & "CID='" & Request.Form("CID") & "',"

if they are numeric fields you shouldn't put single quotes around it e.g. '4' should just be 4.

squashed_Frog · #3 May 13th, 2006, 17:08

Hey! cheers for the advice...however it doesn't seem to be working, all three fields (SID, CID and GRADE) are numeric fields so i changed my query to;

sql="UPDATE STUDENTCOURSE SET"
sql=sql & "SID='" & Request.Form(SID) & "',"
sql=sql & "CID='" & Request.Form(CID) & "',"
sql=sql & "GRADE='" & Request.Form(GRADE) & "'"
sql=sql & "WHERE GRADE='" & grade & "'"

It still doesn't seem to be working am i'm still getting the "no update permissions" string displayed when i try to submit the query!

Sorry to be a pain i'm really lacking in my SQL knowledge!! ...do u think it could be something else???

Ur help is MUCH appreciated! cheers!

crazyluv · #4 May 15th, 2006, 03:49

There are a few thing I found in your code that are incorrect.

Code: Select all

conn.Open "e:\domains\s\squashed-frog.com\user\htdocs\webdevelopment\ryanlstudent.m  db"

The extension has a space in it. I suspect that the one you're testing doesn't but here both conn.open are like this.

Code: Select all

sql="UPDATE STUDENTCOURSE SET"
  sql=sql & "SID='" & Request.Form(SID) & "',"
  sql=sql & "CID='" & Request.Form(CID) & "',"
  sql=sql & "GRADE='" & Request.Form(GRADE) & "'"
  sql=sql & "WHERE GRADE='" & grade & "'"

You are constructing this wrong. You have failed to add spaces inbetween SET and SID also you need one before WHERE.

Also

Code: Select all

if err<>0 then
response.write("No update permissions!")
else
response.write("Record " & grade & " was updated!")
end if

is not caching the error correctly as you may have permision but it still saying you don't because of the incorrect syntax in the SQL. You need to change that. I can't remember the code for detecting no permisions but you should be able to find it real easy on the net.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Object Oriented Programming with PHP?	JustinStudios	PHP Forum	9	Jan 20th, 2008 11:25
Recursive programming in php	masha	PHP Forum	4	Oct 1st, 2007 12:37
Problem with Programming	SuseLinux	PHP Forum	6	Nov 4th, 2006 14:15
programming-designs.com	programming-designs	Free Web Site Critique	5	Aug 27th, 2006 19:31

		Webforumz.com > Main Forums > Program Your Website > Classic ASP
ASP/SQL programming help