Update multiple records and fields problem

[J.D. Buehls]

I need to update a table based on input into text fields on a form
The html page has two columns, Responsibility # and Responsibility
The Res. # has an number (like 1,2,3) and the Responsibilty is string.

Everything displays perfectly but I need the user to be able to update
both fields. So he could rearrange the numbers and also change the
text of any of them.

I have done plenty of update queries, but none of this variety. So
far, I have used a loop to give each textbox a diff. name and tried
passing my update query page the number of rows. Then loop through
that and try to update for each row where the RESP_NUM ="whaterver".

dim val
dim iloop
for iloop = 1 to count
val="RESP"&iloop
strSQLsaveresp = "UPDATE RESP SET " _
& "RESP = '" & CStr(Replace(Request.Form(val),"'", "''")) & "' " _
& "WHERE (POSCODE = '" & id & "' AND RESP_NUM = '" &
Request.Form("RESP_NUM") & "')"

cnnDBEdit.Execute(strSQLsaveresp)
next

(count is passed and is the number of records displayed on the page)
Textboxes are name resp1, resp2, etc.

That code is obviously not gonna work, but.....

The "Responsibilities" table is set up as follow
|POSCODE|RESP_NUM|RESP|
-----------------------
|*70362 | 1 |this is responsibility #1..yada
|*70362 | 2 |this is responsibility #2..yada

and on for a variable number of responsibilites per poscode
.......

This is intranet and doesnt necessarily need to check if each record
has been changed before updating. Just update keeping the resp_num
and resp matching after the user changes either box on the page.

Cant find any tutorials that help do this. Some do multiple records,
but not multiple fields and records.

Thanks everybody!

[Greg Griffiths]

UPDATE takes more than one parameter :

[url]http://www.google.com/search?sourceid=navclient&q=SQL+Update[/url]

"J.D. Buehls" wrote:

> I need to update a table based on input into text fields on a form
> The html page has two columns, Responsibility # and Responsibility
> The Res. # has an number (like 1,2,3) and the Responsibilty is string.
>
> Everything displays perfectly but I need the user to be able to update
> both fields. So he could rearrange the numbers and also change the
> text of any of them.
>
> I have done plenty of update queries, but none of this variety. So
> far, I have used a loop to give each textbox a diff. name and tried
> passing my update query page the number of rows. Then loop through
> that and try to update for each row where the RESP_NUM ="whaterver".
>
> dim val
> dim iloop
> for iloop = 1 to count
> val="RESP"&iloop
> strSQLsaveresp = "UPDATE RESP SET " _
> & "RESP = '" & CStr(Replace(Request.Form(val),"'", "''")) & "' " _
> & "WHERE (POSCODE = '" & id & "' AND RESP_NUM = '" &
> Request.Form("RESP_NUM") & "')"
>
> cnnDBEdit.Execute(strSQLsaveresp)
> next
>
> (count is passed and is the number of records displayed on the page)
> Textboxes are name resp1, resp2, etc.
>
> That code is obviously not gonna work, but.....
>
> The "Responsibilities" table is set up as follow
> |POSCODE|RESP_NUM|RESP|
> -----------------------
> |*70362 | 1 |this is responsibility #1..yada
> |*70362 | 2 |this is responsibility #2..yada
>
> and on for a variable number of responsibilites per poscode
> ......
>
> This is intranet and doesnt necessarily need to check if each record
> has been changed before updating. Just update keeping the resp_num
> and resp matching after the user changes either box on the page.
>
> Cant find any tutorials that help do this. Some do multiple records,
> but not multiple fields and records.
>
> Thanks everybody!

[J.D. Buehls]

Does this really answer my question? I mean, I can do update queries
no problem, like this one in my page:

strSQLsavegeneral = "UPDATE SUMMARY SET " _ & "USED_ EQUIP = '"
& CStr(Replace(Request.Form"USED_EQUIP"), "'", "''")) & "', " _
& "USED_MATLS = '" & CStr(Replace Request.Form "USED_MATLS"),
"'", "''")) & "', " _
& "USED_TOOLS = '" & CStr(Replace(Request.Form "USED_TOOLS"),
"'", "''")) & "' " _
& "WHERE (POSCODE = '" & id & "')"

I mean that is really simple, but what if you have a 2 by X number
layout of textboxes and you need to update each row of textboxes to a
specific record (where they are number 1 through X, which is the value
put into the left textbox). That is not as straightforward as the
basic update you linked to I believe.

