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 Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Stored procedure in ASP -Not an FAQ - F1 please 2

Status
Not open for further replies.

Deadline

Programmer
Feb 28, 2001
367
US
Hi,


The following is my SQL Server Stored procedure:
Code:
CREATE PROCEDURE [Proc_insert]
	(@UPI_1 	[int],
	 @DateFrom_2 	[char](12),
	 @DateTo_3 	[char](12),
	 @locationID_4 	[int],
	 @Status_5 	[char](15))

AS INSERT INTO [GSD_Test].[dbo].[SUSPENDED_PARKING] 
	 ( [UPI],
	 [DateFrom],
	 [DateTo],
	 [locationID],
	 [Status])  
VALUES 
	( @UPI_1,
	 CAST(@DateFrom_2 AS DATETIME),
	 CAST(@DateTo_3 AS DATETIME),
	 @locationID_4,
	 @Status_5)

The following is the ASP code that I am trying to execute, but in vain.
Code:
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")


Set cmd.ActiveConnection = cn
   ' Define the stored procedure's inputs and outputs
   ' Question marks act as placeholders for each parameter for the
   ' stored procedure
   cmd.CommandType = 4 'adCmdStoredProc 
   cmd.CommandText = "{call Proc_Insert_Suspended_parking(?,?,?,?,?)}"
   ' specify parameter info 1 by 1 in the order of the question marks
   ' specified when we defined the stored procedure
   'cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, adParamReturnValue)
      
   cmd.Parameters.Append cmd.CreateParameter( "UPI", adInteger,  adParamInput)
   cmd.Parameters.Append cmd.CreateParameter("DateFrom",  adVarChar , adParamInput,12)
   cmd.Parameters.Append cmd.CreateParameter("DateTo", adVarChar  , adParamInput,12)
   cmd.Parameters.Append cmd.CreateParameter("LocationID", adInteger, adParamInput)
   cmd.Parameters.Append cmd.CreateParameter("Status", adChar, adParamInput,15)
   
	
	
   cmd.Parameters("UPI").Value = Request.Cookies("UPI")
   cmd.Parameters("DateTo").Value =  "'" & cdate(Request.Form("txtToDate"))   & "'"
   cmd.Parameters("DateFrom").Value= "'" & CDate(Request.Form("FromMon") & "/" & Request.Form("FromYear"))  & "'"
   cmd.Parameters("LocationID").Value = Request.Form("selLocation")
   cmd.Parameters("Status").Value = "Suspended"

    Response.Write &quot;<HR>&quot;& cmd.Parameters(0).Name & &quot;*&quot; & cmd.Parameters(0).Value & &quot;<BR>&quot;
	Response.Write cmd.Parameters(1).Name & &quot;*&quot; & cmd.Parameters(1).Value & &quot;<BR>&quot;
	Response.Write cmd.Parameters(2).Name & &quot;*&quot; & cmd.Parameters(2).Value & &quot;<BR>&quot;
	Response.Write cmd.Parameters(3).Name & &quot;*&quot; & cmd.Parameters(3).Value & &quot;<BR>&quot;
	Response.Write cmd.Parameters(4).Name & &quot;*&quot; & cmd.Parameters(4).Value & &quot;<HR>&quot;
	'Response.Write cmd.Properties.length
	cmd.Execute  
   Response.Write  &quot;<HR>&quot; & err.Description & &quot;<HR>&quot;


I have ensured that none of the input parameters are blank or left out.

Why am I getting the following error ?
Code:
No value given for one or more required parameters

This error makes me sick, and I very badly need your assistance. Thank you...
RR

 
Hi RR,

I'm not a real experienced programmer but because of the error I looked to your 5 parameter values.

The datefrom looks a bit complicated to me.

Are you sure the syntax :
Code:
cmd.Parameters(&quot;DateFrom&quot;).Value= &quot;'&quot; & CDate(Request.Form(&quot;FromMon&quot;) & &quot;/&quot; & Request.Form(&quot;FromYear&quot;))  & &quot;'&quot;
is right? I thought it should be (changed two brackets):
Code:
cmd.Parameters(&quot;DateFrom&quot;).Value= &quot;'&quot; & CDate(Request.Form(&quot;FromMon&quot;)) & &quot;/&quot; & Request.Form(&quot;FromYear&quot;)  & &quot;'&quot;

You also can test a simple input like:
Code:
cmd.Parameters(&quot;DateFrom&quot;).Value= &quot;'&quot; & CDate(Request.Form(&quot;FromMon&quot;)) & &quot;'&quot;

Erik
 
Hi Eric,
I managed to make it work. Here's how:

I removed those single quotes before and after the date values.

Next, I added a CAST function to the Date Parameters in the STORED procedure; This is in order to convert the String being passed into Date.

Next, I removed the statement
Code:
cmd.CommandType = 4 'adCmdStoredProc

With the above statement, it never worked for me. I don't know the reason; I'd appreciate if you can tell me why.

Finally, I removed the opening and closing Curly braces in the ActiveCommand statement.

Here is the working code :
Code:
********Stored Procedure***********************
CREATE PROCEDURE [Proc_insert_SUSPENDED_PARKING]
	(@UPI_1 	[int],
	 @DateFrom_2 	[char](12),
	 @DateTo_3 	[char](12),
	 @locationID_4 	[int],
	 @Status_5 	[char](15))

AS INSERT INTO [GSD_Test].[dbo].[SUSPENDED_PARKING] 
	 ( [UPI],
	 [DateFrom],
	 [DateTo],
	 [locationID],
	 [Status])  
VALUES 
	( @UPI_1,
	 CAST(@DateFrom_2 AS DATETIME),
	 CAST(@DateTo_3 AS DATETIME),
	 @locationID_4,
	 @Status_5)

