Running Stored Procedure by Parameters

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

I wanna use stored procedure by parameters (2 inputs) and I unsure I do the
right coding.

Stored Procedure
------------------
create procedure Opening_Balance (@Julian_Date_From, @Julian_Date_To,) as

select illitm, sum(qty) as qty, sum(weight) as weight
from f4111
where ildgl <> 0
and ildgl <= @Julian_Date_From
and ildgl >= @Julian_Date_To
and ilmcu like '%620%'
and illitm = '010720'
group by illitm
go

ASP.Net 1.1
--------------
Dim constr As String = "server='SQLSVR';Database='JDE_ERP_ARCHIVAL'"
Dim sqlcon As System.Data.SqlClient.sqlconnection = New
System.Data.SqlClient.sqlconnection(constr)
Dim sqlcmd As System.Data.SqlClient.SqlCommand = New SqlCommand()
Dim sqlda As New system.Data.SqlClient.sqldataAdapter()
Dim prm As New system.Data.SqlClient.SqlParameter()
Dim prm1 As New system.Data.SqlClient.SqlParameter()

Try
sqlcmd = sqlcon.CreateCommand
sqlcmd.CommandText = "[Opening_Balance]"
sqlcmd.CommandType = commandtype.StoredProcedure

prm = cmd.parameters.Add("(@Julian_Date_From",SqlDbType.Int, 6)
prm1 = cmd.parameters.Add("(@Julian_Date_To",SqlDbType.Int, 6)

prm.value = 105101
prm1.value = 105131

sqlda.SelectCommand = sqlcmd
sqlcon.Open

sqlcmd.ExecuteNonQuery
Finally
sqlcon.Close()
sqlcon.Dispose()
End Try

constr = Nothing
sqlcon = Nothing
sqlcmd = Nothing
sqlda = Nothing

Please advise.

Many thanks.
 
My preference is creating the parameter first and then attaching it to the
command object (more explicit), but the general thrust of your code looks
fine for what you state you wish to do.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
I wrote a long article on callinig SPs from ADO.NET. See
www.betav.com/articles (handling gazintas)
I'll also be presenting a workshop on executing SPs at DevTeach on the 18th
(see my site for details).

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