Solved

sql to excel

Posted on 2009-06-29
14
239 Views
Last Modified: 2012-05-07
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
Comment
Question by:sevensnake77
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 4

Expert Comment

by:samierana
ID: 24738819
Try following line to change from:
Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter
To
Dim dataAdapter As New System.Data.OleDb.OleDbDataAdapter
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 24738873
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
 
LVL 9

Author Comment

by:sevensnake77
ID: 24739297
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24739539
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
 
LVL 9

Author Comment

by:sevensnake77
ID: 24739737
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
 
LVL 9

Author Comment

by:sevensnake77
ID: 24739759
scratch last
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 24739801
This is the error I am getting

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).


dataAdapter.Fill(dataSet)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24739825
Post the code and the location of the error.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 24739901

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
 
LVL 9

Author Comment

by:sevensnake77
ID: 24740034
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24740202
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
 

Expert Comment

by:Kashyap Makadia
ID: 24742392
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
 

Expert Comment

by:Kashyap Makadia
ID: 24742399
0
 
LVL 4

Expert Comment

by:samierana
ID: 24751916
I think my solution is the same as the accepted one and I responded first.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now