Solved

(datetime) SQL SErver 2005  Parameter object is improperly defined.

Posted on 2009-07-14
28
646 Views
Last Modified: 2012-05-07
Hello All;

Ran into an issue, need some advice.

Here is my code
======================
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", addatetime, adParamInput, , strMyBirth)
======================
Here is my Error
======================
ADODB.Parameters error '800a0e7c'
Parameter object is improperly defined. Inconsistent or incomplete information was provided.
RegForm.asp, line 121
======================

I am passing    02/19/1971   mm/dd/yyyy

This is for an Update Statement.

Thank All.
Carrzkiss
0
Comment
Question by:Wayne Barron
  • 15
  • 10
  • 3
28 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855013
You have a missing argument:
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", addatetime, adParamInput, {you don't have anything typed in here}, strMyBirth)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855027
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24855035
what will go there?
for a datetime, everything that I have seen, does not show nothing added in that area
Since it is a datetime field.??

I have ready been to that link, and it is not telling you what needs to be put in the [size]
Section of a   addatetime
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 30

Author Comment

by:Wayne Barron
ID: 24855053
I put the following values, and still the same error
0
8

And nothing, still the same error
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855057
I posted the link to the create parameter information as it has been a while since I looked at ASP code with ADO, but adDateTime may not be valid.  Reference indicates adDate.  You may want to pass 'Nothing' for the fourth parameter (size) or you can pass just the first three, then set the value after the fact.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855068
Try like this:
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", adDate, adParamInput, Nothing, strMyBirth)

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855089
And actually, the empty param should be fine, so probably the data type as I showed changing to adDate or parameter name.
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", adDate, adParamInput, , strMyBirth)
 
'or like this:
 
NewMem.Parameters.Append NewMem.CreateParameter("[MyBirth]", adDate, adParamInput, , strMyBirth)

Open in new window

0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24855093
ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.
RegForm.asp, line 147

The field is a  datetype = datetime
So, that is not going to work, have already tried all different dates and nothing works.
Either the error above.
(or)
The error that the question is about.

Everything worked fine, until I added in the Parameters
Then it broke, here on this line
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855141
Do you mind posting the remainder of the code showing the parametized query and will try to duplicate and fix locally.
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24855182
ok
I found this
'Const adDBTimeStamp = 135  ' this was my original, and I changed it to the bottom. as I found online that it was the bottom one???

Const adDateTime = 135

But this change, gives me this error
============
ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.
RegForm.asp, line 147
============
<%
strMyFN = trim(Request.Form("MyFN"))
strMyLN = trim(Request.Form("MyLN"))
strUsername =trim( Request.Form("Username"))
strPassword = trim(Request.Form("Password"))
strPassword1 = trim(request.Form("Password1"))
strMyEmail = trim(Request.Form("MyEmail"))
strMyBirth = Request.Form("MyBirth")
strMyAddress = trim(Request.Form("MyAddress"))
strMyCity =trim( Request.Form("MyCity"))
strMyState = trim(Request.Form("MyState"))
strMyZip = trim(Request.Form("MyZip"))
strMyId = Int(Request.Form("MYID"))
strMyPh	= trim(Request.Form("MyPh"))
strMyCp = trim(Request.Form("MyCp"))
strMyHt = Int(Request.Form("MyHt"))
strMyFrame = trim(Request.Form("MyFrame"))
strMyGr = trim(Request.Form("MyGr"))
strMyWt = trim(Request.Form("MyWt"))
strPrivate = trim(Request.Form("Private"))
strOrgID = Int(request.Form("OrgID"))
strOrgPayment = trim(Request.Form("OrgPayment"))
 
