Solved

DataGrid Showing Store Procedures result

Posted on 2006-06-10
5
247 Views
Last Modified: 2010-04-23
Hi there,
I have the following situation, I'm invoquing a store procedure that takes as an input a sigle string that will get evaluate and it's supposed to give a result. Everthing since to be working fine but the only thing i got stuck on is in the part of displaying the resul of the store procedure in my Datagrid. I want to know how to accomplish this part? Here is the store procedure along with the code:

CREATE PROCEDURE test
@ID nchar (5)
AS
SELECT     OrderID, CustomerID, EmployeeID, RequiredDate, ShippedDate
FROM         dbo.Orders
WHERE    CustomerID = @ID
GO

And the code goes like this:

Imports System
Imports System.Data
Imports System.Data.SqlClient

...

 Private Sub cmdSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
       
        'initialized variables
        Dim SqlConnection As SqlConnection = New SqlConnection("Server=OROZCO;Integrated Security=SSPI;database=Northwind")
        Dim SqlCommand As SqlCommand = New SqlCommand("Test", SqlConnection)
        SqlCommand.CommandType = CommandType.StoredProcedure
        Dim sqlparameter1 As SqlParameter = New SqlParameter("@ID", SqlDbType.NChar)
        sqlparameter1.Direction = ParameterDirection.Input
        sqlparameter1.Value = txtOrder.Text

        SqlCommand.Parameters.Add(sqlparameter1)

        SqlConnection.Open()

        Dim sqlTransaction As SqlTransaction = SqlConnection.BeginTransaction()
        SqlCommand.Transaction = sqlTransaction

        SqlCommand.ExecuteNonQuery()
       
        sqlTransaction.Commit()
        SqlConnection.Close()

    End Sub

my Datagrid has as name Datagrid1
Thanks,
JSCTECHY
0
Comment
Question by:jsctechy
  • 2
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
anyoneis earned 500 total points
ID: 16879744
JASTechy:

>>        Dim sqlTransaction As SqlTransaction = SqlConnection.BeginTransaction()
>>        SqlCommand.Transaction = sqlTransaction
>>
>>        SqlCommand.ExecuteNonQuery()
>>       
>>        sqlTransaction.Commit()
>>        SqlConnection.Close()

The transaction is not necessary. But it is not hurting you at this point - think of it as unnecessary overhead.

You need a place to put the data. And you need to use something other than SqlCommand.ExecuteNonQuery, which:

"Executes a Transact-SQL statement against the connection and returns the number of rows affected. "

You need more than the count of rows, you need an actual result set. How about this:

[code]
...
        'initialized variables
        Dim sqlConnection As SqlConnection = New SqlConnection("Server=(local);Integrated Security=SSPI;database=Northwind")
        Dim sqlCommand As SqlCommand = New SqlCommand("Test", sqlConnection)
        sqlCommand.CommandType = CommandType.StoredProcedure
        Dim sqlparameter1 As SqlParameter = New SqlParameter("@ID", SqlDbType.NChar)
        sqlparameter1.Direction = ParameterDirection.Input
        sqlparameter1.Value = txtOrder.Text

        sqlCommand.Parameters.Add(sqlparameter1)
        Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter()
        sqlDataAdapter.SelectCommand = sqlCommand

        Dim dt As DataTable = New DataTable()
        sqlConnection.Open()

        sqlDataAdapter.Fill(dt)
        sqlConnection.Close()

        DataGrid1.DataSource = dt

[/code]

I have brought in a DataAdapter, and I let it fill up a DataTable, which I then set as the DataSource for the DataGrid.

David
0
 
LVL 1

Author Comment

by:jsctechy
ID: 16879758
It was too late but thanks ;-) i come up with this approach:

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

...

 Public Sub InputParameter()
        ' create Connection
        Dim SqlConnection As SqlConnection = New SqlConnection("Server=OROZCO;Integrated Security=SSPI;database=Northwind")

        'invoke the store procedure
        Dim SqlCommand As SqlCommand = New SqlCommand("Test", SqlConnection)
        SqlCommand.CommandType = CommandType.StoredProcedure

        'Declare and give values to the variables inside the Store procedure
        Dim sqlparameter1 As SqlParameter = New SqlParameter("@ID", SqlDbType.NChar)
        sqlparameter1.Direction = ParameterDirection.Input
        sqlparameter1.Value = txtOrder.Text

        SqlCommand.Parameters.Add(sqlparameter1)

        'Create the data adapter
        Dim adapter As SqlDataAdapter = New SqlDataAdapter(SqlCommand)

        'Create the data set
        Dim dataset As DataSet = New DataSet("order History")

        'Fill data into the grid
        adapter.Fill(dataset)
        DataGrid1.DataSource = dataset.Tables(0)

    End Sub

Private Sub cmdSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
        'call my function
        InputParameter()
    End Sub
End Class

and it works fine
0
 
LVL 1

Author Comment

by:jsctechy
ID: 16879759
But I'm sure your solution will also work because what i was missing you added therefore i will give you the points :-)
Thank you so much  
0
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16879761
Hi, you can use a dataadapter with your command and then fill a datatable with the result:

        Dim SqlConnection As SqlConnection = New SqlConnection("Server=OROZCO;Integrated Security=SSPI;database=Northwind")
        Dim SqlCommand As SqlCommand = New SqlCommand("Test", SqlConnection)
        SqlCommand.CommandType = CommandType.StoredProcedure
        Dim sqlparameter1 As SqlParameter = New SqlParameter("@ID", SqlDbType.NChar)
        sqlparameter1.Direction = ParameterDirection.Input
        sqlparameter1.Value = txtOrder.Text

        SqlCommand.Parameters.Add(sqlparameter1)

' New code:

        Dim MyDataAdapter As New SqlClient.SqlDataAdapter
        MyDataAdapter.SelectCommand = SqlCommand

        Dim MyDataTable As New DataTable
        MyDataAdapter.Fill(MyDataTable)
        DataGridView1.DataSource = MyDataTable
0
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16879764
... note to self - always refresh question before answering ... :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question