Question how to search data from sql database using stored procedure

tashiduks

Member
Joined
Apr 19, 2010
Messages
22
Programming Experience
Beginner
Dear Expertise,

I am problem with coding to search the data from database using stored procedure and vb.net form. I am coding with vb language not the C#.

Explanation:

I have stored procedure named "usp_GetPayByGrade" :

VB.NET:
 /*
 Name: usp_GetPayByGrade
 Description: Search the Record to dbo.hrGradePayScale table by gpsIntGrade
 Author: Tashi
 Modification: Update
  
 Description Date Changed By
 Created Procedure 27/10/2011 Tashi
 */
 CREATE proc [dbo].[usp_GetPayByGrade]
 (
 @gpsIntGrade varchar(50)
 )
 WITH ENCRYPTION
 AS
 SELECT 
gpsGrade,
 gpsIntGrade,
 gpsMinPay,
 gpsIncrement,
 gpsMaxPay,
 gpsGISGroup,
 gpsGISCont,
 gpsSWSCont,
 gpsSWSPercentageYN,
 gpsRetirementAge
 FROM [dbo].[hrGradePayScale]
 WHERE gpsIntGrade =@gpsIntGrade

when i run this store procedure in SQL its giving me correct result.

I have a form named "frmGetPayByGrade" with:

1. Textboxes:
gpsGrade
gpsIntGrade = in this textbox user will enter the value
gpsMinPay
gpsIncrement
gpsMaxPay
gpsGISGroup
gpsGISCont
gpsSWSCont
2. buttons:
btnSearch
btnClose

When user enters the value at "gpsIntGrade" textbox and hits the "btnsearch" it should run the stored procedure and fill the stored procedure result in respective textbox.

I have been trying to search but could not get the proper solution.

CAN ANYONE HELP ME???????
 
I wrote a blog entry recently giving code for basic data access, it can be seen here: The Curve | Simple MSSQL Data Access Layer - Using VB.NET

I will break it down for you here.

Firstly, here is the code to create a basic data access class

VB.NET:
Option Explicit On
Option Strict On
Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Configuration
 
'Remember to add references in your application for System.Configuration
'After .NET 2.0 the configuration classes were moved out of system.dll
 
Namespace DataAccessLayer
 
    Public Class DataAccess
 
        'SQL Connection
        Private con As SqlConnection
        'ConnectionString() Property will store connection string in here
        Private strConXString As String = ""
        'CommandText() Property stores data here. SQL command info
        Private cmd As IDbCommand = New SqlCommand
 
 
        'Command text will be the name of your Stored Procedure or your T-SQL string
        Public Property CommandText() As String
            Get
                Return cmd.CommandText
            End Get
            Set(ByVal value As String)
                'Setting a new command text will clear previous params
                cmd.CommandText = value
                cmd.Parameters.Clear()
            End Set
        End Property
 
        Public Property ConnectionString() As String
            Get
                Return strConXString
            End Get
            Set(ByVal value As String)
                strConXString = value
            End Set
        End Property
 
        'Constructor
        Public Sub New(ByVal database As String, isStoredProcedure As Boolean)
 
            'Get connection String from webconfig
            If ConfigurationManager.ConnectionStrings(database).ConnectionString = Nothing Then
                'if connection string is missing, throw error.
                Throw New Exception("Web Config does not contain a connection string named " & database)
                Exit Sub
            Else
                'Set connection string.
                ConnectionString() = ConfigurationManager.ConnectionStrings(database).ConnectionString
 
                'Create a SQL Connection
                con = New SqlConnection
                'Get Connection String from Property
                con.ConnectionString = ConnectionString()
 
                'Set the SQL command connection
                cmd.Connection = con
 
                'Set Command Type (Stored Proc or T-SQL string)
                If isStoredProcedure = True Then
                    cmd.CommandType = CommandType.StoredProcedure
                Else
                    cmd.CommandType = CommandType.Text
                End If
 
            End If
        End Sub
 
        'Add Parameters to the SQL Command, for executing stored procedures
        Public Sub addParameter(ByVal var_name As String, ByVal value As Object)
            'add a stored proc parameter to sqlcommand
            Dim param As New SqlParameter(var_name, value)
            cmd.Parameters.Add(param)
        End Sub
 
 
        Public Function ExecuteDataSet() As DataSet
            'Create data objects
            Dim da As SqlDataAdapter = Nothing
            Dim ds As DataSet = Nothing
 
            'Make Sure Command Text is set, if not then thow error.
            If CommandText() = Nothing Or CommandText = "" Then
                Throw New Exception("Command Text must be set to a Stored Procedure name or T-SQL string")
                Exit Function
            End If
 
            'Open Connection
            If cmd.Connection.State = ConnectionState.Closed Then
                cmd.Connection.Open()
            End If
 
            'Set Command text to SQL command
            cmd.CommandText = CommandText()
 
            'Instantiate the SQLDataAdapter
            da = New SqlDataAdapter()
 
            'set command info to SQLDataAdapter
            da.SelectCommand = CType(cmd, SqlCommand)
            'Instantiate Dataset object
            ds = New DataSet("dsResults")
 
            'set which dataset to fill with returned records
            da.Fill(ds)
 
            Return ds
        End Function
 
 
        Public Sub ExecuteNonQuery()
 
            'Make sure commandtext is set
            If CommandText() = Nothing Or CommandText = "" Then
                Throw New Exception("Command Text must be set to a Stored Procedure name or T-SQL string")
                Exit Sub
            End If
 
            'Set command text to sqlcommand 
            cmd.CommandText = CommandText()
 
            'Open connection if its closed.
            If cmd.Connection.State = ConnectionState.Closed Then
                cmd.Connection.Open()
            End If
 
            'execute query
            cmd.ExecuteNonQuery()
            cmd.Connection.Close()
 
        End Sub
 
    End Class
End Namespace

For your example, you could call your stored procedure using the above class like this. You should have a connection string on your app.config or web.config, whatever that string is called replaces "myDatabase"

VB.NET:
Dim dataObject As New DataAccessLayer.DataAccess("myDatabase", True)
dataObject.CommandText = "usp_GetPayByGrade"

Dim ds As DataSet = dataObject.ExecuteDataSet

You will now have a Dataset with your data to do with as you please.
 
Thanks for the solution. But the coding which you have provided to me is bit complicated. So i would appreciate if you can provide the simple solution. If i highlight how i want is:

After enter the search value into the textbox and when user hits the "Search Button" the form should call store procedure and retrieve the data from the database and fetch the result into the respective form controls.

Waiting for your kind response...
 
In easy steps...

1) copy and paste the first code example I gave you into a module or class file.
2) add a connection string to your app.config or web.config( Store connection string in web.config - ConnectionStrings.com )
3) use the second example of code I gave you to access the data

That will give you the data in a dataset. Then you can extract the data from that dataset to your textbox. If you need help getting the data out to the textbox thats fine, but get the first bit working first.
 
Back
Top