Multiple inserts into a database

[Thatguy2001au]

Hi there!

I am developing a page which allows the user to type in a track listing for a cd. I have a table in my database which is sql server 2000 for this. The problem is, is that i need every track to be a single record into that table.

I have developed a form with multiple textboxes for the tracks. How can i make it so that when the form is submitted, it will insert each track into the table one at a time until they have all been done???

Any help will be greatly appreciated. If i need to add multiple checkboxes or multiple forms, please let me know.

thanks.

[raf]

i've got some code for that at home. If you like, i can post it later on (this evening)

it works like this:
- in the form, al the textfieldsnames (your tracknames) start with the same charactes. Only these fields start with these caracters (something like raftrack1, raftrack2 etc) and checkboxes are called more or less the same (rafcheck1) (actually, you don't need the checkboxes, if you just check the length of the textfieldvalues, you know enough.)

- In the asp page, i use a for each loop. Inside this loop, i check the textfieldnames and decompose them and increment a 'filledin-counter'. I then build the sql statement on the fly (using both the value, as parts of the fieldname, like the number), execute it, check if a record was inserted, generate a feedbackmessage, increment an 'insertedcounter'.

So my loop will run for all tracks, insert them (if all goes well) and at the end will check if the two counters are the same. If it is, i redirect to a page that sais something like (all went well etc), if it's not, i display the feedbackmessages for each track.

Let me know if this gets you along or if you'd need the code.

[Thatguy2001au]

Thanks Raf

That sounds perfect. If you could post the code on here later it would be a great help.

Thanks again!

[raf]

I modifyed some code of mine. Haven't tested it (no time) but let me know if you can't get if working.

[code]
<%
dim sql, box, numinserted, numfilledin, track

numinserted="0"
numfilledin="0"

for each box in request.form
if Left(box, 5) = "track" and Len(request.form("box")) > 0 then 'all textfelds for tracks need to build like this "track1", "track2". Only process fields that are filled in
track=Cstr(Right(box, (Len(box)-5))) ' select the number and convert it to a string
numfilledin = numfilledin + 1
sql = "INSERT INTO table (tracklabel, trackID, othervaribles) VALUES ('" + replace(request.form("box"), "'","''") + "','" + track + "',othervalues)"

conGranIT.Execute sql, numinserted

if numinserted = 1 then
totnumupdated = totnumupdated + numupdated
response.write("Track " & track & " processed")
else
response.write("Track " & track & " not processed correctly")
end if
end if
next

conGranIT.Close
set conGranIT = nothing

if numinserted = numfilledin
response.redirect("somepage.asp")
end if
%>
[code]

[Thatguy2001au]

Thankf raf for the code,

I'm just having a little trouble. In my form, there are 3 textboxes, 1 for TRACK_ID, 1 for TRACK_NAME, and 1 for TRACK_NO.

At the moment, there are 2 of each textbox in the form:

idtrack1 - track1 - notrack1
idtrack2 - track2 - notrack2

I can't quite figure out how to modify your code to get the form to insert these 2 records into the database once i hit the submit button.

My table in my database is called TRACKS and the fields are:

TRACK_ID - TRACK_NAME - TRACK_NO

I don't suppose i could bother you to modify your code so it will do the insert. Don't worry about creating the connection or anything, I'm just after how to do the loop and the SQL Statement.

Thanks alot.

[raf]

OK. Lets take a step back. You say:
there are 3 textboxes, 1 for TRACK_ID, 1 for TRACK_NAME, and 1 for TRACK_NO.

Why do you need these three textboxes? I wrote the code supposing you'de have a form like this

form----------

blablabla
Type in the tracktitles

track 1 : |textfield named track1|
track 2 : |textfield named track2|
etc
>submit<

end form ------------

So if the user inserts some text in the textbow, the code knows enoug to fill in a value for the variables in the db. It just takes the tracknumber from the textfield name. The trackname is the value from the textfield, and the trackid should be an autonumber variable in your db. So that value will be generated by the db.

So you only need one textbox for each track. If the user leaves it open, then the condition on top of the code will go to the next textfield.

No?

The trackID shouldn't be a textfield !! Just a key (primary key) for that table.

Tell me why this couldn't work for you. (Can't see a reason why, but i don't know your app)

[Thatguy2001au]

Thanks raf

First, the reason as to why i can't make TRACK_ID and autonumber is because this is what i will be using to relate the tracks to a certain cd. So basicaly this will be a foreign key in another table which i have for ALBUMS. Basically, each album has a ALBUM_NO which is basically what the TRACK_ID is. That way, i can build a relationship between the ALBUM table and the TRACKS table. I have no need for an autonumber field in the TRACKS table.

Basically, the user will type in the ALBUM_NO in the textbox called TRACK_ID so they can relate that track to a certain album.

I guess i could do without the TRACK_NO (notrack) textbox. But i atleast need to be able to add the Track name and the Track No. So, there will atleast be 2 texboxes i need to add to the database per record.

So lets cut it down to 2 texboxes:

TRACK_ID (which is the same as ALBUM_NO in the Albums table) &
TRACK_NAME

How could i do this?

[raf]

Now, its your app so you can do what you like. No problem. But this is how it's normally done:

You have an albums table, with albumname etc + and albumID (as the primary key for this table)
You have a trackstable with all tracks from all albums. Each track is a record. Each track has a trackname, number of track on album number etc + a trackID (primary key for this table. The albumID is also inserted in this table for each track (as a foreign key in a numeric variable). You can then set a n-1 relationship between the trackstable and the album table.

Say a user inserts a new album. He fills in the albumname, groupname etc. Submits the form. A new record is created in the album table. The albumID (autonumber variable!) is returned after the insert (plenty of threads in this forum about how to retrieve that ID for numerous db-formats) and is dragged along in the form (in a hidden field), querystring or stored in a sessionvariable.
On the second screen, the user filles in the trackinfo. When submitted, the albumID is inserted with each trackrecord.


If you now want to get all tracks from an album, you just use a join between these two tables, with a condition on the albumtabele (where album.albumID = 12).

But like i said, it's your choice.