I could've sworn I posted this yesterday, but when I looked I didn't see it, so if this is a duplicate posting, I apologize.
I actually have two issues/questions:
Issue 1:
I have an autonumber field in an access db table that I grab and later use to update a record in another table withing the same db.
The code I use to get it from the db table is:
'Retrieve the Registration Identification Number
strRegisterID = Rs("Register_ID")
Prior to testing my code and actually updating the db, I'm trying to write it to the page to make sure their isn't a loop or massive problem with my code.
I'm writing to the page like this:
for each objItem in request.Form()
if left(objItem,5)="cksub" and request.Form(objItem) <> "" then
response.write("strRegisterID = ")
response.write(strRegisterID)
tempsub=request.form(objItem)
response.write("tempsub = ")
response.write(tempsub)
end if
tempsub = ""
next
When I do this, I'm getting a type mismatch error.
Question 1: Is this because I'm in the "for each objItem" loop that doesn't want to use the strRegisterID?
* * * * * * * * * * * * * * * * * * * * * * * * * * *
Issue 2:
I need to update two separate tables within the same database using two recordsets, which I've never done before. I've created the following code for the update and want to know if anyone can tell if I'm on track or if my code is wacko. The end result is taking information from a form and placing it in my db as so:
All of the text fields and one checkbox called enotify are needed for my WebRegister table. But, all Checkbox selections starting with the value "cksub" and <> "" will be sent to the WebCommodities table, along with the key field (which is retreived from the WebRegister table after adding the record to it, and since its an autonumber field and I need it to be inserted into the WebCommodities table to identify the user, I have to add the record first. (Note: the checkboxes were generated dynamically and there may be more than one set of checkboxes on the page because user needs to be able to select one or all of them per set) Anyway, my code is as follows:
<%
'Grab variables from the querystring and remove any apostrophes
FormCompany=Request.Form("txtCompany")
strCompany=Replace(FormCompany, "'", "")
FormAddress=Request.Form("txtAddress")
strAddress=Replace(FormAddress, "'", "")
FormCity=Request.Form("txtCity")
strCity=Replace(FormCity, "'", "")
strState=Request.Form("txtState")
strZip=Request.Form("txtZip")
strPhone=Request.Form("txtPhone")
strFax=Request.Form("txtFax")
FormWebsite=Request.Form("txtWebsite")
strWebsite=Replace(FormWebsite, "'", "")
FormContact=Request.Form("txtContact")
strContact=Replace(FormContact, "'", "")
strCPhone=Request.Form("txtCPhone")
FormEmail=Request.Form("txtEmail")
strEmail=Replace(FormEmail, "'", "")
If Request.Form("txtnotify") = "-1" Then
FormNotify=Request.Form("txtnotify")
strNotify = "You have chosen to receive automatic e-mail notification "
strNotify = strNotify & "when goods and/or services you indicated, come up for bid."
Else
FormNotify = "0"
strNotify = "You will not be notified when goods and/or services you indicated "
strNotify = strNotify & "come up for bid"
End If
strhidDate=Request.Form("txtDate")
'Create the statement to add a new record into the table "WebRegister".
'The first parenthesis set defines the field names as defined in the table and the second set
'of parenthesis places the values as assigned above (taken from the user input of the form
'on the Supplier_Reg.asp page) into the described fields of the table. The replace function
'[example: replace(strCompany, "'", "")] removes any apostrophes the user may have entered
'(which causes code problems) before inserting the data into the records. The replace
'function should be used whenever a user is free to key in data of their choosing.
'Create a RecordSet object
Set SupRs = Server.CreateObject("ADODB.RecordSet") 'Create Supplier/Contact Info Recordset
Set ComRs = Server.CreateObject("ADODB.RecordSet") 'Create Commodities Info Recordset
'Open the table
strProvider="Provider=MSDASQL.1;Persist Security Info=False;Data Source=Register"
SupRs.Open "WebRegister", strProvider, adOpenKeySet, adLockPessimistic
'Add a new record to WebRegister Table
SupRs.AddNew
SupRs("Company_Name") = strCompany
SupRs("St_Address") = strAddress
SupRs("City") = strState
SupRs("Zip") = strZip
SupRs("Phone_Num") = strPhone
SupRs("Fax_Num") = strFax
SupRs("Web_Site") = strWebsite
SupRs("Contact_Person") = strContact
SupRs("Contact_Phone") = strCPhone
SupRs("Contact_Email") = strEmail
SupRs("Respond_Da") = strhidDate
SupRs("E_Notify")= FormNotify
'Update the record
SupRs.Update
'Retrieve the Registration Identification Number
strRegisterID = Rs("Register_ID")
'Close the first recordset
SupRs.Close
Set SupRs = Nothing
'Open the next recordset
ComRs.Open "WebCommodities", strProvider, adOpenKeySet, adLockPessimistic
'Add a new record(s) to WebCommodities Table
'Need RegisterID for each checkbox selected
for each objItem in request.Form()
if left(objItem,5)="cksub" and request.Form(objItem) <> "" then
ComRs.AddNew
ComRs("Register_ID") = strRegisterID
ComRs("SubCategory") = request.form(objItem)
ComRs.Update
next
'Close the second recordset
ComRs.Close
Set ComRs = Nothing
%>
As far as my second issue is concerned, I'm not real confident that the For...Next loop to add the record(s) to the WebCommodities table is going to do the trick. There is other code on the page, but I believe its immaterial to my issues. I'd really appreciate any help on this!!
type mismatch and update loop - HELP!
Linear Mode