Set NewMem=Server.CreateObject("ADODB.Command")
NewMem.ActiveConnection=objConn
MemberSQLUpdate = "UPDATE Members SET "&_
" [MyFN] = ?, "&_
" [MyLN] = ?, "&_
" [UserName] = ?, "&_
" [Password] = ?, "&_
" [Email] = ?, "&_
" [MyBirth] = ?, "&_
" [MyAdd] = ?, "&_
" [MyCity] = ?, "&_
" [MyState] = ?, "&_
" [MyZc] = ?, "&_
" [MyCp] = ?, "&_
" [MyPh] = ?, "&_
" [MyHt] =?, "&_
" [MyFrame] = ?, "&_
" [MyGr] = ?, "&_
" [Private] = ?, "&_
" [Payment] = ? "&_
" WHERE [Id]=?"
'response.Write MemberSQLUpdate
'response.End()
NewMem.Parameters.Append NewMem.CreateParameter("@MyFN", adVarChar, adParamInput, 255, strMyFN)
NewMem.Parameters.Append NewMem.CreateParameter("@MyLN", adVarChar, adParamInput, 255, strMyLN)
NewMem.Parameters.Append NewMem.CreateParameter("@UserName", adVarChar, adParamInput, 25, strUsername)
NewMem.Parameters.Append NewMem.CreateParameter("@Password", adVarChar, adParamInput, 25, strPassword)
NewMem.Parameters.Append NewMem.CreateParameter("@Email", adVarChar, adParamInput, 50, strMyEmail)
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", adDateTime, adParamInput, , strMyBirth)
NewMem.Parameters.Append NewMem.CreateParameter("@MyAdd", adVarChar, adParamInput, 255, strMyAddress)
NewMem.Parameters.Append NewMem.CreateParameter("@MyCity", adVarChar, adParamInput, 255, strMyCity)
NewMem.Parameters.Append NewMem.CreateParameter("@MyState", adVarChar, adParamInput, 3, strMyState)
NewMem.Parameters.Append NewMem.CreateParameter("@MyZc", adVarChar, adParamInput, 10, strMyZip)
NewMem.Parameters.Append NewMem.CreateParameter("@MyCp", adVarChar, adParamInput, 15, strMyCp)
NewMem.Parameters.Append NewMem.CreateParameter("@MyPh", adVarChar, adParamInput, 15, strMyPh)
NewMem.Parameters.Append NewMem.CreateParameter("@MyHt", adInteger, adParamInput, , strMyHt)
NewMem.Parameters.Append NewMem.CreateParameter("@MyFrame", adVarChar, adParamInput, 10, strMyFrame)
NewMem.Parameters.Append NewMem.CreateParameter("@MyGr", adVarChar, adParamInput, 6, strMyGr)
NewMem.Parameters.Append NewMem.CreateParameter("@Private", adVarChar, adParamInput, 10, strPrivate)
NewMem.Parameters.Append NewMem.CreateParameter("@Payment", adVarChar, adParamInput, 25, strOrgPayment)
NewMem.Parameters.Append NewMem.CreateParameter("@Id", adInteger, adParamInput, , strMyId)
NewMem.CommandText=MemberSQLUpdate
NewMem.Execute
%>

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855202
Try wrapping using CDate(strMyBirth).
<%
strMyFN = trim(Request.Form("MyFN"))
strMyLN = trim(Request.Form("MyLN"))
strUsername =trim( Request.Form("Username"))
strPassword = trim(Request.Form("Password"))
strPassword1 = trim(request.Form("Password1"))
strMyEmail = trim(Request.Form("MyEmail"))
strMyBirth = Request.Form("MyBirth")
strMyAddress = trim(Request.Form("MyAddress"))
strMyCity =trim( Request.Form("MyCity"))
strMyState = trim(Request.Form("MyState"))
strMyZip = trim(Request.Form("MyZip"))
strMyId = Int(Request.Form("MYID"))
strMyPh = trim(Request.Form("MyPh"))
strMyCp = trim(Request.Form("MyCp"))
strMyHt = Int(Request.Form("MyHt"))
strMyFrame = trim(Request.Form("MyFrame"))
strMyGr = trim(Request.Form("MyGr"))
strMyWt = trim(Request.Form("MyWt"))
strPrivate = trim(Request.Form("Private"))
strOrgID = Int(request.Form("OrgID"))
strOrgPayment = trim(Request.Form("OrgPayment"))
 
