×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Classic ASP multiple record update with Access

Classic ASP multiple record update with Access

Classic ASP multiple record update with Access

(OP)
How can I update a table (Access database) with multiple selections from a list box at once.
Can someone help me with the correct ASP code for this.
Thanks for help.

RE: Classic ASP multiple record update with Access

No code? No table structure?

Your querystring should look something like:
lboName=Value1&lboName=Value2

I then use code like:

CODE --> vbscript

strNames = "'" & Replace(Request.QueryString("lboName"),", ","','") & "'" 
so strNames = 'Value1','Value2'
I can then use a where condition like:

Quote (vbscript)

"Name In (" & strNames & ")"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Classic ASP multiple record update with Access

(OP)
Thanks dhookom for tips.
I am not familiar with these select multi-codes. Consequently, I cannot locate the exact location of your code.

A list box (code below) shows the records with the following 3 fields of table A.

A_Id (= num)
A_Name (= text)
A_Member (= numeric) => 1 = member and 0 = no member (default = 1)

The intention is, for example, to select the 5 names (records) for which the member field must be updated all at once to 0 (instead of 1 per one).

code listbox

CODE -->

<form name="Member" onSubmit="return validatie(this);" METHOD="POST" ACTION="A_updB.asp">

<select multiple NAME="A_Id">

<%
rs.MoveFirst
Do While Not rs.EOF
%>
<option value="<%=rs("A_Id")%>">
<%=rs("A_Id")&".&nbsp;&nbsp;"&rs("A_Name")&" "&rs("A_member")%> 
</option> 


code update

CODE -->

lngRecordNo = Request.Form("Rec")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database.mdb")
Set rs = Server.CreateObject("ADODB.Recordset")

SQL = "UPDATE TableA "&_
"SET A_Member = 0, A_UpDT = now() "&_
"WHERE A_Id=" & lngRecordNo 


Problems
When 1 record is selected, it is 'prepared for update' with the code : lngRecordNo = Request.Form ("Rec")
How can I save multiple selected records - in "lboName" (?) - and then update (in once) in the SQL?

Is a simple selection in the listbox sufficient or must a select box per record be provided?

Thanks.

RE: Classic ASP multiple record update with Access

I'm really rusty regarding ASP and generally have to feel my way through it by using

CODE --> ClassicASP

Response.Write(Request("A_Id")) 

Once you know the format of the request, you can figure out how to use the value(s) in your update query.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Classic ASP multiple record update with Access

(OP)

Quote:

Once you know the format of the request, you can figure out how to use the value(s) in your update query.
That's my problem: how should I convert my 'select multiple' in a 'format of request' with a correct syntax; in other words: how do I create a request format for/with more than one record (for example 4 or 5 or 9 ...) selected from the above-mentioned listbox?
The 'Response.Write(Request("A_Id"))'-test can only be done afterwards, or am I wrong?

Thanks for tips.

RE: Classic ASP multiple record update with Access

I learn by testing for instance adding these lines will show you how many and which values are chosen.

CODE --> vbscript

<%
 Response.write(Request.form("A_Id").count)
 Response.write(Request.form("A_Id"))
%> 

The request for form("A_Id") should return a string of delimited numbers that have been selected like "23,55,72". This string can be used in your update statement.

CODE --> vbScript

strRecordNos = Request.Form("A_Id")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database.mdb")
Set rs = Server.CreateObject("ADODB.Recordset")

SQL = "UPDATE TableA "&_
"SET A_Member = 0, A_UpDT = now() "&_
"WHERE A_Id IN (" & strRecordNos & ")" 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Classic ASP multiple record update with Access

(OP)
The select multiple module now works perfectly. Thanks Dhookom for the good tips.

Follow-up question.

The structure of the main menu in the current application is as follows:

=> Choice
radiobutton 1 => 'select multiple' cf. above = link to continuation_for_multiple.asp
radiobutton 2 => link to continuation file2.asp
radiobutton 3 => link to continuation file3.asp
radiobutton 4 => link to continuation file4.asp

=> Code form
<form name = "Member List" onSubmit = "return validation (this);" METHOD = "POST" ACTION = "continuation_for_multiple.asp">

=> Submit
<INPUT TYPE = "submit" VALUE = "Execute">


In this case : the Request.Form("A_Id") are only 'delivered' to "continuation_for_multiple.asp"

My problem
How can I get the (specific) Request.Form("A_Id") of radio button 2 are delivered to continuationfile2.asp
and the (specific) Request.Form("A_Id") of 3 to continuationfile3.asp and the (specific) Request.Form("A_Id") of 4 to continuationfile3.asp ?

Probably with 'if' in <form name ...> but how / where to insert this code? - Is this the right method?

Thanks for tips.

RE: Classic ASP multiple record update with Access

Please mark this thread as complete and start a new thread with your new question.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close