Solved

Urgent: Why does this code not insert new column?

Posted on 2011-09-19
6
312 Views
Last Modified: 2012-05-12
Hi, Experts -
I'm in a jam and need quick help:  I have a working ASP 3.0 program that inserts a new row into a table.  All works fine.  Users want a new column to be added - defined as a MEMO type file in the Access mdb, it appears at the end of the table and is called, "quiz_line_dtl"  I created the new column in the mdb, uploaded it to the server, modified the ASP program to display the captured results before updating the database (which it does display), and everything seems to go fine until I examine the table - no new column there...!?!
Below is the ASP code that inserts the new row with the new column called quiz_line_dtl.  The values in vstrQuizLineDtl were displayed for debugging and appear fine.
' I N S E R T  a new row into tbl_QuizMakerResults

		Set rsWeb=Server.CreateObject("ADODB.Recordset")

		Const adOpenDynamic = 3, adLockOptimistic = 3

		sql=("SELECT * FROM tbl_QuizMakerResults;")

		'D E B U G  display sql code
		Response.write("7. sql = " & sql & "<br />")
	
		rsWeb.open sql, HRConnection, adOpenDynamic, adLockOptimistic

		' Create a new record in recordset
		rsWeb.AddNew

		if vstrQuizName <> "" 		then rsWeb("quiz_name")=vstrQuizName 				else rsWeb("quiz_name")=" "
		if vstrUserID <> "" 		then rsWeb("quiz_Assigned_ID") = vstrUserID			else rsWeb("quiz_Assigned_ID")=" "
		if vstrPassword <> "" 		then rsWeb("quiz_Assigned_PW") = vstrPassword		else rsWeb("quiz_Assigned_PW")=" "
		if vstrLastName <> "" 		then rsWeb("quiz_LastName") = vstrLastName 			else rsWeb("quiz_LastName")=" "
		if vstrFirstName <> "" 		then rsWeb("quiz_FirstName") = vstrFirstName 		else rsWeb("quiz_FirstName")=" "
		if vstrEmplID <> "" 		then rsWeb("quiz_EmplID") = vstrEmplID		 		else rsWeb("quiz_EmplID")=" "
		if vstrQuizStatus <> "" 	then rsWeb("quiz_status") = vstrQuizStatus 			else rsWeb("quiz_status")=" "
		if vstrQuizScore <> "" 		then rsWeb("quiz_score_pctg") = vstrQuizScore 		else rsWeb("quiz_score_pctg")=" "
		if vstrQuizRaw <> "" 		then rsWeb("quiz_raw_score") = vstrQuizRaw 			else rsWeb("quiz_raw_score")=" "
		if vstrQuizPassing <> "" 	then rsWeb("quiz_passing_score") = vstrQuizPassing 	else rsWeb("quiz_passing_score")=" "
		if vstrQuizMax <> "" 		then rsWeb("quiz_max_score") = vstrQuizMax 			else rsWeb("quiz_max_score")=" "
		if vstrQuizMin <> "" 		then rsWeb("quiz_min_score") = vstrQuizMin 			else rsWeb("quiz_min_score")=" "
		if vstrQuizDate <> "" 		then rsWeb("quiz_date_taken") = vstrQuizDate 		else rsWeb("quiz_date_taken")=" "
		if vstrQuizTime <> "" 		then rsWeb("quiz_time_taken") = vstrQuizTime 		else rsWeb("quiz_time_taken")=" "
		if vstrQuizLineDtl <> "" 	then rsWeb("quiz_line_dtl") = vstrQuizLineDtl 		else rsWeb("quiz_line_dtl")=" "

		rsWeb("lastmodified") = vstrQuizDate
		rsWeb("quiz_ip")=Request.ServerVariables("remote_host")

		' Update and close the recordset, clear memory
		rsWeb.Update
		rsWeb.Close
		Set rsWeb=Nothing

Open in new window

display of QuizLineDtl prior to mdb update
0
Comment
Question by:OGSan
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:pateljitu
ID: 36569152
Try this code please:

Also see article related to Cursor and Lock type:

http://www.w3schools.com/ado/met_rs_open.asp

For e.g. in your code you defined cursor adOpenDynamic = 3  (3 = adOpenStatic)
Const adOpenDynamic = 2, adLockOptimistic = 3

