Question About ADO.NET Row Position..

[melegant]

ADO.NET

i love you, and I hate you. (but i am happy to have found this forums)

 

I am writing a routine to take autoincrement tables from access 2000 and move them into SQL.

 

I am filling a DATATABLE with the custinfo table (using OLE)

I am sorting the data on column CUSTID (which is the autoincrement field in ACCESS)

 

OLEadapter.fill(datatable) ' custinf table

 

Then, i am looping through the rows and checking for gaps etc etc.

 

when I hit customer id # 1499 (the value in the column 'custid') i am at row position 896

 

the next row position in the datatable puts me a customer id # 1503..skipping 2 rows (there is no 1500), i am at row position 897

 

i found the two missing rows, 1501 and 1502 AT THE END of the datatable, in positions 915 and 916 (there are 916 rows returned by my query)

 

WHAT THE HECK IS THAT all about? THANKS!

[Nerseus]

Silly question, but can you just use DTS in SQL Server? It was made to import tables and data from other databases. It even allows you to write some VBScript in between the process if you need to do something other than just copy data and tables...

 

As for your ADO.NET problem:

First, do you have an identity column on SQL Server? Are you saying that it looks like the Access table isn't being read in the right order and that your subsequent INSERTs into SQL Server aren't in the right order?

 

How are you sorting your Access data, with an ORDER BY or by using a DataView with a sort order?

 

Set a breakpoint to go off when you hit these rows (1499) and step through, seeing what values are getting passed to the SQL Server insert. Hopefully you can debug it that way.

 

-nerseus

[melegant]

Thanks for the reply..

 

So, to answer your questions yes, I could use DTS..however I thought it would be an interesting project to write something like this to help further my understanding of ADO.NET.

 

Now,

Yes I do have a custid column with an IDENTITY (1,1) property on SQL, however the problem so far has been just the data from my OLE source (access). The problem arises well before I get to the insert.

 

I tried no sort, (custid is the primary key in my access table), i tried using the table.select() method as well.

 

I put a break in and watched as i looped through. everything works as inteneded. ADO.NET is simply putting those two rows on the end.

 

I will post the whole code when i get into the office in the morning..

[Nerseus]

Assuming your sort is correct, I can't explain why the DataSet wouldn't bring them back in the right order... I'm curious to see what you find out.

 

Is this an app and database that you could upload for us to try out?

 

-nerseus

[melegant]

Here is the class I am writing..I will say that I tried this morning by adding an ORDER BY to my SELECT statement..and that fixed the problem..however I don't understand why any other method I tried gave me 914 out of 916 rows in order?

 

 

