Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unanswered: Trouble Updating ADODB Recordsets

    I've just added the code to process and update forms, but it doesn't work ... I want to be able to mass update many fields in a table at once. Here is part of my code, but I can't give a specific error message or line because it just gives an internal server error. The problem is somewhere between if Request("Validate1").count <> 0 then. I really need help, I'm not to familiar with ASP let alone ADO. Thanks.

    Code:
    <%
    	Pf_ID			= mscsPage.RequestString("Pf_ID")
    	
    	Set errorList = Server.CreateObject("Commerce.SimpleList")
    	Set cmdTemp = cycleADOConnection()
    	
    	If Pf_ID <> "" Then
    		query = "1"
    		quoted_Pf_ID	= "'" & Replace(Pf_ID,"'","''") & "'"
    		quoted_Pf_ID	= Replace(quoted_Pf_ID,"*","%")
    		fields 		= Request.Form("fields")
    		if fields = "" then fields = "Pf_ID" else fields = "Pf_ID, " & fields
    		
    		cmdTemp.CommandText = "SELECT " & fields & " FROM " & MSCSTables.Product & " WHERE Pf_ID LIKE" & quoted_Pf_ID
    		Set rsProduct = Server.CreateObject("ADODB.Recordset")
    		rsProduct.Open cmdTemp, , adOpenStatic, adLockReadOnly
    		
    		if Request("Validate1").count <> 0 then			
    			cmdTemp.CommandText = "SELECT " & fields & " FROM " & MSCSTables.Product & " WHERE Pf_ID LIKE" & quoted_Pf_ID
    			Set rsProduct = Server.CreateObject("ADODB.Recordset")
    			rsProduct.Open cmdTemp, , adOpenStatic, adLockOptimistic
    		
    			do until rsProduct.EOF
    				for each x in rsProduct.Fields
    					if x.name = "Pf_ID" then
    						tag = x.value
    						
    						'Product ID : Req
    						'**********************************************************************
    						'Pf_ID = mscsPage.RequestString("Pf_ID", "", 1, 100)
    						'if IsNull(Pf_ID) then
    						'	errorList.Add "Product ID must be between 1 and 100 characters."
    						'end if
    						
    						'Product Name : Req
    						'**********************************************************************
    						Name = mscsPage.RequestString(tag & "_name", "", 1, 255)
    						if IsNull(Name) then
    							errorList.Add "Product name must be between 1 and 255 characters."
    						else
    							if name = "" then name = null
    							rsProduct.Update "Name", name
    							
    						end if
    						
    						'Product Description : Req
    						'**********************************************************************
    						Description = mscsPage.RequestString(tag & "_description", "", 1, 255)
    						if IsNull(description) then
    							errorList.Add "description must be between 1 and 255 characters"
    						else
    							if description = "" then description = null
    							rsProduct.Update "description", description
    						end if
    						
    						'Product Unit Of Measure : Req
    						'**********************************************************************
    						UnitCode = mscsPage.RequestString(tag & "_UnitCode", "", 1, 3)
    						if IsNull(UnitCode) then
    							errorList.Add "Product Unit Code must be between 1 and 3 characters."
    						else
    							rsProduct.Update "UnitCode", UnitCode
    						end if
    						
    						'Product Package Size : Req
    						'**********************************************************************
    						Package_Qty = mscsPage.RequestString(tag & "_Package_Qty", "", 1, 3)
    						if IsNull(Package_Qty) OR Trim(Package_Qty) = "" Then
    							errorList.Add "Package Size must be entered."
    						else
    							rsProduct.Update "Package_Qty", Package_Qty
    						end if
    						
    						'Product Price : Req
    						'**********************************************************************
    						List_Price = mscsPage.RequestMoneyAsNumber(tag & "_List_Price", "", 0, 2147483647)
    						if IsNull(List_Price) then
    							errorList.Add "List_Price must be between 0 and 2147483647"
    						else
    							if List_Price = "" then List_Price = null
    							rsProduct.Update "List_Price", List_Price
    						end if
    						
    						Location = mscsPage.RequestString(tag & "_Location", NULL, 0, 50)
    						If IsNull(Location) then
    							errorList.Add "Location must be between 0 and 50"
    						Else
    							rsProduct.Update "Location", Location
    						end if
    						
    						With_Image = Request(tag & "_With_Image")
    						IF IsNull(With_Image) OR With_Image = "" THEN
    							With_Image = "0"
    							rsProduct.Update "With_Image", With_Image
    						else
    							rsProduct.Update "With_Image", With_Image
    						end if
    						
    						Sale_Price = mscsPage.RequestMoneyAsNumber(tag & "_Sale_Price", "0", 0, 2147483647)
    						if IsNull(Sale_Price) then
    							errorList.Add "Sale_Price must be between 0 and 2147483647"
    						else
    							if Sale_Price = "" then Sale_Price = null
    							rsProduct.Update "Sale_Price", Sale_Price
    						end if
    						
    						Sale_Start = mscsPage.RequestDate(tag & "_Sale_Start", "1/1/2000")
    						if IsNull(Sale_Start) then
    							errorList.Add "Sale_Start must be a valid date"
    						else
    							if Sale_Start = "" then Sale_Start = null
    							rsProduct.Update "Sale_Start", Sale_Start
    						end if
    						
    						Sale_End = mscsPage.RequestDate(tag & "_Sale_End", "1/1/2000")
    						if IsNull(Sale_End) then
    							errorList.Add "Sale_End must be a valid date"
    						else
    							if Sale_End = "" then Sale_End = null
    							rsProduct.Update "Sale_End", Sale_End
    						end if
    						
    						if Not IsNull(Sale_Start) and  Not IsNull(Sale_End) and DateDiff("d", Sale_End, Sale_Start) > 0 then
    							errorList.Add "Sale_End must be a valid date on or after Sale_Start"
    						end if
    						
    						Prod_Weight = Trim(Request(tag & "_Prod_Weight"))
    						If IsNull(Prod_Weight) OR IsEmpty(Prod_Weight) OR Prod_Weight = "" then
    							errorList.Add "Product Weight must be between 0 and 10000"
    						else
    							rsProduct.Update "Prod_Weight", Prod_Weight
    						end if
    						
    						In_Stock = mscsPage.RequestString(tag & "_In_Stock", "", 1, 5)
    						if IsNull(In_Stock) OR In_Stock = "" then
    							errorList.Add "In Stock must be between 0 and 30000"
    						else
    							rsProduct.Update "In_Stock", In_Stock
    						end if
    						
    						Feature = Request(tag & "_Feature")
    						if IsNull(Feature) OR IsEmpty(Feature) OR Trim(Feature) = "" then
    							Feature		= " "
    							rsProduct.Update "Feature", Feature
    						Else
    							Feature = ChrToSign(Feature)
    							rsProduct.Update "Feature", Feature
    						End If
    						
    						Doc_Name	= LCase(mscsPage.RequestString(tag & "_Doc_Name", NULL, 1, 50))
    						If IsNull(Doc_Name) OR IsEmpty(Doc_Name) OR Trim(Doc_Name) = "" Then
    							Doc_Name = NULL
    							rsProduct.Update "Doc_Name", Doc_Name
    						Else
    							rsProduct.Update "Doc_Name", Doc_Name
    						End If
    						
    						if err.Number <> 0 then
    							errorList.Add "Database error when updating product: " & err.Number & "  " & err.Description
    						end if
    						err.Clear
    					end if
    				next 
    				rsProduct.MoveNext
    			loop
    		end if
    	Else
    		quoted_Pf_ID = ""
    		'Response.Redirect "product.asp"
    		
    		cmdTemp.CommandText = "SELECT * FROM " & MSCSTables.Product
    		Set rsProduct = Server.CreateObject("ADODB.Recordset")
    		rsProduct.Open cmdTemp, , adOpenStatic, adLockReadOnly
    	End If
    // Andrew Rosborough

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    On the recordset you're using to do the update, try adOpenDynamic or adOpenKeyset instead of adOpenStatic
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Mar 2004
    Posts
    14
    thanks for trying, but that doesn't seem to help ... for now I've taken out the update conditions, and am only trying to get one field to update out of the database. This made the 500 internal errors go away, but nothing updates.
    // Andrew Rosborough

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Why don't you try executing the SQL through the command object:
    Code:
    set rsProduct = cmdTemp.Execute (, , adCmdTable)
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    Mar 2004
    Posts
    14

    Talking Fixed it!

    I think there was a missing then or setting of an undefined variable somewhere. I rebuilt that condition and it works now:

    Code:
    <!--#INCLUDE FILE="inc/Manager.asp" -->
    
    <%
    	' Designed by Andrew Rosborough 5/2004
    	
    	Pf_ID			= mscsPage.RequestString("Pf_ID")
    	
    	Set errorList = Server.CreateObject("Commerce.SimpleList")
    	Set cmdTemp = cycleADOConnection()
    	
    	If Pf_ID <> "" Then
    		query = "1"
    		quoted_Pf_ID	= "'" & Replace(Pf_ID,"'","''") & "'"
    		quoted_Pf_ID	= Replace(quoted_Pf_ID,"*","%")
    		fields 		= Request.Form("fields")
    		if fields = "" then fields = "Pf_ID" else fields = "Pf_ID, " & fields
    		
    		cmdTemp.CommandText = "SELECT " & fields & " FROM " & MSCSTables.Product & " WHERE Pf_ID LIKE" & quoted_Pf_ID
    		Set rsProduct = Server.CreateObject("ADODB.Recordset")
    		rsProduct.Open cmdTemp, , adOpenStatic, adLockReadOnly
    		
    	ElseIf Request("Validate1").count <> 0 then
    		
    		fields = request("fields")
    		quoted_Pf_ID = request("quoted_Pf_ID")
    		
    		cmdTemp.CommandText = "SELECT " & fields & " FROM " & MSCSTables.Product & " WHERE Pf_ID LIKE" & quoted_Pf_ID
    		Set rsProduct = Server.CreateObject("ADODB.Recordset")
    		rsProduct.Open cmdTemp, , adOpenStatic, adLockOptimistic
    		
    		while not rsProduct.EOF
    			
    			for each x in rsProduct.Fields
    				if x.name = "Pf_ID" then
    					tag = x.value
    				elseif x.name = "Name" then
    					'Product Name : Req
    					'**********************************************************************
    					Name = mscsPage.RequestString(tag & "_name", "", 1, 255)
    					if IsNull(Name) then
    						errorList.Add "Product name must be between 1 and 255 characters."
    					else
    						if name = "" then name = null
    						rsProduct.Update "Name", name
    					end if
    				elseif x.name = "Description" then
    					'Product Description : Req
    					'**********************************************************************
    					Description = mscsPage.RequestString(tag & "_description", "", 1, 255)
    					if IsNull(description) then
    						errorList.Add "description must be between 1 and 255 characters"
    					else
    						if description = "" then description = null
    						rsProduct.Update "description", description
    					end if
    				elseif x.name = "Feature" then
    					Feature = request(tag & "_Feature")
    					if IsNull(Feature) OR IsEmpty(Feature) OR Trim(Feature) = "" then
    						Feature	= ""
    						rsProduct.Update "Feature", Feature
    					Else
    						Feature = ChrToSign(Feature)
    						rsProduct.Update "Feature", Feature
    					End If
    				elseif x.name = "UnitCode" then
    					'Product Unit Of Measure : Req
    					'**********************************************************************
    					UnitCode = mscsPage.RequestString(tag & "_UnitCode", "", 1, 3)
    					if IsNull(UnitCode) then
    						errorList.Add "Product Unit Code must be between 1 and 3 characters."
    					else
    						rsProduct.Update "UnitCode", UnitCode
    					end if
    				elseif x.name = "Package_Qty" then
    					'Product Package Size : Req
    					'**********************************************************************
    					Package_Qty = mscsPage.RequestString(tag & "_Package_Qty", "", 1, 3)
    					if IsNull(Package_Qty) OR Trim(Package_Qty) = "" Then
    						errorList.Add "Package Size must be entered."
    					else
    						rsProduct.Update "Package_Qty", Package_Qty
    					end if
    				elseif x.name = "Prod_Weight" then
    					Prod_Weight = Trim(Request(tag & "_Prod_Weight"))
    					If IsNull(Prod_Weight) OR IsEmpty(Prod_Weight) OR Prod_Weight = "" then
    						errorList.Add "Product Weight must be between 0 and 10000"
    					else
    						rsProduct.Update "Prod_Weight", Prod_Weight
    					end if
    				elseif x.name = "List_Price" then
    					'Product Price : Req
    					'**********************************************************************
    					List_Price = mscsPage.RequestMoneyAsNumber(tag & "_List_Price", "", 0, 2147483647)
    					if IsNull(List_Price) then
    						errorList.Add "List_Price must be between 0 and 2147483647"
    					else
    						if List_Price = "" then List_Price = null
    						rsProduct.Update "List_Price", List_Price
    					end if
    				elseif x.name = "Sale_Price" then
    					Sale_Price = mscsPage.RequestMoneyAsNumber(tag & "_Sale_Price", "0", 0, 2147483647)
    					if IsNull(Sale_Price) then
    						errorList.Add "Sale_Price must be between 0 and 2147483647"
    					else
    						if Sale_Price = "" then Sale_Price = null
    						rsProduct.Update "Sale_Price", Sale_Price
    					end if
    				elseif x.name = "Sale_Start" then
    					Sale_Start = mscsPage.RequestDate(tag & "_Sale_Start", "1/1/2000")
    					if IsNull(Sale_Start) then
    						errorList.Add "Sale_Start must be a valid date"
    					else
    						if Sale_Start = "" then Sale_Start = null
    						rsProduct.Update "Sale_Start", Sale_Start
    					end if
    				elseif x.name = "Sale_End" then
    					Sale_End = mscsPage.RequestDate(tag & "_Sale_End", "1/1/2000")
    					if IsNull(Sale_End) then
    						errorList.Add "Sale_End must be a valid date"
    					else
    						if Sale_End = "" then Sale_End = null
    						rsProduct.Update "Sale_End", Sale_End
    					end if
    				elseif x.name = "Attr_Label1" then
    					Attr_Label1 = mscsPage.RequestString(tag & "_Attr_Label1", "", 1, 255)
    					if IsNull(Attr_Label1) then
    						errorList.Add "Attr_Label1 must be between 0 and 255 characters"
    					else
    						if Attr_Label1 = "" then Attr_Label1 = null
    						rsProduct.Update "Attr_Label1", Attr_Label1
    					end if
    				elseif x.name = "Attr_Label2" then
    					Attr_Label2 = mscsPage.RequestString(tag & "_Attr_Label2", "", 0, 255)
    					if IsNull(Attr_Label2) then
    						errorList.Add "Attr_Label2 must be between 0 and 255 characters"
    					else
    						if Attr_Label2 = "" then Attr_Label2 = null
    						rsProduct.Update "Attr_Label2", Attr_Label2
    					end if
    				elseif x.name = "Attr_Label3" then
    					Attr_Label3 = mscsPage.RequestString(tag & "_Attr_Label3", "", 0, 255)
    					if IsNull(Attr_Label3) then
    						errorList.Add "Attr_Label3 must be between 0 and 255 characters"
    					else
    						if Attr_Label3 = "" then Attr_Label3 = null
    						rsProduct.Update "Attr_Label3", Attr_Label3
    					end if
    				elseif x.name = "Attr_Label4" then
    					Attr_Label4 = mscsPage.RequestString(tag & "_Attr_Label4", "", 0, 255)
    					if IsNull(Attr_Label4) then
    						errorList.Add "Attr_Label4 must be between 0 and 255 characters"
    					else
    						if Attr_Label4 = "" then Attr_Label4 = null
    						rsProduct.Update "Attr_Label4", Attr_Label4
    					end if
    				elseif x.name = "Attr_Label5" then
    					Attr_Label5 = mscsPage.RequestString(tag & "_Attr_Label5", "", 0, 255)
    					if IsNull(Attr_Label5) then
    						errorList.Add "Attr_Label5 must be between 0 and 255 characters"
    					else
    						if Attr_Label5 = "" then Attr_Label5 = null
    						rsProduct.Update "Attr_Label5", Attr_Label5
    					end if
    				elseif x.name = "With_Image" then
    					With_Image = Request(tag & "_With_Image")
    					IF IsNull(With_Image) OR With_Image = "" THEN
    						With_Image = "0"
    						rsProduct.Update "With_Image", With_Image
    					else
    						rsProduct.Update "With_Image", With_Image
    					end if
    				elseif x.name = "In_Stock" then
    					In_Stock = mscsPage.RequestString(tag & "_In_Stock", "", 1, 5)
    					if IsNull(In_Stock) OR In_Stock = "" then
    						errorList.Add "In Stock must be between 0 and 30000"
    					else
    						rsProduct.Update "In_Stock", In_Stock
    					end if
    				elseif x.name = "Location" then
    					Location = mscsPage.RequestString(tag & "_Location", NULL, 0, 50)
    					If IsNull(Location) then
    						errorList.Add "Location must be between 0 and 50"
    					Else
    						rsProduct.Update "Location", Location
    					end if
    				elseif x.name = "Doc_Name" then
    					Doc_Name = LCase(mscsPage.RequestString(tag & "_Doc_Name", NULL, 1, 50))
    					If IsNull(Doc_Name) OR IsEmpty(Doc_Name) OR Trim(Doc_Name) = "" Then
    						Doc_Name = NULL
    						rsProduct.Update "Doc_Name", Doc_Name
    					Else
    						rsProduct.Update "Doc_Name", Doc_Name
    					End If
    				
    				end if
    			next 
    			rsProduct.MoveNext
    		wend
    		
    		rsProduct.Close
    		Set rsProduct	= Nothing
    		cmdTemp.CommandText = "SELECT * FROM " & MSCSTables.Product
    		Set rsProduct = Server.CreateObject("ADODB.Recordset")
    		rsProduct.Open cmdTemp, , adOpenStatic, adLockReadOnly
    	Else
    		quoted_Pf_ID = ""
    		'Response.Redirect "product.asp"
    		
    		cmdTemp.CommandText = "SELECT * FROM " & MSCSTables.Product
    		Set rsProduct = Server.CreateObject("ADODB.Recordset")
    		rsProduct.Open cmdTemp, , adOpenStatic, adLockReadOnly
    	End If
    %>
    thanks for your suggestions though.
    // Andrew Rosborough

Posting Permissions

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