and try Const adOpenKeyset = 1 in your recordset.
' I N S E R T  a new row into tbl_QuizMakerResults

		Set rsWeb=Server.CreateObject("ADODB.Recordset")

		Const adOpenDynamic = 2, adLockOptimistic = 3

		sql=("SELECT * FROM tbl_QuizMakerResults;")

		'D E B U G  display sql code
		Response.write("7. sql = " & sql & "<br />")
	
		rsWeb.open sql, HRConnection, adOpenDynamic, adLockOptimistic

		' Create a new record in recordset
		rsWeb.AddNew

		if vstrQuizName <> "" 		then rsWeb("quiz_name")=vstrQuizName 				else rsWeb("quiz_name")=" "
		if vstrUserID <> "" 		then rsWeb("quiz_Assigned_ID") = vstrUserID			else rsWeb("quiz_Assigned_ID")=" "
		if vstrPassword <> "" 		then rsWeb("quiz_Assigned_PW") = vstrPassword		else rsWeb("quiz_Assigned_PW")=" "
		if vstrLastName <> "" 		then rsWeb("quiz_LastName") = vstrLastName 			else rsWeb("quiz_LastName")=" "
		if vstrFirstName <> "" 		then rsWeb("quiz_FirstName") = vstrFirstName 		else rsWeb("quiz_FirstName")=" "
		if vstrEmplID <> "" 		then rsWeb("quiz_EmplID") = vstrEmplID		 		else rsWeb("quiz_EmplID")=" "
		if vstrQuizStatus <> "" 	then rsWeb("quiz_status") = vstrQuizStatus 			else rsWeb("quiz_status")=" "
		if vstrQuizScore <> "" 		then rsWeb("quiz_score_pctg") = vstrQuizScore 		else rsWeb("quiz_score_pctg")=" "
		if vstrQuizRaw <> "" 		then rsWeb("quiz_raw_score") = vstrQuizRaw 			else rsWeb("quiz_raw_score")=" "
		if vstrQuizPassing <> "" 	then rsWeb("quiz_passing_score") = vstrQuizPassing 	else rsWeb("quiz_passing_score")=" "
		if vstrQuizMax <> "" 		then rsWeb("quiz_max_score") = vstrQuizMax 			else rsWeb("quiz_max_score")=" "
		if vstrQuizMin <> "" 		then rsWeb("quiz_min_score") = vstrQuizMin 			else rsWeb("quiz_min_score")=" "
		if vstrQuizDate <> "" 		then rsWeb("quiz_date_taken") = vstrQuizDate 		else rsWeb("quiz_date_taken")=" "
		if vstrQuizTime <> "" 		then rsWeb("quiz_time_taken") = vstrQuizTime 		else rsWeb("quiz_time_taken")=" "
		if vstrQuizLineDtl <> "" 	then rsWeb("quiz_line_dtl") = vstrQuizLineDtl 		else rsWeb("quiz_line_dtl")=" "

		rsWeb("lastmodified") = vstrQuizDate
		rsWeb("quiz_ip")=Request.ServerVariables("remote_host")

		' Update and close the recordset, clear memory
		rsWeb.Update
		rsWeb.Close
		Set rsWeb=Nothing

Open in new window

0
 
LVL 1

Author Comment

by:OGSan
ID: 36569394
Thanks for the reply, Pateljitu, but there was no change - I'm still not getting those new values added to the new column in my table.
I inserted the adOpenKeyset = 1 as well as the adOpenDynamic = 2, but it was a nogo.  I've included the code that precedes the actual insert - maybe I'm doing something wrong upstream that is causing this bad behavior.
Appreciate any insights anyone can provide me on this problem.
Thanks!
' Open connection string for QuizMakerResults.mdb
		if	not IsObject(HRConnection) then
			strDataPath = server.MapPath("/hr/db/public/QuizMakerResults.mdb;")
			set HRConnection=Server.CreateObject("ADODB.Connection")
		       strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
   	              + " Data Source= " & strDataPath & ";"_
       	          + " Mode=Share Deny None;"
			HRConnection.ConnectionTimeout = 15
			HRConnection.CommandTimeout =  10
			HRConnection.CursorLocation = 3
 		    HRConnection.Mode = 3 'adModeReadWrite 
 		    if	HRConnection.state = 0 then
 		        HRConnection.Open strConnectString
		    end if  
		end if

		' U P D A T E  the tbl_AssignedUserID row with Today's Date/Time - this makes this AssignedID unuseable if an attempt is made to retake test.

		Set rsAssignedID=Server.CreateObject("ADODB.Recordset")

		sql = ("UPDATE tbl_AssignedUserID SET tbl_AssignedUserID.DateUsed = '" & Now() & "' WHERE ((AssignedUserID = '" & vstrUserID & "') AND (AssignedPassword = '" & vstrPassword & "'));")

		'D E B U G  display sql code
		'Response.write("1. sql = " & sql & "<br />")

		set rsAssignedID = HRConnection.execute(sql)	

		' R E T R I E V E  the tbl_AssignedUserID row just updated for First and Last Name values.

		sql = ("SELECT LastName, FirstName, EmplID, ContactEmail, ContactPhone " &_
			   "FROM tbl_AssignedUserID " &_
			   "WHERE ((AssignedUserID = '" & vstrUserID & "') AND (AssignedPassword = '" & vstrPassword & "'));")

		'D E B U G  display sql code
		'Response.write("2. sql = " & sql & "<br />")

		set rsAssignedID = HRConnection.execute(sql)

		vstrLastName 		= rsAssignedID.fields.item(0).value
		vstrFirstName 		= rsAssignedID.fields.item(1).value
		vstrEmplID 			= rsAssignedID.fields.item(2).value
		vstrContactEmail	= rsAssignedID.fields.item(3).value
		vstrContactPhone	= rsAssignedID.fields.item(4).value

	'D E B U G  display sql code
	'Response.write("3. vstrLastName = " & vstrLastName & "<br />")
	'Response.write("4. vstrFirstName = " & vstrFirstName & "<br />")
	'Response.write("5. vstrEmplID = " & vstrEmplID & "<br />")
	'Response.write("6. vstrContactEmail = " & vstrContactEmail & "<br />")

		set rsAssignedID = nothing

