Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameterized query :(

Status
Not open for further replies.

madiaz91911

Technical User
Dec 15, 2011
7
US
I really want to use parameterized queries in my web site. But sometimes, Id rather eat a can of worms than use these typoe queries.

Here is my latest nightmare.

##################################

date = 2/20/2013

cmdStoredProc.parameters.append cmdStoredProc.createparameter("@MDate",7,1,0,Date) ' no work

ERROR MESSAGE - Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

so changed date valued to : 20130320

ERROR MESSAGE - Application uses a value of the wrong type for the current operation.

#######################################

Date = 3/20/2013

cmdStoredProc.parameters.append cmdStoredProc.createparameter("@MDate",135,1,0,Date) ' no work

ERROR MESSAGE - Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

#################################

strDate=20130320

cmdStoredProc.parameters.append cmdStoredProc.createparameter("@MDate",133,1,0,Date) ' no work

ERROR MESSAGE - Application uses a value of the wrong type for the current operation.

################################

Iim so pissed I just want to spit!

 
Date is a reserved word in ASP classic. I suggest you try changing the variable name.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Yes, I understand that, thanks. I just used that to show you the date value in the format that is being passed to the parameter.

Just noticed that I enter "Date" in the parameter. I actually used "strDate" in my code. Then I changed the value of strDate to what I thought it reqired i.e., 20130320 vs 03/20/2013

so they actually looked like:

##################################

strDate= 2/20/2013

cmdStoredProc.parameters.append cmdStoredProc.createparameter("@MDate",7,1,0,strDate) ' no work

ERROR MESSAGE - Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

so changed date valued to : 20130320

ERROR MESSAGE - Application uses a value of the wrong type for the current operation.

#######################################

strDate= 3/20/2013

cmdStoredProc.parameters.append cmdStoredProc.createparameter("@MDate",135,1,0,strDate) ' no work

ERROR MESSAGE - Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

#################################

strDate=20130320

cmdStoredProc.parameters.append cmdStoredProc.createparameter("@MDate",133,1,0,strDate) ' no work

ERROR MESSAGE - Application uses a value of the wrong type for the current operation.

################################
 
Try this:

Code:
strDate = [!]#[/!]2/20/2013[!]#[/!]

One of the things I find annoying with classic ASP is that every variable is a variant. Weirder still... variants have an underlying data type.

By using the # symbol around your constant, this tells the compiler/interpreter that you are actually referring to a Date data type.

If this does not solve your problem, then please post the header part of the stored procedure you are calling. I'm really talking about the create procedure part as well as the parameters.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I'm using MS SQL Server. I been pulling my hair out on this one. I did come up with a soution, although it makes no sense to me.

I iterated through the input parameters in the same order that I created the stored procedure in. Example:

CREATE PROCEDURE NoneYaBusiness.sp_ModAssetManufactures (
@ID int,
@MBy int,
@Name nvarchar(50),
@Toggle varchar(3)
)
as
Update Asset_Manufactures set Manufacture=@Name, Enable=@Toggle, ModBy=@MBy, ModDate=GetDate() Where MID=@ID

Actually there were two solutions. First I just got rid of the data value all together and did it within the procedure.

BUT, my main fix, which I did with other stored procedures which were having the same issue.

Here you will see that the parameters where written in the same order as in the stored prcedure. If I changed them in a different order in my asp code, it would bomb??????

cmdStoredProc.parameters.append cmdStoredProc.createparameter("@ID",2,1,0,intID) cmdStoredProc.parameters.append cmdStoredProc.createparameter("@MBy",2,1,0,UserID)cmdStoredProc.parameters.append cmdStoredProc.createparameter("@Name",202,1,50,strType)
cmdStoredProc.parameters.append cmdStoredProc.createparameter("@Toggle",129,1,3,strEnable)

What the? Who the? Where the?


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top