Public Class DataManip
   'ACCESS
   Private OLEconstring As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\temp\contracts.mdb;"
   Private OLEcn As New OleDb.OleDbConnection(OLEconstring)
   Private OLEdr As OleDb.OleDbDataReader
   Private OLEda As OleDb.OleDbDataAdapter
   Private OLEdt As DataTable
   Private OLEds As DataSet
   Private OLEdrw As DataRow
   'SQL
       Private conString As String = "Database=Contracts; data source=ACIWS411_B5;Integrated Security=SSPI"
   Private SQLcn As New SqlClient.SqlConnection(conString)
   Private SQLda As SqlClient.SqlDataAdapter
   Private SQLdt As DataTable
   Private SQLds As DataSet
   Private SQLdr As SqlClient.SqlDataReader
   Private SQLdrw As DataRow
   Private myDataSet As DataSet

   Private myOLECmd As String
   Private mySQLCmd As String

   Property OLECmdText() As String
       Get
           Return myOLECmd
       End Get
       Set(ByVal Value As String)
           myOLECmd = Value
       End Set
   End Property

   Property SQLCmdText() As String
       Get
           Return mySQLCmd
       End Get
       Set(ByVal Value As String)
           mySQLCmd = Value
       End Set
   End Property


   Public Sub CustImp()

       OLEda = New OleDb.OleDbDataAdapter(myOLECmd, OLEcn)
       OLEds = New DataSet()
       OLEcn.Open()
       OLEda.Fill(OLEds, "Subs") ' fill DataSet
       OLEdt = OLEds.Tables("Subs") ' setup Table
       OLEdt.Select("", "custid", DataViewRowState.CurrentRows)

       If OLEdt.Rows.Count = 0 Then
           OLEcn.Close()
           GoTo ending
       End If

       SQLda = New SqlClient.SqlDataAdapter(mySQLCmd, SQLcn)
       SQLcn.Open()

       SQLdt = New DataTable()
       SQLda.Fill(SQLdt)

       Dim Dtcount As Integer
       Dim diff As Integer
       Dim prevnum As Integer = 0
       Dim currrow As Integer = 0
       Dim test As String
       Dim SQLcbDEL As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(SQLda)

       Do Until currrow = OLEdt.Rows.Count
          
           If currrow = 896 Then
               OLEdrw = OLEdt.Rows.Item(currrow)
               diff = Convert.ToInt32(OLEdrw.Item("custid"))
           End If

           OLEdrw = OLEdt.Rows.Item(currrow)
           diff = Convert.ToInt32(OLEdrw.Item("custid")) - prevnum
           Do Until diff = 0
               If diff = 1 Then
                   SQLdrw = SQLdt.NewRow()
                   SQLdt.Rows.Add(SQLdrw)
                   test = OLEdrw.Item("cname")
                   SQLdrw("cname") = OLEdrw.Item("cname")
                   prevnum = Convert.ToInt32(OLEdrw.Item("custid"))
                   Dtcount = Dtcount + 1
                   diff = diff - 1
                   currrow = currrow + 1
               Else
                   Do Until diff = 1
                       SQLdrw = SQLdt.NewRow
                       SQLdt.Rows.Add(SQLdrw)
                       SQLdrw("cname") = "BOGUS"
                       diff = diff - 1
                   Loop
                   prevnum = Convert.ToInt32(OLEdrw.Item("custid"))
               End If
           Loop
       Loop
       SQLda.Update(SQLdt)
Ending:

   End Sub


   Public Sub CustDel()
       SQLda = New SqlClient.SqlDataAdapter(mySQLCmd, SQLcn)
       SQLdt = New DataTable()
       SQLcn.Open()
       SQLda.Fill(SQLdt)

       Dim Dtcount As Integer

       For Dtcount = 0 To SQLdt.Rows.Count - 1
           SQLdt.Rows(Dtcount).Delete()
       Next
       Dim SQLcb As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(SQLda)
       SQLda.Update(SQLdt)

   End Sub
End Class

[Nerseus]

Whew - I would hope the sorting works :)

 

I don't know about Access, but in SQL Server a primary key doesn't necessarily mean that a select on that table will come back in that order. The only ways I know of to guarantee a sort is to issue an ORDER BY or to define a clustered index (which doesn't have to be on the Primary Key column). You should *never* make assumptions about the ordering of data. Also, using a code-based sort should always work (a DataView with a sort, or a DataTable.Select command with a sort).

 

Another bad SQL practice I see a lot is people assuming column orders on an INSERT (where they use "INSERT INTO table1 VALUES (...)". You should always specify the columns you want to insert, such as "INSERT INTO table1 (col1, col2) VALUES(...)".

 

-nerseus

[melegant]

Thanks Nerseus...

 

You are 100% right about not assuming that the rows come back in order..which is why I opted to use the datatable.select method..however when calling for the records without a sort, it put them in order ., save for those two rows (which wrongly led me to believe that an indexed access field comes in order :D )

 

Again this was more of an experiment to help me get used to ADO.NET (which it well has)..it's pitfalls and it's highlights.

 

The final solution was to use an ORDER BY...however the Datable.Select method did NOT work...

 

Ah well, on to my next experiment...getting around this pesky ASPNET local account issue...