SunQuest
 
           Visual Basic Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Programming > Visual Basic Programming
Reload this Page

Insert into database working sporadically

Discuss Insert into database working sporadically in the Visual Basic Programming forum on ASP Free.
Insert into database working sporadically Visual Basic Programming forum discussing coding strategies, development and techniques. Go from prototype to product quickly using this robust platform.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!
  #1  
Old November 29th, 2004, 11:08 AM
suekari suekari is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Nov 2004
Posts: 2 suekari User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Insert into database working sporadically
I've working on a page designed by a web developer that no longer works for a company. It takes values that were submitted via a form and adds them to a database. It works most of the time, but they have had problems where the data is not added to the database and no error message is given - everything looks like the values were submitted only they are not in the database.

I've pasted the main bit of code for the page below. If anyone can give me any guidance, I would really appreciate it. Thanks so much!

-suekari

<%
on error resume next
'----------------------------------------------------------------------
' Build emsg - form values in ASCII form
'----------------------------------------------------------------------
Dim emsg
crlf = chr(13) & chr(10)
emsg = "Name: " & Request.Form("Name") & crlf _
& "Email: " & Request.Form("Email") & crlf _
& "Account Number: " & Request.Form("Account Number") & crlf _
& "Date read: " & Request.Form("Date read") & crlf & crlf
emsg = emsg & "1st Meter - number: " & CStr(Request.Form("1st Meter number")) & " reading: " & CStr(Request.Form("1st Meter reading")) & crlf
emsg = emsg & "2nd Meter - number: " & CStr(Request.Form("2nd Meter number")) & " reading: " & CStr(Request.Form("2nd Meter reading")) & crlf
emsg = emsg & "3rd Meter - number: " & CStr(Request.Form("3rd Meter number")) & " reading: " & CStr(Request.Form("3rd Meter reading")) & crlf
emsg = emsg & "4th Meter - number: " & CStr(Request.Form("4th Meter number")) & " reading: " & CStr(Request.Form("4th Meter reading")) & crlf
comments = crlf & "Comments:" & crlf & Request.Form("Comments")
sendmsg = emsg & comments & crlf & crlf
if(Request.Form("Automatic Payment Plan")>"") then
sendmsg = sendmsg & "Automatic Payment Plan info requested" & crlf
end if

'----------------------------------------------------------------------
' displayError function - send form values and error diagnostics
'----------------------------------------------------------------------
function displayError(from)
dim errmsg, CRLF
CRLF = chr(13) & chr(10)
set errmsg = CreateObject("CDO.Message")
errmsg.From = Request.Form("Email")
errmsg.To = "emailaddress here was replaced"
errmsg.Subject = "Meter Readings ERROR from Website"
errmsg.TextBody = "Error on meter readings" & CRLF & from & CRLF & Err.Number & " - " & Err.Description & CRLF & "Source: " & err.source & CRLF & "-----------------------------" & sendmsg

errmsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "192.168.1.87"
errmsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
errmsg.Configuration.Fields.Update
errmsg.Send
set errmsg = Nothing

response.write "<center><br><font face='Arial,Helvetica' size=-1 color=cc3333><b>A fatal database error occurred.<br>An administrator has been notified.</b></font></center></td><td width=1 bgcolor=999966></td><td width=180 valign=top></td></tr></table></center></body></html>"
response.end
end function

'----------------------------------------------------------------------
' Retrieve customer name from dispatch database
'----------------------------------------------------------------------
Dim cConn, getSQL, cRs
Set cConn = Server.CreateObject("ADODB.Connection")
cConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\..\minnkota.mdb")
if(Err.Number>0) then
call displayError("Error opening dispatch database.")
end if
getSQL = "select Name from dispatch where [Account #] = " & Request.Form("Account Number")
Set cRs = cConn.Execute(getSQL)
if(Err.Number>0) then
call displayError("Error retrieving from dispatch database.")
end if
if (cRs.EOF) then
vName = "ACCOUNT NOT FOUND"
else
vName = cRs(0)
end if
cConn.Close

'----------------------------------------------------------------------
' Insert submittal info into meter reading database
'----------------------------------------------------------------------
Dim mConn, insSQL, identRS
Set mConn = Server.CreateObject("ADODB.Connection")
mConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\..\meter.mdb")
if(Err.Number>0) then
call displayError("Error opening meter database.")
end if
insSQL = "insert into submittals (acct, name, vName, dateSubmitted, dateRead, comments, autoPay) values(" & Request.Form("Account Number") & ", '" & Request.Form("Name") & "', '" & vName & "', #" & now() & "#, #" & Request.Form("Date read") & "#, '" & Request.Form("Comments") & "', " & (Request.Form("Automatic Payment Plan")>"") & ");"
mConn.Execute(insSQL)
if(Err.Number>0) then
call displayError("Error inserting submittals into meter database.")
end if

