Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Urgent: Why does this code not insert new column?

Posted on 2011-09-19
6
Medium Priority
?
319 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

604 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