Parameterized query :(

[madiaz91911]

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!

 

[gmmastros]

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

 

[madiaz91911]

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.

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

 

[gmmastros]

Try this:

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

 

[madiaz91911]

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?