Fiesty DataSets and Unyeilding tableAdapters

Question

Hi all.

I have written a simple application in vb.net (2.0) that reads data from an excel sheet (previously stored in focus databases) and stores it into a sql server database. I am using typed datasets and table adapters to store the data. I have an "Import" button whose event handler does the following in turn.

1. connect to the excel sheet through an ole db conn

2. read the contents in a while loop using the executeReader() method

3. store the values in variables where necessary conversions and validations are performed.

4. call an insert method in the associated dataset tableAdapter that stores these processed values in the sql db.

This is all working as it should. Now I need to check for existing records before I import the values - Ignore completely if the value is found.

According to msdn, I should be able to use my tableAdapters update method by passing a dataSet/Table/Row(s). So this is how I tried to tackle it.

Legend:

lbf = my DataSet

lbf_COKEnCOLE = the Database table associated with lbf DataSet

GetRecordByIdDateLab = runs the following SQL Query on the data

SELECT *

FROM lbf_COKEnCOLE

WHERE (ORIGINATOR_ID = @ORIGINATOR_ID) AND (SMPL_DTE = @SMPL_DTE) AND (LAB_NUM = @LAB_NUM)

(The actual sql refers to all the columns by names instead of using *)

'Code starts here

Dim tableadapter As New lbfTableAdapters.lbf_COKEnCOLETableAdapter

Dim dataTable As lbf.lbf_COKEnCOLEDataTable = Nothing

'check for existing record by using GetRecordByIdDateLab method

dataTable = tableadapter.GetRecordByIdDateLab(ORIGINATOR_ID, SMPL_DTE, LAB_NUM)

If Not dataTable Is Nothing Then

If dataTable.Rows.Count > 0 Then

If Not dataTable(0).ORIGINATOR_ID = Nothing Then

'Row = dataTable(0)

dataTable(0).TURN = TURN

dataTable(0).SMPL_ANALYS_HOUR = SMPL_ANALYS_HOUR

dataTable(0).SMPL_ANALYS_MIN = SMPL_ANALYS_MIN

dataTable(0).MOISTURE = MOISTURE

dataTable(0).S = S

dataTable(0).VM = VM

dataTable(0).ASH = ASH

dataTable(0).HARDNESS = HARDNESS

dataTable(0).STABILITY = STABILITY

dataTable(0).QRT_TUMBLE = TUMBLE_30M

dataTable(0).APTSPC_GRAV = APTSPC_GRAV

dataTable(0).FREE_SWL_I = FREE_SWL_I

dataTable(0).PULV8TH = PULV8TH

dataTable(0).QRT_PULV = QRT_PULV

dataTable(0).SCR_4 = SCR_4

dataTable(0).SCR_3 = SCR_3

dataTable(0).SCR_2 = SCR_2

dataTable(0).SCR_1NHALF = SCR_1NHALF

dataTable(0).SCR_1 = SCR_1

dataTable(0).SCR_3QRT = SCR_3QRT

dataTable(0).SCR_HALF = SCR_HALF

dataTable(0).SCR_38THS = SCR_38THS

dataTable(0).SCR_QRT = SCR_QRT

dataTable(0).QRT_PULV = QRT_PULV

dataTable(0).SCR_8TH = SCR_8TH

dataTable(0).SCR_20M = SCR_20M

dataTable(0).SCR_30M = SCR_30M

dataTable(0).SCR_50M = SCR_50M

dataTable(0).SCR_100M = SCR_100M

dataTable(0).SCR_PAN = SCR_PAN

dataTable(0).SCR_QRT_PLUS = SCR_QRT_PLUS

dataTable(0).SAMPL_LOC = SAMPL_LOC

 

tableadapter.Update(dataTable) '<<THIS IS WHERE IT CRASHES WITH "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

End If

 

 

'The following tableAdapter works perfectly whenever the condition is true (for new entries that is)

ElseIf dataTable.Rows.Count = 0 Then

tableadapter.InsertQuery(counter, ORIGINATOR_ID, SMPL_DTE, LAB_NUM, TURN, SMPL_ANALYS_HOUR, SMPL_ANALYS_MIN, _

MOIS_GRAV, MOISTURE, S, VM, ASH, HARDNESS, STABILITY, QRT_TUMBLE, TUMBLE_30M, APTSPC_GRAV, _

FREE_SWL_I, PULV8TH, QRT_PULV, SCR_4, SCR_3, SCR_2, SCR_1NHALF, SCR_1, SCR_3QRT, SCR_HALF, _

SCR_38THS, SCR_QRT, SCR_8TH, SCR_20M, SCR_30M, SCR_50M, SCR_100M, SCR_PAN, SCR_QRT_PLUS, SAMPL_LOC)

End If

End If

 

****************************************

More details:

I have DataTable under "Watch" and the value for it shows up as "dataTable has not been declared". This ofcourse is misleading because it is, plus intellisense picks it up where I'm updating the column values by referencing them with dataTable(0).columnName

I have been wondering if

dataTable = tableadapter.GetRecordByIdDateLab(ORIGINATOR_ID, SMPL_DTE, LAB_NUM)

does what its supposed to do, ie. return a dataTable with the matching record. I tried to recieve the data in a row but it only allowed it to be saved in a datatable instantiated in this manner. Furthermore, when I read the values read into the datatable, each column value shows up only as lbf_COKEnCOLE.column (something to this effect) in curly braces. Almost everything else thats returned, like index values and what not that I suppose is used to keep the schema of the table has errors in it.

Any suggestions and help would be greatly appreciated!! I am trying to avoid suddenly using command objects when I'm doing everything else through a data Access layer. lastly, I have read and reread this msdn page

http://msdn.microsoft.com/en-us/library/ms233819(VS.80).aspx

and am trying to do everything as shown here, obviously to no avail :(

 

All .net people out there, if you would help this awkward programmer, it would be greatly appreciated! Thanks

~Ghazanfar