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!

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

DB Problem

Status
Not open for further replies.
Zipster

Zipster

Programmer
Nov 13, 2000
107
GB
Hi,

I am having a major problem when doing a database insert using the following code (I've done response.write for debugging):

-----------------
Set RS = idConn.execute("INSERT INTO tblIntraCal(txtType, txtNotes, Createdon, Modifiedon, userID) VALUES ('" & MyType & "' ,'" &_
Replace(MyNotes, chr(34), "") & "', CAST('" & Date() & " " & Time() & "' AS DATETIME), CAST('" & Date() & " " & Time() & "' AS DATETIME), " &_
UserID & "); SELECT @@identity").nextrecordset

Response.Write "INSERT INTO tblIntraCal(txtType, txtNotes, Createdon, Modifiedon, userID) VALUES ('" & MyType & "' ,'" &_
Replace(MyNotes, chr(34), "") & "', CAST('" & Date() & " " & Time() & "' AS DATETIME), CAST('" & Date() & " " & Time() & "' AS DATETIME), " &_
UserID & "); SELECT @@identity" & vbcrlf

SQLNextID = CInt(RS(0))

Response.Write SQLNextID

Set RS = Nothing

For i = 1 to Request.Form("checker").count
Response.Write Request.Form("checker")(i) & vbcrlf
If Request.Form("checker")(i) <> "" Then MyString = MyString & "INSERT INTO tblIntraCalDates(nID, dateCAL) VALUES (" & SQLNextID & ",'" & CDate(Request.Form("checker")(i)) & "')"
Next
idConn.execute(MyString)

If Err.Number <> 0 then
idConn.RollbackTrans
Response.Write "An error occured with this transaction, Contact Admin!" & Err.Description
idConn.close
Set idConn = Nothing
Else
idConn.CommitTrans
idConn.close

Set idConn = Nothing
'Response.Redirect("holiday.asp?uname=" & UserID)
End If
---------------------------------

For some reason SOMETIMES both SQL statements execute twice and putting values in both tables twice?!?!

On the previous screen that is submitted I have a year planner with lots of check boxes to select days of the year (each check box has a different date value).

The response.write output is correct and doesn't show any doubling up.

Hope someone can understand and help us.

Thanks.

 
Sort by date Sort by votes
userID is a keyword use square braces around it...something like this...

[userID]

-DNG
 
Upvote 0 Downvote
I've Response.Write everything that possible could be written:

Response.Write Request.Form("checker")(i) & vbcrlf
Is just before idConn.execute(MyString).

There doesn't seem to be anything wrong with my code. The only thing I could think of that's make it do is using the idConn to execute and return the RS(0).

Hope someone can help.

 
Upvote 0 Downvote
Yes but you havent done a Response.Write of MyString which contains your INSERT code and which is appended to on each iteration of your For...Next loop.

Please try a Response.Write of MyString after the For...Next loop and see what is reads like.

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation
 
Upvote 0 Downvote
You know what I thought I'd just check what the first insert is like first (make sure it was working properly):

Set RS = idConn.execute("INSERT INTO tblIntraCal(txtType, txtNotes, Createdon, Modifiedon, userID) VALUES ('" & MyType & "' ,'" & Replace(MyNotes, chr(34), "") & "', CAST('" & Date() & " " & Time() & "' AS DATETIME), CAST('" & Date() & " " & Time() & "' AS DATETIME), " & UserID & "); SELECT @@identity").nextrecordset

and have commented out the other INSERT below it and am getting double inserts just with this statement.

I can't understand what I'm doing wrong, this statement isn't in a loop or anything yet I'm getting two inserts into the DB?!?!!?

Here's my full code for the page:

<%
Response.Write("Please wait..." & vbcrlf & vbcrlf & "Please DO NOT use your computer while I'm updating!")

Dim MyType, MyNotes, UserID, MyID, idConn, idConn2, RS, SQLNextID, i

MyType = ReplaceQuotes(Request.QueryString("Type"))
MyNotes = ReplaceQuotes(Request.Form("Notes"))
UserID = ReplaceQuotes(Request.Form("UserID"))

'Get next ID from the tblIntraCal to be used in tblIntraCalDates
Set idConn=Server.CreateObject("ADODB.Connection")
idConn.Open objConnection

'On Error Resume Next

idConn.beginTrans

Dim MyString
Set RS = idConn.execute("INSERT INTO tblIntraCal(txtType, txtNotes, Createdon, Modifiedon, userID) VALUES ('" & MyType & "' ,'" &_
Replace(MyNotes, chr(34), "") & "', CAST('" & Date() & " " & Time() & "' AS DATETIME), CAST('" & Date() & " " & Time() & "' AS DATETIME), " &_
UserID & "); SELECT @@identity").nextrecordset

Response.Write "INSERT INTO tblIntraCal(txtType, txtNotes, Createdon, Modifiedon, userID) VALUES ('" & MyType & "' ,'" &_
Replace(MyNotes, chr(34), "") & "', CAST('" & Date() & " " & Time() & "' AS DATETIME), CAST('" & Date() & " " & Time() & "' AS DATETIME), " &_
UserID & "); SELECT @@identity" & vbcrlf

SQLNextID = CInt(RS(0))

Response.Write SQLNextID

Set RS = Nothing
RS = ""

'For i = 1 to Request.Form("checker").count
' Response.Write Request.Form("checker")(i) & vbcrlf
' If Request.Form("checker")(i) <> "" Then MyString = MyString & "INSERT INTO tblIntraCalDates(nID, dateCAL) VALUES (" & SQLNextID & ",'" & CDate(Request.Form("checker")(i)) & "');"
' Next
' Response.Write(MyString)
' idConn.execute(MyString)

If Err.Number <> 0 then
idConn.RollbackTrans
Response.Write "An error occured with this transaction, Contact Admin!" & Err.Description
idConn.close
Set idConn = Nothing
Else
idConn.CommitTrans
idConn.close
Set idConn = Nothing
' Response.Redirect("holiday.asp?uname=" & UserID)
End If
idConn = ""

%>


Results were:

Please wait... Please DO NOT use your computer while I'm updating!INSERT INTO tblIntraCal(txtType, txtNotes, Createdon, Modifiedon, userID) VALUES ('HOL' ,'', CAST('19/09/2005 16:45:54' AS DATETIME), CAST('19/09/2005 16:45:54' AS DATETIME), 2); SELECT @@identity 165

Yet two items we're inserted into tblIntraCal!!! ARGHH!!!

 
Upvote 0 Downvote
actually i meant to suggest this first
Code: 
Set RS = idConn.execute("INSERT INTO tblIntraCal(txtType, txtNotes, Createdon, Modifiedon, userID) VALUES ('" & MyType & "' ,'" &_
Replace(MyNotes, chr(34), "") & "', CAST('" & Date() & " " & Time() & "' AS DATETIME), CAST('" & Date() & " " & Time() & "' AS DATETIME), " &_
UserID & "); SELECT @@identity")[red].nextrecordset[/red]
I've not seen that syntax either (the bit in [red]red[/red]). Try taking that bit out and try it again.

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation
 
Upvote 0 Downvote
A few suggestions.

1) What database are you using? I'm assuming SQL Server.

2) Could you do your inserts via a stored procedure, returning the identity either through the return value or an output parameter.

3) Could your users be double-clicking causing your page to run twice? (unlikely)

4) Is this code in a function? Is this function being called twice?

5) Are there any triggers on the table that may cause this duplication?

btw. You should use SCOPE_IDENTITY() instead of @@IDENTITY as the value of @@IDENTITY can be changed by triggers.

Hope this is useful.

Chaz
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
977
leifoet
leifoet
leifoet
  • Locked
  • Question
How to log in without a popup problem?
Replies
3
Views
299
ggriffit
ggriffit
ZeeHashmi
  • Locked
  • Question
ASP : Email Body UTF-8 Problem
Replies
3
Views
610
ZeeHashmi
ZeeHashmi
PSchubert
  • Locked
  • Question
Data in DB table cell not displaying in asp page table cell
Replies
4
Views
314
Ed Mozley
Ed Mozley
leifoet
  • Locked
  • Question
Problem with updating database reords.
2
Replies
31
Views
918
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top