Re: Exporting data to Excel
Yes, it is a limitation when using ADO.NET and even the older ADO. The default data field type is set to Text (255) so if you have a Memo field in your database you will generate the error if the text is more then the 255. You can not change the data type of the Excel sheet but you can use other methods for getting the memo field into Excel like using the Excel Object Model.
Re: Exporting data to Excel
would you have any examples of how to do this?
Thank You
Re: Exporting data to Excel
No, but I do have code on automating Excel from vb.net that should help get you started.
http://www.vbforums.com/showpost.php...38&postcount=3
Re: Exporting data to Excel
I will be honest, I have not seen anything to could help me, but I could be missing the obvious.
Here is my code that populates the excel sheet. could you please make suggestions?
Thanx
VB Code:
MyConnection = New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\ECN Database\Reports\" & strJob & ".xls" & _
";Extended Properties=""Excel 8.0;HDR=No;IMEX=0""")
MyConnection.Open()
MyCommand = New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", MyConnection)
Dim cmdBuilder As New OleDbCommandBuilder(MyCommand)
cmdBuilder.QuotePrefix = "["
cmdBuilder.QuoteSuffix = "]"
DSExcel = New DataSet
MyCommand.FillSchema(DSExcel, SchemaType.Source, "excel")
MyCommand.Fill(DSExcel, "excel")
'Debug.WriteLine(DSData.Tables(0).Columns.Count)
'Debug.WriteLine(DSData.Tables(0).Rows.Count)
For i As Integer = 0 To DsecnEntriesV.Tables(0).Rows.Count - 1
Dim dr As DataRow = DSExcel.Tables(0).NewRow
For j As Integer = 0 To DsecnEntriesV.Tables(0).Columns.Count - 2
dr(j) = DsecnEntriesV.Tables(0).Rows(i).Item(j)
Next
DSExcel.Tables(0).Rows.Add(dr)
dr = Nothing
Next
MyCommand.Update(DSExcel, "excel")
MyConnection.Close()
DSExcel.Tables(0).Clear()
Re: Exporting data to Excel
Looks fine. Using ADO.NET does leave you with no options for formatting the excel sheet but if exporting the data is all thats needed then you should be good to go.
Re: Exporting data to Excel
but this is where I run into the maxlength issue. I have use this code many times before and it works fine...unless i run into 255+ characters.
so I guess I am trying to see how to modify the code to make it work :-(
Re: Exporting data to Excel
:lol: Oh thats right. Forgot what the thread issue was. :D
I had a thread on this before but cant find it right now. I think I had solved it by using an ADO recordset but the problem was in VB 6 not .NET. So for this I know you can use the Excel Object Model with no issues but then using Excel seem to be something you dont want to do?
Re: Exporting data to Excel
i'll admit that I do not know what exactly you are talking about :-)
Re: Exporting data to Excel
By adding a reference to Excel you can use ifts functions and properties to make it do what ever you want it to do, etc.
Re: Exporting data to Excel
I am not opposed to it at all. I actually do this later when I end up formating the workbook. I just never considered dumping the data that way, because it seemed tidious
Re: Exporting data to Excel
Nothing more then looping like you were doing to populate your dataset. ;)
Did you also try using an OleDbCommand object and passing Parameters already?
Re: Exporting data to Excel
:-) yeah I just realized that.
I did it the way you suggested and it worked fine.
what do you mean by oledbcommand object and passing parameters?
Obviously I have not, but if you explain how, I could try.
Re: Exporting data to Excel
I remembered this thread - http://vbforums.com/showthread.php?t=422955
The sql insert into string may be too long as a result of the memo fields contents. ;)
But if your going to be formatting the worksheet using the EOM then there is not too many reasons to not use it to insert your data unless you have a large sheet where performance may be an issue.
Re: Exporting data to Excel