How can I store the sqlserver data types to enable me to build parameters later on?

[Moorzee]

I have a datatable that I load from a datareader. How can I store the sqlserver data types to enable me to build parameters later on? At current I get system data types i.e system.Int32 etc...... Or can I easily map system data types to sqlserver datatypes???????

I am using ComponentOne tdbgrid to store records and have attached the code.

Many thanks for any input.

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] PopulateGrid([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] SPName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] TableName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]'Set up the grid and display the data
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intCurrentRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intCount [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objCol [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] C1.Win.C1TrueDBGrid.C1DataColumn
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intCurrentCol [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] grdOddStyle [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] C1.Win.C1TrueDBGrid.Style
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] grdEvenStyle [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] C1.Win.C1TrueDBGrid.Style
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] drDatReader [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlDataReader
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dtDatTable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objDatCol [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataColumn
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] columns [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataColumnCollection = dtDatTable.Columns
[/SIZE][SIZE=2][COLOR=#008000]'Dim dsDatSet As New DataSet()
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmdCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand(SPName)
[/SIZE][SIZE=2][COLOR=#008000]'Dim adpDatAdapt As New SqlClient.SqlDataAdapter(cmdCommand)
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'set the odd/even style properties for the grid
[/COLOR][/SIZE][SIZE=2]grdOddStyle.BackColor = Color.AliceBlue
grdEvenStyle.BackColor = Color.Silver
[/SIZE][SIZE=2][COLOR=#008000]'open datareader
[/COLOR][/SIZE][SIZE=2]cmdCommand.Connection = frmMain.objNoahDB.DBConnection
drDatReader = cmdCommand.ExecuteReader
[/SIZE][SIZE=2][COLOR=#008000]'Alternate colour every row
[/COLOR][/SIZE][SIZE=2]grdRecords.EvenRowStyle = grdEvenStyle
grdRecords.OddRowStyle = grdOddStyle
dtDatTable.Load(drDatReader)
[/SIZE][SIZE=2][COLOR=#008000]'Bind data to grid
[/COLOR][/SIZE][SIZE=2]grdRecords.DataSource = dtDatTable
[/SIZE][SIZE=2][COLOR=#008000]'Close the reader object
[/COLOR][/SIZE][SIZE=2]drDatReader.Close()
[/SIZE][SIZE=2][COLOR=#008000]'Store the data type for sorting etc later on*****************PROBLEM HERE
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] objDatCol [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] columns
grdRecords.Columns(objDatCol.Ordinal).Tag = Mid(objDatCol.DataType.ToString, 8)
[/SIZE][SIZE=2][COLOR=#008000]'objCol.Tag = drDatReader.GetDataTypeName(intCurrentCol).ToString
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'objCol.Tag = dtDatTable.
[/COLOR][/SIZE][SIZE=2]intCurrentCol += 1
[/SIZE][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]'***********************PROBLEM END 
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Alter grid caption
[/COLOR][/SIZE][SIZE=2]grdRecords.Caption = grdRecords.RowCount & [/SIZE][SIZE=2][COLOR=#800000]" Records"
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Set up headings/widths etc
[/COLOR][/SIZE][SIZE=2]SetUpGrid()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MessageBox.Show([/SIZE][SIZE=2][COLOR=#800000]"Error whilst populating grid for "[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Tag & [/SIZE][SIZE=2][COLOR=#800000]" form."[/COLOR][/SIZE][SIZE=2] & vbNewLine _
& ex.Message, [/SIZE][SIZE=2][COLOR=#800000]"Error"[/COLOR][/SIZE][SIZE=2], MessageBoxButtons.OK, MessageBoxIcon.Error)
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][SIZE=2] drDatReader [/SIZE][SIZE=2][COLOR=#0000ff]Is[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]drDatReader.Dispose()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

 

[cjard]

in .net2 you would tell the table adapter about the stored procedure you intend to run a query aagainst, and it will get the parameters from the db for you.. if its a dynamic sql, then you might not have to supply type information.. i know with some of my dynamic queries that oracle doesnt require the data type, just the name of the parameter and its value..

 

[Moorzee]

Table adapters I reckon I best go have a look!!! (Never used one!)

You are a busy geeza cj. Nearly every data prog query answered in the space of minutes .

Cheers.