Stored Procedure Implementation

Question

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 18-Apr-13 18:02pm

alecxa05

Updated 18-Apr-13 19:32pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jameel VM · Answer 1 · 2013-04-18T19:10:00

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

Member 9996585 · Answer 2 · 2013-04-18T21:16:00

Solution 2

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

Posted 18-Apr-13 21:16pm

Member 9996585