Set NewMem=Server.CreateObject("ADODB.Command")
NewMem.ActiveConnection=objConn
MemberSQLUpdate = "UPDATE Members SET "&_
" [MyFN] = ?, "&_
" [MyLN] = ?, "&_
" [UserName] = ?, "&_
" [Password] = ?, "&_
" [Email] = ?, "&_
" [MyBirth] = ?, "&_
" [MyAdd] = ?, "&_
" [MyCity] = ?, "&_
" [MyState] = ?, "&_
" [MyZc] = ?, "&_
" [MyCp] = ?, "&_
" [MyPh] = ?, "&_
" [MyHt] =?, "&_
" [MyFrame] = ?, "&_
" [MyGr] = ?, "&_
" [Private] = ?, "&_
" [Payment] = ? "&_
" WHERE [Id]=?"
'response.Write MemberSQLUpdate
'response.End()
NewMem.Parameters.Append NewMem.CreateParameter("@MyFN", adVarChar, adParamInput, 255, strMyFN)
NewMem.Parameters.Append NewMem.CreateParameter("@MyLN", adVarChar, adParamInput, 255, strMyLN)
NewMem.Parameters.Append NewMem.CreateParameter("@UserName", adVarChar, adParamInput, 25, strUsername)
NewMem.Parameters.Append NewMem.CreateParameter("@Password", adVarChar, adParamInput, 25, strPassword)
NewMem.Parameters.Append NewMem.CreateParameter("@Email", adVarChar, adParamInput, 50, strMyEmail)
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", adDateTime, adParamInput, , CDate(strMyBirth))
NewMem.Parameters.Append NewMem.CreateParameter("@MyAdd", adVarChar, adParamInput, 255, strMyAddress)
NewMem.Parameters.Append NewMem.CreateParameter("@MyCity", adVarChar, adParamInput, 255, strMyCity)
NewMem.Parameters.Append NewMem.CreateParameter("@MyState", adVarChar, adParamInput, 3, strMyState)
NewMem.Parameters.Append NewMem.CreateParameter("@MyZc", adVarChar, adParamInput, 10, strMyZip)
NewMem.Parameters.Append NewMem.CreateParameter("@MyCp", adVarChar, adParamInput, 15, strMyCp)
NewMem.Parameters.Append NewMem.CreateParameter("@MyPh", adVarChar, adParamInput, 15, strMyPh)
NewMem.Parameters.Append NewMem.CreateParameter("@MyHt", adInteger, adParamInput, , strMyHt)
NewMem.Parameters.Append NewMem.CreateParameter("@MyFrame", adVarChar, adParamInput, 10, strMyFrame)
NewMem.Parameters.Append NewMem.CreateParameter("@MyGr", adVarChar, adParamInput, 6, strMyGr)
NewMem.Parameters.Append NewMem.CreateParameter("@Private", adVarChar, adParamInput, 10, strPrivate)
NewMem.Parameters.Append NewMem.CreateParameter("@Payment", adVarChar, adParamInput, 25, strOrgPayment)
NewMem.Parameters.Append NewMem.CreateParameter("@Id", adInteger, adParamInput, , strMyId)
NewMem.CommandText=MemberSQLUpdate
NewMem.Execute
%>

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855206
(And I apologize I jumped in thinking I remembered correctly, but obviously haven't used ASP in a while that I have to think through what the issue is -- if we don't solve soon, you can request attention and see if this is fresh in the mind of another expert)
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24855268
>>Type mismatch: 'CDate'
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855329
Yeah, couldn't remember if it wanted object of date type.  Think my original thought would have worked, but again you can try to draw attention of other experts who may spot what I am missing.
<%
Const adDate = 7
 
strMyFN = trim(Request.Form("MyFN"))
strMyLN = trim(Request.Form("MyLN"))
strUsername =trim( Request.Form("Username"))
strPassword = trim(Request.Form("Password"))
strPassword1 = trim(request.Form("Password1"))
strMyEmail = trim(Request.Form("MyEmail"))
strMyBirth = Request.Form("MyBirth")
strMyAddress = trim(Request.Form("MyAddress"))
strMyCity =trim( Request.Form("MyCity"))
strMyState = trim(Request.Form("MyState"))
strMyZip = trim(Request.Form("MyZip"))
strMyId = Int(Request.Form("MYID"))
strMyPh = trim(Request.Form("MyPh"))
strMyCp = trim(Request.Form("MyCp"))
strMyHt = Int(Request.Form("MyHt"))
strMyFrame = trim(Request.Form("MyFrame"))
strMyGr = trim(Request.Form("MyGr"))
strMyWt = trim(Request.Form("MyWt"))
strPrivate = trim(Request.Form("Private"))
strOrgID = Int(request.Form("OrgID"))
strOrgPayment = trim(Request.Form("OrgPayment"))
 
