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

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
LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
You have a missing argument:
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", addatetime, adParamInput, {you don't have anything typed in here}, strMyBirth)
0
Kevin CrossChief Technology OfficerCommented:
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Wayne BarronAuthor, Web DeveloperAuthor Commented:
I put the following values, and still the same error
0
8

And nothing, still the same error
0
Kevin CrossChief Technology OfficerCommented:
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
Kevin CrossChief Technology OfficerCommented:
Try like this:
NewMem.Parameters.Append NewMem.CreateParameter("@MyBirth", adDate, adParamInput, Nothing, strMyBirth)

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
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
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
Kevin CrossChief Technology OfficerCommented:
Do you mind posting the remainder of the code showing the parametized query and will try to duplicate and fix locally.
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
Kevin CrossChief Technology OfficerCommented:
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
Kevin CrossChief Technology OfficerCommented:
(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
Wayne BarronAuthor, Web DeveloperAuthor Commented:
>>Type mismatch: 'CDate'
0
Kevin CrossChief Technology OfficerCommented:
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
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
Kevin CrossChief Technology OfficerCommented:
Try putting in literal value of 7 or correct one for adDate directly in createParameter and see if works then.
0
Kevin CrossChief Technology OfficerCommented:
Try putting in literal value of 7 or correct one for adDate directly in createParameter and see if works then.
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
already tried that one.
Gives the same error.
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Actually the correct ADO constant is adDBTimeStamp see here for a complete list:
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
0
Kevin CrossChief Technology OfficerCommented:
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
Anthony PerkinsCommented:
>> 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
Kevin CrossChief Technology OfficerCommented:
Ah, I didn't see that.  Must have been in another question. :)
0
Anthony PerkinsCommented:
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
Kevin CrossChief Technology OfficerCommented:
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
Wayne BarronAuthor, Web DeveloperAuthor Commented:
>>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
Kevin CrossChief Technology OfficerCommented:
Have a good one yourself, sir! :)
Regards,
Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.