Classic ASP multiple record update with Access

[leifoet]

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.

 

[dhookom]

No code? No table structure?

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

I then use code like:

strNames = "'" & Replace(Request.QueryString("lboName"),", ","','") & "'"

so strNames = 'Value1','Value2'
I can then use a where condition like:

"Name In (" & strNames & ")"

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

 

[leifoet]

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

<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

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.

 

[dhookom]

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

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

 

[leifoet]

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.

 

[dhookom]

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

<%
 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.

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

 

[leifoet]

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.

 

[dhookom]

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