Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ASP > Trouble Updating ADODB Recordsets

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 05-04-04, 16:46
arosboro arosboro is offline
Registered User
  
Join Date: Mar 2004
Posts: 14
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
Reply With Quote
  #2 (permalink)  
Old 05-05-04, 01:56
Seppuku Seppuku is offline
Useless...
  
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.
Reply With Quote
  #3 (permalink)  
Old 05-05-04, 15:54
arosboro arosboro is offline
Registered User
  
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
Reply With Quote
  #4 (permalink)  
Old 05-05-04, 15:56
Seppuku Seppuku is offline
Useless...
  
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.
Reply With Quote
  #5 (permalink)  
Old 05-06-04, 18:31
arosboro arosboro is offline
Registered User
  
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
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.