Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DataGrid Showing Store Procedures result

Posted on 2006-06-10
5
Medium Priority
?
265 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

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…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Suggested Courses

885 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