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=OROZ
Dim SqlCommand As SqlCommand = New SqlCommand("Test", SqlConnection)
SqlCommand.CommandType = CommandType.StoredProcedur
Dim sqlparameter1 As SqlParameter = New SqlParameter("@ID", SqlDbType.NChar)
sqlparameter1.Direction = ParameterDirection.Input
sqlparameter1.Value = txtOrder.Text
SqlCommand.Parameters.Add(
SqlConnection.Open()
Dim sqlTransaction As SqlTransaction = SqlConnection.BeginTransac
SqlCommand.Transaction = sqlTransaction
SqlCommand.ExecuteNonQuery
sqlTransaction.Commit()
SqlConnection.Close()
End Sub
my Datagrid has as name Datagrid1
Thanks,
JSCTECHY
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=OROZ
Dim SqlCommand As SqlCommand = New SqlCommand("Test", SqlConnection)
SqlCommand.CommandType = CommandType.StoredProcedur
Dim sqlparameter1 As SqlParameter = New SqlParameter("@ID", SqlDbType.NChar)
sqlparameter1.Direction = ParameterDirection.Input
sqlparameter1.Value = txtOrder.Text
SqlCommand.Parameters.Add(
SqlConnection.Open()
Dim sqlTransaction As SqlTransaction = SqlConnection.BeginTransac
SqlCommand.Transaction = sqlTransaction
SqlCommand.ExecuteNonQuery
sqlTransaction.Commit()
SqlConnection.Close()
End Sub
my Datagrid has as name Datagrid1
Thanks,
JSCTECHY
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thank you so much
Hi, you can use a dataadapter with your command and then fill a datatable with the result:
Dim SqlConnection As SqlConnection = New SqlConnection("Server=OROZ
Dim SqlCommand As SqlCommand = New SqlCommand("Test", SqlConnection)
SqlCommand.CommandType = CommandType.StoredProcedur
Dim sqlparameter1 As SqlParameter = New SqlParameter("@ID", SqlDbType.NChar)
sqlparameter1.Direction = ParameterDirection.Input
sqlparameter1.Value = txtOrder.Text
SqlCommand.Parameters.Add(
' New code:
Dim MyDataAdapter As New SqlClient.SqlDataAdapter
MyDataAdapter.SelectComman
Dim MyDataTable As New DataTable
MyDataAdapter.Fill(MyDataT
DataGridView1.DataSource = MyDataTable
Dim SqlConnection As SqlConnection = New SqlConnection("Server=OROZ
Dim SqlCommand As SqlCommand = New SqlCommand("Test", SqlConnection)
SqlCommand.CommandType = CommandType.StoredProcedur
Dim sqlparameter1 As SqlParameter = New SqlParameter("@ID", SqlDbType.NChar)
sqlparameter1.Direction = ParameterDirection.Input
sqlparameter1.Value = txtOrder.Text
SqlCommand.Parameters.Add(
' New code:
Dim MyDataAdapter As New SqlClient.SqlDataAdapter
MyDataAdapter.SelectComman
Dim MyDataTable As New DataTable
MyDataAdapter.Fill(MyDataT
DataGridView1.DataSource = MyDataTable
... note to self - always refresh question before answering ... :-)
ASKER
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=OROZ
'invoke the store procedure
Dim SqlCommand As SqlCommand = New SqlCommand("Test", SqlConnection)
SqlCommand.CommandType = CommandType.StoredProcedur
'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(
'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