Populating datagrid1.view with a SQL Server stored procedure

Question

I got a stored procedure in SQL Server I created some inner joins and now how will I populate my datagrid using that stored procedure.

Here is my code that is not working

Dim cmd As New SqlCommand
        Dim reader As SqlDataReader

        cmd.CommandText = "OfficeEquipmentProfile"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = sqlconn

        sqlconn.Open()



        sAdapter = New SqlDataAdapter(cmd)
        sBuilder = New SqlCommandBuilder(sAdapter)
        sDs = New DataSet
        sAdapter.Fill(sDs, "tblOfficeEquipmentProfile")
        sAdapter.Fill(sDs, "tblDepartment")
        sAdapter.Fill(sDs, "tblLocation")
        sAdapter.Fill(sDs, "tblOfficeEquipmentCategory")
        sAdapter.Fill(sDs, "tblApplication")
        sAdapter.Fill(sDs, "tblApplicationLicense")
        sAdapter.Fill(sDs, "tblEquipmentApplication")
        sAdapter.Fill(sDs, "tblOfficeEquipmentBrand")
        sAdapter.Fill(sDs, "tblOfficeEquipmentModel")
        sAdapter.Fill(sDs, "tblOfficeEquipmentServiceOrder")
        sAdapter.Fill(sDs, "tblOfficeEquipmentPMplan")


        sTable = sDs.Tables("tblOfficeEquipmentProfile")
        sTable = sDs.Tables("tblDepartment")
        sTable = sDs.Tables("tblLocation")
        sTable = sDs.Tables("tblOfficeEquipmentCategory")
        sTable = sDs.Tables("tblApplication")
        sTable = sDs.Tables("tblApplicationLicense")
        sTable = sDs.Tables("tblEquipmentApplication")
        sTable = sDs.Tables("tblOfficeEquipmentBrand")
        sTable = sDs.Tables("tblOfficeEquipmentServiceOrder")
        sTable = sDs.Tables("tblOfficeEquipmentPMplan")

        DataGrid1.DataSource = sDs.Tables("tblOfficeEquipmentProfile, tblDepartment, tblLocation, tblOfficeEquipmentCategory, tblApplication,tblApplicationLicense, tblEquipmentApplication, tblOfficeEquipmentBrand, tblOfficeEquipmentServiceOrder,tblEquipmentPMplan")
        DataGrid1.ReadOnly = True
        'Button1.Enabled = False
        'DataGrid1.SelectionMode = DataGridViewSelectionMode.FullRowSelect


        reader = cmd.ExecuteReader()
        sqlconn.Close()

I just want to display records from the database

it returned no error, at the same time didnt display any records from the database — ivandinglasan, Mar 12 '13 at 08:02
Please show your stored procedure. I cant see parameters you are calling in cmd.parameter — DevelopmentIsMyPassion, Mar 12 '13 at 08:13
what parameter your stored procedure expects? I cant see any parameters while you are calling this stored procedure in your .net code — DevelopmentIsMyPassion, Mar 12 '13 at 08:28
kindly send me your email ill send it to you mine is ivan.jerome003@gmail.com — ivandinglasan, Mar 12 '13 at 08:31
Your storedprocedure should return multiple tables. And you need only one Fill(Dataset). [Look at this question](http://stackoverflow.com/questions/7239450/returning-multiple-tables-from-a-stored-procedure) — Steve, Mar 12 '13 at 08:53
because i use inner join. it contains many tables combined to show all columns in 1 datagrid — ivandinglasan, Mar 12 '13 at 08:56
inner join doesn't return separate tables, but only one with the combined columns from one or more tables. If you expect to have multiple tables returned by the sp you need (inside the sp) a select for each table as the link posted before explains. — Steve, Mar 12 '13 at 08:57
@AshReva, ivan - You can use the SO chat facility rather than publishing email addresses. — APC, Mar 13 '13 at 05:41
@APC sorry sir i cant use chat due to low reputation new account — ivandinglasan, Mar 13 '13 at 08:24

DevelopmentIsMyPassion · Accepted Answer · 2013-03-13T07:06:56.537

1

As you are returning columns from different tables and not multiple tables then you just need this code.

 Dim Command As SqlCommand = New SqlCommand()
 Command.Connection = Connection
 Command.CommandText = "OfficeEquipmentProfile"
 Command.CommandType = CommandType.StoredProcedure

 Dim sAdapter As SqlDataAdapter = New SqlDataAdapter(Command)

 Dim DataSet As DataSet = New DataSet(Command.CommandText)

 sAdapter.Fill(DataSet)
 DataGrid1.DataSource = DataSet.Tables(0)

edited Mar 13 '13 at 07:06

answered Mar 12 '13 at 09:05

DevelopmentIsMyPassion

3,541
4
34
60

DataGrid1.DataBind() not a member of system.windows.forms.datagrid im using vb.net 2003 – ivandinglasan Mar 13 '13 at 00:41
@APC, AshReva Thank you very much! you both help a lot! – ivandinglasan Mar 13 '13 at 08:30

Steve · Answer 2 · 2013-03-12T09:19:38.333

Try with this code

Dim cmd As New SqlCommand
cmd.CommandText = "OfficeEquipmentProfile"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlconn
sqlconn.Open()

sAdapter = New SqlDataAdapter(cmd)
sBuilder = New SqlCommandBuilder(sAdapter)
sDs = New DataSet
sAdapter.Fill(sDs)
DataGrid1.DataSource = sDs

The storedprocedure return one or more tables, but you don't need to call a Fill for each table. One is enough. Then assign the whole Dataset to the Datasource or, if you return more than one table and need a specific table

DataGrid1.DataSource = sDs
DataGrid1.DataMember = "tablename"

score 0 · Answer 3 · answered Jan 14 '14 at 02:52

    Try
        If con.State = ConnectionState.Open Then con.Close()
        con.Open()
        Dim dset As New DataSet
        Dim dbind As New BindingSource
        Dim strquery As String
        strquery = "SELECT prod_code, prod_no, prod_suffix, prod_lvl, customer, model, family, company_code, company_name, company_address, running_no, template_code FROM products_tbl  WHERE  template_code = 'n'and company_code = 'YTPL' and prod_no = '" & txt_product.Text & "' and prod_suffix = '" & txt_suffix.Text & "' and prod_lvl = '" & txt_level.Text & "'"
        Dim adap As New SqlDataAdapter(strquery, con)
        dset = New DataSet
        adap.Fill(dset)
        dbind.DataSource = dset.Tables(0)
        dg.DataSource = dbind
    Catch ex As Exception
        MsgBox("Trace No 3: System Error or Data Error!" + Chr(13) + ex.Message + Chr(13) + "Please Contact Your System Administrator!", vbInformation, "Message")
    End Try

Populating datagrid1.view with a SQL Server stored procedure

3 Answers3