Set NewMem=Server.CreateObject("ADODB.Command")
NewMem.ActiveConnection=objConn
MemberSQLUpdate = "UPDATE Members SET "&_
" [MyFN] = ?, "&_
" [MyLN] = ?, "&_
" [UserName] = ?, "&_
" [Password] = ?, "&_
" [Email] = ?, "&_
" [MyBirth] = ?, "&_
" [MyAdd] = ?, "&_
" [MyCity] = ?, "&_
" [MyState] = ?, "&_
" [MyZc] = ?, "&_
" [MyCp] = ?, "&_
" [MyPh] = ?, "&_
" [MyHt] =?, "&_
" [MyFrame] = ?, "&_
" [MyGr] = ?, "&_
" [Private] = ?, "&_
" [Payment] = ? "&_
" WHERE [Id]=?"
'response.Write MemberSQLUpdate
'response.End()
NewMem.Parameters.Append NewMem.CreateParameter("@MyFN", adVarChar, adParamInput, 255, strMyFN)
NewMem.Parameters.Append NewMem.CreateParameter("@MyLN", adVarChar, adParamInput, 255, strMyLN)
NewMem.Parameters.Append NewMem.CreateParameter("@UserName", adVarChar, adParamInput, 25, strUsername)
NewMem.Parameters.Append NewMem.CreateParameter("@Password", adVarChar, adParamInput, 25, strPassword)
NewMem.Parameters.Append NewMem.CreateParameter("@Email", adVarChar, adParamInput, 50, strMyEmail)
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", adDate, adParamInput, , strMyBirth)
NewMem.Parameters.Append NewMem.CreateParameter("@MyAdd", adVarChar, adParamInput, 255, strMyAddress)
NewMem.Parameters.Append NewMem.CreateParameter("@MyCity", adVarChar, adParamInput, 255, strMyCity)
NewMem.Parameters.Append NewMem.CreateParameter("@MyState", adVarChar, adParamInput, 3, strMyState)
NewMem.Parameters.Append NewMem.CreateParameter("@MyZc", adVarChar, adParamInput, 10, strMyZip)
NewMem.Parameters.Append NewMem.CreateParameter("@MyCp", adVarChar, adParamInput, 15, strMyCp)
NewMem.Parameters.Append NewMem.CreateParameter("@MyPh", adVarChar, adParamInput, 15, strMyPh)
NewMem.Parameters.Append NewMem.CreateParameter("@MyHt", adInteger, adParamInput, , strMyHt)
NewMem.Parameters.Append NewMem.CreateParameter("@MyFrame", adVarChar, adParamInput, 10, strMyFrame)
NewMem.Parameters.Append NewMem.CreateParameter("@MyGr", adVarChar, adParamInput, 6, strMyGr)
NewMem.Parameters.Append NewMem.CreateParameter("@Private", adVarChar, adParamInput, 10, strPrivate)
NewMem.Parameters.Append NewMem.CreateParameter("@Payment", adVarChar, adParamInput, 25, strOrgPayment)
NewMem.Parameters.Append NewMem.CreateParameter("@Id", adInteger, adParamInput, , strMyId)
NewMem.CommandText=MemberSQLUpdate
NewMem.Execute
%>

Open in new window

0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24855358
I found this link
http://episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/143001520831

Which points out this: (Same as you have mentioned here)
Datetime..........adDate
BUT
When I change it, I get this:
I still get this error

=====================
ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.
RegForm.asp, line 146
=====================

I can change it to the adMoon,, and still get the same blasted error probably.
Going to shot this code in a minute.

Site needs to go LIVE by in the morning, and wouldn't you know it.
It is always when you get ready to release them, that they go bad.

