sql to excel

Try following line to change from:
Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter
To
Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter

way ahead of you guys and I done that but I get this error now.

The SelectCommand property has not been initialized before calling 'Fill'

Hi sevensnake77;

You need to remove this line of code:

Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter

And add it to this section of code passing in the command object:

Dim command As New OleDbCommand(sqlStr, objConn)
Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter(command)
objConn.Open()

Fernando

Ok I made some modifications why I was out. but I get a different error.

Value cannot be null.
Parameter name: dataSet

dataAdapter.Fill(dataSet)

Dim dirInfo As New DirectoryInfo(Server.MapPath("\store\Webspecials")) ' + StoreID
        Dim myReader As System.Data.OleDb.OleDbDataReader
        Dim FileInfo = "webspecials.xls"
        Dim dataSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim objConn As System.Data.OleDb.OleDbConnection
        'Dim sqlStr As String = "SELECT ProductID, Webdealunit, Price, DateBegindelivery,TimeLeftDate,KrasdaleItem,DealCost, DealUnit,PalletQty,UnitCost,  Description, MaxOrderqty, Trailer,TotalCasesava, Size, Pack, casecost, PalletCost, Caseqty, TrailerQty, DateEnddelivery, ImagePath  FROM webspecials$ WHERE OnCatalogPromotion = 1 And  TimeLeftDate > getDate()", extendedString As String
        objConn = New System.Data.OleDb.OleDbConnection( _
              "provider=Microsoft.Jet.OLEDB.4.0; " & _
              "data source=" & Server.MapPath("\store\webspecials\" & "\" & FileInfo) & "; " & _
              "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";")
       
        objConn.Open()
        Dim strSql As String = "SELECT Product.ProductID, Product.Webdealunit, Product.Price, Product.DateBegindelivery, Product.TimeLeftDate,Product.Item,Product.DealCost, Product.DealUnit,Product.PalletQty,Product.UnitCost,  Product.Description, Product.MaxOrderqty, Product.Trailer,Product.TotalCasesava, Product.Size, Product.Pack, Product.casecost, Product.PalletCost, Product.Caseqty, Product.TrailerQty, Product.DateEnddelivery, Product.ImagePath  FROM Product$ WHERE Product.OnCatalogPromotion = 1 And  Product.TimeLeftDate > getDate()"
        Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter(strSql, objConn)
 
       

Select allOpen in new window

scratch last

This is the error I am getting

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).


dataAdapter.Fill(dataSet)

Post the code and the location of the error.

Sorry guys I had a really long day, I know we all been there where you mind is not on work. To many outside distractions.  I am going to look over this and I will get back to you. Thanks

Ok Fernando I tried it your way and I am recieving and error,  
IErrorInfo.GetDescription failed with E_FAIL(0x80004005).


Line 292:            dataAdapter.Fill(dataSet)

 Dim dirInfo As New DirectoryInfo(Server.MapPath("\store\Webspecials")) ' + StoreID
        Dim myReader As System.Data.OleDb.OleDbDataReader
        Dim FileInfo = "webspecials.xls"
        Dim dataSet As New DataSet
 
        Dim MyCommand As New System.Data.OleDb.OleDbDataAdapter
        Dim objConn As System.Data.OleDb.OleDbConnection
        'Dim sqlStr As String = "SELECT ProductID, Webdealunit, Price, DateBegindelivery,TimeLeftDate,KrasdaleItem,DealCost, DealUnit,PalletQty,UnitCost,  Description, MaxOrderqty, Trailer,TotalCasesava, Size, Pack, casecost, PalletCost, Caseqty, TrailerQty, DateEnddelivery, ImagePath  FROM webspecials$ WHERE OnCatalogPromotion = 1 And  TimeLeftDate > getDate()", extendedString As String
        objConn = New System.Data.OleDb.OleDbConnection( _
              "provider=Microsoft.Jet.OLEDB.4.0; " & _
              "data source=" & Server.MapPath("\store\webspecials\" & "\" & FileInfo) & "; " & _
              "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";")
        Dim strSql As String = "SELECT ProductID, Webdealunit, Price, DateBegindelivery, TimeLeftDate,Item,DealCost, DealUnit,PalletQty,UnitCost,  Description, MaxOrderqty, Trailer,TotalCasesava, Size, Pack, casecost, PalletCost, Caseqty, TrailerQty, DateEnddelivery, ImagePath  FROM [Product$] WHERE   TimeLeftDate > getDate()"
        Dim command As New OleDbCommand(strSql, objConn)
        Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter(command)
        '   Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter(strSql, objConn)
 
        objConn.Open()
       
        If Not Page.IsPostBack() Then
            dataAdapter.Fill(dataSet)
            lblRecordCount.Text = CStr(dataSet.Tables(0).Rows.Count) - 1
            dataSet = Nothing
            dataSet = New DataSet
        End If
        dataAdapter.Fill(dataSet, CInt(lblCurrentIndex.Text), CInt(lblPageSize.Text), "Product")
        list.DataSource = dataSet.Tables("Product")
        ' list.DataBind()
        Dim sMsg As String
        sMsg = "<script>" & vbCrLf & "var maxTimer = " & lblRecordCount.Text & ";" & vbCrLf & "var nullTimers = new Array();" & vbCrLf & "function GetTimeTest( millisec ) {" & vbCrLf & "function D2( val ) {" & vbCrLf & "return ( val < 10) ? '0' + val : val}" & vbCrLf & "var sec     = Math.floor( millisec / 1000 )" & vbCrLf & "var days    = Math.floor( sec / 86400 )" & vbCrLf & "sec -= days * 86400" & vbCrLf & "var hours   = Math.floor( sec / 3600 )" & vbCrLf & "sec -= hours * 3600" & vbCrLf & "var minutes = Math.floor( sec / 60 )" & vbCrLf & "sec -= minutes * 60" & vbCrLf & "return days + ' days ' + D2( hours ) + ':' + D2( minutes ) + ':' + D2( sec )" & vbCrLf & "}" & vbCrLf & "function CountDown()" & vbCrLf & "{" & vbCrLf & "var now   = new Date()" & vbCrLf & "for ( var i = 0; i <= maxTimer; i++ )" & vbCrLf & "{" & vbCrLf & "div  = document.getElementById( 'Timer' + i )" & vbCrLf & "div2 = document.getElementById( 'Timer' + i + 'd' )" & vbCrLf & "var endDate = new Date(div.innerHTML)" & vbCrLf & " if (endDate.getTime() - now.getTime() <= 0) {" & vbCrLf & "if (!nullTimers[i]) {" & vbCrLf & "nullTimers[i] = 1;" & vbCrLf & "div2.innerHTML =  'Product has expired'" & vbCrLf & "}" & vbCrLf & "} else {" & vbCrLf & "div2.innerHTML = GetTimeTest( endDate.getTime() - now.getTime() )" & vbCrLf & "div2=document.all.tags('blink');" & vbCrLf & "}" & vbCrLf & "div2=document.all.tags('blink');" & vbCrLf & "}" & vbCrLf & "}" & vbCrLf & "for (i=0;i<=maxTimer;i++) nullTimers[i] = 0;" & vbCrLf & "var TimerID = setInterval( 'CountDown()', 1000 )" & vbCrLf & "</script>"
        ' Label1.Text = "good to go"
        objConn.Close()
        Dim amount As Decimal = ShoppingCart.GetTotalAmount()
        totalAmountLabel.Text = String.Format("{0:c}", amount)
        totalAmountLabel.CssClass = "LabelGreen"
        Response.Write(sMsg)
        Return

Select allOpen in new window

Hi sevensnake77;

This normally happens when a field name or table name is the same as a reserve word. Seeming that you have [ ] around the table name the only other thing in the select statement that looks to be using a reserve word is the column Size. So change Size to [Size] in the select statement.

Fernando

I think that you need to visit this links

http://www.codeproject.com/KB/vb/Excel_Connectivity.aspx

http://www.codeproject.com/KB/database/SqlBulkCopy.aspx

it with the source code it might helpful for you.

--Kashyap

for your error

 E_FAIL(0x80004005).

Visit:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/12a20f0f-3828-4f93-a000-7a2002fb2802

I think my solution is the same as the accepted one and I responded first.