Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Form won't post to database...

Status
Not open for further replies.

Snapa02

Programmer
Mar 25, 2007
9
US
Hi there - I need some help with my code if you all might be so kind. Here is the scope - I have created a 2 page database app that will create and show work orders when I create them. I am currently running this on my home IIS but will be getting ready to upload to my web server. See the first page - I had it posting to the database, but now since I have created the second page that displays the results of the database - the posting page won't post.

Here is the code of the posting page (can you show me what I am doing wrong? Please bear in mind I am somewhat programming challenged as I do not speak tech in this way. So please make your explanation easy to follow if you could.

Okay - here is my code for my form page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Ekho3 Technology Work Orders</title>
<style type="text/css">
<!--
body,td,th {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 10px;
}
-->
</style>
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) { //reloads the window if Nav4 resized
if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
<link href="style/css.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
a:link {
color: #000000;
}
a:hover {
color: #990000;
}
a:visited {
color: #000000;
}
-->
</style></head>

<body>
<form action="changerequests.asp" method="post" name="workorders" id="workorders">
<p class="heading">Ekho3 Technology Work Order Request </p>
<p><a href="changeorderresults.asp">Change Order Results</a> </p>
<div id="lrsenddata" style="position:absolute; width:200px; height:115px; z-index:1; left: 467px; top: 60px;" class="aspfont">
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:/Inetpub/
sql = "INSERT INTO workorder (clientname, mdate, workordernumber, priority, datedue, approval, designtime, description, completed, completiondate, hoursused, followupnotes) VALUES ('" & Request.Form("clientname") & "','" & Request.Form("mdate") & "','" & Request.Form("workordernumber") & "','" & Request.Form("priority") & "','" & Request.Form("datedue") & "','" & Request.Form("approval") & "','" & Request.Form("designtime") & "','" & Request.Form("description") & "','" & Request.Form("completed") & "','" & Request.Form("completiondate") & "','" & Request.Form("hoursused") & "','" & Request.Form("followupnotes") & "')"
on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>
</div>
<table border="0" cellpadding="4" cellspacing="0">
<tr>
<td valign="top">Client Name: </td>
<td><select name="clientname" id="clientname">
<option value="Bella Vista Church">Bella Vista Church</option>
<option value="Betty Rummel">Betty Rummel</option>
<option value="CD McDonald">CD McDonald</option>
<option value="Crossroads Church">Crossroads Church</option>
<option value="Data Specialists">Data Specialists</option>
<option value="Ekho3 Photography">Ekho3 Photography</option>
<option value="Ekho3 Technology">Ekho3 Technology</option>
<option value="Front Door Realty">Front Door Realty</option>
<option value="GoZabo.com">GoZabo.com</option>
<option value="Jeffrey Parker Architect">Jeffrey Parker Architect</option>
<option value="Judy Williams">Judy Williams</option>
<option value="Kim Selesky">Kim Selesky</option>
<option value="Klackle Orchards">Klackle Orchards</option>
<option value="Mari-Mar Antiques">Mari-Mar Antiques</option>
<option value="Merritt Auction Services">Merritt Auction Services</option>
<option value="Merrill & Margaret Russell">Merrill &amp; Margaret Russell</option>
<option value="Micro Visions, Inc.">Micro Visions, Inc.</option>
<option value="MVI - Bright Technologies">MVI - Bright Technologies</option>
<option value="MVI - Burns Contracting">MVI - Burns Contracting</option>
<option value="MVI - Ed Young, DDS.">MVI - Ed Young, DDS.</option>
<option value="MVI - Heather Lane Pottery">MVI - Heather Lane Pottery</option>
<option value="MVI - John Tamboer">MVI - John Tamboer</option>
<option value="MVI - Sebright Products">MVI - Sebright Products</option>
<option value="MVI - Tyrone Township">MVI - Tyrone Township</option>
<option value="MVI - United Electric">MVI - United Electric</option>
<option value="Michigan Fundraising">Michigan Fundraising</option>
<option value="Pique, LLC.">Pique, LLC.</option>
<option value="Robin & Patrick Strong">Robin &amp; Patrick Strong</option>
<option value="Rockford Package Supply">Rockford Package Supply</option>
<option value="Russell Siding">Russell Siding</option>
<option value="Sow Hope">Sow Hope</option>
<option value="The Studio Salon">The Studio Salon</option>
<option value="The Gameroom Guys">The Gameroom Guys</option>
<option value="Thomas Home Interiors">Thomas Home Interiors</option>
<option value="Trinitas Classical School">Trinitas Classical School</option>
<option value="Turk Lake Auto Sales">Turk Lake Auto Sales</option>
<option value="Wise Photography">Wise Photography</option>
</select></td>
</tr>
<tr>
<td valign="top">Date: </td>
<td><input name="mdate" type="text" id="mdate" size="27"></td>
</tr>
<tr>
<td valign="top">Priority Level: </td>
<td><select name="priority" id="priority">
<option value="1 - Urgent (ASAP)">1 - Urgent (ASAP)</option>
<option value="2 - High (2 Days)">2 - High (2 Days)</option>
<option value="3 - Medium (1 Week)" selected>3 - Medium (1 Week)</option>
<option value="4 - Low (2 Weeks)">4 - Low (2 Weeks)</option>
<option value="5 - Pending Contact">5 - Pending Contact</option>
</select></td>
</tr>
<tr>
<td valign="top">Approved: </td>
<td><select name="approval" id="approval">
<option value="Approved" selected>Yes</option>
<option value="Not Approved">No</option>
</select></td>
</tr>
<tr>
<td valign="top">Estimated Design Time: </td>
<td><input name="designtime" type="text" id="designtime" size="8">
Hrs. </td>
</tr>
<tr>
<td valign="top">Job Desciption: </td>
<td><textarea name="description" cols="45" rows="6" id="description"></textarea></td>
</tr>
<tr>
<td valign="top">Completed: </td>
<td><select name="completed" id="completed">
<option value="Completed">Yes</option>
<option value="Not Completed" selected>No</option>
</select></td>
</tr>
<tr>
<td valign="top">&nbsp;</td>
<td><div align="right">
<input type="submit" name="Submit" value="Submit">
<input type="reset" name="Reset" value="Reset">
</div></td>
</tr>
</table>
</form>
</body>
</html>

Thank you for all of your help!!
 
seems you are inserting nothing every time that page loads sense you go top down

load...
body...
form...
open db...
insert into db form collection...
load form...

see the problem? You insert into the database but your form has not yet been posted.

You need to do the insert portion in the page that is in the action of the form or condition the page to check to see if the form has been posted (user hit submit) What is the page in the action doing?

The other problem is that MM ;-) But that's my personal opinion.

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]
 
