SQL INSERT - how to avoid this error: Parameter ?_1 has no default value.

baxtalo
baxtalo used Ask the Experts™
on
Hi Experts,
I'm trying to insert some data in my table, but I keep getting this error message. Would you please let me know what the problem is?
Thank you
strSubject = request.Form("Subject")
strNightDate = request.Form("NightDate")
strPersonAddedOn = request.Form("PersonAddedOn")
strComputerName = request.Form("ComputerName")
strLoggedBy = request.Form("LoggedBy")
strAllNights = request.Form("AllNights")

'create connection object
Set MesIns=CreateObject("ADODB.Command")
MesIns.ActiveConnection=objConn
MesIns.commandtext = "insert into Nightly_Table (Subject, PersonAddedOn, NightDate, ComputerName, LoggedBy, AllNights) VALUES (?,?,?,?,?,?)"
MesIns.Parameters.Append MesIns.CreateParameter("@Subject", 200, 1, 255, strSubject)
MesIns.Parameters.Append MesIns.CreateParameter("@PersonAddedOn", 200, 1, 255, strPersonAddedOn)
MesIns.Parameters.Append MesIns.CreateParameter("@NightDate", 200, 1, 255, strNightDate)
MesIns.Parameters.Append MesIns.CreateParameter("@ComputerName", 200, 1, 255, strComputerName)
MesIns.Parameters.Append MesIns.CreateParameter("@LoggedBy", 200, 1, 255, strLoggedBy)
MesIns.Parameters.Append MesIns.CreateParameter("@AllNights", 200, 1, 255, strAllNights)
MesIns.Execute

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
ASP/vb does not like string parameters with no value (empty string).
For each one, try putting in a single space:

if strSubject="" then strSubject = " "
MesIns.Parameters.Append MesIns.CreateParameter("@Subject", 200, 1, 255, strSubject)

Same for the others.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
The other option is to detect '' and swap for null

Dim v as Variant
if strSubject="" then v = null else v = strSubject
MesIns.Parameters.Append MesIns.CreateParameter("@Subject", 200, 1, 255, v)

or directly

if strSubject="" then
MesIns.Parameters.Append MesIns.CreateParameter("@Subject", 200, 1, 255, null)
else
MesIns.Parameters.Append MesIns.CreateParameter("@Subject", 200, 1, 255, strSubject)
end if

Author

Commented:
Now I can submit it, but it just inserts empty lines in the database
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Do these have any values at all?

strSubject = request.Form("Subject")
strNightDate = request.Form("NightDate")
strPersonAddedOn = request.Form("PersonAddedOn")
strComputerName = request.Form("ComputerName")
strLoggedBy = request.Form("LoggedBy")
strAllNights = request.Form("AllNights")

what about adding these lines:

strSubject = request.Form("Subject")
Response.Write "DEBUG: strSubject is " & strSubject & "<br>"
strNightDate = request.Form("NightDate")
Response.Write "DEBUG: strNightDate is " & strNightDate & "<br>"

etc

Author

Commented:
I rewrote the whole thing and now I get this error message:
DEBUG: strFltDate is
DEBUG: strSubject is
DEBUG: strPersonAddedOn is
DEBUG: strComputerName is
DEBUG: strLoggedBy is
DEBUG: strAllFlights is

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/ato/NOCC/Pushback/Nightly_Broadcast_send.asp, line 40

strFltDate = request.Form("FltDate")
Response.Write "DEBUG: strFltDate is " & strFltDate & "<br>"
strSubject = request.Form("Subject")
Response.Write "DEBUG: strSubject is " & strSubject & "<br>"
strPersonAddedOn = request.Form("PersonAddedOn")
Response.Write "DEBUG: strPersonAddedOn is " & strPersonAddedOn & "<br>"
strComputerName = request.Form("ComputerName")
Response.Write "DEBUG: strComputerName is " & strComputerName & "<br>"
strLoggedBy = request.Form("LoggedBy")
Response.Write "DEBUG: strLoggedBy is " & strLoggedBy & "<br>"
strAllFlights = request.Form("AllFlights")
Response.Write "DEBUG: strAllFlights is " & strAllFlights & "<br>"




'create connection object
Set MesIns=CreateObject("ADODB.Command")
MesIns.ActiveConnection=objConn
MesIns.commandtext = "insert into Nightly_Table (Subject, FltDate, PersonAddedOn, ComputerName, LoggedBy, AllFlights) VALUES (?,?,?,?,?,?)"
if strSubject="" then strSubject = " "
MesIns.Parameters.Append MesIns.CreateParameter("@Subject", 200, 1, 255, strSubject)
if strFltDate="" then strFltDate = " "
MesIns.Parameters.Append MesIns.CreateParameter("@FltDate", 200, 1, 255, strFltDate)
if strPersonAddedOn="" then strPersonAddedOn = " "
MesIns.Parameters.Append MesIns.CreateParameter("@PersonAddedOn", 200, 1, 255, strPersonAddedOn)
if strComputerName="" then strComputerName = " "
MesIns.Parameters.Append MesIns.CreateParameter("@ComputerName", 200, 1, 255, strComputerName)
if strLoggedBy="" then strLoggedBy = " "
MesIns.Parameters.Append MesIns.CreateParameter("@LoggedBy", 200, 1, 255, strLoggedBy)
if strAllFlights="" then strAllFlights = " "
MesIns.Parameters.Append MesIns.CreateParameter("@AllFlights", 200, 1, 255, strAllFlights)
MesIns.Execute

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010
Commented:
DEBUG: strFltDate is
DEBUG: strSubject is
DEBUG: strPersonAddedOn is
DEBUG: strComputerName is
DEBUG: strLoggedBy is
DEBUG: strAllFlights is

That tells me that nothing was posted to the form.
In your HTML, is it FORM method=POST or method=GET?
For get, it is Request.QueryString not Request.Form

Author

Commented:
OH!!!!!
This was the problem....
Thank you very much. I changed it to post and now it's working fine. I'm learning the hard way.
I'm am really grateful. Thanks for your time and your patience with a dummy.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial