PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2008 Filling a datagridview with the help of a stored procedure variable-VBForums
Results 1 to 9 of 9

Thread: Filling a datagridview with the help of a stored procedure variable

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2002
    Location
    Canada
    Posts
    455

    Filling a datagridview with the help of a stored procedure variable

    Dear all,

    Using VB2008
    How can we fill the datagrid with information from a stored procedure and a sp variable.

    We have a dataset named: DBnamesDataSet, a source: ShownameBindingSource and a adapter: Show_nameTableAdapter.

    --------------------------------------------------------------------------
    Stored procedure:
    alter PROCEDURE [dbo].[show_name]

    @name varchar(50)
    AS

    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_name
    WHERE (name = @name)

    END
    --------------------------------------------------------------------------

    Nice regards,
    Michelle.

  2. #2
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Filling a datagridview with the help of a stored procedure variable

    Are you talking bound or unbound?
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  3. #3
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Filling a datagridview with the help of a stored procedure variable

    Here's how to execute a stored procedure and get a couple parameters. (mConn is a connection object)

    Code:
    Public Function Save() As Boolean
    
            Dim cmd As SqlCommand
            Dim parameter As SqlParameter
    
    
    
                        cmd = New SqlCommand("SP_Name", mConn)
                        cmd.CommandType = CommandType.StoredProcedure
                        parameter = cmd.Parameters.Add("@ReturnMessage", SqlDbType.VarChar, 100)
                        parameter.Direction = ParameterDirection.Output
                        parameter = cmd.Parameters.Add("@ReturnCode", SqlDbType.Int)
                        parameter.Direction = ParameterDirection.Output
                        mintSPRC = cmd.ExecuteNonQuery()
                        If mintSPRC = -1 Then
                            If Val(cmd.Parameters(1).Value) = 0 Then
                                Return True
                            Else
                                Return False
                            End If
                        Else
                            mstrLastError = cmd.Parameters(0).Value.ToString
                            Return False
                        End If
               
        End Function
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2002
    Location
    Canada
    Posts
    455

    Re: Filling a datagridview with the help of a stored procedure variable

    Hello Marman,

    Thanks for the information.

    We are trying to get the datagridview filled with the help of a stored predure and a stored procedure variable.

    Have you information about this?

    Nice regards,
    Michelle.

  5. #5
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Filling a datagridview with the help of a stored procedure variable

    Bound or Unbound?
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2002
    Location
    Canada
    Posts
    455

    Re: Filling a datagridview with the help of a stored procedure variable

    Hello Marman,

    The datagridview gets his information unbound (if undestood from http://dev.mysql.com/tech-resources/...ew-vb-net.html)

    Nice regards,
    Michelle.

  7. #7
    Frenzied Member
    Join Date
    Jan 2010
    Location
    Connecticut
    Posts
    1,687

    Re: Filling a datagridview with the help of a stored procedure variable

    OK, here's some code that set the columns
    Code:
        Private Sub SetGridColumns(ByVal strComboInfo As String(,))
    
            Dim intColID As Integer
            Dim chkCol As DataGridViewCheckBoxColumn
    
    
            With grdComplete
                intColID = .Columns.Add("EmployeeID", "Employee ID")
                .Columns(intColID).Width = 50
                intColID = .Columns.Add("Employee", "Employee")
                .Columns(intColID).Width = 100
                intColID = .Columns.Add("TypeCode", "Type Code")
                .Columns(intColID).Width = 50
                intColID = .Columns.Add("Date", "Date")
                .Columns(intColID).Width = 75
                intColID = .Columns.Add("Time", "Time")
                .Columns(intColID).Width = 50
                chkCol = New DataGridViewCheckBoxColumn
                With chkCol
                    .Name = "Internal"
                    .Width = 45
                End With
                .Columns.Add(chkCol)
                intColID = .Columns.Add("TrainerID", "Trainer ID")
                .Columns(intColID).Width = 50
                intColID = .Columns.Add("Trainer", "Trainer")
                .Columns(intColID).Width = 100
                intColID = .Columns.Add("TrainingDescription", "Description")
                .Columns(intColID).Width = 300
                .Columns(intColID).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                intColID = .Columns.Add("Index", "Index")
                .Columns(intColID).MinimumWidth = 2
                .Columns(intColID).Width = 0
            End With
    
        End Sub
    :

    Here's some code that fills the grid:
    Code:
       Private Sub FillDetailGrid()
    
            Dim strRow As String()
            Dim Detail As TrainDetailCollection.TrainDetail
    
    
            ReDim strRow(9)
            grdComplete.Rows.Clear()
    
            For Each Detail In mcolDetail
                With Detail
                    strRow(eDetailColumns.EmployeeID) = .EmployeeID
                    strRow(eDetailColumns.EmployeeName) = GetUserName(.EmployeeID)
                    strRow(eDetailColumns.TypeCode) = .TypeCode
                    strRow(eDetailColumns.Date) = .TrainDate.ToString
                    strRow(eDetailColumns.Time) = .TimeInHours.ToString
                    strRow(eDetailColumns.Internal) = .InternalTrainer.ToString
                    strRow(eDetailColumns.TrainerID) = .Trainer
                    strRow(eDetailColumns.TrainerName) = GetUserName(.Trainer)
                    strRow(eDetailColumns.Description) = .Description
                End With
                strRow(eDetailColumns.Index) = mcolDetail.IndexOf(Detail).ToString
                grdComplete.Rows.Add(strRow)
            Next
    
        End Sub
    I hope that's enough to get you going.
    VB6 Library

    If I helped you then please help me and rate my post!
    If you solved your problem, then please mark the post resolved

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,919

    Re: Filling a datagridview with the help of a stored procedure variable

    Here's the simplest way to populate a DataGridView from a sproc:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here"),
    2.       command As New SqlCommand("sproc name here", connection),
    3.       adapter As New SqlDataAdapter(command)
    4.     command.CommandType = CommandType.StoredProcedure
    5.  
    6.     'Add parameters, e.g.
    7.     command.Parameters.AddWithValue("@ParentID", parentID)
    8.  
    9.     Dim table As New DataTable
    10.  
    11.     'Get the data.
    12.     adapter.Fill(table)
    13.  
    14.     'Display the data.
    15.     Me.DataGridView1.DataSource = table
    16. End Using

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2002
    Location
    Canada
    Posts
    455

    Re: Filling a datagridview with the help of a stored procedure variable

    Both of you:

    Thanks a lot for the information.

    It's working!!

    Nice regards,
    Michelle.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width