import excel to sql server but the data in sql server was not sorted ?????

[kank]

My excel data are sorted but why when I import into sql server, they are not sorted :-( why???? Pls help

 Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Book2.xlsx; Extended Properties=""Excel 12.0 Xml; HDR=Yes""")
        ExcelConnection.Open()

        Dim expr As String = "SELECT * FROM [Sheet1$] order by month_year, emp_id"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader


        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlconn)
            bulkCopy.DestinationTableName = "tblPayroll_prev"

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                sqlconn.Close()
                GetData_prev(da2.SelectCommand.CommandText)
                sqlconn.Close()
                MessageBox.Show("Import successful for " + mnth + "!")
            Catch ex As Exception
                MsgBox(ex.ToString)
                sqlconn.Close()
            End Try
        End Using

Thanks alot

 

[kank]

sorry, use this instead.


Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Book2.xlsx; Extended Properties=""Excel 12.0 Xml; HDR=Yes""")
ExcelConnection.Open()

Dim expr As String = "SELECT * FROM [Sheet1$] "
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objDR As OleDbDataReader


Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlconn)
bulkCopy.DestinationTableName = "tblPayroll_prev"

Try
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
objDR.Close()
sqlconn.Close()
GetData_prev(da2.SelectCommand.CommandText)
sqlconn.Close()
MessageBox.Show("Import successful for " + mnth + "!")
Catch ex As Exception
MsgBox(ex.ToString)
sqlconn.Close()
End Try
End Using

 

[nK0de]

Well, you haven't used the ORDER BY clause in your SQL query..

also don't forget to wrap your code around code tags..

 

[cjard]

Unless you specify ORDER BY in the query, SQL Server will return results in whatever order it feels like (probably the order they are on disk) - you have no control over that. Use ORDER BY