none
How to display record from SQL Server in a textbox by stored procedure

    Question

  • I have table called  ItemsPricesTbl:

    its contains : 

        ItemID
        FirstUnitWholeSalePrice
        FirstUnitShopperPrice
        FirstUnitDemotionsPrice
        FirstUnitPriceDefault
        SecondUnitWholeSalePrice
        SecondUnitShopperPrice
        SecondUnitDemotionsPrice
        SecondUnitPriceDefault
        ThirdUnitWholeSalePrice
        ThirdUnitShopperPrice
        ThirdUnitDemotionsPrice
        ThirdUnitPriceDefault
        DefaultPrice



    The stored procedure to get data is:

        
        ALTER PROCEDURE [dbo].[Get_Prices_Item_By_ID]
            @ItemID int
        AS
        BEGIN 
            SELECT
                ItemID, FirstUnitWholeSalePrice, FirstUnitShopperPrice, 
                FirstUnitDemotionsPrice, FirstUnitPriceDefault,
                SecondUnitWholeSalePrice, SecondUnitShopperPrice,
                SecondUnitDemotionsPrice, SecondUnitPriceDefault,
                ThirdUnitWholeSalePrice, ThirdUnitShopperPrice,
                ThirdUnitDemotionsPrice, ThirdUnitPriceDefault,
                DefaultPrice 
            FROM
                ItemsPricesTbl 
            WHERE
                ItemID = @ItemID
        END




    In Vb I have a DatabaseManager class which has this code 

        Public Function fillTable(ByRef cmd As SqlCommand, ByRef dt As DataTable) As Integer
            Dim retval As Integer = -1
            dt = New DataTable
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = Me.Connection
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)
        
            If (Not dt Is Nothing) Then retval = dt.Rows.Count
            Return retval
        End Function




    Also I have two classes: the data class and a business class .

    Data class code :

        Friend Sub Get_Prices_Item_By_ID(ByRef dt As DataTable, ByVal ItemID As Integer)
            Dim cmd As New SqlCommand("Get_Prices_Item_By_ID")
            cmd.Parameters.Add("@ItemID", SqlDbType.Int).Value = ItemID
            dm.fillTable(cmd, dt)
        End Sub


    And business class code :


        Public Function Get_Prices_Item_By_ID(ByVal ItemID As Integer) As DataTable
            Dim dt As New DataTable
            p.Get_Prices_Item_By_ID(dt, ItemID)
            Return dt
        End Function




    What code do I have to put in search button?

    I have tried this :


        FrmManage_Items.txtwholesaleone.Text = dt.Rows(0)("FirstUnitWholeSalePrice").ToString
                        FrmManage_Items.txtcustomerone.Text = dt.Rows(0)("FirstUnitShopperPrice").ToString
                        FrmManage_Items.txtsaleone.Text = dt.Rows(0)("FirstUnitDemotionsPrice").ToString
                        'FrmManage_Items.RadioButton4.Checked = dt.Rows.ToString
                        FrmManage_Items.txtwholesaletwo.Text = dt.Rows(0)("SecondUnitWholeSalePrice").ToString
                        FrmManage_Items.txtcustomertwo.Text = dt.Rows(0)("SecondUnitShopperPrice").ToString
                        FrmManage_Items.txtsaletwo.Text = dt.Rows(0)("SecondUnitDemotionsPrice").ToString
                        'FrmManage_Items.RadioButton5.Checked = dt.Rows.ToString
                        FrmManage_Items.txtwholesalethird.Text = dt.Rows(0)("ThirdUnitWholeSalePrice").ToString
                        FrmManage_Items.txtcustomerthird.Text = dt.Rows(0)("ThirdUnitShopperPrice").ToString
                        FrmManage_Items.txtsalethird.Text = dt.Rows(0)("ThirdUnitDemotionsPrice").ToString
                        'FrmManage_Items.RadioButton6.Checked = dt.Rows.ToString
                        FrmManage_Items.TextBox20.Text = dt.Rows(0)("DefultPrice").ToString




    i want to enter the id of item in txtitemid.text

    and it will search for this id and return back the rest of record if found 

        FirstUnitWholeSalePrice.text
        FirstUnitShopperPrice.text
        FirstUnitDemotionsPrice.text
        FirstUnitPriceDefault.text
        SecondUnitWholeSalePrice.text
        SecondUnitShopperPrice.text
        SecondUnitDemotionsPrice.text
        SecondUnitPriceDefault.text
        ThirdUnitWholeSalePrice.text
        ThirdUnitShopperPrice.text
        ThirdUnitDemotionsPrice.text
        ThirdUnitPriceDefault.text
        DefaultPrice.text


    Sunday, April 30, 2017 1:41 PM

