Passing 2 paramter to store procedure ???

20 Jan 2006 12:45 PM

serge calderara

Dear all,

What is the way to pass 2 input parameters to a store procedure ?
I have donme the following, but got an error that @Start was expected but
not provide ???

===============
m_SqlParam = New SqlClient.SqlParameter("@Start", SqlDbType.DateTime)
m_SqlParam.Value = CType(sStart, DateTime)
m_SqlParam.Direction = ParameterDirection.Input
m_objSqlCmd.Parameters.Add(m_SqlParam)

' define second sqlprocedure paramter
m_SqlParam = New SqlClient.SqlParameter("@End", SqlDbType.DateTime)
m_SqlParam.Value = CType(sEnd, DateTime)
m_SqlParam.Direction = ParameterDirection.Input
m_objSqlCmd.Parameters.Add(m_SqlParam)

What could be wrong ???

regards
serge

20 Jan 2006 12:59 PM

W.G. Ryan - MVP

Serge - that looks ok but first, double check the
m_objSqlCmd.Parameters.Count and verify that you have two. The only thing
that looks like it might be causing a problem is reinstantiating the same
parameter object although that should work. If you don't have 2 params in
the collection than it's probably the latter, otherwise it's probably a
typo - double check the names of the params in the proc vs your code.
Show quote

"serge calderara" <sergecalder***@discussions.microsoft.com> wrote in
message news:49D6D759-1091-4856-9229-88B352F25020@microsoft.com...
> Dear all,
>
> What is the way to pass 2 input parameters to a store procedure ?
> I have donme the following, but got an error that @Start was expected but
> not provide ???
>
> ===============
> m_SqlParam = New SqlClient.SqlParameter("@Start", SqlDbType.DateTime)
> m_SqlParam.Value = CType(sStart, DateTime)
> m_SqlParam.Direction = ParameterDirection.Input
> m_objSqlCmd.Parameters.Add(m_SqlParam)
>
> ' define second sqlprocedure paramter
> m_SqlParam = New SqlClient.SqlParameter("@End", SqlDbType.DateTime)
> m_SqlParam.Value = CType(sEnd, DateTime)
> m_SqlParam.Direction = ParameterDirection.Input
> m_objSqlCmd.Parameters.Add(m_SqlParam)
>
> What could be wrong ???
>
> regards
> serge

20 Jan 2006 1:19 PM

Ranjan Sakalley

Hello serge,

