Easy peasy: How do I create an untyped dataset to match a table in SQL

[jwarmuth]

Let's say I have a simple two column table in SQL (2000).

First column is the primary key(int) and the second column is just a text column.

What would the syntax to create this untyped dataset in a vb app look like? I ask because I'm having trouble getting one of my untyped datasets 'updating' it's partner in an SQL db.

I believe it is due to how I am creating and configuring my untyped dataset. Specifically, the setting of the primary key.

If I could get a good example of how to accomplish this, I'm sure I can work through this issue. TIA.

Jeff W.
MCSE, CNE

 

[chrissie1]

why not give us your code?

dim contemp as new sqlconnection
dim comtemp as new sqlcommand
dim adptemp as new sqldatatdapter
dim dttemp as new datatable
dim cbutemp as new sqlcommandbuilder

contemp.connectionstring = 'connectionstring here
contemp.open
comtemp.connection = contemp
comtemp.commandtext = "select field1,field2 from table1"
adptemp.selectcommand = comtemp
adptemp.fill(dttemp)
adptemp.fillschema(dttemp,source)
cbutemp.dataadapter = adptemp
adptemp.deletecommand = cbutemp.getdeletecommand
adptemp.insertcommand = cbutemp.getinsertcommand
adptemp.updatecommand = cbutemp.getupdatecommand

or something like that.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[jwarmuth]

It's hard to explain because it's kind complicated, but I'll give it a try.

What I'm doing is bringing in XML data, formatting for my purposes, adding it to an untyped dataset, which is to then merge with a typed dataset I've created from my existing SQL database.

The code I use for creating the dataset is:

'create untyped dataset and table
                Dim ds_formattedXML As DataSet = New DataSet
                Dim dt_formattedXML As DataTable = New DataTable("StocksDaily")
                ds_formattedXML.Tables.Add(dt_formattedXML)

                'define table column types
                
                Dim dcArray() As DataColumn = New DataColumn() {dt_formattedXML.Columns("stkD_ID")}
                
                dt_formattedXML.PrimaryKey = dcArray
                'dt_formattedXML.Columns.Add(dcArray, Type.GetType("System.Decimal"))

                'dt_formattedXML.Columns.Add("stkD_ID", Type.GetType("System.Decimal"))

                dt_formattedXML.Columns.Add("stkD_symbol", Type.GetType("System.String"))
                dt_formattedXML.Columns.Add("stkD_last", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_datetime", Type.GetType("System.DateTime"))
                dt_formattedXML.Columns.Add("stkD_change", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_open", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_high", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_low", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_volume", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_mktcap", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_previousClose", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_percentageChange", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_annRangeUpper", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_annRangeLower", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_earns", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_pe", Type.GetType("System.Decimal"))
                dt_formattedXML.Columns.Add("stkD_name", Type.GetType("System.String"))

                'create the datarow 
                Dim dr_formattedXML As DataRow = dt_formattedXML.NewRow()

                'Fill the datarow with values and add it to the table
                dr_formattedXML("stkD_symbol") = symbolValue
                dr_formattedXML("stkD_last") = lastValue
                'combine date and time variables
                Dim tempDatetime As String = dateValue & " " & timeValue
                dr_formattedXML("stkD_datetime") = tempDatetime
                dr_formattedXML("stkD_change") = changeValue
                dr_formattedXML("stkD_open") = openValue
                dr_formattedXML("stkD_high") = highValue
                dr_formattedXML("stkD_low") = lowValue
                dr_formattedXML("stkD_volume") = volumeValue
                dr_formattedXML("stkD_mktcap") = mktcapValue
                dr_formattedXML("stkD_previousClose") = previousCloseValue
                dr_formattedXML("stkD_percentageChange") = percentageChangeValue
                dr_formattedXML("stkD_annRangeUpper") = annRangeUpperValue
                dr_formattedXML("stkD_annRangeLower") = annRangeLowerValue
                dr_formattedXML("stkD_earns") = earnsValue
                dr_formattedXML("stkD_pe") = peValue
                dr_formattedXML("stkD_name") = nameValue

                dt_formattedXML.Rows.Add(dr_formattedXML)

This portion works fine, I've tested it, it's good.

Now that code above is inside a function that returns the newly compiled dataset.

Here is a screenshot of the .xsd of the typed dataset it is being matched up to:

So when I try to merge the two datasets, somehow they become misaligned triggering an error. Here's the merge code:

'create untyped dataset for returned XML data for transfer into typed dataset
            Dim ds_stockRecord As New DataSet
            Dim ds_stockRecord2 As New DataSet

            Dim ii As Integer
            For ii = 0 To sht_numberOfStocks - 1
                quoteToXMLFile(arr_stocksList(ii))
                ds_stockRecord = formatXMLtoDataset(tempXMLFileLoc)
                ds_stockRecord2.Merge(ds_stockRecord, True, MissingSchemaAction.AddWithKey)
            Next ii

           'merge datasets
           Ds_stocksDaily2.Merge(ds_stockRecord2)

It errors at that last merge call returning:

"System.ArgumentException: Parameter value '162700000000' is out of range.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at Phaeton.MainForm.OnLoad(EventArgs e) in E:\Personal...."

Here's a screen shot of the ds_stocksDaily2 dataset binded to a datagrid; notice how the column 'stkD_mktCap' is somehow buggered and placed at the end, instead of being in the middle?

I can't figure out why/where the datasets get confused.

Does any of this make any sense?!!!

Jeff W.
MCSE, CNE

 

[jwarmuth]

*bump*

Jeff W.
MCSE, CNE

 

[chrissie1]

I think that the value 1627.. is a bit big for the decimal type you gave to it. what happens if these values are all 0?

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting