Update Queries in SQL

Results 1 to 2 of 2

Thread: Update Queries in SQL

  1. #1
    Join Date
    Dec 1969

    Default Update Queries in SQL

    Apologies I did post earlier in the day, but have been unable to resolve the problems that I had, so I am hoping that someone can help if I explain better what i am trying to do.<BR><BR>I need to update 2 records, in 2 tables, bascically I need all users that are registered on my site to change there &#039;Profile&#039; to the correct one, to do this, I have created a table which is a copy of all the users that in the db, and assigned a field called "Checked" to all of them set at Zero.<BR>Whe the users login, it checks this table to see if they are registered, and checks to see if the checked field is Zero, if it is, then they are redirected to update their profile...<BR><BR><BR>Fine so far, everything works. BUT when I try and make the asp page update both tables (set the new user Profile, and change the zero to a 1 ), I now get an error saying that :<BR><BR>ADODB.Recordset error &#039;800a0e78&#039; <BR><BR>The operation requested by the application is not allowed if the object is closed. <BR>( The same query works if I make it a select query ?!)<BR><BR>I have copied some code out of access which worked, but it failed in SQL 7, and have tried the suggestions given by people in the forum, but was unable to get BOTH tables updated.<BR><BR>I apologise for my igorance, but I am new to using serve databases, and cannot seem to create the right syntax in my asp page to make the query run. Here is the code that I am trying at the moment ( suggested by one of the forum ), and it updates 1 table in SQL query anylzer(logins.Status), but not the other table, plus I receive the same error as ablceo when I try and run the code through my asp page.<BR><BR>Any help or guidance would be amazing. Thankyou in advance.<BR><BR>&#060;%LANGUAGE="VBSCRIPT"%&#062;< BR><BR>&#060;%<BR>Dim struser, strstatus, strname<BR><BR>struser = session("User")<BR>strstatus = request.querystring("status")<BR>strname = session("name")<BR><BR> &#039; -- Declare Variables<BR> Dim objConn &#039; Our Connection Object<BR> Dim objRS &#039; Our Recordset Object<BR> Dim strSQL &#039; Our SQL String to access the database<BR> Dim strConnection &#039; Our Connection string to access the database<BR> Dim i &#039; a counter variable<BR> Dim strResult_1514 &#039; Our Function Return Value<BR><BR><BR> &#039; -- Create objects<BR> Set objConn = Server.CreateObject("ADODB.Connection")<BR> Set objRS = Server.CreateObject("ADODB.Recordset")<BR><BR> &#039; -- Connection String Value<BR> strConnection = "DSN=kpi&baag;uid=kpiuser_2039;password=dut20ton39 z"<BR><BR> &#039; -- Open the Connection<BR> objConn.Open strConnection<BR> update logins<BR>set logins.Status = &#039;Coach&#039;<BR>Where logins.username IN ( SELECT logins.username FROM logins JOIN qcr_status_check ON<BR>Logins.Username = qcr_status_check.Username WHERE qcr_status_check.Checked =&#039;1&#039;)<BR>AND logins.username = &#039;morrisp6&#039;<BR><BR>response.write strsql<BR><BR> <BR> &#039; -- Populate our Recordset with data<BR>objRS.open strsql, objconn<BR><BR>if objRS.eof then<BR> response.write "Data Load in Progress, please wait 5 minutes before retrying."<BR> response.end<BR> End if<BR><BR> &#039; -- Close objects<BR> &#039;objRS.Close<BR> &#039;set objRS = Nothing<BR> &#039;objConn.Close<BR> &#039;set objConn = Nothing<BR><BR>response.write "Status Updated, &#060;a href=""logout.asp""&#062;You will need to log out, in order for the changes to take place."<BR><BR>%&#062;<BR><BR>Paul

  2. #2
    Join Date
    Dec 1969

    Default I just responded below...


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts