Solved

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

Posted on 2009-07-14
28
649 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 10
  • 3
28 Comments
 
LVL 60

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 60

Expert Comment

by:Kevin Cross
ID: 24855027
0
 
LVL 31

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

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 60

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 60

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 60

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 31

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 60

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 31

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 60

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 60

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 31

Author Comment

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

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 31

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 60

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 60

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 31

Author Comment

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

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 60

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 60

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 31

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 60

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 31

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 60

Expert Comment

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

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

695 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