-1

I'm trying to update a two records with the same social in Classic ASP but I'm not sure how. This is what I have... but it don't update both records.

    set rsTblEmpl=Server.CreateObject("ADODB.Recordset")
    if 1=1 then
        sql = "select * from tblEmpl where ssn=" & strSsn & ";"
    else
        sql = "select * from tblEmpl where eight_id=" & intEight_id & ";"
    end if 
    rsTblEmpl.open sql, conn, 2, 3
    if not rsTblEmpl.eof then
        rsTblEmpl("posid")   = StrPosId
        rsTblEmpl("posname") = StrPosName
        rsTblEmpl.update
    end if
    rsTblEmpl.close
    set rsTblEmpl=nothing
Dale K
  • 25,246
  • 15
  • 42
  • 71
Sdot2323
  • 59
  • 5
  • 1
    Why select then update? Why not just use an `update` sql statement. Also watch out for SQL injection here. Look at [parameterized queries](https://yomotherboard.com/prevent-sql-injection-with-classic-asp/) and/or stored procedures. – Jon P Oct 19 '20 at 21:52
  • Can I use if statements in update sql statement – Sdot2323 Oct 19 '20 at 21:56
  • You could use a Stored Procedure in SQL Server to handle the conditional logic and perform the `UPDATE` statement. – user692942 Oct 20 '20 at 06:04
  • There are plenty of questions about calling Stored Procedures from VBScript using `ADODB.Command` you just need to search for them. – user692942 Oct 20 '20 at 07:11
  • 1
    Does this answer your question? [Using Stored Procedure in Classical ASP .. execute and get results](https://stackoverflow.com/questions/21915874/using-stored-procedure-in-classical-asp-execute-and-get-results) – user692942 Oct 20 '20 at 08:16

1 Answers1

1

The reason why it's only updating 1 value is because you're not looping over all records. You're only going to be updating the first record that it finds. So if there are multiple records with the same SSN you will have to change the if statement to a loop.

do until rsTblEmpl.eof
    rsTblEmpl("posid")   = StrPosId
    rsTblEmpl("posname") = StrPosName
    rsTblEmpl.update
    rsTblEmpl.movenext
loop

Or you can do them all in one go using a parameterized UPDATE statement:

UPDATE tblEmpl SET posid = ?, posname = ? WHERE ssn = ?

The if 1=1 at the top I'll assume is a placeholder, because now it will always be true, so the else is not needed.

Daniel Nordh
  • 362
  • 3
  • 15