I copied your code and ran it, and its working fine ( just created a sample
SP that takes in these two parameters.

It may be that you are clearing this sql parameter collection somewhere.
I think you can just open Profiler.exe, see whats the query that is passed
to the SQL Server. Otherwise, you can run a check before executing, by iterating
through the parameter collection.

There is nothing wrong with the code that you have posted.

HTH,
r.

Show quote

> Dear all,
>
> What is the way to pass 2 input parameters to a store procedure ?
> I have donme the following, but got an error that @Start was expected
> but
> not provide ???
> ===============
> m_SqlParam = New SqlClient.SqlParameter("@Start", SqlDbType.DateTime)
> m_SqlParam.Value = CType(sStart, DateTime)
> m_SqlParam.Direction = ParameterDirection.Input
> m_objSqlCmd.Parameters.Add(m_SqlParam)
> ' define second sqlprocedure paramter
> m_SqlParam = New SqlClient.SqlParameter("@End", SqlDbType.DateTime)
> m_SqlParam.Value = CType(sEnd, DateTime)
> m_SqlParam.Direction = ParameterDirection.Input
> m_objSqlCmd.Parameters.Add(m_SqlParam)
> What could be wrong ???
>
> regards
> serge

20 Jan 2006 2:37 PM

Cor Ligthert [MVP]

Serge,

As thirth solution.

Are you sure that you are not newly instancing the parametercollectin
m_objSQLCmdParameters, I get the idea that you have this in a procedure
where you provide that new collection.

Or return a new collection by instance through the way you reference it.

Cor

21 Jan 2006 3:11 PM

serge calderara

got it. I was effectively create a new instance of parmater colection
I remove this and it work now.

But what is strange is that event by creating this new parameter instance,
it waas assiciated to the same store procedure and when I was looking with
debug my parmaters list I get the two desired parameter prooerly enter.

thnaks

Show quote

"Cor Ligthert [MVP]" wrote:

> Serge,
>
> As thirth solution.
>
> Are you sure that you are not newly instancing the parametercollectin
> m_objSQLCmdParameters, I get the idea that you have this in a procedure
> where you provide that new collection.
>
> Or return a new collection by instance through the way you reference it.
>
> Cor
>
>
>

21 Jan 2006 6:50 PM

William (Bill) Vaughn

Ah, you're making this a lot harder than it has to be. Try using the Add
constructor:

m_objSqlCmd.Parameters.Add("@Start",SqlDbType.DateTime).Value =
CType(sStart, DateTime)
m_objSqlCmd.Parameters.Add("@End",SqlDbType.DateTime).Value =
CType(sEnd, DateTime)

This is faster and less likely to cause object collisions. I expect your
syntax overlaid the existing m_SqlParam with "@End".

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Show quote

"serge calderara" <sergecalder***@discussions.microsoft.com> wrote in
message news:49D6D759-1091-4856-9229-88B352F25020@microsoft.com...
> Dear all,
>
> What is the way to pass 2 input parameters to a store procedure ?
> I have donme the following, but got an error that @Start was expected but
> not provide ???
>
> ===============
> m_SqlParam = New SqlClient.SqlParameter("@Start", SqlDbType.DateTime)
> m_SqlParam.Value = CType(sStart, DateTime)
> m_SqlParam.Direction = ParameterDirection.Input
>
> ' define second sqlprocedure paramter
> m_SqlParam = New SqlClient.SqlParameter("@End", SqlDbType.DateTime)
> m_SqlParam.Value = CType(sEnd, DateTime)
> m_SqlParam.Direction = ParameterDirection.Input
> m_objSqlCmd.Parameters.Add(m_SqlParam)
>
> What could be wrong ???
>
> regards
> serge

23 Jan 2006 8:33 AM

serge calderara

Thnaks for your advise wiliam

Show quote

"William (Bill) Vaughn" wrote:

> Ah, you're making this a lot harder than it has to be. Try using the Add
> constructor:
>
>      m_objSqlCmd.Parameters.Add("@Start",SqlDbType.DateTime).Value =
> CType(sStart, DateTime)
>      m_objSqlCmd.Parameters.Add("@End",SqlDbType.DateTime).Value =
> CType(sEnd, DateTime)
>
> This is faster and less likely to cause object collisions. I expect your
> syntax overlaid the existing m_SqlParam with "@End".
>
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
> "serge calderara" <sergecalder***@discussions.microsoft.com> wrote in
> message news:49D6D759-1091-4856-9229-88B352F25020@microsoft.com...
> > Dear all,
> >
> > What is the way to pass 2 input parameters to a store procedure ?
> > I have donme the following, but got an error that @Start was expected but
> > not provide ???
> >
> > ===============
> > m_SqlParam = New SqlClient.SqlParameter("@Start", SqlDbType.DateTime)
> > m_SqlParam.Value = CType(sStart, DateTime)
> > m_SqlParam.Direction = ParameterDirection.Input
> >
> >        ' define second sqlprocedure paramter
> > m_SqlParam = New SqlClient.SqlParameter("@End", SqlDbType.DateTime)
> > m_SqlParam.Value = CType(sEnd, DateTime)
> > m_SqlParam.Direction = ParameterDirection.Input
> > m_objSqlCmd.Parameters.Add(m_SqlParam)
> >
> > What could be wrong ???
> >
> > regards
> > serge
>
>
>