' I N S E R T  a new row into tbl_QuizMakerResults

		Set rsWeb=Server.CreateObject("ADODB.Recordset")

		Const adOpenDynamic = 2, adLockOptimistic = 3, adOpenKeyset = 1

		sql=("SELECT * FROM tbl_QuizMakerResults;")

		'D E B U G  display sql code
		Response.write("7. sql = " & sql & "<br />")
	
		rsWeb.open sql, HRConnection, adOpenDynamic, adLockOptimistic

		' Create a new record in recordset
		rsWeb.AddNew

		if vstrQuizName <> "" 		then rsWeb("quiz_name")=vstrQuizName 				else rsWeb("quiz_name")=" "
		if vstrUserID <> "" 		then rsWeb("quiz_Assigned_ID") = vstrUserID			else rsWeb("quiz_Assigned_ID")=" "
		if vstrPassword <> "" 		then rsWeb("quiz_Assigned_PW") = vstrPassword		else rsWeb("quiz_Assigned_PW")=" "
		if vstrLastName <> "" 		then rsWeb("quiz_LastName") = vstrLastName 			else rsWeb("quiz_LastName")=" "
		if vstrFirstName <> "" 		then rsWeb("quiz_FirstName") = vstrFirstName 		else rsWeb("quiz_FirstName")=" "
		if vstrEmplID <> "" 		then rsWeb("quiz_EmplID") = vstrEmplID		 		else rsWeb("quiz_EmplID")=" "
		if vstrQuizStatus <> "" 	then rsWeb("quiz_status") = vstrQuizStatus 			else rsWeb("quiz_status")=" "
		if vstrQuizScore <> "" 		then rsWeb("quiz_score_pctg") = vstrQuizScore 		else rsWeb("quiz_score_pctg")=" "
		if vstrQuizRaw <> "" 		then rsWeb("quiz_raw_score") = vstrQuizRaw 			else rsWeb("quiz_raw_score")=" "
		if vstrQuizPassing <> "" 	then rsWeb("quiz_passing_score") = vstrQuizPassing 	else rsWeb("quiz_passing_score")=" "
		if vstrQuizMax <> "" 		then rsWeb("quiz_max_score") = vstrQuizMax 			else rsWeb("quiz_max_score")=" "
		if vstrQuizMin <> "" 		then rsWeb("quiz_min_score") = vstrQuizMin 			else rsWeb("quiz_min_score")=" "
		if vstrQuizDate <> "" 		then rsWeb("quiz_date_taken") = vstrQuizDate 		else rsWeb("quiz_date_taken")=" "
		if vstrQuizTime <> "" 		then rsWeb("quiz_time_taken") = vstrQuizTime 		else rsWeb("quiz_time_taken")=" "
		if vstrQuizLineDtl <> "" 	then rsWeb("quiz_line_dtl") = vstrQuizLineDtl 		else rsWeb("quiz_line_dtl")=" "

		rsWeb("lastmodified") = vstrQuizDate
		rsWeb("quiz_ip")=Request.ServerVariables("remote_host")

		' Update and close the recordset, clear memory
		rsWeb.Update
		rsWeb.Close
		Set rsWeb=Nothing

