Inserting multiple values of the same db field into a table

tazzer · Jan 29, 2003

Hi folks,

I have created an application that will allow users to log visitors to our humble organisation. I am using an Access database back end that I currently input the visitor name into (one text field only). However I want to be able to insert not just one name at a time but rather 2 to 5 names (for delegations)in order to speed things up. In other words I want to be able to insert several records of the same field type with one hit of the Submit button.

My code is as follows:

asp page 1

<tr>
<td><b><font size="2">Visitor</font></b></td>
<td><input type="text" name="date_today" size="11"><font size="2"></font></td>
<td> <input type="submit" value="Submit Form" name="Submit"></td>
</tr>

asp confirmation page

<%
dim objConn, strQuery
set objConn = Server.CreateObject("ADODB.Connection&quot

'objConn.Open("DSN=Visitors&quot

objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Microsoft Site Server\Data\Publishing\ininet\Databases\visitors.mdb;Persist Security Info=False&quot

strQuery = "INSERT INTO daily_Visitors(visitor_name) "
strQuery = strQuery & "VALUES ('"
strQuery = strQuery & Request.Form("visitor&quot

& "')"
objConn.Execute(strQuery)

objConn.close
%>

Instead of inserting just the one value into the visitor_name field, I want to insert 5 values into the visitor_name field, meaning 5 new records.

Can anyone help?

Cheers,

Tazzer

onpnt · Jan 29, 2003

you'll have to place your execute into a loop and change the insert statement on every new value passed in the request.
suedocode would be something like this
for count = 0 to request length
sql = "INSERT INTO( ) VALUE("
sql = sql & request objects & &quot

"
rs.execute(sql)
next
_______________________________________________
[sub]{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee&quot

;alert(Nstr); }[/sub]
_______________________________________________
for the best results to your questions: FAQ333-2924
has you're questio

onpnt · Jan 29, 2003

sorry if I made that a bit confusing with the rs. in there, that should be connection.execute.
trying to do to things at the same time.
for count = 0 to request length
sql = "INSERT INTO( ) VALUE("
sql = sql & request objects & &quot

"
connectionObject.execute(sql)
next _______________________________________________
[sub]{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee&quot

;alert(Nstr); }[/sub]
_______________________________________________
for the best results to your questions: FAQ333-2924
has you're questio

jeepxo · Jan 29, 2003

is there an easy way to do this...depends on your page I guess.

I haven't tried it but I am sure it would work

You could have 5 input boxes all with the name "date_today"
Then when you do your Request("date_today&quot

you will get all of the values entered comma delimited.

You could then say

For each item in Request("date_today&quot

do the insert
Next

or you have 1 text box, ask them to separate the names with comma's and parse through the string to find each of the names, then create a loop to do the insert.

mwolf00 · Jan 29, 2003

In both of the above methods you need to check to make sure that the field has something in it before you insert...

formData = request("visitor&quot

if isArray(formData) then
for x = 0 to uBound(formData)
if trim(formData(x)) <> "" then
sql = "INSERT INTO myTable VALUES ('"& formData(x) &"')"
connectionObject.execute(sql)
end if
next
else
if trim(formData) <> "" then
sql = "INSERT INTO myTable VALUES ('"& formData &"')"
connectionObject.execute(sql)
end if
end if

a more elegant method would be to allow the user to click a button to add another form field (using javascript). If you're interested in the method, let me know... Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

onpnt · Jan 29, 2003

mwolf, good point but I really supplied no code. the suedocode was a mirror of you supplying the code basically. Wanted to make that clear in hope there was the possibity that suedocode was misinterpruted as it being working code. I would hope checking for a value would be given, but.... [wink]

_______________________________________________
[sub]{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee&quot

;alert(Nstr); }[/sub]
_______________________________________________
for the best results to your questions: FAQ333-2924
has you're questio

Tarwn · Jan 29, 2003

I like the javascript add a row idea, that is always fun, I have a timecard that allows you to enter as many days as you want by dynamically adding new rows to the form. It comes off as really professional, or at least as relatively nifty

-Tarwn

ps - sorry, everyone else said something so I had to add a post

________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

onpnt · Jan 29, 2003

I had to do that one a few weeks ago too. I thought it turned out pretty cool. here's the function if interested in going that route
function addBox() {
var lng = frm.length + 1
var Nfield = "numbers" + lng
//alert(Nfield);
frm.innerHTML += '<input type="text" name="' + Nfield +'" class=txtbx><br><br>'
}

obviously trimmed down but working _______________________________________________
[sub]{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee&quot

;alert(Nstr); }[/sub]
_______________________________________________
for the best results to your questions: FAQ333-2924

mwolf00 · Jan 29, 2003

onpnt - have you tried it with nodes? even niftier (javascript DOM rocks!) Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

Tarwn · Jan 29, 2003

Yep, I did mine that way...Try doing it when the rows need select boxes that pull info from the db AND can handle someone pasting in info from excel by dynamically growing to fit the rows from excel and selecting the correct option from the select box...(that was a bit difficult...I mean...darnit, onpnt is going to get me again)

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

tazzer · Jan 29, 2003

Thanks for your help so far folks. Its much appreciated.

Used mwolf00's code from earlier and now can insert from several input fields into the same field in the db table. Unfortunately it puts all of the field inputs into the same record whereas I want the inputs to go in as separate records:

<form name="frmAddContacts" method="post" action="addcontact3.asp">
<tr><td><b>First Name:</b></td>
<td><input type="text" name="txtFirstName" width=30></td></tr>
<tr><td><b>First Name:</b></td>
<td><input type="text" name="txtFirstName" width=30></td></tr>
<tr><td><b>First Name:</b></td>
<td><input type="text" name="txtFirstName" width=30></td></tr>
<tr><td align="left" colspan="2"><input type="submit" value="Add Contact"></tr>

formData = Request.form("txtFirstName&quot

if isArray(formData) then
for x = 0 to uBound(formData)
if trim(formData(x)) <> "" then
sql = "INSERT INTO contacts(firstname) VALUES ('"& formData(x) &"')"
conn.execute(sql)
end if
next
else
if trim(formData) <> "" then
sql = "INSERT INTO contacts(firstname) VALUES ('"& formData &"')"
conn.execute(sql)
end if
end if

Help! Where am I going wrong?

jeepxo · Jan 29, 2003

well first off it was me, I wouldn't be using an insert.
I would instead do an update as a transaction intead.

SQL = "select * from contacts"
oConn.Open "myDSN", "username", "password"
rs.open sql, oConn, adOpenKeyset, adLockBatchPessimistic

then do your
for x=0 to Ubound(Formdata)
rs.addnew
rs("firstname&quot

= formData(x)
next x
rs.updateBatch
rs.close
oConn.close

This way I do everything as a batch update, if one fails, none of them are input so I don't get incomplete data.
I don't have to worry about escaping quotes or other foolish things that users might input.

I just have to worry that my field type in the database supports the data that is being input into it.

Tarwn · Jan 29, 2003

A) How is the ability to do transactions limited to using the Recordset object's AddNew or Update function, and
B) What about efficiency?

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

Tarwn · Jan 29, 2003

Oh, and the only problem above that I could see by glancing at mwolfs code is that the comma delimited string being received in the Request collection needs to be split on comma-space to turn it into an array:

Code:

formData = Split(request(&quot;visitor&quot;),&quot;, &quot;)

Oh, and heres something difficult for the single quotes that may appear in names like "O'brian":

Code:

formData = Replace(request(&quot;visitor&quot;),&quot;'&quot;,&quot;''&quot;)
formData = Split(formData,&quot;, &quot;)

-Tarwn
________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

jeepxo · Jan 29, 2003

sorry that would be AdLockBatchOptimistic not AdLockBatchPessimistic

jeepxo · Jan 29, 2003

"How is the ability to do transactions limited to using the Recordset object's AddNew or Update function"

Its not. No where did I say it was.

As for the performance difference, I don't know which is faster, however if performance is your biggest issue then you should consider doing it in ASP.NET and not ASP anyway.

I would suspect that a straight insert would be more efficient.

tazzer · Jan 30, 2003

Hi guys,

Nearly doing what I want now; I used Tarwn's line :-
formData = Split(Request.form("txtFirstName&quot

,", &quot

which did the trick.

However I want to insert a second field into the database as well eg.

<tr>
<td><b>First Name:</b></td>
<td><input type="text" name="txtFirstName" width=30></td>
<td><b>Last Name:</b></td>
<td><input type="text" name="txtLastName" width=30></td>
</tr>
<tr>
<td><b>First Name:</b></td>
<td><input type="text" name="txtFirstName" width=30></td>
<td><b>Last Name:</b></td>
<td><input type="text" name="txtLastName" width=30></td>
</tr>

I have tried without luck to insert the two fields together. How can I alter the array to ensure that the two fields are inserted as the same record for each?

Yours,

Tazzer

Tarwn · Jan 30, 2003

If you split both of them into seperate arrays, than as you are looping through one you can also use the loop counter as the index for the second. ie:

Code:

for x = 0 to uBound(formData)
    if trim(formData(x)) <> &quot;&quot; then
      sql = &quot;INSERT INTO contacts(firstname,lastname) VALUES ('&quot;& formData(x) &&quot;','&quot; & OtherFormData(x) & &quot;')&quot;
      conn.execute(sql)
    end if
  next

where otherFormData would be the array you split lastname into.

-Tarwn ________________________________________________________________________________
Sometimes it is how you ask the question: faq333-2924
Many ASP questions have already been answered, please check faq333-3048 and use the search tool before posting

tazzer · Jan 30, 2003

Tarwn,

Thanks for the advice; I've got the code working a treat now!

And thanks again to all the guys who contributed to this thread.

Tazzer

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!

Inserting multiple values of the same db field into a table

Technical User

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor