asked on

Urgent: Why does this code not insert new column?

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

pateljitu

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

OGSan

ASKER

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

ASKER CERTIFIED SOLUTION

pateljitu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OGSan

ASKER

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...!

SOLUTION

OGSan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OGSan

ASKER

Unable to find solution using first approach. Decided to alter approach after determining that there is just no way to get this going.