Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
_ 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:
 
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:
 
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 18-Apr-13 19:02pm
Edited 18-Apr-13 20:32pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I have created a basic Sql helper class. Try this
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
  Permalink  
v2
Comments
alecxa05 at 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 Moideen at 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 Moideen at 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 Moideen at 19-Apr-13 1:49am
   
create a new class like i have created and just pass the storeprocedure name.
alecxa05 at 19-Apr-13 4:51am
   
_ I'm just using simple implementation.. no hardcodings..
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I can develope your Payroll System.
Thanks
Raj
rajgaurav2702@gmail.com
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 5,170
1 DamithSL 4,357
2 Maciej Los 3,750
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,851


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 19 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100