'----------------------------------------------------------------------
' Get record ID of this submittal record in meter database
'----------------------------------------------------------------------
set identRS = mConn.Execute("SELECT @@IDENTITY")
newID = identRS(0)
identRS.Close

'----------------------------------------------------------------------
' Insert readings into meter reading database
'----------------------------------------------------------------------
if(Request.Form("1st Meter number")>"") then
insSQL = "insert into readings (subId, meterNum, meterReading) values(" & newID & ", " & Request.Form("1st Meter number") & ", " & Request.Form("1st Meter reading")& ")"
mConn.Execute(insSQL)
if(Err.Number>0) then
call displayError("Error inserting readings into meter database.")
end if
end if

if(Request.Form("2nd Meter number")>"") then
insSQL = "insert into readings (subId, meterNum, meterReading) values(" & newID & ", " & Request.Form("2nd Meter number") & ", " & Request.Form("2nd Meter reading")& ")"
mConn.Execute(insSQL)
if(Err.Number>0) then
call displayError("Error inserting readings into meter database.")
end if
end if

if(Request.Form("3rd Meter number")>"") then
insSQL = "insert into readings (subId, meterNum, meterReading) values(" & newID & ", " & Request.Form("3rd Meter number") & ", " & Request.Form("3rd Meter reading")& ")"
mConn.Execute(insSQL)
if(Err.Number>0) then
call displayError("Error inserting readings into meter database.")
end if
end if

if(Request.Form("4th Meter number")>"") then
insSQL = "insert into readings (subId, meterNum, meterReading) values(" & newID & ", " & Request.Form("4th Meter number") & ", " & Request.Form("4th Meter reading")& ")"
mConn.Execute(insSQL)
if(Err.Number>0) then
call displayError("Error inserting readings into meter database.")
end if
end if

'----------------------------------------------------------------------
' Output form values
'----------------------------------------------------------------------
response.write ("<pre>" & emsg & "</pre>")
response.write ("<br>" & comments & "<br><br>")
if(Request.Form("Automatic Payment Plan")>"") then
response.write ("<pre>Automatic Payment Plan info requested</pre><br>")
end if

mConn.Close
%>
Reply With Quote
  #2  
Old December 1st, 2004, 03:41 PM
suekari suekari is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Nov 2004
Posts: 2 suekari User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Here is the code in a code block
-Suekari

Code:
<html>
<head>
<title>Thank you for your meter readings</title>
<!--#include virtual="../mainjs.html" -->
</head>
<body bgcolor=ffffff>
<center>
<table width=600 cellspacing=0 cellpadding=0 border=0>
<tr><td colspan=3>
<!--#include virtual="../mainbar.html" -->
<img src="../images/dot.gif" width=1 height=4 border=0 alt=""></td></tr>
<tr><td valign=top>
<img src="meterbar.gif" width=400 height=14 alt="Online Meter Reading"><br>
<table width=420 cellpadding=0 cellspacing=0 border=0>
<tr><td width=10><img src="../images/dot.gif" width=10 height=1 border=0></td>
<td width=410>
<font size=-1><br>
<%
on error resume next
'----------------------------------------------------------------------
' Build emsg - form values in ASCII form
'----------------------------------------------------------------------
Dim emsg
crlf = chr(13) & chr(10)
emsg = "Name: " & Request.Form("Name") & crlf _
	 & "Email: " & Request.Form("Email") & crlf _
	 & "Account Number: " & Request.Form("Account Number") & crlf _
	 & "Date read: " & Request.Form("Date read") & crlf & crlf
	emsg = emsg & "1st Meter - number: " & CStr(Request.Form("1st Meter number")) & " reading: " & CStr(Request.Form("1st Meter reading")) & crlf
	emsg = emsg & "2nd Meter - number: " & CStr(Request.Form("2nd Meter number")) & " reading: " & CStr(Request.Form("2nd Meter reading")) & crlf
	emsg = emsg & "3rd Meter - number: " & CStr(Request.Form("3rd Meter number")) & " reading: " & CStr(Request.Form("3rd Meter reading")) & crlf
	emsg = emsg & "4th Meter - number: " & CStr(Request.Form("4th Meter number")) & " reading: " & CStr(Request.Form("4th Meter reading")) & crlf
comments = crlf & "Comments:" & crlf & Request.Form("Comments")
sendmsg = emsg & comments & crlf & crlf
if(Request.Form("Automatic Payment Plan")>"") then
	sendmsg = sendmsg & "Automatic Payment Plan info requested" & crlf