Carrzkiss
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 24855382
Try putting in literal value of 7 or correct one for adDate directly in createParameter and see if works then.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855395
Try putting in literal value of 7 or correct one for adDate directly in createParameter and see if works then.
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24855403
already tried that one.
Gives the same error.
0
 
LVL 30

Accepted Solution

by:
Wayne Barron earned 0 total points
ID: 24855458
You DO NOT want to know what the problem was....
ARRRRRRRRRRG (I feel like Charlie Brown right now)

OK.
In my Form, it was inserting the (Email) address's into the (Bithdate) field.
And it was hidden, so everytime that it would try to insert the value into the database.
Of course, it would not.
Because, it was a string & date
So.

There you go.
An annoying situation, that took the better of 3 hrs to find out that it was a simple
Typo that I did when I wrote the registration / proof page about 2 months ago.

Thank you for your help mwvisa1:
After I found that problem, I test it with
adDate
&
adDateTime
And they both work as expected.

(Going to [Accept this as Answer], and give you the points, for taking the time to try and help.)

Take Care
Carrzkiss
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24855514
Actually the correct ADO constant is adDBTimeStamp see here for a complete list:
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855642
Glad you found that.

Happy coding!

Best regards,
Kevin

P.S. @acperkins, that is what stuck out to me to, but Carrzkiss is defining const value 135 (which is adDBTimeStamp) to variable addatetime.  That was throwing me off too, but the int being used is correct.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24855896
>> Carrzkiss is defining const value 135 (which is adDBTimeStamp) to variable addatetime. <<
I saw that.  But then I had previously recommended the author use ADOVBS.inc, which has the correct values ...
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24855937
Ah, I didn't see that.  Must have been in another question. :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24856029
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24856291
To AC
I am using the [ADOVBS.inc]
I downloaded it as soon as you recommended it prior.

My issue it turns out how nothing to do with rather or not it was correct or not.
It was the fact that it had a "string" involved within it, that was causing the issue.

I listened to what you stated AC.
And for this issue, i was "Testing" to see if the problem was within the code or within something
Else.

So.
Any more questions regarding the "test" that I did on this one?
@mwvisa1
>>>but Carrzkiss is defining const value 135 (which is adDBTimeStamp) to variable addatetime.
It was a TEST.
I stated that I was trying everything to see if I could find a problem.
How was that throwing you off, when I planly stated that I was trying what ever I could
To see if I could find the problem?

Sorry that everyone seems to have gotten confused.
Shouldn't be.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24857389
Sorry, carrzkiss.  I didn't mean that in anyway.  I just meant that I was focusing in on the fact that addatetime is not the actual name of the ADO constant why I was suggesting you change to something else and was missing the point early on.  You are fine.  I didn't realize there was another question in play and just letting AC know that you were indeed using the int const for adDBTimeStamp as I came to realize.

Not confused at all.
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 24857589
>>addatetime is not the actual name of the ADO constant
Of which I remarked on that here http:#a24855358

See, I am learning SQL, regardless of how fast other would LOVE for newbies to catch on.
It does not always work that way.

The only reason why I change to ADO constant to a   addatetime
Was that I did a search online about it, and found where someone had it listed in their ADOVBS.inc file
And there for I "thought" why not, give it a shot, I done tried everything else right?
So, I tried it. and until I found out what the real problem was, I was not sure if the
ADO was set correctly or not.
Until I found that link that I posted on here [ http:#a24855358 ]
That confirmed that I was doing it incorrectly, and that the inforamtion that I got from that other site
Was indeed wrong.
And your information was correct, SO, that made me start looking else were besides in the code I was in. And that is how I come up with the issue with the Email address inserting itself into the Birthday field.

@acperkins
About the other Thread,  that is the first time that I have seen your post on there.
I never went back onto it after it was resolved, as I have been way to busy trying to get this site done.
SO.
NOW I know that their is no   addatetime.
I did not before.

Do not think that I am not listening/reading what you write,
It is just that I never read what you wrote on the other Thread, because I never saw it.

You both have a good one.
I have a lot of work to do and a little time to do it in.

Carrzkiss
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24857634
Have a good one yourself, sir! :)
Regards,
Kevin
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

803 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