Thanks again.


Greg Griffiths <greg2@surfaid.org> wrote in message news:<40215444.A4AB3ED0@surfaid.org>...

> UPDATE takes more than one parameter :
>
> [url]http://www.google.com/search?sourceid=navclient&q=SQL+Update[/url]
>
> "J.D. Buehls" wrote:
>

> > I need to update a table based on input into text fields on a form
> > The html page has two columns, Responsibility # and Responsibility
> > The Res. # has an number (like 1,2,3) and the Responsibilty is string.
> >
> > Everything displays perfectly but I need the user to be able to update
> > both fields. So he could rearrange the numbers and also change the
> > text of any of them.
> >
> > I have done plenty of update queries, but none of this variety. So
> > far, I have used a loop to give each textbox a diff. name and tried
> > passing my update query page the number of rows. Then loop through
> > that and try to update for each row where the RESP_NUM ="whaterver".
> >
> > dim val
> > dim iloop
> > for iloop = 1 to count
> > val="RESP"&iloop
> > strSQLsaveresp = "UPDATE RESP SET " _
> > & "RESP = '" & CStr(Replace(Request.Form(val),"'", "''")) & "' " _
> > & "WHERE (POSCODE = '" & id & "' AND RESP_NUM = '" &
> > Request.Form("RESP_NUM") & "')"
> >
> > cnnDBEdit.Execute(strSQLsaveresp)
> > next
> >
> > (count is passed and is the number of records displayed on the page)
> > Textboxes are name resp1, resp2, etc.
> >
> > That code is obviously not gonna work, but.....
> >
> > The "Responsibilities" table is set up as follow
> > |POSCODE|RESP_NUM|RESP|

> -----------------------

> > |*70362 | 1 |this is responsibility #1..yada
> > |*70362 | 2 |this is responsibility #2..yada
> >
> > and on for a variable number of responsibilites per poscode
> > ......
> >
> > This is intranet and doesnt necessarily need to check if each record
> > has been changed before updating. Just update keeping the resp_num
> > and resp matching after the user changes either box on the page.
> >
> > Cant find any tutorials that help do this. Some do multiple records,
> > but not multiple fields and records.
> >
> > Thanks everybody!

[McKirahan]

"J.D. Buehls" <bulldawgfan12000@yahoo.com> wrote in message
news:733c346f.0402040955.180bbfc7@posting.google.c om...

> I need to update a table based on input into text fields on a form
> The html page has two columns, Responsibility # and Responsibility
> The Res. # has an number (like 1,2,3) and the Responsibilty is string.
>
> Everything displays perfectly but I need the user to be able to update
> both fields. So he could rearrange the numbers and also change the
> text of any of them.

Two thoughts:


1) Don't store each Responsibility as a distinct row. Concatenate (Join)
all Responsibilities in a Memo field. Their relative position is their
"Responsibility #".


2) Use one text field to insert entries into a "multiple select" form
field. Update the table from the drop-down entries.

For updating you could add up and down arrows calling JavaScript functions
to reposition entries.

Set the "size=" to the maximum number of entries so they're visible and use
"style=" to set the width.

[Chris Hohmann]

"J.D. Buehls" <bulldawgfan12000@yahoo.com> wrote in message
news:733c346f.0402040955.180bbfc7@posting.google.c om...

> I need to update a table based on input into text fields on a form
> The html page has two columns, Responsibility # and Responsibility
> The Res. # has an number (like 1,2,3) and the Responsibilty is string.
>
> Everything displays perfectly but I need the user to be able to update
> both fields. So he could rearrange the numbers and also change the
> text of any of them.
>
> I have done plenty of update queries, but none of this variety. So
> far, I have used a loop to give each textbox a diff. name and tried
> passing my update query page the number of rows. Then loop through
> that and try to update for each row where the RESP_NUM ="whaterver".
>
> dim val
> dim iloop
> for iloop = 1 to count
> val="RESP"&iloop
> strSQLsaveresp = "UPDATE RESP SET " _
> & "RESP = '" & CStr(Replace(Request.Form(val),"'", "''")) & "' " _
> & "WHERE (POSCODE = '" & id & "' AND RESP_NUM = '" &
> Request.Form("RESP_NUM") & "')"
>
> cnnDBEdit.Execute(strSQLsaveresp)
> next
>
> (count is passed and is the number of records displayed on the page)
> Textboxes are name resp1, resp2, etc.
>
> That code is obviously not gonna work, but.....
>
> The "Responsibilities" table is set up as follow
> |POSCODE|RESP_NUM|RESP|
> -----------------------
> |*70362 | 1 |this is responsibility #1..yada
> |*70362 | 2 |this is responsibility #2..yada
>
> and on for a variable number of responsibilites per poscode
> ......
>
> This is intranet and doesnt necessarily need to check if each record
> has been changed before updating. Just update keeping the resp_num
> and resp matching after the user changes either box on the page.
>
> Cant find any tutorials that help do this. Some do multiple records,
> but not multiple fields and records.
>
> Thanks everybody!

