Passing 2 paramter to store procedure ???

[Guest]

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

 

[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.

 

[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.

 

[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

 

[Guest]

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

 

[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.
__________________________________

 

[Guest]

Thnaks for your advise wiliam

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.
__________________________________