[Webinar] Streamline your web hosting managementRegister Today


Error inserting hidden fields into sql db

Posted on 2010-03-28
Medium Priority
Last Modified: 2012-05-09
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
Question by:GMMC_man
  • 5
  • 4
LVL 60

Expert Comment

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

Author Comment

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

Expert Comment

ID: 28910204
post that SQL statement here.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 28911084
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

    ' 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
        MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
      End If
    End If
  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' );
<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" />
LVL 60

Expert Comment

ID: 28911281
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

Author Comment

ID: 28911526
I'm not a regular programmer.You are going to have to break it down to me.
LVL 60

Expert Comment

ID: 28911648
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.  

Author Comment

ID: 28913051
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.

Accepted Solution

GMMC_man earned 0 total points
ID: 29912869
I reset my insert code and placed the values within the execute statement instead of pulling them from the form.

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 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