|
What I want to do for my company is turn our price book into a desktop application. I figured I could use an external file for the price data, for future editing purposes. I've been trying to do this in different ways for a while now, and it's sort of frustrating (this is my first real exposure to database programming and the like).
So I'm trying to read an Excel File into a DataTable. I have a sub going, but for some reason I keep getting a null reference exception:
System.NullReferenceException was unhandled
Message="Object reference not set to an instance of an object."
Private Sub ReadExcelData()
'Try
xlBook = GetObject("C:\Documents and Settings\Administrator.OFFICE2\Desktop\Michael\All_Item_Prices_2008.xls")
Dim xlsSheet As Excel.Worksheet = xlBook.Worksheets(1)
xlBook.Application.Visible = True
xlBook.Windows(1).Visible = True
xlBook.Application.WindowState = Excel.XlWindowState.xlMinimized
Dim irow As Integer = 1
Dim icol As Integer = 1
'DataTable:
'104 Rows
'91 Columns icol irow
'For icol = 1 To 91
'xlsSheet.Cells(ROW, COLUMN).value
MsgBox(xlsSheet.Cells(1, icol).Value.ToString)
If xlsSheet.Cells(1, icol).Value <> Nothing Then
New_Column = New DataColumn _
(xlsSheet.Cells(1, icol).Value.ToString)
Else
New_Column = New DataColumn("FILLER " & icol)
End If
'*************************************************
'NULL REFERENCE HAPPENS HERE
ItemDataTable.Columns.Add(New_Column)
'NULL REFERENCE HAPPENS HERE
'*************************************************
'For irow = 1 To 104
'MsgBox(xlsSheet.Cells(irow, icol).Value)
'ItemDataTable.Rows.Add = xlsSheet.Cells(irow, icol).Value.ToString
New_Row = ItemTable.NewRow
New_Row.Item(icol) = xlsSheet.Cells(irow, icol).Value.ToString
'Next
'Next
'Catch
'End Try
End Sub
If someone can tell me either what I'm doing wrong, or knows a better way (I have all of the prices and stuff saved as an Excel spreadsheet), please let me know. I would really appreciate it!
As I sit here, I contemplate the last words of Socrates: "I drank what?".
Pokémon Pearl Friend Code: 4554-2418-6836
|
|
|
|
|
where have you declared your data table? Couldn't see the definition in the code you have posted.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
|
|
|
|
|
Whoops!
Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices
Imports Microsoft
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Dim WithEvents xlBook As Excel.Workbook
'DataTable:
'104 Rows
'91 Columns
'*****************************
Dim ItemDataTable As DataTable
'*****************************
Dim con As OleDbConnection
Dim da As OleDbDataAdapter
Dim ds As New DataSet
Dim fileName As String = ""
Dim New_Column As DataColumn
Dim New_Row As DataRow
Dim ItemTable As New DataTable
Some of this stuff is unused in this example, like the OleDb stuff.
As I sit here, I contemplate the last words of Socrates: "I drank what?".
Pokémon Pearl Friend Code: 4554-2418-6836
|
|
|
|
|
CodeMonkey85 wrote: Dim ItemDataTable As DataTable
You have declared this table but not defined it. It must be null and hence the exception.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
|
|
|
|
|
How would I define a DataTable? By the looks of it, you specify it as a source for another object or something.
This might not even be the right way to do what I want to do, bear in mind. I'm looking for any easy way to store the data from my sheet so that I can programmatically retrieve it.
As I sit here, I contemplate the last words of Socrates: "I drank what?".
Pokémon Pearl Friend Code: 4554-2418-6836
|
|
|
|
|
In that case, you can use Excel as any other database. You can connect to it. Run SQL queries. Do many things. If you want ot get ths into a grid, then a sinmple google search will help you.
"If you had to identify, in one word, the reason why the human race has not achieved, and never will achieve, its full potential, that word would be 'meetings'." - Dave Barry
|
|
|
|
|
I have done many things. And I've performed at least ten Google searches and tried everything I've seen. But for some unbeknownst reason to me, I can't seem to get what I want.
It seems like it should be a simple matter to read data from a spreadsheet and use that data as you please, but for example, I can't seem to retrieve the correct data from my DataTable even when I think I finally populated it with the spreadsheet data. I don't think it was even meant to be used that way, honestly.
As I sit here, I contemplate the last words of Socrates: "I drank what?".
Pokémon Pearl Friend Code: 4554-2418-6836
|
|
|
|
|
Hello Friend,
You can use excel sheet as database it self and can retrieve data using a oledb connection to it from your application,
or Manually you can do it by
->Go to access
->Create a new blank database
->Now go to File, GetExternal Data, Import
->after clicking import you you will get file dialog open, just select your excel spreadsheet in it.. rest of things you can do it easily... call this acess database easily from your application if you feel it easy.
Thanks
|
|
|
|
|
Yeah, I tried using oledb before, and for some reason certain cells came up blank for no apparent reason. I dunno.
I could try that Access thing too, I guess. Frankly, I'm about to pull my hair out on this one.
Thanks!
As I sit here, I contemplate the last words of Socrates: "I drank what?".
Pokémon Pearl Friend Code: 4554-2418-6836
|
|
|
|
|
access thing should work fine for you buddy, 
|
|
|
|
|
Yeah, I'm now trying to figure out how to read data from the Access file.
As I sit here, I contemplate the last words of Socrates: "I drank what?".
Pokémon Pearl Friend Code: 4554-2418-6836
|
|
|
|
|
Dim connect As New OleDbConnection
connect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourdatabase.mdb;Persist Security Info=True"
connect.Open()
your code and sql here..
connect.Close()
|
|
|
|
|
Yeah, tried that... see this thread[^]
I dunno. I'll keep trying different things, but if anyone has ever done anything like this again and has some code (or can at least figure out what's wrong with my code), I would appreciate it a bunch.
Thanks!
As I sit here, I contemplate the last words of Socrates: "I drank what?".
Pokémon Pearl Friend Code: 4554-2418-6836
|
|
|
|
|
Hi,
I am trying to call a form from my custom control (ComboBox), I have a property in the custom control to get the form name.
After adding the component to the form I can only select the current form (the component's parent form).
What should I do to get all the form names there (or at least type a form name).
Public Shadows Property NewForm() As System.Windows.Forms.Form
Get
Return _frm
End Get
Set(ByVal Value As System.Windows.Forms.Form)
_frm = Value
End Set
End Property
Thanks in advance.
|
|
|
|
|
Randana wrote: What should I do to get all the form names there (or at least type a form name).
Which form name do you wanna get here... 
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Hi,
what i need is to give a form name of a form in the project as the property at design time. So that at run time i can call that form.
thanks for replying.
|
|
|
|
|
It is a bit confusing that your post mentions just the form's name, but code you provided returns a form.
Keep in mind that a form is nothing more than a class with a visual interface. This means that anything you can do with a class, you can do with a form. Since ComboBoxes can hold any item, there is no reason they could not hold references to the forms you are using:
Dim F1 As New MyFormOne
Dim F2 As New MyFormTwo
Dim MyCntl As New MyControl
MyCntl.Items.Add(F1)
MyCntl.Items.Add(F2) The text that is displayed in the ComboBox is derived from the object's ToString method. To have the box display the name, override this method in the form definition, like this:
Public Overrides Function ToString() As String
Return Me.Name
End FunctionLastly, you can retrieve the form itself by looking at the ComboBox' SelectedItem property:
Dim SelectedForm As Form = Nothing
If MyCntl.SelectedItem IsNot Nothing Then
SelectedForm = CType(MyCntl.SelectedItem, Form)
End IfFrom here, you can do whatever you want with SelectedForm. Hope this helps. 
|
|
|
|
|
When I Open a modal form in vb.net 3.5 application, I want to make the background form semi transparant.
Just like windows.
How it is Possible?
Please, Help me.
|
|
|
|
|
Set the Opacity property of the form to some value less than 100(depends how much transparent you want  )
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
It not just the transparant.
I wanted that when I open a Modal form then the back ground form color will be Faded.
just like when you click on the Shutdown button of the windows the background will be faded out
|
|
|
|
|
Hi folks, I hope someone can help me with a VB.NET problem, as it has been driving me bananas all day.
I have a checkbox which is binded to a datatable. Some example code is shown below:
' Access the database<br />
strSQL = "SELECT ID, Name FROM tblMYTABLE"<br />
cnnData.ConnectionString = "[information about my Microsoft Access database]"<br />
cnnData.open()<br />
daData = new OleDb.OleDbDataAdapter(strSQL, cnnData)<br />
cbData = new OleDb.OleDbCommandBuilder(daData)<br />
daData.Fill(dtData)<br />
<br />
' Bind the table to the checkbox (assuming there are records in tblMYTABLE)<br />
<br />
cboMyTable.DataSource = dtData<br />
cboMyTable.DataBindings.Add("Tag", dtData, "ID")<br />
cboMyTable.DataBindings.Add("Text", dtData, "Name")<br />
cboMyTable.DisplayMember = "Name"
So far, everything is working fine - all records in the tblMYTABLE table are shown as individual checkbox items.
However, here is the problem I have been having. I have another table in the database (a many-to-many table with two fields, both of which are the primary keys of other tables) which essentially will dictate whether or not one of the checkbox items generated above is checked or not. To give an example, let's say tblMYTABLE contains the following records:
ID Name
-=-----
1 Jim
2 Joe
3 John
4 Jill
5 Jack
The many-to-many table contains the following IDs from tblMYTABLE (let us call this table tblMANYTOMANY, and let us assume for simplicity that OtherID doesn't matter in this case):
ID OtherID
------------
1 65
4 65
2 65
Therefore, in the checkbox above, I would want to show the items relating to Jim, Jill and Joe as being ticked. I would like this to be done either during the binding process itself (i.e. within the code shown above) or as a separate piece of code immediately afterwards which goes through each of the items and checks them off as necessary, but for the life of me I've no idea how this would be done.
What I would like to do is something like this:
Dim i as integer<br />
<br />
For i = 0 to (cboMyTable.Items.Count - 1)<br />
<br />
strSQL_SecondSQL = "SELECT ID, OtherID FROM tblMANYTOMANY WHERE OtherID=65 AND ID=" & cboMyTable.Items.Tag<br />
<br />
' Open the table, if there is a record there then check the item, if not, leave it as unchecked <br />
<br />
Next
The problem above is that there is no "movement" through the items (or none that I can see), i.e. there is no movement from the first item to the second, the second to the third and so on. I suppose the other way of doing it would be something like (using ASP-style script):
while not (cboMyTable.Items.Count - 1)<br />
<br />
' My code<br />
<br />
cboMyTable.Item.MoveNext<br />
<br />
wend
However, there is no MoveNext property.
I know I could probably use arrays, or to change the original SQL statement, but I don't want to over-complicate the code if it is just a simple thing to fix...
Sorry this sounds very confusing - I hope someone can help!
c0ley
|
|
|
|
|
First one clarifying question.
Is cboMyTable really a checkbox or for example a checked listbox? If it would be a single check box your code wouldn't compile since CheckBox class doesn't have Items property.
Mika
|
|
|
|
|
Hi, yeah it's a checked listbox.
|
|
|
|
|
Okay, first point (based on documentation, which is a bit confusing after your observations)
CheckedListBox.DataSource Property
Gets or sets the data source for the control. This property is not relevant for this class
Back to your question, instead of MoveNext (which doesn't exist) you could use the indexer of Items property (well actually indexer of the ListBox.ObjectCollection class)
In VB I recall that the syntax is
cboMyTable.Item(counter)
However you could also modify the SQL statement so that you don't loop each item in the listbox, but instead you first get all the records and then match them to items. In this case you would use SQL statement like
SELECT ID, OtherID FROM tblMANYTOMANY WHERE OtherID=65
and then fill a datatable based on that and for each row found in datatable, check the corresponding item in the listbox. Although you mentioned that modifying the query wasn't the first option, this would significantly bring scalability to database operations especially if you plan to upgrade to server side database such as SQL Server
Hope this helps,
Mika
|
|
|
|
|
Hi Mika, unfortunately that didn't work.
To make it easier, and forgetting database access for a while, I can narrow it down to the following issue. Hopefully this might help you to find the root of the problem, and to identify where I'm going wrong!!!
The problem is that I have searched through the listbox properties, and cannot find a way of getting the tags from each of the items in a listbox through a FOR loop. What I would like to do is something like this:
Dim i as integer = 0<br />
<br />
For i = 0 to (MyListBox.items.count - 1)<br />
<br />
' Attempt 1 - doesn't work (Tag isn't a property of Items anyway)<br />
<br />
msgbox("The tag in this item is: " & MyListBox.Items.Tag(i))<br />
<br />
' Attempt 2 - doesn't work either (Tag is a property of the listbox, but it<br />
does not seem to like being treated as an "array")<br />
<br />
msgbox("The tag in this item is: " & MyListBox.Tag(i))<br />
<br />
Next
In both cases, the application compiles but returns an error message: "If a member in a class library has been removed, recompile any assemblies that reference that library". I am not certain what this means, but I'm guessing it is the fact that listboxes don't like their items being treated as "arrays".
I have also changed i = 0 to i = 1 in the forlorn hope that the listbox started at 1 rather than 0, but with no luck.
I'm sure there are ways around this, but I don't want to write pages of extra code if I'm just missing one line...
Thanks again, I really appreciate it!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
