none
Trouble with TableAdapter.update() RRS feed

  • 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

     

     

    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)

    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 Sad

     

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

    ~Ghazanfar

    Thursday, May 29, 2008 7:43 PM

All replies

  • You do not need to use TableAdapter/DataAdapter to insert records into database and to check if record exists in your database you should use EXISTS statement, so your SQL batch would look like

     

    IF NOT EXISTS (SELECT * FROM lbf_COKEnCOLE WHERE ORIGINATOR_ID = @ORIGINATOR_ID AND SMPL_DTE = @SMPL_DTE AND LAB_NUM = @LAB_NUM)
     INSERT INTO  ...


    Then you execute that statement using ExecuteNonQuery method of SqlCommand class. Using SqlComamnd will be clear way to execute action SQL statements and removes overhead associated with the TableAdapter/DataAdapter

     

    Saturday, May 31, 2008 1:30 AM
    Moderator
  • Hi VMazur,

    I read somewhere in another thread that having table adapters and SqlCommand connecting to a database may cause a bunch of errors, probably because you cano only have one datareader assciated with a database at a time - and TableAdapters have an implicit dataReader. If this is true, then using SqlCommand would be problematic for me. all my inserts and deletes are done using tableadapters so far (I have more than one excel sheet tpo transfer). It is only with the Update method that I have trouble with.
    Have you heard anything about the possible issue with using SqlCommand and tableAdapters simultaneously?

    The sql batch you wrote was definately helpful. I dont have much experience with it and I didnt realise this would be the way to doit if I did use SqlCommand instead.
    Is there a "else if" part to this "IF NOT EXISTS"?  I mean, when the IF NOT EXISTS returns falls (ie exists) can I write an update query?

    Thanks for your help!


    Ghazanfar
    Saturday, May 31, 2008 10:31 PM
  • TableAdapter would use DataReader internally only when DataTable is loaded from the database, after that reader will be closed and will not affect other command against same SqlConnetion. But, I would not worry about the reader in your case, since TableAdapter would use separate connection from the command that will issue UPDATE (or other) action queries and it will work fine.

     

    Tuesday, June 17, 2008 10:02 AM
    Moderator