Solved

DataGrid Showing Store Procedures result

Posted on 2006-06-10
5
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: 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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

624 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