Open in new window

0
 
LVL 15

Accepted Solution

by:
pateljitu earned 500 total points
ID: 36569529
Try this code, replace existing line with provided sample:

Related article:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=80
Const adOpenDynamic = 2, adLockOptimistic = 3, adOpenKeyset = 1
        dim db_vstrQuizLineDtl
        
		sql=("SELECT * FROM tbl_QuizMakerResults;")

		'D E B U G  display sql code
		Response.write("7. sql = " & sql & "<br />")
	
		rsWeb.open "tbl_QuizMakerResults", HRConnection, adOpenDynamic, adLockOptimistic

		' Create a new record in recordset
		rsWeb.AddNew

		if vstrQuizName <> "" 		then rsWeb("quiz_name")=vstrQuizName 				else rsWeb("quiz_name")=" "
		if vstrUserID <> "" 		then rsWeb("quiz_Assigned_ID") = vstrUserID			else rsWeb("quiz_Assigned_ID")=" "
		if vstrPassword <> "" 		then rsWeb("quiz_Assigned_PW") = vstrPassword		else rsWeb("quiz_Assigned_PW")=" "
		if vstrLastName <> "" 		then rsWeb("quiz_LastName") = vstrLastName 			else rsWeb("quiz_LastName")=" "
		if vstrFirstName <> "" 		then rsWeb("quiz_FirstName") = vstrFirstName 		else rsWeb("quiz_FirstName")=" "
		if vstrEmplID <> "" 		then rsWeb("quiz_EmplID") = vstrEmplID		 		else rsWeb("quiz_EmplID")=" "
		if vstrQuizStatus <> "" 	then rsWeb("quiz_status") = vstrQuizStatus 			else rsWeb("quiz_status")=" "
		if vstrQuizScore <> "" 		then rsWeb("quiz_score_pctg") = vstrQuizScore 		else rsWeb("quiz_score_pctg")=" "
		if vstrQuizRaw <> "" 		then rsWeb("quiz_raw_score") = vstrQuizRaw 			else rsWeb("quiz_raw_score")=" "
		if vstrQuizPassing <> "" 	then rsWeb("quiz_passing_score") = vstrQuizPassing 	else rsWeb("quiz_passing_score")=" "
		if vstrQuizMax <> "" 		then rsWeb("quiz_max_score") = vstrQuizMax 			else rsWeb("quiz_max_score")=" "
		if vstrQuizMin <> "" 		then rsWeb("quiz_min_score") = vstrQuizMin 			else rsWeb("quiz_min_score")=" "
		if vstrQuizDate <> "" 		then rsWeb("quiz_date_taken") = vstrQuizDate 		else rsWeb("quiz_date_taken")=" "
		if vstrQuizTime <> "" 		then rsWeb("quiz_time_taken") = vstrQuizTime 		else rsWeb("quiz_time_taken")=" "
		
		if vstrQuizLineDtl <> "" then 
            db_vstrQuizLineDtl = vstrQuizLineDtl 		
        else
            db_vstrQuizLineDtl = ""    
        end if
            
        rsWeb("quiz_line_dtl") = db_vstrQuizLineDtl 
		rsWeb("lastmodified") = vstrQuizDate
		rsWeb("quiz_ip")=Request.ServerVariables("remote_host")

		' Update and close the recordset, clear memory
		rsWeb.Update
		rsWeb.Close
		Set rsWeb=Nothing

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:OGSan
ID: 36569965
Hi, patelitu -
I'm reading the article you linked to above right now.  I replaced my code with yours above, but still did not see any values being inserted into the column.
Below is an image of my field definitions for this table, showing that the MEMO field is defined at the end of the table.  At least I did that correctly...! fields
0
 
LVL 1

Assisted Solution

by:OGSan
OGSan earned 0 total points
ID: 36571281
Hi, patelitu - I'm going to scratch this method of using a MEMO field and instead create a separate table containing each of the line items.  I've got some more time now.  Thanks for the assist on this, even though it didn't work out.  Judging from the posts I've seen - this is just not a workable solution.
Thanks, again.
Jeff (aka OGSan)
0
 
LVL 1

Author Closing Comment

by:OGSan
ID: 36594801
Unable to find solution using first approach.  Decided to alter approach after determining that there is just no way to get this going.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Live mode in DW, need to creae Session 4 107
JQuery to parse xml string and get element by id 4 48
Save ms data to server side. 19 55
html Uncheck Checkbox 2 25
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question