Insert Record - Get ID from Session Variable

Hi,

I am sure I have used this functionality ok before, but for some reason it doesn't want to play ball at the moment.

I am inserting a record with Data Assist and am storing the new ID in a session variable called var_auctionid. However, when I try to recall the recordset, filtering by record ID using the var_auctionid session variable it always errors.

I've tried to print this to screen and it appears the session variable is always empty.

Am I missing something?

I am using Classic ASP for this site.

Thanks
Ian

I can't tell by your description alone what might cause that problem. There is nothing obvious wrong with the concept. How are you storing the session variable? Using the one that is automatically stored by the insert record server behavior I assume?

Hi Ray,

Thanks for replying. Yes you are absolutely spot on.

I set the Data Assist insert record behaviour to store the ID as the Session Variable. I can see that the IDs are getting created by looking in the SQL table, but when the redirect page loads (again specified in the DA Insert behaviour) the Session Variable appears empty.

Thanks
Ian

Tell me more about the form... what fields are you updating? What values did you set?

You may want to add some debug code right where the session variable is being set to see if it is at least trying to set it correctly.

Use:
Response.Write(Session("variablename"))
Response.End

to write the session variable right after it is created to see if it can be referenced immediately after it is set or if it is being set at all.

Thanks Ray,

As the session is set within the code for the DA Insert behaviour I'm guessing it would look something like this?

<%
' WA Application Builder Insert
if (Request.ServerVariables("Request_Method") = "POST") then
WA_connection = MM_portvaleSQL_STRING
WA_table = "dbo.auction"
WA_sessionName = "var_auctionid"
WA_redirectURL = "review.asp"
WA_keepQueryString = false
WA_indexField = "auctionid"
WA_fieldNamesStr = "auctionname|auctiontext|auctionstartprice|auctionstartdate|auctionenddate|auctionenabled"
WA_fieldValuesStr = "" & cStr(WA_DFP_UploadObj.Form("itemname")) & "" & "|" & "" & cStr(WA_DFP_UploadObj.Form("description")) & "" & "|" & "" & cStr(WA_DFP_UploadObj.Form("reserve")) & "" & "|" & "" & cStr( Session("var_startdate") ) & "" & "|" & "" & cStr( Session("var_enddate") ) & "" & "|" & "False"
WA_columnTypesStr = "',none,''|',none,''|',none,''|',none,NULL|',none,NULL|',none,''"
WA_comparisonStr = " LIKE | LIKE | LIKE | = | = | LIKE "
WA_fieldNames = Split(WA_fieldNamesStr,"|")
WA_fieldValues = Split(WA_fieldValuesStr,"|")
WA_columns = Split(WA_columnTypesStr,"|")
WA_comparisions = Split(WA_comparisonStr, "|")

insertParamsObj = WA_AB_generateInsertParams(WA_fieldNames, WA_columns, WA_fieldValues, -1)
set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = WA_connection
MM_editCmd.CommandText = "INSERT INTO " & WA_table & " (" & insertParamsObj(1) & ") VALUES (" & insertParamsObj(2) & ")"
MM_editCmd.Execute()
MM_editCmd.ActiveConnection.Close()
obj = WA_AB_generateWhereClause(WA_fieldNames, WA_columns, WA_fieldValues, WA_comparisions)
sqlstr = "SELECT " & WA_indexField & " FROM " & WA_table & " WHERE " & obj & " ORDER BY " & WA_indexField & " DESC"
set WA_AppBuilderRecordset = Server.CreateObject("ADODB.Recordset")
WA_AppBuilderRecordset.ActiveConnection = WA_connection
WA_AppBuilderRecordset.Source = sqlstr
WA_AppBuilderRecordset.CursorType = 0
WA_AppBuilderRecordset.CursorLocation = 2
WA_AppBuilderRecordset.LockType = 1
WA_AppBuilderRecordset.Open()
if (NOT WA_AppBuilderRecordset.EOF) then Session(WA_sessionName) = WA_AppBuilderRecordset.Fields.Item(WA_indexField).Value

Response.Write(Session("var_auctionid"))
Response.End

WA_AppBuilderRecordset.Close()
if (WA_redirectURL <> "") then
if (WA_keepQueryString AND Request.QueryString <> "" AND Request.QueryString.Count > 0) then
if (inStr(WA_redirectURL,"?") > 0) then
WA_redirectURL = WA_redirectURL & "&"
else
WA_redirectURL = WA_redirectURL & "?"
end if
WA_redirectURL = WA_redirectURL & Request.QueryString
end if
Response.Redirect(WA_redirectURL)
end if
end if
%>

In which case the page is returned blank and no value is written to screen.

Best Regards,
Ian

Hi Ray,

A bit more info here, and I dont know if I am barking up the wrong tree.

If I add this line

Session("var_auctionid") = WA_AppBuilderRecordset.Fields.Item(WA_indexField).Value

AFTER this one

if (NOT WA_AppBuilderRecordset.EOF) then Session(WA_sessionName) = WA_AppBuilderRecordset.Fields.Item(WA_indexField).Value

Which in theory is doing the same thing, but without using the WA function variables and excluding the "If NOT" statement then I get this error:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/members/additem.asp, line 55

Which suggests the the extension is not picking up the newly inserted record - which does exist by the way because I checked the SQL table and also the ID field is definately an autonumber.

Thanks
Ian

Try writing:

Response.Write(sqlstr)

That will tell you the sql statement used to find the newly inserted record and may give a clue as to why it isn't returned... I can't seem to spot the problem.

Aha! Sorted it after looking at the SQL string. It was a boolean field that was throwing a wobbler.

Perfect. So to get the ID of the new record it just pulls back a recordset with the form details that have just been posted and ordering by ID DESC? I guess it doesnt have to be any more scientific than that!

Thanks Ray