Creating an sql adapter,datatable and populating the fields of a grid

[mcupryk]

I have the following stored proc:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


ALTER PROCEDURE dbo.stp_SelTransOverrides

-- SELECT ALL EIS TRANSACTION PROCESSING OVERRIDE RECORDS
/*******************************************************************************
*** Name: dbo.stp_SelTransOverrides
*** Creation Date: 08/17/2005
*** Author: Mathieu Cupryk
*** Modified:
***
*** Input: none
*** Output: ALL Transaction Override Records
*** Order: Transaction_Eff_Date
*** Database: CCFinSol
*** Purpose: Select all TransactionOverride Records
***
*** Copyright: @2005 - Corporate Customer, Zurich N.A., all rights reserved
********************************************************************************/

AS
BEGIN

SELECT dbo.AcctNames.Acct_Name AS [Account Name], dbo.TransOverride.Policy_Nbr AS [Policy Number],
dbo.TransOverride.Trans_CodeOrig AS [Original Transaction Code], dbo.TransOverride.Trans_Eff_Date AS [Trans. Eff. Date],
dbo.TransOverride.Override_Code AS [Override Action], dbo.TransOverride.NR_CodeOvr AS [Override New/Renewal],
dbo.TransOverride.Trans_CodeOvr AS [Override Transaction Code], dbo.TransOverride.CreatedUID AS UserId, dbo.TransOverride.ModifiedDate AS Date
FROM dbo.AcctIDPolicyX INNER JOIN dbo.AcctNames ON dbo.AcctIDPolicyX.Acct_Id = dbo.AcctNames.Acct_Id INNER JOIN dbo.TransOverride ON dbo.AcctIDPolicyX.Policy_nbr = dbo.TransOverride.Policy_Nbr
ORDER BY dbo.TransOverride.Trans_Eff_Date
RETURN 0
END


GO

I must populate a grid.

Can someone start me off.
Case clsUtilityTableDatAdap.UtilityTableID.TransOverride
_UtilityTableName = "Transaction Override"
_UtilityTableDap = clsUtilityTableDatAdap.GetTransOverrideDap(_cnnFinSol)
_UtilityTableDap.Fill(_UtilityTableDset)
_UtilityTableDset.Tables(0).PrimaryKey = _
New DataColumn() {_UtilityTableDset.Tables(0).Columns("Policy_Nbr"), _UtilityTableDset.Tables(0).Columns("Trans_CodeOrig"), _UtilityTableDset.Tables(0).Columns("Trans_Eff_Date")}


The I am not the table is structured

CREATE TABLE [TransOverride] (
[Policy_Nbr] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Trans_CodeOrig] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Trans_Eff_Date] [datetime] NOT NULL ,
[Override_Code] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NR_CodeOvr] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Trans_CodeOvr] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF__TransOver__Creat__70A8B9AE] DEFAULT (getdate()),
[CreatedUID] [nvarchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF__TransOver__Modif__719CDDE7] DEFAULT (getdate()),
[ModifiedUID] [nvarchar] (48) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Plan_Year] [int] NOT NULL CONSTRAINT [DF__TransOver__Plan___56757D0D] DEFAULT (2004),
CONSTRAINT [cnTransOverride_PK] PRIMARY KEY CLUSTERED
(
[Policy_Nbr],
[Trans_CodeOrig],
[Trans_Eff_Date]
) ON [PRIMARY] ,
CONSTRAINT [cnTransOverride_NR_CodeOvr_CK] CHECK ([NR_CodeOvr] = '' or ([NR_CodeOvr] = 'R' or [NR_CodeOvr] = 'N')),
CONSTRAINT [cnTransOverride_Override_Code_CK] CHECK ([Override_Code] = '' or ([Override_Code] = 'OT' or ([Override_Code] = 'AU' or ([Override_Code] = 'MS' or ([Override_Code] = 'EN' or ([Override_Code] = 'AO' or [Override_Code] = 'AC'))))))
) ON [PRIMARY]
GO

I need to get started building the dataset and the sqladapter and to be able to populate the grid.


Any Help or Good Suggestions would be so much appreciated.

Thank You.
Matt

[Asgorath]

Try something like this...

Option Strict On
Option Explicit On 
 
Imports System.Data
Imports System.Data.SqlClient

Dim con As New SqlConnection
        Dim ds As New DataSet
        Dim cn As String = "packet size=32767;integrated security=SSPI;data source=YourServerName;persist security info=False;initial catalog=CCFinSol"
        Dim ap As New SqlDataAdapter
 
        con.ConnectionString = cn
        con.Open()
        ap.SelectCommand.Connection = con
        ap.SelectCommand.CommandType = CommandType.StoredProcedure
        ap.SelectCommand.CommandText = "stp_SelTransOverrides"
        ap.Fill(ds, "YourTableName")
        Me.DataGrid1.DataSource = ds.Tables("YourTableName")
        Me.DataGrid1.ReadOnly = True

Regards
Jorge