Updating multiple records in one shot

[jasonsalas]

Hi everyone,

I'm trying to think of the best way to implement this...I've got a single Access DB table (tblPeople) with about 70 records:

fName (Text)
fVotes (Number)

What I'd like to do is be able to run an UPDATE statement against all of the records at once to change the values of fVotes, without having to repeatedly go back and fill out a form for each one. If I have to create a form with 70 votes, I'll do that...but can I do a mass update all in one shot?

Thanks!
JS

 

[rkfox]

What do you want to update the records with? Where is the data coming from? You can update the records either using a SQL Update command or loop thru the records using the record set command Update.

 

[hithere]

you might want to look at the "split" command and the "trim" command. we have a form that we use those two commands in to insert multiple records to a db. the split allows you to define what character you want to separate your fields by and then we used trim to eliminate any extra spaces.

myvar=trim(myvar)

myarray=split(myvar, ",",-1,1)

hth
mb

 

[Dynapen]

I think that hithere is writing a response to a different post, becuase trim and split aren't something that would fit into this thread.

Basically do a Update Query.

SQL = " Update tblPeople set fname = whatever, fvotes = whatever2 "

That would update every record to what ever value you have for fname and fvotes. If you want to update just fvotes then

Update tblPeople set fvotes = whatever

To limit you updates try

Update tblPeople set fvotes = whatever where fvotes = currentvalue

 

[jasonsalas]

I was thinking about doing this from values entered into a Web form, and then running an UPDATE query...but I've been wondering how I can update all 70 records (with different values for each, being votes cast for those people), all at one time.

I could build a form with 70 "people" fields and 70 "votes" fields, but how could I easily and seamlessle do the mas update? I was thinking about using the following (but I don't know if that'll work in Access):

UPDATE tblPeople SET fVotes = intNewVotes WHERE fName IN ("1", "2", "3", "4", "5&quot

Where the "IN" keyword is a list of values against which the variable intNewVotes would be matched. It's really rough, but I'm trying to figure out how to make it work.

 

[SarkMan]

Name all of your input boxes, option boxes with a similiar name pattern

for example
<input type=&quot;text&quot; name=&quot;txtFieldID&quot;>
<input type=&quot;text&quot; name=&quot;txtFieldID&quot;>

The fieldID will be the table Field to be updated
then loop through the forms collection and construct an update statements separated by ; as follows
request.form(Item)- will be the value entered by the person
Item - will be the name of the field with a txt prefix

for each item in Request.Form
if instr(1,Item,&quot;txt&quot<>0 then

'Response.Write Item & Request.Form(Item) & &quot;<BR>&quot;
strSQL=strSQL & &quot;Update tblPeople SET fVotes = '&quot; & request.form(Item) & &quot;' WHERE fName = '&quot; & replace(Item,&quot;txt&quot;,&quot;&quot & &quot;';&quot;

end if
next

'print the sql string to make sure it is OK before hitting the database
response.write strSQL
response.end

This will give you a large sql statement and then open a connection usig ado and execute the sql.

 

[jasonsalas]

Thanks SarkMan.

I also thinking about trying something like that...but with a Do...Loop including the SQL string within it:

Do While Not objRS.EOF
&quot;UPDATE tblPeople SET fVotes &quot; & objRS(&quot;fVotes&quot & &quot; WHERE fName = '&quot; & objRS(&quot;fName&quot & &quot;'&quot;
objRS.MoveNext
Loop

I'll try this and let you know how it goes.

Thanks again for the help.

 

[RushiShroff]

If you are using SQL server, it completely supporting Transactions.
So you need to just concatenate all update statements with one another like..

Update tblNam SET fnm='xyz' where fnm2='whatever'Update tblNam SET fnm='xyz1' where fnm2='whatever1'Update tblNam SET fnm='xyz3' where fnm2='whatever3'Update tblNam SET fnm='xyz4' where fnm2='whatever4'

And then execute them at one go..
objConn.execute abovestring

It works fine with SQL server.This is going to be basis for MTS also as it is one of the basic rules in ACID. Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;

 

[jasonsalas]

Thanks...I actually wound up modifying SarkMan's script...rather than concatenating the entire SQL string into one long one...which I was having trouble with, getting the error &quot;Invalid characters found after end of SQL string&quot;, I just included the execution of the SQL string in the For...Next iteration:

For Each Item In Request.Form
If InStr(1,Item,&quot;txt&quot <> 0 Then
Response.Write(Item & Request.Form(Item) & &quot;<br>&quot
Response.Write(strSQL & &quot;<br>&quot
strSQL = &quot;UPDATE tblPeople SET fVotes = &quot; & Request.Form(Item) & &quot; WHERE fName = &quot; & Replace(Item,&quot;txt&quot;,&quot;&quot & &quot;&quot;
End If
objConn.Execute(strSQL)
Next

Thanks again!

 

[Guest_imported]

I have asp and JavaScript problem with inserting multiple columns(EquipmentNumber) to a table(EquipmentDetails)according to the user inputs in to Count and Quantity text fields.

If the user inputs value 10 in Quantity and 2 in count then the EquipmentNumber column in database should have the values &quot;EquipmentNumber/2,EquipmentNumber/3 and so on.. till EquipmentNumber/10.

Any help is greatly appreciated.

regards
Harry

 

[RushiShroff]

same way you have to concatenate them

Dim val1, val2, MainVal
for i=val1 to val2

MainVal=&quot;EquipmentNumber/&quot;&i
insert into EquipmentDetails(EquipmentNumber) values('&quot;& MainVal &&quot;')

next

This way you can set.If you are using Access database then you have to execute statement within loop.(Multiple execute) Rushi Shroff Rushi@emqube.com
&quot;Life is beautiful.&quot;

 

[SarkMan]

The invalid character was probably the ; at the end of the string

you could have added the following to remove it
after the next statement

strSQL=left(strSQL,len(strSQL)-1)