Solved

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

Posted on 2009-07-14
28
643 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
Comment Utility
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
Comment Utility
0
 
LVL 30

Author Comment

by:Wayne Barron
Comment Utility
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
 
LVL 30

Author Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
(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
Comment Utility
>>Type mismatch: 'CDate'
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 30

Author Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
already tried that one.
Gives the same error.
0
 
LVL 30

Accepted Solution

by:
Wayne Barron earned 0 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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
Comment Utility
Ah, I didn't see that.  Must have been in another question. :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
0
 
LVL 30

Author Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
Have a good one yourself, sir! :)
Regards,
Kevin
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now