|
|
I actually have two issues/questions:
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?
* * * * * * * * * * * * * * * * * * * * * * * * * * *
My next issue is updating two separate tables within the same database using two recordsets. 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 another table. (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 recordset
SupRs.Close
Set SupRs = Nothing
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 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!!
|
|
|
|
Problem #1:
if left(objItem,5)="cksub" and request.Form(objItem) <> "" then
|
|
Should be:
if left(Request.Form(objItem),5)="cksub" and request.Form(objItem) <> "" then
|
|
As for your second issue, you didn't really say that you were having a specific issue. You just asked if your code is whacko. In reviewing that part of your code, I don't see any code problems except I cannot see how you are getting
'Retrieve the Registration Identification Number
strRegisterID = Rs("Register_ID")
|
|
But like you said, you didn't paste all of the code. My assumption is that what you wanted to do with the above line is retrieve the new ID from the previously inserted record? However, the Recordset Object "Rs" is never defined in the code you show. If the above line is correct, then I have to wonder why you didn't just grab that value before this point. Why leave that recordset (Rs) hanging around just to retrieve this one value in the middle of another Recordset transaction? Of course, maybe the rest of your code (not shown) makes this line understandable.
Good luck, and thanks for posting in CodeToad!
<Added>
Oh, and, I find it odd that you remove all single quotes from the values entered by your users. This only causes code problems if you don't code to handle them. There are 3 places that single quotes will cause problems.
1) Inserting values into the database.
2) Writing values into HTML.
3) Writing values into javascript.
When inserting into the database, replace single quotes with 2 single quotes.
| strMyValue = Replace(strMyValue,"'","''") |
|
When inserting into HTML, wrap values with double-quotes.
When inserting into javascript, replace single quotes with a forcing character followed by the single quote.
| strMyValue = Replace(strMyValue,"'","\'") |
|
|
|
|
|
|
|
First of all, I've solved issue number 1: My first recordset was defined as SupRs. So, when it got to the following code, it came up with a type mismatch error:
'Retrieve the Registration Identification Number
strRegisterID = Rs("Register_ID")
The line above should've been:
strRegisterID = SupRs("Register_ID")
So, that problem is solved.
Regarding issue number two:
I inserted your code 'windblown' and this is the output:
Adding new record for objitem: cksub4400
Added field strRegisterID with a value of: 14
However, I think I did a poor job of explaining my true issue.
In the WebCommodities table, I need to insert a new record for each checkbox checked - I created the following code to check my values (keep in mind that the number of elements in the array and its values will be different each time)
CkBoxArray="("
for each objItem in request.Form()
if left(objItem,5)="cksub" and request.Form(objItem) <> "" then
CkBoxArray=CkBoxArray & request.Form(objItem) & ","
end if
next
theLen=len(CkBoxArray)
CkBoxArray=left(CkBoxArray,theLen-1)
CkBoxArray=CkBoxArray & ")"
response.Write("Checkboxes with value of cksub plus categoryid concatenated = " & CkBoxArray & "<br>")
Based on the above code, this is the output:
Checkboxes with value of cksub plus categoryid concatenated = (4401, 4402, 4403,4202, 4203, 4204, 4205, 4208)
So, in this instance I'd need to insert 8 new records, each with the same registerid - so if I were to display those 8 records from my access table after they were inserted, I'd have this
Table: WebCommodities
Fields: WebRecID (autonumber), RegisterID, SubCategory
1 10 4401
2 10 4402
3 10 4403
4 10 4202
5 10 4203
6 10 4204
7 10 4205
8 10 4208
So, basically what I need to figure out is how to loop thru my checkbox array and insert a record for each value with the same Register ID. Hopefully, this explains things more directly. Based on this scenario, I don't think my loop will work.......Thanks!!
|
|
|
|
Ok, I'm narrowing down the problem. I've commented out this code:
'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
' end if
'next
and replaced it with this:
for each objItem in request.Form()
if left(objItem,5)="cksub" and request.Form(objItem) <> "" then
ComRs.AddNew
'<debug>
response.write "Adding new record for objitem: " & objitem & "<br>"
'</debug>
ComRs("Register_ID") = strRegisterID
'<debug>
response.write "Added field strRegisterID with a value of: " & strRegisterID & "<br>"
'</debug>
ComRs("SubCategory") = request.form(objItem)
'<debug>
response.write "SubCategory Updated with a value of: " & request.form(objitem) & "<br>"
'</debug>
ComRs.Update
'<debug>
response.write "Update Success!"
'</debug>
end if
next
I think my problem is that I need to loop thru the CkBoxArray to insert the SubCategory values into the db, because I got the same error with this until I tried a little experiment:
I opened up page one, keyed in data and only selected one main category (Office Equipment). Then on page two, I only selected one checkbox from the Office Equipment list of checkboxes and everything worked. The response.write version produced this on the final page:
Checkboxes with value of cksub plus categoryid concatenated = (4202)
Adding new record for objitem: cksub4200
Added field strRegisterID with a value of: 4
SubCategory Updated with a value of: 4202
Update Success!
So, I think that if I select more than one, its trying to place the entire array of values into the subcategory field (which is only 4 characters)
So, I think I need to change this code:
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
end if
next
So that
ComRs("SubCategory") = request.form(objItem)
is actually using the array element. But, I need to keep in mind that the number of elements will vary.
If I select two main categories on the first page (say: Office Equipment and Office Supplies)
and select the checkboxes with values of 4201, 4202, 4203, and 4204 from Office Equipment subcategory list
and select the checkboxes with values of 4401, 4402, 4403, and 4404 from the Office Supplies subcategory list, then my array contains this:
CkBoxArray = (4401, 4402, 4403, 4404, 4201, 4202, 4203, 4204)
So basically the loop I have is trying to insert
4401, 4402, 4403, 4404, 4201, 4202, 4203, 4204 into a four character field.
Error 0x80040E21 is said to be caused when you try to insert too much data into a specified field, for example if you enter a string that is 20 characters lone into a field that is defined as 4 characters. So now that I've narrowed down the problem, can anyone help me fix my loop so that the registerID for each record inserted remains the same, but the subcategory is using 1 element at a time from the array? Thanks!!!!!
|
|
|
|
Seala, unfortunately, I don't have time at present to provide a detailed, code-tested reply. BUT, here are some tips that hopefully will get you over the hump.
The VBScript Split() function is VERY handy. This will create an Array from a string splitting the values on each comma. The Trim statement will remove any spaces in the string before splitting into the array. Then I show two methods to enumerate the array. First, you can just use the "for each" method. Second, if you want to know how many items are in the array before you process it, you can use the Ubound() function. Keep in mind that VBScript arrays are zero-based, so the first item is index 0, then 1, then 2, etc. So if your array has 5 items in it, the Ubound() function will return the integer 4 meaning 0,1,2,3,4.
aryCheckboxes = Split(Trim(Request.Form("checkboxes")),",")
for each item in aryCheckboxes
response.Write(item & "<br>")
next
arrayLength = Ubound(aryCheckboxes)
for idx = 0 to arrayLength
response.Write("Array Item " & idx & ": " & aryCheckboxes(idx) & "<br>")
next
|
|
|
|
|
|
|
|
I'm having a similar problem, although it's not nearly as sophisicated as yours. I've been having problems with this for a week and I can't figure it out.
Please help! I send infor from an HTML form to an ASP page to be processed and added to the database tables (2 tables) and fields.
Could someone PLEASE give me a straight answer instead of saying I have to pass "something" into "some other thing" and have a "Do While" statement. I just began leasring ASP like a week ago, and have NO idea what people are talking about when they get REAL technical.
HELP!!
Here's the code below, plus some things that are bolded to see what I'm having the actual problem with.
<%@ LANGUAGE="VBScript" %>
<%
'Dimension variables
Dim adoCon
Dim rsAddMovie
Dim strSQLMovie
Dim strSQLActor
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'Set an active connection to the Connection object using DSN connection
adoCon.Open "DSN=movie"
'Create an ADO recordset object
Set rsAddMovie = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variables with the SQL statements to query the database
strSQLMovie = "SELECT tblMovies.Name, tblMovies.GenreID, tblMovies.Director, tblMovies.YearReleased, tblMovies.Length, tblMovies.RatingID, tblMovies.Review, tblMovies.CodecID, tblMovies.CDs, tblMovies.Notes FROM tblMovies;"
strSQLActor = "SELECT tblActors.Fname, tblActors.Lname FROM tblActors;"
'Set the cursor type we are using so we can navigate through the recordset
rsAddMovie.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddMovie.LockType = 3
'Open the recordset with the SQL query
rsAddMovie.Open strSQLMovie, strSQLActor, adoCon
'Tell the recordset we are adding a new record to it
rsAddMovie.AddNew
'Add a new record to the recordset
rsAddMovie.Fields("MovieName") = Request.Form("txtMovieName")
rsAddMovie.Fields("GenreID") = Request.Form("optGenre")
rsAddMovie.Fields("Director") = Request.Form("txtDirector")
rsAddMovie.Fields("YearReleased") = Request.Form("txtYearReleased")
rsAddMovie.Fields("Length") = Request.Form("Length")
rsAddMovie.Fields("RatingID") = Request.Form("optRating")
rsAddMovie.Fields("Review") = Request.Form("txtReview")
rsAddMovie.Fields("CodecID") = Request.Form("txtCodec")
rsAddMovie.Fields("CDs") = Request.Form("txtCds")
rsAddMovie.Fields("Notes") = Request.Form("txtNotes")
rsAddMovie.Fields("Fname") = Request.Form("txtFname")
rsAddMovie.Fields("Lname") = Request.Form("txtLname")
'Write the updated recordset to the database
rsAddMovie.Update
'Reset server objects
rsAddMovie.Close
Set rsAddMovie = Nothing
Set adoCon = Nothing
'Redirect to the guestbook.asp page
Response.Redirect "http://localhost/movie.asp"
%>
|
|
|
|
|
|
|
|
seala
Troy Wolf