Hi there, onpnt - thank you for the reply, I appreciate it. Yes, you are right, when I submit the form - nothing is going into the database. I took off the on error resume next handle to see if I would get an error, but no - it isn't giving me an error. I'm really sorry - I am a very beginner programmer so I kind of follow what you are saying, but kind of don't. Can you tell me how you would re-order the code if it were you and then what were you talking about with the MM? Thank you for your time, I really appreciate it.
 
Snapa02, what are the names of your 2 pages? I would also recommend checking to see if the form has been submitted. i.e.

Code:
<%

if not len(request.form("submit")) = 0 then
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:/Inetpub/[URL unfurl="true"]wwwroot/ekho3/WorkOrders/workorders.mdb"[/URL]

sql = "INSERT INTO workorder (clientname, mdate, workordernumber, priority, datedue, approval, designtime, description, completed, completiondate, hoursused, followupnotes) VALUES ('" & Request.Form("clientname") & "','" & Request.Form("mdate") & "','" & Request.Form("workordernumber") & "','" & Request.Form("priority") & "','" & Request.Form("datedue") & "','" & Request.Form("approval") & "','" & Request.Form("designtime") & "','" & Request.Form("description") & "','" & Request.Form("completed") & "','" & Request.Form("completiondate") & "','" & Request.Form("hoursused") & "','" & Request.Form("followupnotes") & "')"
on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
end if
%>

For the textarea "development" I would also suggest you add a bit of code like this to handle apostrophies:

Code:
replace(Request.Form("description"),"'","''")

this is telling the server to replace single apostrophies with double one's. Apostrophies in SQL mean "end of string" therefore if a users adds a word with an apostrophy it will freak out!

cheers

nick
 
Hello Nickdel -

