none
how to pass value (@FirstName) use (where) in StoredProcedure and show Results datagridview ?

    Question

  • my code StoredProcedure :

    LTER PROCEDURE [dbo].[storePro]
    	-- Add the parameters for the stored procedure here
    	
    	  @FirstName varchar(50)
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	with cet1 as 
    (
    SELECT		COUNT(1) as CNT, FirstName, LastName, MobileNo
    
    FROM		CUSTOMER where FirstName like @FirstName
    
    GROUP BY	FirstName, LastName, MobileNo  
    )
    select FirstName, sum(CNT) as cnt
    from cet1
    group by FirstName
    END

    my code vb.net :

    Imports System.Data.SqlClient
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Try
    
                Dim connectionString As String = "Data Source=PC;Password=12345;Persist Security Info=True;User ID=sa;Initial Catalog=data;Data Source=PC"
                Dim connection1 As New SqlConnection(connectionString)
                Dim Command As New SqlCommand("storePro", connection1)
                Dim dataadapter1 As New SqlDataAdapter(Command)
                'Command.CommandType = CommandType.StoredProcedure
                dataadapter1.SelectCommand.CommandText = CommandType.StoredProcedure
                Command.Parameters.AddWithValue("@FirstName", TextBox1.Text)
                Dim ds1 As New DataSet()
                connection1.Open()
                dataadapter1.Fill(ds1, " * ")
                connection1.Close()
                DataGridView1.DataSource = ds1
                DataGridView1.DataMember = " * "
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub
    
    End Class


    • Edited by srajmuneer Wednesday, March 29, 2017 5:37 PM
    Wednesday, March 29, 2017 5:36 PM

Answers

  • Simple example using a connection and command, yes I varied from a DataAdapter :-)

    Imports System.Data.SqlClient
    
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New Operations
            DataGridView1.DataSource = ops.GetData("Owner")
        End Sub
    End Class
    
    Public Class Operations
        Private ConnectionString As String = "Data Source=.\SQLEXPRESS;" &
            "Initial Catalog=NORTHWND;Integrated Security=True"
        Public Function GetData(ByVal ContactTitle As String) As DataTable
            Dim dt As New DataTable
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With
                    {
                        .Connection = cn, .CommandText = "uspContactTitle",
                        .CommandType = CommandType.StoredProcedure
                    }
                    cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
            Return dt
        End Function
    End Class
    

    SP

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE dbo.uspContactTitle (@ContactTitle AS NVARCHAR (40)) AS
    BEGIN
    SELECT
          [CompanyName]
          ,[ContactName]
          ,[Phone]
      FROM [NORTHWND].[dbo].[Customers]
      WHERE ContactTitle = @ContactTitle
    END
    
    GO
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by srajmuneer Wednesday, March 29, 2017 8:06 PM
    Wednesday, March 29, 2017 7:24 PM
    Moderator

All replies

  • Besides showing code, can you indicate what the results/issue are? Is the fill not working? Do you have SQL-Server Management Studio? If so you can execute the Stored procedure and pass your param value in.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 29, 2017 5:50 PM
    Moderator
  • thank you Kareninstructor
    , yes Is the fill not working
    Wednesday, March 29, 2017 6:06 PM
  • Srajmuneer,

    I think your program would run better if you rephrase this

    Dim ds1 As New DataSet()
    connection1.Open()
    dataadapter1.Fill(ds1, " * ")
    connection1.Close()
    DataGridView1.DataSource = ds1

    To this

    Dim dt1 As New DataTable dataadapter1.Fill(dt1) DataGridView1.DataSource = dt1

    'no datamember needed

    The dataadapter has an inbuild open and close of the connection and the datatable suits better


    Success
    Cor


    Wednesday, March 29, 2017 6:34 PM
  • Simple example using a connection and command, yes I varied from a DataAdapter :-)

    Imports System.Data.SqlClient
    
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New Operations
            DataGridView1.DataSource = ops.GetData("Owner")
        End Sub
    End Class
    
    Public Class Operations
        Private ConnectionString As String = "Data Source=.\SQLEXPRESS;" &
            "Initial Catalog=NORTHWND;Integrated Security=True"
        Public Function GetData(ByVal ContactTitle As String) As DataTable
            Dim dt As New DataTable
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With
                    {
                        .Connection = cn, .CommandText = "uspContactTitle",
                        .CommandType = CommandType.StoredProcedure
                    }
                    cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
            Return dt
        End Function
    End Class
    

    SP

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE dbo.uspContactTitle (@ContactTitle AS NVARCHAR (40)) AS
    BEGIN
    SELECT
          [CompanyName]
          ,[ContactName]
          ,[Phone]
      FROM [NORTHWND].[dbo].[Customers]
      WHERE ContactTitle = @ContactTitle
    END
    
    GO
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by srajmuneer Wednesday, March 29, 2017 8:06 PM
    Wednesday, March 29, 2017 7:24 PM
    Moderator
  • thank you very much Kareninstructor
    Wednesday, March 29, 2017 8:06 PM