asked on

Error inserting hidden fields into sql db

I'm having difficulty inserting data into my sql database. I've got four fields; textAnswer, userID, assignmentID and date. None of these fields can contain NULL values. I'm getting the value for txtAnswer from the user, userID, assignmentID are coming from session variables and date is coming from <% date()%> . For some reason the normal insert statement is producing this error. Cannot insert the value NULL into column 'userID', table 'myClubonServer1.dbo.steps_answers'; column does not allow nulls. INSERT fails

chapmandew

Sounds like your session variable is null. are you using in-line SQL for the insert or stored procedures?

GMMC_man

ASKER

I have the values printing out on the page just to make sure they are there. They are all showing up.

chapmandew

post that SQL statement here.

GMMC_man

ASKER

<%
If (CStr(Request("MM_insert")) = "frmWorkbook") Then
If (Not MM_abortEdit) Then
' execute the insert
Dim MM_editCmd

Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_connFCdata_STRING
MM_editCmd.CommandText = "INSERT INTO dbo.steps_answers (Answer1, userID, AssignmentID, UpdateDate) VALUES (?, ?, ?, ?)"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 201, 1, -1, Request.Form("txtAnswer")) ' adLongVarChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 5, 1, -1, MM_IIF(Request.Form("user"), Request.Form("user"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, -1, MM_IIF(Request.Form("assignment"), Request.Form("assignment"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 135, 1, -1, MM_IIF(Request.Form("UpdateDate"), Request.Form("UpdateDate"), null)) ' adDBTimeStamp
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

' append the query string to the redirect URL
Dim MM_editRedirectUrl
MM_editRedirectUrl = "AnswerProcess.asp?transID="
If (Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
Response.Redirect(MM_editRedirectUrl)
End If
End If
%>

Here are the form fields
<textarea name="txtAnswer" cols="59" rows="8" id="txtAnswer" onblur="MM_validateForm('txtAnswer','','R');return document.MM_returnValue"></textarea></td>
<script type="text/javascript">
CKEDITOR.replace( 'txtAnswer' );
</script>
<input name="user" type="hidden" id="user" value="<%Session("MM_userID")%>" />
<input name="assignment" type="hidden" id="assignment" value="<%Session("MM_CurrentAssignment")%>" />
<input name="UpdateDate" type="hidden" id="UpdateDate" value="<% Date() %>" />
<input type="hidden" name="MM_insert" value="frmWorkbook" />
<input type="submit" name="btnNext" id="btnNext" value="Next" />

chapmandew

seems like you don't want to be doing this IIF

MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 5, 1, -1, MM_IIF(Request.Form("user"), Request.Form("user"), null)) ' adDouble

GMMC_man

ASKER

I'm not a regular programmer.You are going to have to break it down to me.

chapmandew

Im not a web programmer, but the code looks like if the request.form("user") session variable is null, it just uses a NULL value.

GMMC_man

ASKER

I'm not sure why that variable is not showing up int the form or the sql is not getting the info from the form. The variable is set and displaying.

ASKER CERTIFIED SOLUTION

GMMC_man

membership

This solution is only available to members.

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

Start Free Trial