Passing params to stored procedure--

Getting a bit confused on passsing params to a stored procedure. I have
created and verfied a query that accepts one paramter, a text field, in
MsAcces2000.
INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
VALUES (1, [GarmentType], 5,'torn');

When trying to call the procedure on the connection, I am getting an HTTP
500 internal server error.
I have tried delimiting the field with and without single quotes with no
luck

objConn.qryInsertOrderDetail shirt
objConn.qryInsertOrderDetail 'shir'

I am pretty sure it's a syntax thing, as I have changed the query to prompt
for number fields:
INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
VALUES ([OrderID], 'shirt', [Qty],'torn');

And called the query successfully with:

objConn.qryInsertOrderDetail 1,5

What am I overlooking? Thanx

G

Snippet below

<%
'SQL of query that is being called
'INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
'VALUES (1, [GarmentType], 5,'torn');

Dim ObjConn

Set ObjConn = Server.CreateObject("ADODB.Connection")
Response.Write "got this far"

'open the connection
objConn.Open strConn

'execute the query/stored procedure on the connection
objConn.qryInsertOrderDetail torn

'Close and clean up
objConn.Close
Set ObjConn = Nothing
%>

Re: Passing params to stored procedure--

Turn off "Show friendly HTTP errors" in Tools - Internet Options - Advanced
to see the real error message instead of the Status 500.

Have not used the 2.7 method much... have you tried it the old way?

Have not used Access much but how about...

objConn.Execute "EXEC qryInsertOrderDetail 'shirt'", , adCmdText +
adExecuteNoRecords

Maybe the EXEC word could be omitted. Maybe change adCmdText for
adCmdStoredProc.


"Gz" wrote in message
news:Oz1h9S0zEHA.2568@TK2MSFTNGP10.phx.gbl...
> Getting a bit confused on passsing params to a stored procedure. I have
> created and verfied a query that accepts one paramter, a text field, in
> MsAcces2000.
> INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
> VALUES (1, [GarmentType], 5,'torn');
>
> When trying to call the procedure on the connection, I am getting an HTTP
> 500 internal server error.
> I have tried delimiting the field with and without single quotes with no
> luck
>
> objConn.qryInsertOrderDetail shirt
> objConn.qryInsertOrderDetail 'shir'
>
> I am pretty sure it's a syntax thing, as I have changed the query to
prompt
> for number fields:
> INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
> VALUES ([OrderID], 'shirt', [Qty],'torn');
>
> And called the query successfully with:
>
> objConn.qryInsertOrderDetail 1,5
>
> What am I overlooking? Thanx
>
> G
>
> Snippet below
>
> <%
> 'SQL of query that is being called
> 'INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
> 'VALUES (1, [GarmentType], 5,'torn');
>
> Dim ObjConn
>
> Set ObjConn = Server.CreateObject("ADODB.Connection")
> Response.Write "got this far"
>
> 'open the connection
> objConn.Open strConn
>
> 'execute the query/stored procedure on the connection
> objConn.qryInsertOrderDetail torn
>
> 'Close and clean up
> objConn.Close
> Set ObjConn = Nothing
> %>
>
>

Re: Passing params to stored procedure--

Dave,
Thanks for the help. The method you suggested worked. I now pass 4
params to a function that called the query and passes the params.
Thanks!!!
Function InsertDetail(OID,GT,QTY,PROB)
Dim objConn
Dim StrCmd

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strconn

'build the string
strcmd = "qryInsertOrderDetail " & OID & ",'" & GT & "'," & QTY & ",'"
& PROB & "'"
'response.write strcmd

'execute the query/stored procedure on the connection
objConn.Execute strCmd, adcmdText + adExecuteNoREcords



'Close and clean up
objConn.Close
Set ObjConn = Nothing

end function



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Passing params to stored procedure--

Gz wrote:
> Getting a bit confused on passsing params to a stored procedure. I
> have created and verfied a query that accepts one paramter, a text
> field, in MsAcces2000.
> INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
> VALUES (1, [GarmentType], 5,'torn');
>
While you say that David's suggestion worked for you, that technique has
some problems which you can read about here:

http://tinyurl.com/jyy0


http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Passing params to stored procedure--

I am not sure the technique has any "problems", just the understanding of
how and when it should be used.

In Gz's example, he was using a string constant "shirt", not parameter
values or using a variable to contain the SQL statement.

He did not want any output or return values.




