Passing 2 paramter to store procedure ???

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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.
 
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
 
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
 
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.
__________________________________
 
Thnaks for your advise wiliam

William (Bill) Vaughn said:
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.
__________________________________
Click to expand...
 
Back
Top