UPDATE DUMMY FUNCTION

[cumap]

Hi all,

I'm doing a batch update for our products database, and as far as I concern, I would never know how many and which particular fields will be edited and updated. All I did is set on/off button for those fields and user will select which field(s) to edit and hit Update button when finished. The form will then sent to Update.asp page to process and the UPDATE function will have to be smart enough to update the field(s) that was active and leave other inactive field(s) alone (because I experienced blank/NULL data when update everything included those inactive fields.)

Because of not knowing how many fields and which fields will be active, this is what I come up:

strSQL = "UPDATE tblProducts SET 1=1"
		if len(sName)>0 then
			strSQL = strSQL & ", ShortName = '" & sName & "' "
		end if
		if len(lName)>0 then	
			strSQL = strSQL & ", LongName = '" & lName & "' "
		end if
		if len(ptypeID)>0 then
			strSQL = strSQL & ", ProdTypeID = '" & ptypeID & "' " 
		end if
		if len(pmanuID)>0 then
			strSQL = strSQL & ", ManuID = " & pmanuID & " "
		end if
		strSQL = strSQL & " WHERE ProductID = " & strID

I used the "1=1" as a dummy account so that, for sure, any next field will includes ", " and so on.

Here is a test result of the sql

UPDATE tblProducts SET 1=1, ProdTypeID = '6,5,7,8' WHERE ProductID = 17


it seems innocent enough; however, data is not updated at all. I tested and know for sure the "1=1" is the problem.

Is there a way for me to work this out? I meant I still need some kind of a dummy account, but it should work for this situation. Of course, any other ADO suggestion that by-pass this technique is also welcome.

Thank you all.

 

[cumap]

Nevermind all,

I found an ADO the solved my problem, here is the new code

<%
'declare the variables
Response.Buffer = true

On Error Resume Next

		
Dim strErrorMessage
Dim bolErrors

'Initialize variables
strErrorMessage = ""	'The error messages for tech. support
bolErrors = False	'Have we found any errors yet?

'Now our two subs
sub TrapError(strError)
	bolErrors = True	'Egad, we've found an error!
	strErrorMessage = strErrorMessage & strError & ", "
end sub

'first, get the total # of items that could be updated
Dim icount
icount = request.form("count")

'we need to obtain each Desc, fileName, ProdTypeID, ManuID and ID
Dim sName, lName, ptypeID, pmanuID, sDelete, ptype, pCurPage

'get support type
ptype = Request.form("type")

'get current page
pCurPage = Request.form("curPage")
Dim iLoop
For iLoop = 0 to icount
	sName 	= Request.form(iLoop & ".ShortName")
	lName 	= Request.form(iLoop & ".LongName")
	ptypeID	= Request.form(iLoop & ".ProdTypeID")
	pmanuID	= Request.Form(iLoop & ".ManuID")
	sDelete = Request.form(iLoop & ".delete")
	strID 	= Request.form(iLoop & ".ProductID")
		
	if len(sName)>0 OR len(lName)>0 OR len(ptypeID)>0 OR len(pmanuID)>0 OR len(sDelete)>0 then
	
		'update the appropriate column
		Set rs = Server.CreateObject("ADODB.Recordset")
		rs.CursorType 	= 2
		rs.LockType		= 3
		rs.Open "SELECT ShortName,LongName,ProdTypeID,ManuID FROM tblProducts WHERE ProductID = " & strID, objConn
		
		if len(sName)>0 then
			rs.Fields("ShortName") = sName
		end if
		if len(lName)>0 then	
			rs.Fields("LongName") = lName
		end if
		if len(ptypeID)>0 then
			rs.Fields("ProdTypeID") = ptypeID
		end if
		if len(pmanuID)>0 then
			rs.Fields("ManuID") = pmanuID
		end if
		rs.Update
		if len(sDelete) > 0 then
			'Delete records from ProductSupport
			strSQL = "DELETE FROM tblProducts " _
				   & "	WHERE ProductID = " &  trim(sDelete)
			objConn.Execute strSQL
		
			'Delete records from Supports
			strSQL = "DELETE FROM tblProductSupport " _
				   & "	WHERE idProdSupport = " &  trim(sDelete)
			objConn.Execute strSQL
		end if
		
		If Err.number <> 0 then
			TrapError Err.description
		End If
		
	end if
	
Next
call closeRS(rs)
call closedb()

if len(sName)>0 OR len(lName)>0 OR len(ptypeID)>0 OR len(pmanuID)>0 OR len(sDelete)>0 then
	Response.write "<tr><td colspan=8><center><p><b><font color='red'>Supports were updated/deleted! One Moment...</font></b></p></center></td></tr>"
else
	Response.write "<tr><td colspan=8><center><p><b><font color='red'>No records have been selected to update or delete.</font></b></p></center></td></tr>"
end if

%>

<meta http-equiv="refresh" content="2;URL=default.asp?type=<%=ptype%>&curPage=<%=curPage%>">

If anything you might find that can help this batch update works better, please don't hesitate to do so. I'm learning ASP and always looking for a better way to code.