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 isnt being read in the right order and that your subsequent INSERTs into SQL Server arent 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 cant explain why the DataSet wouldnt bring them back in the right order... Im 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 dont 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 dont know about Access, but in SQL Server a primary key doesnt 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 doesnt 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 )

Again this was more of an experiment to help me get used to ADO.NET (which it well has)..its pitfalls and its 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...