How to fill a data grid using a stored procedure

[Badia]

Hi everyone , Im almost new do this subject Please help me if you can.

How to fill a data grid using a stored procedure

The stored proc name is purchase_transaction,there is an input parameter which is purchase_order_number1

CREATE PROCEDURE dbo.Purchase_Transaction
(@Purchase_order_Number1 int ,@Purchase_transaction_id int  output,@Purchase_Order_Number int output,@Item_id int output)


	
AS
	select Purchase_transaction_id,Purchase_Order_Number,Item_id from Purchase_transactions
              where Purchase_order_Number=@Purchase_order_Number1
	RETURN
GO

The datagrids name is dgPo
Here is how my code looks like:

 Dim myCmd1 As SqlCommand = New SqlCommand("Purchase_Transaction", objConn)
                myCmd1.CommandType = CommandType.StoredProcedure
                myCmd1.Parameters.Add("@Purchase_order_Number1", SqlDbType.Int)
                myCmd1.Parameters("@Purchase_order_Number1").Value = CInt(txtOrderNo.Text)
                myCmd1.Parameters("Purchase_order_Number1").Direction = ParameterDirection.Input

                Dim dr2 As SqlDataReader
                
                    Try
                        objConn.Open()
                    dr2 = myCmd1.ExecuteReader()
                    While dr2.Read
                         bind dr2 to dgpo
                         DgPO= dr2.Item("purchase_transaction_id")
                    End While
                Catch

                End Try
               
                DgPO.Enabled = False
            End While

            objConn.Close()

        Catch exc As Exception
            MsgBox(exc.Message)
        End Try

Even if you can think of another approach please help me with it.

 

[Arch4ngel]

Dim myCmd1 As SqlCommand = New SqlCommand("Purchase_Transaction", objConn)

myCmd1.CommandType = CommandType.StoredProcedure

myCmd1.Parameters.Add("@Purchase_order_Number1", SqlDbType.Int)

myCmd1.Parameters("@Purchase_order_Number1").Value = CInt(txtOrderNo.Text)

myCmd1.Parameters("Purchase_order_Number1").Direction = ParameterDirection.Input


Dim dr2 As SqlDataReader

DataGrid1.DataSource = dr2
DataGrid1.DataBind() ONLY IN ASP.NET

dr2.Close()

 

[Badia]

Dear Arch4ngel
Im getting this error message " An sql parameter Name purchase_order_Number1 is not contained by this sqlparameter Collectiom".

 

[Arch4ngel]

change :
myCmd1.Parameters("Purchase_order_Number1").Direction = ParameterDirection.Input

to:

myCmd1.Parameters("@Purchase_order_Number1").Direction = ParameterDirection.Input

 

[Badia]

Thanks Arch4ngel for your respons
I dont have that error any more, but the datagrid is not displaying the data yet. Is it possible to use the Fill method when using a datareader?

 

[Arch4ngel]

In which kind of app are you displaying your Data ? windows application or ASP.NET application ?

Fill method is only for DataAdapter. DataReader use Read. But the Read method will be called as often as needed by your Datagrid to fill all the data.

 

[Badia]

Im dispalying my data in a windows application. here how the code looks like now

 Dim dr2 As SqlDataReader
                Try
                    objConn.Open()
                    dr2 = myCmd1.ExecuteReader()
                    While dr2.Read
                        DgPO.DataSource = dr2
                    End While
                Catch

                End Try

 

[Arch4ngel]

remove the while. I didnt have a while in my answer.

If you only take part of my answer and change it as you like... dont complain it dont work

it should not be in a while loop. Only : DgPO.DataSource = dr2

 

[Badia]

Its working now - After taking you answer as is
I get this error Procedure purchase_transaction expects parameter @purchase_transaction_id which was not supported. I have tried to remove purchase_transaction_id from the stored proc but didnt work.
I told you Im new to this subject, thanks for your support.

 

[Arch4ngel]

Make sure that when you add your parameter that they are named EXACTLY the same (with "@") because itll not work because itll be unrecognized.

 

[Badia]

Im still having a problem with displaying data in the datagrid. Do you think that I need to use a data set whith the datareader? or is there something wrong with my stored procedure ?

 

[Badia]

I had a look at some threads, some people dont think that it is possible to use a data reader with A datagrid,
instead they suggest using a datatable.
In this case I think I think I have to startover.