All replies

  • Hi,

    I'm not sure what you are after. The basics are, have a class for data operations e.g.

    Imports System.Data.SqlClient
    
    Public Class OperationsStoredProcedures
        Private ConnectionString As String =
            <C>
                Data Source=KARENS-PC;
                Initial Catalog=NorthWindDemo;
                Integrated Security=True
            </C>.Value
        Public Property Table As DataTable
        Public Sub Load(ByVal id As Integer)
            Table = New DataTable
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn, .CommandType = CommandType.StoredProcedure}
                    cmd.CommandText = "uspSelectProductById"
                    cmd.Parameters.AddWithValue("@pId", id)
                    cn.Open()
                    Table.Load(cmd.ExecuteReader)
                End Using
            End Using
        End Sub
    End Class
    

    Or

    Imports System.Data.SqlClient
    
    Public Class OperationsStoredProcedures
        Private ConnectionString As String =
            <C>
                Data Source=KARENS-PC;
                Initial Catalog=NorthWindDemo;
                Integrated Security=True
            </C>.Value
        Public Property Table As DataTable
        Private mException As Exception
        Public ReadOnly Property Exception As Exception
            Get
                Return mException
            End Get
        End Property
        Public Function Load(ByVal id As Integer) As Boolean
            Table = New DataTable
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn, .CommandType = CommandType.StoredProcedure}
                    cmd.CommandText = "uspSelectProductById"
                    cmd.Parameters.AddWithValue("@pId", id)
                    Try
                        cn.Open()
                        Table.Load(cmd.ExecuteReader)
                        Return True
                    Catch ex As Exception
                        mException = ex
                        Return False
                    End Try
                End Using
            End Using
        End Function
    End Class
    

    Then call it e.g.

    Dim demo As New OperationsStoredProcedures
    Dim dt As DataTable = demo.Table
    If demo.Load(4) Then
        DataGridView1.DataSource = dt
    Else
        MessageBox.Show($"Failed returning data: {demo.Exception}")
    End If
    

    Of course 4 being passed in would be say from a NumericUpDown, TextBox or perhaps a ListBox or ComboBox etc. for the id of the item you want.

    SP

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE dbo.uspSelectProductById (@pId AS INT) AS
    BEGIN
    SELECT ProductID
          ,ProductName
          ,SupplierID
          ,CategoryID
          ,QuantityPerUnit
          ,UnitPrice
          ,UnitsInStock
          ,UnitsOnOrder
          ,ReorderLevel
          ,Discontinued
          ,DiscontinuedDate
      FROM .Products
      WHERE ProductID = @pId
    END
    GO
    So with that, what are you after?


    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

    Sunday, April 30, 2017 2:50 PM
    Moderator
  • Iam getting 

     There is no row at position 0
    Sunday, April 30, 2017 3:15 PM
  • If I run my query with a valid id then one record (which I expected as this is a primary key) is returned in the DataTable and back this up by executing it in SQL Server Management Studio

    If passing an non-existing id, zero records are returned

    So when you get that message no row at position 0, no matter the case no records were found to match the value sent to the stored procedure.

    Have you tested it in SQL-Server Management Studio? If you don't have it then create a new text file in your project, rename it to say test.sql, drop in code to execute it as shown below


    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

    Sunday, April 30, 2017 4:35 PM
    Moderator
  • Khalid,

    At least the method filltable is not right, it is all about reference types and therefore ByRef is not needed (it onlycreates an extra address)

    I would change it to this. 

     Public Function fillTable(ByVal cmd As SqlCommand, ByVal dt As DataTable) As Integer
            Dim retval As Integer = -1
        
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = Me.Connection
            Dim da As New SqlDataAdapter(cmd)
            da.Fill(dt)
        
            If (Not dt Is Nothing) Then retval = dt.Rows.Count
            Return retval
        End Function
    Be aware that I removed that creation of the new DataTable


    Success
    Cor


    Sunday, April 30, 2017 5:09 PM
  • If you follow the example before you in the example, you should be able to figure it all out.

    http://www.codeguru.com/columns/vb/using-sql-stored-procedures-with-vb.net.htm

    Sunday, April 30, 2017 8:37 PM