how to execute this procedure in vb.net/c#

[siri]

Hi,I am doing a project where I am displaying the attendance of employees for a particular month.I want to know what method of command object should I use for this stored procedure ?Here is the code*******************create procedure demo@empid int,@firstday datetime,@noofdays int--@attreport varchar(30) outputasbegindeclare @count intdeclare @msg varchar(60)select @msg=' 'select @count=0while @count < @noofdaysbeginif exists(select * from attendancewhere datediff(d,attdate,dateadd(d,@count,@firstday))=0 )beginselect @msg =@msg + 'P'+','endelsebeginselect @msg = @msg + 'A'+','endselect @count =@count + 1endselect @msg--select @attreport=@msgend*********************Now I want to get the value of @msg in vb.net. So which method of Command Objectshould I use to get the value of @msg from my stored procedure ?Whether it is executescalar or executenonquery or any other method plz let me know with the code snippet

[aspnetguy]

C#

SqlConnection cnt = new SqlConnection("connection string here");SqlCommand cmd = new SqlCommand("demo",cnt);cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add("@empid",SqlDbType.Int);cmd.Parameters.Add("@firstday",SqlDbType.DateTime);cmd.Parameters.Add("@noofdays",SqlDbType.Int);cmd.Parameters["@empid"].Value = yourEmpIdVariable;cmd.Parameters["@firstday"].Value = firstDayVariable;cmd.Parameters["@noofdays"].Value = numberOfDaysVariable;cnt.Open();SqlDataReader dr = cmd.ExecuteReader();//do whatever else you want with resultscnt.Close();

[siri]

C#

SqlConnection cnt = new SqlConnection("connection string here");SqlCommand cmd = new SqlCommand("demo",cnt);cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add("@empid",SqlDbType.Int);cmd.Parameters.Add("@firstday",SqlDbType.DateTime);cmd.Parameters.Add("@noofdays",SqlDbType.Int);cmd.Parameters["@empid"].Value = yourEmpIdVariable;cmd.Parameters["@firstday"].Value = firstDayVariable;cmd.Parameters["@noofdays"].Value = numberOfDaysVariable;cnt.Open();SqlDataReader dr = cmd.ExecuteReader();//do whatever else you want with resultscnt.Close();

Thanks for the code snippet. Can you kindly let me know that instead of using cmd.ExecuteReader can I use cmd.ExecuteScalar since my procedure returns a single value.

[aspnetguy]

not sure. I have never run a SP like that. Give it a try and see.

[siri]

Hi,I tried with executeScalar. The program with execute scalar method as well. Below is the complete code which I am displaying the attendance detailson a datagridview. I hope will be really helpful to others as well.*****************************************************************************Public Class Form12 Inherits System.Windows.Forms.Form Dim con As New SqlConnection Private Function daysinmonth(ByVal dtp As DateTimePicker) As Integer Dim flag As Boolean = True Dim d1 As Date Dim days As Integer Dim str As String days = 31 str = dtp.Value.Month.ToString & "/31" & "/" & dtp.Value.Year.ToString Try d1 = DateTime.Parse(str) Catch ex As Exception flag = False Finally If flag = True Then days = 31 Else If DateTime.IsLeapYear(dtp.Value.Year) Then If dtp.Value.Month = 2 Then days = 29 Else days = 30 End If Else If dtp.Value.Month = 2 Then days = 28 Else days = 30 End If End If End If End Try Return days End Function Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim dr As DataRow Dim cmd As SqlCommand Dim ds As New DataSet Dim arr() As String Dim i As Integer Dim firstday As Date = DateTime.Parse(d1.Value.Month.ToString _ & "/01/" & d1.Value.Year.ToString) Dim style As DataGridViewCellStyle Dim d2, d3 As Date d2 = Now Dim t1 As TimeSpan Try Dim query As String query = "select empid,fname + ' ' + lname as EmpName,lanid, " _ & "processid,subprocessid from employee where " _ & "processid=12 and subprocessid=23 and dateofleaving is null" Dim ada As New SqlDataAdapter(query, con) ada.Fill(ds) dg.Columns.Add("Empid", "Empid") dg.Columns.Add("Ename", "Ename") dg.Columns(0).Width = 50 dg.Columns(1).Width = 90 For i = 1 To daysinmonth(d1) style = New DataGridViewCellStyle style.Alignment = DataGridViewContentAlignment.MiddleLeft dg.Columns.Add(i, i) dg.Columns(i + 1).Width = 30 dg.Columns(i + 1).HeaderCell.Style = style Next If con.State = ConnectionState.Closed Then con.Open() End If For Each dr In ds.Tables(0).Rows cmd = New SqlCommand("demo1", con) cmd.CommandType = CommandType.StoredProcedure Dim param As SqlParameter param = cmd.Parameters.Add("@lanid", SqlDbType.VarChar) param.Value = dr.Item("lanid").ToString param.Direction = ParameterDirection.Input param = cmd.Parameters.Add("@processid", SqlDbType.Int) param.Value = dr.Item("processid").ToString param.Direction = ParameterDirection.Input param = cmd.Parameters.Add("@subprocessid", SqlDbType.Int) param.Value = dr.Item("subprocessid").ToString param.Direction = ParameterDirection.Input param = cmd.Parameters.Add("@days", SqlDbType.Int) param.Value = daysinmonth(d1) param.Direction = ParameterDirection.Input param = cmd.Parameters.Add("@firstday", SqlDbType.DateTime) param.Value = firstday param.Direction = ParameterDirection.Input arr = CType(cmd.ExecuteScalar, String).Split(",") cmd.Dispose() cmd = Nothing Dim dgrow As New DataGridViewRow dgrow.CreateCells(dg) dgrow.Cells(0).Value = dr.Item("empid") dgrow.Cells(1).Value = dr.Item("EmpName") For i = 0 To (arr.GetUpperBound(0) - 1) dgrow.Cells(2 + i).Value = arr(i) Next dg.Rows.Add(dgrow) Next Catch ex As Exception MsgBox(ex.Message) Finally If Not con Is Nothing Then con.Close() End If Timer1.Enabled = False End Try d3 = Now t1 = d3.Subtract(d2) MsgBox(t1.TotalSeconds.ToString) MsgBox(dg.Rows.Count.ToString) End Sub