Thank you for getting back with me about my dilema - I appreciate it. The names of my 2 pages are changerequests.asp (this is the form and the processing page) and the other page that shows my results is changeorderresults.asp (I did not put the code for this page up here because it is working okay).

I will give your code a try and see if it will work for me. If not - I'll be back. :)
 
Hello Nickdel - I tried the code and it didn't work... It acts like it wants to submit - or that it is submitting and then comes back to the same page with no errors. (I do want it to come back to the same page because I will use the page over again after I have submitted a record). Can you comment on what the other helper had said about me maybe having my code out of order? I am not sure why it is doing this...
 
Snapa, to be honest there is no reason that I can see why it shouldn't be working. What the other chap was saying is that ideally you should put server side code at the top of the page however this makes no difference when posting forms.

Try this and see what sql is being produced, it may not be due to your asp coding but a problem adding the record to the access db (you would expect to see an error with this though):

Code:
<%
if not len(request.form("submit")) = 0 then
'set conn=Server.CreateObject("ADODB.Connection")
'conn.Provider="Microsoft.Jet.OLEDB.4.0"
'conn.Open "C:/Inetpub/[URL unfurl="true"]wwwroot/ekho3/WorkOrders/workorders.mdb"[/URL]

sql = "INSERT INTO workorder (clientname, mdate, workordernumber, priority, datedue, approval, designtime, description, completed, completiondate, hoursused, followupnotes) VALUES ('" & Request.Form("clientname") & "','" & Request.Form("mdate") & "','" & Request.Form("workordernumber") & "','" & Request.Form("priority") & "','" & Request.Form("datedue") & "','" & Request.Form("approval") & "','" & Request.Form("designtime") & "','" & Request.Form("description") & "','" & Request.Form("completed") & "','" & Request.Form("completiondate") & "','" & Request.Form("hoursused") & "','" & Request.Form("followupnotes") & "')"
on error resume next
'conn.Execute sql,recaffected
response.write sql
if err<>0 then
Response.Write("")
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
'conn.close
end if
%>

I've just copied your code exactly, run it and it posts ok.

Let me know how the SQL looks, we'll figure it out!

Nick
 
Snapa, just noticed that "on error" you dont do anything. Add some sort of error handling so we can see the problem

Code:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:/Inetpub/[URL unfurl="true"]wwwroot/workorders.mdb"[/URL]

sql = "INSERT INTO workorder (clientname, mdate, workordernumber, priority, datedue, approval, designtime, description, completed, completiondate, hoursused, followupnotes) VALUES ('" & Request.Form("clientname") & "','" & Request.Form("mdate") & "','" & Request.Form("workordernumber") & "','" & Request.Form("priority") & "','" & Request.Form("datedue") & "','" & Request.Form("approval") & "','" & Request.Form("designtime") & "','" & Request.Form("description") & "','" & Request.Form("completed") & "','" & Request.Form("completiondate") & "','" & Request.Form("hoursused") & "','" & Request.Form("followupnotes") & "')"
on error resume next
conn.Execute sql,recaffected
response.write sql
if err<>0 then
response.write err.number & " " & err.description
else
Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>

The more I think about this the more I think it is a problem with your SQL. The code above will tell you.

Cheers

Nick
 
Hello Nickdel -

Your new code worked!! It is posting to the database now, but the only thing - your error handling worked great and it is giving me back and error

- INSERT INTO workorder (clientname, mdate, priority, approval, designtime, mdescription, completed) VALUES ('','','','','','','')-2147217913 Data type mismatch in criteria expression.

I recreated the database too, by the way - and editted out some of the fields. I changed the date field to mdate and descriptions to mdescription because I wasn't sure if they were reserved fields or not. Anyway - do you know how to handle this type of error? I looked at my date field in my Access Database and it is set to date... should I set it to something else? I really appreciate your help with this - it has been bugging me all day. :)

 
Ok, the data type for your mdate field in the database is obviously set to date/time, however you are passing a text string into it. I've had a lot of problems with ASP and MS Access regarding dates because I'm in the uk but you could try using CDate which will convert the string to a date before passing this to the DB. i.e.

Code:
CDate(Request.Form("mdate"))

failing that, look up and have a play with "Datepart"

hope this helps.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top