Click here to Skip to main content
15,942,710 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
_ hooray! need help for my project .. I am developing a Payroll System .. I have a Stored Procedure created using SSMS 2005 ..details:
Stored Procedure Name: Find_Emp
Command/Query=

SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO =EMP0000.EMP_NO WHERE EMP0000.EMP_NO=@empno

In my GUI using Visual Studio 2010, I have a ListView where the list of employee listed..

I want to call the procedure in SelectedIndexChanged Event of the ListView ..

My problem is don't have an idea how to call this procedure in visual studio 2010 .. any help please ?

It's quite complicated I think .. I am using Visual Studio 2010(vb.net) .. your code is in c# ? am I right ?

by the way here's some details:

in module:

VB
Public Sub Recordset_Connection(ByVal nMode As Integer)

      If nMode = 0 Then
          RstFileRead = Nothing
          RstFileRead = New ADODB.Recordset
          RstFileRead.Open(strFileRead, Conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)
      ElseIf nMode = 1 Then
          RstFileUpdate = Nothing
          RstFileUpdate = New ADODB.Recordset
          RstFileUpdate.Open(strFileUpdate, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
      End If

  End Sub



in my form , I create a sub procedure like the one below:

VB
Private Sub LoadListEmp()

      Dim LvItem As New ListViewItem

      ListView1.Items.Clear()

      strFileRead = "TIMESHEET" : Recordset_Connection(0)

      With RstFileRead

          Do While Not .EOF

              LvItem = New ListViewItem

              LvItem = ListView1.Items.Add(.Fields("EMP_NO").Value)
              LvItem.SubItems.Add(.Fields("L_NAME").Value & "," & (.Fields("F_NAME").Value))
              LvItem.SubItems.Add(.Fields("DEPT").Value)

              .MoveNext()

          Loop

      End With

  End Sub


-I call this procedure in Form_Load Event so it will list the Employees in ListView with the query provided in the TIMESHEET which is a Stored Procedure..

TIMESHEET Query:

SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO =EMP0000.EMP_NO INNER JOIN ATT0000 ON ATT0000.EMP_NO = EMP0000.EMP_NO



I have another Stored Procedure provided with the command as below:

FIND_EMP Query:

SELECT * FROM EMP0000 INNER JOIN EMP0001 ON EMP0001.EMP_NO =EMP0000.EMP_NO INNER JOIN ATT0000 ON ATT0000.EMP_NO = EMP0000.EMP_NO WHERE EMP_0000.EMP_NO = @empno


Now I want to call the FIND_EMP Stored Procedure using Visual Studio 2010.. When I click or Select an item in ListView, how to call the FIND_EMP procedure ?



thanks!
Posted
Updated 18-Apr-13 19:32pm
v3

I have created a basic Sql helper class. Try this
VB
Public Class SqlHelper
	Implements IDisposable
	Private sqlConnection As SqlConnection

	Public Function ExecuteReader(procedureName As String, sqlParameters As SqlParameter()) As SqlDataReader
		Try
			Dim dataReader As SqlDataReader
			Me.Open()
			Dim sqlCommand = New SqlCommand(procedureName, sqlConnection)
			sqlCommand.CommandType = CommandType.StoredProcedure
			If sqlParameters IsNot Nothing Then
				For Each parameter As SqlParameter In sqlParameters
					sqlCommand.Parameters.Add(parameter)
				Next
			End If
			dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
			Return dataReader
		Catch ex As Exception
			Throw ex
		End Try

	End Function
	Public Function ExecuteReader(sqlQuery As String) As SqlDataReader
		Try
			Dim dataReader As SqlDataReader
			Me.Open()
			Dim sqlCommand = New SqlCommand(sqlQuery, sqlConnection)
			sqlCommand.CommandType = CommandType.Text
			dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

			Return dataReader
		Catch ex As Exception
			Throw ex
		End Try

	End Function
	Private Sub Open()
		If sqlConnection Is Nothing Then
			sqlConnection = New SqlConnection(AppConfig.ConnectionString)
			sqlConnection.Open()
		End If
	End Sub

	Public Sub Close()
		If sqlConnection IsNot Nothing Then
			sqlConnection.Close()
		End If
	End Sub
	Public Sub Dispose()
		If sqlConnection IsNot Nothing Then
			sqlConnection.Dispose()
			sqlConnection = Nothing
		End If
	End Sub
End Class

Public Class AppConfig
	Public Shared ReadOnly Property ConnectionString() As String
		Get
			Return ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
		End Get
	End Property
End Class



Hope this helps
 
Share this answer
 
v2
Comments
alecxa05 19-Apr-13 1:36am    
Jameel Moideen thanks for your response.. appreciated! but It's quite complicated I think .. I am using Visual Studio 2010(vb.net) .. your code is in c# ? am I right ?

i have updated my problems and improved my questions .. please see .. really need this to present next next week . =(
Jameel VM 19-Apr-13 1:44am    
there is a online code converter from c# to vb.net and vb.net to c#.net. By using this you can easily convert this.
http://converter.telerik.com/
Jameel VM 19-Apr-13 1:47am    
the code you have tried is like vb. It's old implementation. I have updated the c# to vb.net.
Jameel VM 19-Apr-13 1:49am    
create a new class like i have created and just pass the storeprocedure name.
alecxa05 19-Apr-13 4:51am    
_ I'm just using simple implementation.. no hardcodings..
I can develope your Payroll System.
Thanks
Raj
rajgaurav2702@gmail.com
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900