"Bob Barrows [MVP]" wrote in message
news:uFSsJW9zEHA.1264@TK2MSFTNGP12.phx.gbl...
> Gz wrote:
> > Getting a bit confused on passsing params to a stored procedure. I
> > have created and verfied a query that accepts one paramter, a text
> > field, in MsAcces2000.
> > INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
> > VALUES (1, [GarmentType], 5,'torn');
> >
> While you say that David's suggestion worked for you, that technique has
> some problems which you can read about here:
>
> http://tinyurl.com/jyy0
>
>
>
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Re: Passing params to stored procedure--

The problems I'm referring to include sql injection and incorrect handling
of delimiter characters, both of which are avoided by using a
non-dynamic-sql solution.

Bob Barrows

David Morgan wrote:
> I am not sure the technique has any "problems", just the
> understanding of
> how and when it should be used.
>
> In Gz's example, he was using a string constant "shirt", not parameter
> values or using a variable to contain the SQL statement.
>
> He did not want any output or return values.
>
>
>
>
> "Bob Barrows [MVP]" wrote in message
> news:uFSsJW9zEHA.1264@TK2MSFTNGP12.phx.gbl...
>> Gz wrote:
>>> Getting a bit confused on passsing params to a stored procedure. I
>>> have created and verfied a query that accepts one paramter, a text
>>> field, in MsAcces2000.
>>> INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
>>> VALUES (1, [GarmentType], 5,'torn');
>>>
>> While you say that David's suggestion worked for you, that technique
>> has
>> some problems which you can read about here:
>>
>> http://tinyurl.com/jyy0
>>
>>
>>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I
>> don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Passing params to stored procedure--

Agreed, but in his original post, (the one to which I was responding), he
was doing neither!

I'm being pedantic. Monday morning blues....

"While you say that David's suggestion worked for you, that technique has
some considerations which should be observed". I don't think the method has
any problems per se.


"Bob Barrows [MVP]" wrote in message
news:ukJ$ZeI0EHA.1296@TK2MSFTNGP10.phx.gbl...
> The problems I'm referring to include sql injection and incorrect handling
> of delimiter characters, both of which are avoided by using a
> non-dynamic-sql solution.
>
> Bob Barrows
>
> David Morgan wrote:
> > I am not sure the technique has any "problems", just the
> > understanding of
> > how and when it should be used.
> >
> > In Gz's example, he was using a string constant "shirt", not parameter
> > values or using a variable to contain the SQL statement.
> >
> > He did not want any output or return values.
> >
> >
> >
> >
> > "Bob Barrows [MVP]" wrote in message
> > news:uFSsJW9zEHA.1264@TK2MSFTNGP12.phx.gbl...
> >> Gz wrote:
> >>> Getting a bit confused on passsing params to a stored procedure. I
> >>> have created and verfied a query that accepts one paramter, a text
> >>> field, in MsAcces2000.
> >>> INSERT INTO tblOrderDetails ( OrderID, GarmentType, Qty, Problem )
> >>> VALUES (1, [GarmentType], 5,'torn');
> >>>
> >> While you say that David's suggestion worked for you, that technique
> >> has
> >> some problems which you can read about here:
> >>
> >> http://tinyurl.com/jyy0
> >>
> >>
> >>
> >
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN
> >>
> >> Bob Barrows
> >>
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I
> >> don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Re: Passing params to stored procedure--

David Morgan wrote:
> Agreed, but in his original post, (the one to which I was
> responding), he
> was doing neither!

I don't understand. How is what the OP was doing relevant to the potential
problems that could be faced by using the dynamic sql technique of calling a
stored procedure? I don't see the connection.

>
> I'm being pedantic. Monday morning blues....
>
> "While you say that David's suggestion worked for you, that technique
> has
> some considerations which should be observed". I don't think the
> method has
> any problems per se.

I pointed out the problems: the potential for sql injection by hackers and
the potential for the incorrect handling of delimiters. I left out the issue
of forcing SQL Server to create an execution plan (wasting a little cpu and
a little bit of the procedure cache) because this is a relatively minor
problem.

All three issues are avoided by not using dynamic sql to execute stored
procedures. A simple

objConn.qryInsertOrderDetail "shirt"

is both safer and more efficient than

objConn.Execute "EXEC qryInsertOrderDetail 'shirt'", , adCmdText +
adExecuteNoRecords


Plus it has the benefit of less typing :-)

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"