Solved

Urgent: Why does this code not insert new column?

Posted on 2011-09-19
6
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
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/…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

728 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