*******************The VB code snippet.

Dim cmd
Set cmd = Server.CreateObject(&quot;ADODB.Command&quot;)


Set cmd.ActiveConnection = cn
   ' Define the stored procedure's inputs and outputs
   ' Question marks act as placeholders for each parameter for the
   ' stored procedure   
   cmd.CommandText = &quot;{call Proc_Insert_Suspended_parking(?,?,?,?,?)}&quot;
   'cmd.CommandType = 4 'adCmdStoredProc 
   ' specify parameter info 1 by 1 in the order of the question marks
   ' specified when we defined the stored procedure
   'cmd.Parameters.Append cmd.CreateParameter(&quot;RetVal&quot;, adInteger, adParamReturnValue)
      
   cmd.Parameters.Append cmd.CreateParameter( &quot;UPI&quot;, adInteger,  adParamInput)
   cmd.Parameters.Append cmd.CreateParameter(&quot;DateFrom&quot;,  adVarChar , adParamInput,12)
   cmd.Parameters.Append cmd.CreateParameter(&quot;DateTo&quot;, adVarChar  , adParamInput,12)
   cmd.Parameters.Append cmd.CreateParameter(&quot;LocationID&quot;, adInteger, adParamInput)
cmd.Parameters.Append cmd.CreateParameter(&quot;Status&quot;, adChar, adParamInput,15)	
cmd.Parameters(&quot;UPI&quot;).Value = Request.Cookies(&quot;UPI&quot;)
cmd.Parameters(&quot;DateTo&quot;).Value =   cdate(Request.Form(&quot;txtToDate&quot;))  
cmd.Parameters(&quot;DateFrom&quot;).Value=  CDate(Request.Form(&quot;FromMon&quot;) & &quot;/&quot; & Request.Form(&quot;FromYear&quot;))
cmd.Parameters(&quot;LocationID&quot;).Value = Request.Form(&quot;selLocation&quot;)
cmd.Parameters(&quot;Status&quot;).Value = &quot;Suspended&quot;
cmd.Execute  
Response.Write  &quot;<HR>&quot; & err.Description & &quot;<HR>&quot;

Thank you very much Erik. Thank you...
RR

 
The reason it didn't work was because in your SP datefrom_2 and dateto_3 were both being passed in as Char(12), while in your database they are being stored as datetime (that's why you needed to explicitly cast them as datetime).

An easier to do it would be to change the def of those two params from char(12) to smalldatetime or datetime (depending on your needs), and take out the explicit cast.

Typically, you want to define the CommandType. The reason you want to do this is because it gives a greater speed increase. When you don't define the commandType , it defaults to adCmdUnknown (don't know the number :-(), which results in more work for the DBMS. The reason it does one more trip is because it goes to the DBMS to find out if it's a SP or just a SQL query or whatever, then uses those results for the parameters. If you explicitly define it, you save the DBMS a step, saving yourself runtime.

hope this helps
leo
 
Leo,
Thank you very much for your time.

Initially I was having a DateTime type for those two Date Parameters. When I tried to test the SP by using SQLServer QUery Analyzer, it said, it won't accept char value for a date parameter. I was attempting something like :

EXEC PROC_INSERT(1,'12/21/2001','12/20/2001',1,&quot;OK&quot;)

Since there weren't any type conversion function like CDATE, I decided to use CAST there in the SP itself. Please comment on what should be done to make it proper and straight ...

And I tried to execute the SP from VB with this same piece of code modified appropriately for VB. I REMOVED the comment on the line cmd.CommandType= adCMDSToredProcedure

You know what... When I traced the program, the moment it passes that line, I found that it adds two more question marks by itself APART from the 5 that I had there..

And where should I give the cmd.CommandType ? Before cmd.CommandText or After cmd.CommandText ?

Thank you once again for your time..







Thank you...
RR

 
you can set the commandType anywhere you'd like, as long as it's before .execute. Personally, I always set it one line before the CommandText, just because it helps keep everything routine.

ok - looking at your code a little further, your cmd text doesn't need to be set in that { call xxx(?,?)} manner. It should just hold the name of the stored proc.

ex:
cmd.CommandText = &quot;Proc_insert_SUSPENDED_PARKING&quot;

Also -
you don't need to explicitly cast the dates from the form. If you are passing them as dates or int, whatever, all you need to do is make sure the surrounding '' and correct formattting is there, if they should be.

Also - the date from should have a day value, so you don't err. any old date value will do.

ex:
cmd.Parameters(&quot;DateTo&quot;).Value = &quot;'&quot; & Request.Form(&quot;txtToDate&quot;) & &quot;'&quot;
cmd.Parameters(&quot;DateFrom&quot;).Value= &quot;'&quot; & Request.Form(&quot;FromMon&quot;) & &quot;/01/&quot; & Request.Form(&quot;FromYear&quot;) &quot;'&quot;

Pretty much though, it just seems like syntax errors, with a little more practice you'll be able to spot these out in a second :). Anyhow - remember, if you try changing the SQL SP parameter, you will need to change the reference from adChar.

I don't know why you couldn't execute your example in Query Anal.... I created a test table and sp, and mine executed fine. Try taking out the INTO. INTO is only necessary if you are taking the results from one table, and putting them into another.

ex:
insert into mytbl (col)
(select col2 from otherTbl)

hope this helps
leo
 
oh ok. I've never seen the method 3 format before, so i guess we all learn something new every day.

other than that - i really don't know what else the problem could be. sorry i couldn't be of more help

leo
 
Leo..
Thank you.. You already helped me enough. This shows your humbleness despite being next only to number 1 in the expert list.
:)

Best wishes.
Thank you...
RR

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top