Solved

DataGrid Showing Store Procedures result

Posted on 2006-06-10
5
226 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change data in datatable 8 31
Javascript Refresh Parent Page from Popup 3 50
Get list of word ducuments in a folder 10 36
Open a word document 23 33
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 …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now