|
| |
|
|
albano
Posts: 132
Joined: 11/30/2003
Status: offline
 |
Insert and update multiple records with one form - 12/18/2003 10:49:18
Hi there! I don't know if anybody could help me with this. Don't know much of asp and database, but I'm very familiar with frontpage and access. Here's the question: Using Spooky's Login a mortgage company will register. I added two more tables in the database: Tbrates (mortgage companies post rates) and rateID (the limited numberes of records a companie may post) which are conected with many-to many relationship. I have a form that post the rates into Tbrates: The thing is the company has to post every rate individually. Is there any way that I can use a single form to enter x number of records with same field names: Each record has 5 fields:ratetype, rate, points, apr, last updated.
If so can I use the same form to update the records?
That's all.
Regards,
_____________________________
Mortgage Site Link Exhange | http://www.truelending.com
|
|
|
|
nsanto17
Posts: 67
Joined: 11/21/2003
Status: offline
|
RE: Insert and update multiple records with one form - 12/18/2003 11:03:20
You can do that... Post your Code so we can take a look at it..
| |
|
|
|
BeTheBall
Posts: 6356
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Insert and update multiple records with one form - 12/18/2003 11:29:07
Inserting multiple records with one form isn't too difficult a proposition. Updating multiple records with one form is also doable, but IMO fairly complex. For the inserting, you can use FP for your form, but I don't believe the DRW can handle the INSERT, you will need to do that part in pure ASP. Here is some sample code I have used. I think rdouglass shared it with me.
<% DIM conntemp, mySQL, myDSN
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\fpdb\YourDatabase.mdb")
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to 5
mySQL = "INSERT INTO YourTable (YourFirstField, YourSecondField, YourThirdField) VALUES"
mySQL = mySQL & " (" & Request("YourFirstField" & i) & ", " & Request("YourSecondField" & i) & ", " & Request("YourThirdField" & i) & ")"
conntemp.execute(mySQL)
NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>
Have your form submit to a new page on which you insert the above code. Let us know if this isn't detailed enough. Good luck.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6356
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Insert and update multiple records with one form - 12/18/2003 15:15:57
I would enter 17 rows in your form with one set of the five form fields in each row. Row ones fields will be named, ratetype1, rate1, points1, apr1, last updated1, then the next row will be ratetype2, rate2, points2, apr2, last updated2, etc. Here is some more efficient code for the actual updating, it would need to be modified to include your other fields:
<% DIM conntemp, mySQL, myDSN
myDSN = " DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath(" fpdb/yourdb.mdb" )
' myDSN would be specific to your environment
set conntemp=server.createobject("adodb.connection" )
conntemp.open myDSN
FOR i = 1 to 17
IF Request("ratetype" & i) > "" THEN
mySQL = " INSERT INTO YourTable (ratetype, rate, points, apr, lock) VALUES"
mySQL = mySQL & " ('" & Request("ratetype" ) & "', '" & Request("rate" & i) & "', '" & Request("points" & i) & "', '" & Request("apr" & i) & "', '" & Request("lock" & i) & "')"
Response.write(mySQL)
conntemp.execute(mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
%>
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6356
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Insert and update multiple records with one form - 12/19/2003 8:41:59
Timestamp is a reserved word. Try surrounding it with [], so this:
INSERT INTO tbrates (UserID, x_email, x_state, ratetype, rate, points, apr, lock, x_website, Timestamp)
Becomes this:
INSERT INTO tbrates (UserID, x_email, x_state, ratetype, rate, points, apr, lock, x_website, [Timestamp])
Also, I assume that timestamp is a date/time field in your db, so change this:
'" & Request("Timestamp" & i) & "'
to
#" & Request("Timestamp" & i) & "#
When inserting to or updating a date field in an Access db, the form field being inserted must be surrounded by ##. Text fields are surrounded by single quotes and numeric fields are not surrounded by anything.
Finally, only include the "& i" portion of code for those fields that repeat in your form. For example, if x_email is only required once, then '" & Request("x_email" & i) & "' would become '" & Request("x_email") & "'
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
rdouglass
Posts: 9205
From: Biddeford, ME USA
Status: offline
|
RE: Insert and update multiple records with one form - 12/19/2003 8:48:48
quote:
INSERT INTO tbrates (UserID, x_email, x_state, ratetype, rate, points, apr, lock, x_website, Timestamp) VALUES ('', 'alstefani@hotmail.com','Florida','30 YR fixed','5.75', '0.00','5.85','45', 'http://www.truelending.com', '12/18/2003 21:36')
All of what Duane said and also you're missing your "UserID" value. And as mentioned, if UserID is numeric, no apostrophes...
<$.02>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
BeTheBall
Posts: 6356
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Insert and update multiple records with one form - 12/19/2003 12:31:48
So does the user always have 17 records or do they sometimes have less? Probably the best way would be to prepopulate the rate type field with the various types. Then, change this line:
IF Request("ratetype" & i) > "" THEN
to check a different field for a value, something like:
IF Request("rate" & i) > "" THEN
You would then have to give clear instructions to the user to not complete lines that aren't applicable.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
ljrichar
Posts: 12
Joined: 10/6/2004
Status: offline
|
RE: For Spooky - 11/1/2004 16:25:05
I am trying to adapt this code for my needs but I'm having problems. Here is the code and the ERROR.
<% DIM conntemp, mySQL, myDSN
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("schedule.mdb")
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to 5
mySQL = "INSERT INTO myschedule (Ref_num, B1) VALUES"
mySQL = mySQL & " (" & Request("Ref_num" & i) & ", " & Request("B1") & ")"
conntemp.execute(mySQL)
ON ERROR GOTO 0
For Each doh In conntemp.Errors
If doh.number = -2147217900 Then
Response.Write "That item already exists in that table!<BR>"
Else
Response.Write "Unexpected error: " & doh.number & " -- " & doh.description
End If
NEXT
NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>
Technical Information (for support personnel)
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/schedule/multi_insert.asp, line 13
Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)
Page:
POST 18 bytes to /schedule/multi_insert.asp
POST Data:
Ref_num=117&B1=Add
Time:
Monday, November 01, 2004, 4:21:42 PM
More information:
Microsoft Support
| |
|
 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
|
|
|
Printable Version 
k¥ 