Assuming MS SQL Server, here's what the query would look like:

UPDATE
R
SET
R.RESP= T.RESP
FROM
RESP AS R,
(
SELECT 1 AS RESP_NUM, 'this is responsibility #1..yada' AS RESP UNION
ALL
SELECT 2, 'this is responsibility #2..yada'
) AS T
WHERE
R.POSCODE = '*70362' AND
R.RESP_NUM = T.RESP_NUM

Constructing the query string in via ASP is left as an exercise for the
user.

HTH
-Chris Hohmann

[Chris Hohmann]

"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:%23bdZLvE7DHA.2924@tk2msftngp13.phx.gbl...

> "J.D. Buehls" <bulldawgfan12000@yahoo.com> wrote in message
> news:733c346f.0402040955.180bbfc7@posting.google.c om...

> > I need to update a table based on input into text fields on a form
> > The html page has two columns, Responsibility # and Responsibility
> > The Res. # has an number (like 1,2,3) and the Responsibilty is

string.

> >
> > Everything displays perfectly but I need the user to be able to

update

> > both fields. So he could rearrange the numbers and also change the
> > text of any of them.
> >
> > I have done plenty of update queries, but none of this variety. So
> > far, I have used a loop to give each textbox a diff. name and tried
> > passing my update query page the number of rows. Then loop through
> > that and try to update for each row where the RESP_NUM ="whaterver".
> >
> > dim val
> > dim iloop
> > for iloop = 1 to count
> > val="RESP"&iloop
> > strSQLsaveresp = "UPDATE RESP SET " _
> > & "RESP = '" & CStr(Replace(Request.Form(val),"'", "''")) & "' " _
> > & "WHERE (POSCODE = '" & id & "' AND RESP_NUM = '" &
> > Request.Form("RESP_NUM") & "')"
> >
> > cnnDBEdit.Execute(strSQLsaveresp)
> > next
> >
> > (count is passed and is the number of records displayed on the page)
> > Textboxes are name resp1, resp2, etc.
> >
> > That code is obviously not gonna work, but.....
> >
> > The "Responsibilities" table is set up as follow
> > |POSCODE|RESP_NUM|RESP|
> > -----------------------
> > |*70362 | 1 |this is responsibility #1..yada
> > |*70362 | 2 |this is responsibility #2..yada
> >
> > and on for a variable number of responsibilites per poscode
> > ......
> >
> > This is intranet and doesnt necessarily need to check if each record
> > has been changed before updating. Just update keeping the resp_num
> > and resp matching after the user changes either box on the page.
> >
> > Cant find any tutorials that help do this. Some do multiple

records,

> > but not multiple fields and records.
> >
> > Thanks everybody!

>
> Assuming MS SQL Server, here's what the query would look like:
>
> UPDATE
> R
> SET
> R.RESP= T.RESP
> FROM
> RESP AS R,
> (
> SELECT 1 AS RESP_NUM, 'this is responsibility #1..yada' AS RESP UNION
> ALL
> SELECT 2, 'this is responsibility #2..yada'
> ) AS T
> WHERE
> R.POSCODE = '*70362' AND
> R.RESP_NUM = T.RESP_NUM
>
> Constructing the query string in via ASP is left as an exercise for

the

> user.
>
> HTH
> -Chris Hohmann

Your posted code indicates that RESP_NUM is a string. Here's the revised
query:
UPDATE
R
SET
R.RESP= T.RESP
FROM
RESP AS R,
(
SELECT '1' AS RESP_NUM, 'this is responsibility #1..yada' AS RESP UNION
ALL
SELECT '2', 'this is responsibility #2..yada'
) AS T
WHERE
R.POSCODE = '*70362' AND
R.RESP_NUM = T.RESP_NUM

HTH
-Chris Hohmann