Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

DataGrid Showing Store Procedures result

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
jsctechy
Asked:
jsctechy
  • 2
  • 2
1 Solution
 
anyoneisCommented:
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
 
jsctechyAuthor Commented:
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
 
jsctechyAuthor Commented:
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
 
vb_jonasCommented:
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
 
vb_jonasCommented:
... note to self - always refresh question before answering ... :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now