Solved

Urgent: Why does this code not insert new column?

Posted on 2011-09-19
6
305 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:OGSan
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now