3 Replies - 4870 Views - Last Post: 12 July 2012 - 07:28 AM Rate Topic: -----

#1 tashee_it  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 15-January 08

how to search data from SQL database using stored procedure and VB.NET

Posted 10 July 2012 - 04:03 AM

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" :

 /*
 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???????

Is This A Good Question/Topic? 0
  • +

Replies To: how to search data from SQL database using stored procedure and VB.NET

#2 m_wylie85  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 96
  • View blog
  • Posts: 899
  • Joined: 15-October 10

Re: how to search data from SQL database using stored procedure and VB.NET

Posted 10 July 2012 - 05:22 AM

I am not really sure what you are asking for but i think you are asking how to wrtie the sql querie but i could be wrong. so i would do it something like below:

        Dim ds As New DataSet()
              OpenDatabaseConnction()

            Dim rs As New ADODB.Recordset()
            rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
            rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic
            rs.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

            rs.Open("select * from Details WHERE columnName = '" & gpsIntGrade.Text & "'", ConnString)

            rs.ActiveConnection = Nothing

            ' close the connection
            CloseDatabaseConnction()

            Dim da As New System.Data.OleDb.OleDbDataAdapter()

            da.Fill(ds, rs, "Details")


Ps i forgot to ask are you using Access Database

This post has been edited by m_wylie85: 10 July 2012 - 05:23 AM

Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 433
  • View blog
  • Posts: 3,006
  • Joined: 12-January 10

Re: how to search data from SQL database using stored procedure and VB.NET

Posted 10 July 2012 - 05:25 AM

his description says sql

now exactly what error are you getting
Was This Post Helpful? 0
  • +
  • -

#4 sela007  Icon User is offline

  • D.I.C Addict

Reputation: 138
  • View blog
  • Posts: 838
  • Joined: 21-December 11

Re: how to search data from SQL database using stored procedure and VB.NET

Posted 12 July 2012 - 07:28 AM

this is the simple way of executing stored procedure using SqlCommand and data adapter:
Dim adapter As SqlDataAdapter
Dim command As New SqlCommand
Dim tbl As New DataTable
command.Connection = 'your connection
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(new SqlParameter("@gpsIntGrade", gpsIntGrade.Text));
command.CommandText = "precedurename"
'connection.Open()
adapter = New SqlDataAdapter(command)
adapter.Fill(tbl)
For Each dr As DataRow In tbl.Rows
'..MsgBox(dr.Item(0))
Next
'connection.Close()

This post has been edited by sela007: 12 July 2012 - 07:38 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1