• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

sql to excel

Yes I am trying to use a datalist that I created, I have now have to change this from using ms-sql to excel sheet.
I am having problems with the dataAdapter.Fill(dataSet)
keeps coming back Object reference not set to an instance of an object

Dim dirInfo As New DirectoryInfo(Server.MapPath("\store\Webspecials")) ' 
        Dim myReader As System.Data.OleDb.OleDbDataReader
        Dim FileInfo = "webspecials.xls"
        Dim DS As System.Data.DataSet
        Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter
        Dim objConn As System.Data.OleDb.OleDbConnection
        Dim sqlStr As String = "SELECT ProductID,  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 command As New OleDbCommand(sqlStr, objConn)
        objConn.Open()
 
 
       
        Dim dataSet As New DataSet
        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>"
 
        objConn.Close()
        Dim amount As Decimal = ShoppingCart.GetTotalAmount()
        totalAmountLabel.Text = String.Format("{0:c}", amount)
        totalAmountLabel.CssClass = "LabelGreen"
        Response.Write(sMsg)
        Return

Open in new window

0
Seven price
Asked:
Seven price
  • 6
  • 4
  • 2
  • +1
1 Solution
 
samieranaCommented:
Try following line to change from:
Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter
To
Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter
0
 
Fernando SotoRetiredCommented:
Hi sevensnake77;

It looks like you crate a variable of type dataAdapter but never instantiate it before you use it.

Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter
'...
dataAdapter.Fill(dataSet)

Try changing this line

Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter

to this

Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter

Fernando
0
 
Seven priceFull StackAuthor Commented:
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'
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Fernando SotoRetiredCommented:
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
0
 
Seven priceFull StackAuthor Commented:
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)
 
       

Open in new window

0
 
Seven priceFull StackAuthor Commented:
scratch last
0
 
Seven priceFull StackAuthor Commented:
This is the error I am getting

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).


dataAdapter.Fill(dataSet)
0
 
Fernando SotoRetiredCommented:
Post the code and the location of the error.
0
 
Seven priceFull StackAuthor Commented:

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
0
 
Seven priceFull StackAuthor Commented:
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

Open in new window

0
 
Fernando SotoRetiredCommented:
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
0
 
Kashyap MakadiaSoftware EngineerCommented:
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
0
 
Kashyap MakadiaSoftware EngineerCommented:
0
 
samieranaCommented:
I think my solution is the same as the accepted one and I responded first.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now