end if
'----------------------------------------------------------------------
' displayError function - send form values and error diagnostics
'----------------------------------------------------------------------
function displayError(from)
dim errmsg, CRLF
CRLF = chr(13) & chr(10)
set errmsg = CreateObject("CDO.Message")
errmsg.From = Request.Form("Email")
errmsg.To = "email address was here"
errmsg.Subject = "Meter Readings ERROR from Website"
errmsg.TextBody = "Error on meter readings" & CRLF & from & CRLF & Err.Number & " - " & Err.Description & CRLF & "Source: " & err.source & CRLF & "-----------------------------" & sendmsg
errmsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "192.168.1.87"
errmsg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
errmsg.Configuration.Fields.Update
errmsg.Send
set errmsg = Nothing
	response.write "<center><br><font face='Arial,Helvetica' size=-1 color=cc3333><b>A fatal database error occurred.<br>An administrator has been notified.</b></font></center></td><td width=1 bgcolor=999966></td><td width=180 valign=top></td></tr></table></center></body></html>"
	response.end
end function
'----------------------------------------------------------------------
' Retrieve customer name from dispatch database
'----------------------------------------------------------------------
Dim cConn, getSQL, cRs
Set cConn = Server.CreateObject("ADODB.Connection")
cConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\..\minnkota.mdb")
if(Err.Number>0) then
	call displayError("Error opening dispatch database.")
end if
getSQL = "select Name from dispatch where [Account #] = " & Request.Form("Account Number")
Set cRs = cConn.Execute(getSQL)
if(Err.Number>0) then
	call displayError("Error retrieving from dispatch database.")
end if
if (cRs.EOF) then
	vName = "ACCOUNT NOT FOUND"
else
	vName = cRs(0)
end if
cConn.Close
'----------------------------------------------------------------------
' Insert submittal info into meter reading database
'----------------------------------------------------------------------
Dim mConn, insSQL, identRS
Set mConn = Server.CreateObject("ADODB.Connection")
mConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("..\..\meter.mdb")
if(Err.Number>0) then
	call displayError("Error opening meter database.")
end if
insSQL = "insert into submittals (acct, name, vName, dateSubmitted, dateRead, comments, autoPay) values(" & Request.Form("Account Number") & ", '" & Request.Form("Name") & "', '" & vName & "', #" & now() & "#, #" & Request.Form("Date read") & "#, '" & Request.Form("Comments") & "', " & (Request.Form("Automatic Payment Plan")>"") & ");"
mConn.Execute(insSQL)
if(Err.Number>0) then
	call displayError("Error inserting submittals into meter database.")
end if
'----------------------------------------------------------------------
' Get record ID of this submittal record in meter database
'----------------------------------------------------------------------
set identRS = mConn.Execute("SELECT @@IDENTITY")
newID = identRS(0)
identRS.Close
'----------------------------------------------------------------------
' Insert readings into meter reading database
'----------------------------------------------------------------------
if(Request.Form("1st Meter number")>"") then
	insSQL = "insert into readings (subId, meterNum, meterReading) values(" & newID & ", " & Request.Form("1st Meter number") & ", " & Request.Form("1st Meter reading")& ")"
	mConn.Execute(insSQL)
	if(Err.Number>0) then
	 call displayError("Error inserting readings into meter database.")
	end if
end if
if(Request.Form("2nd Meter number")>"") then
	insSQL = "insert into readings (subId, meterNum, meterReading) values(" & newID & ", " & Request.Form("2nd Meter number") & ", " & Request.Form("2nd Meter reading")& ")"
	mConn.Execute(insSQL)
	if(Err.Number>0) then
	 call displayError("Error inserting readings into meter database.")
	end if
end if
if(Request.Form("3rd Meter number")>"") then
	insSQL = "insert into readings (subId, meterNum, meterReading) values(" & newID & ", " & Request.Form("3rd Meter number") & ", " & Request.Form("3rd Meter reading")& ")"
	mConn.Execute(insSQL)
	if(Err.Number>0) then
	 call displayError("Error inserting readings into meter database.")
	end if
end if
if(Request.Form("4th Meter number")>"") then
	insSQL = "insert into readings (subId, meterNum, meterReading) values(" & newID & ", " & Request.Form("4th Meter number") & ", " & Request.Form("4th Meter reading")& ")"
	mConn.Execute(insSQL)
	if(Err.Number>0) then
	 call displayError("Error inserting readings into meter database.")
	end if
end if
'----------------------------------------------------------------------
' Output form values
'----------------------------------------------------------------------
response.write ("<pre>" & emsg & "</pre>")
response.write ("<br>" & comments & "<br><br>")
if(Request.Form("Automatic Payment Plan")>"") then
	response.write ("<pre>Automatic Payment Plan info requested</pre><br>")
end if
mConn.Close
%>
<b>Thank you for submitting your meter readings!</b><br><br>
</font>
</td></tr>
</table>
</td><td width=1 bgcolor=999966><img src="../images/dot.gif" width=1 height=1 border=0 alt=""></td>
<td width=180 valign=top>
<!--#include virtual="../mainmenu.html" -->
</td></tr>
</table>
<!--#include virtual="../footer.html" -->
</center>
</body>
</html>
Reply With Quote
Reply

Viewing: ASP Free Forums > Programming > Visual Basic Programming > Insert into database working sporadically

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway.