Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


Insert into Database working sporadically

  		Logged in as: Guest
arrSession:exec spGetSession 2,3,1843
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
All Forums >> [Scripting] >> ASP >> Insert into Database working sporadically
  Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks! 		Page: [1]
Login
Message << Older Topic   Newer Topic >>
Insert into Database working sporadically - 1/14/2005 7:37:54 AM   
  suekari

 

Posts: 1
Score: 0
Joined: 1/14/2005
From:
Status: offline 		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

<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 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
%>

<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>
 
 
Post #: 1
 
Re: Insert into Database working sporadically - 3/2/2005 11:38:32 PM   
  VBS

 

Posts: 110
Score: 0
Joined: 3/2/2005
From: Lebanon
Status: offline 		Dear suekari,
Have u tried BeginTrans and CommitTrans??
if not try them, cause it happened with me before and the problem was from the BeginTrans and CommitTrans...

as an example

dim objCon
dim objRs

set objcon=server.CreateObject("ADODB.Connection")
objCon.ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Server.MapPath("/database.mdb")
objCon.Open
objCon.BeginTrans
set objrs = server.CreateObject("ADODB.Recordset")
set objRs.ActiveConnection =objcon
objCon.CommitTrans
objRs.Open "SELECT * FROM Accounts",objCon,2,3

Hope this helps
Don't forget to post the results

Best Regards

Firas S Assaad

(in reply to suekari)
 
 
Post #: 2
 
Re: Insert into Database working sporadically - 3/2/2005 11:40:23 PM   
  VBS

 

Posts: 110
Score: 0
Joined: 3/2/2005
From: Lebanon
Status: offline 		And try using the normal options instead of SQL Statement

example

Use :

ObjRs.AddNew

Instead of:

objcon.excute "INSERT ....."

(in reply to suekari)
 
 
Post #: 3
 
Re: Insert into Database working sporadically - 4/1/2005 1:43:49 AM   
  JayeshP

 

Posts: 1
Score: 0
Joined: 4/1/2005
From:
Status: offline 		Hi

Remove the line that says 'On error resume next'. This line ignores any errors and continues to execute the remaining code. Now you will see all errors.

Jay

(in reply to suekari)
 
 
Post #: 4
 
Re: Insert into Database working sporadically - 4/1/2005 3:16:28 AM   
  VBS

 

Posts: 110
Score: 0
Joined: 3/2/2005
From: Lebanon
Status: offline 		Dear JayeshP,
If i remove the "On Error Resume Next" Line, the error will not be traped, and i always use this line to trap the error using :

if err.number>0 then
response.write "Error No.: " & err.number & "<br>Error Discription: " & err.discription


Best Regards

Firas S Assaad

(in reply to suekari)
 
 
Post #: 5
 
Re: Insert into Database working sporadically - 4/1/2005 5:42:36 AM   
  larryw24

 

Posts: 8
Score: 0
Joined: 4/1/2005
From: USA
Status: offline 		Most error numbers I get are negative numbers.

Change your tests from
If Err.Number > 0
to
If Err.Number <> 0

(in reply to suekari)
 
 
Post #: 6
 
Re: Insert into Database working sporadically - 4/6/2005 4:45:10 AM   
  VBS

 

Posts: 110
Score: 0
Joined: 3/2/2005
From: Lebanon
Status: offline 		Ur right larryw24, but actually i had the negative error numbers only once in my whole web programming life..., so it is possible for it to happen again

(in reply to suekari)
 
 
Post #: 7
 
 
 
 

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.
All Forums >> [Scripting] >> ASP >> Insert into Database working sporadically Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Forum Software © ASPPlayground.NET Advanced Edition 2.5.5 ANSI