.NET fill error

Code Cleric

Code Cleric

Perch
Fill: SelectCommand.Connection property has not been initialized. That's my error.. Here's my code...

Code: 
<%@ page language="vb" runat="server"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<script language="vb" runat="server">
sub Page_Load(sender As Object, e As EventArgs)
	if Not IsPostBack then
		DataSub
	end if
end sub

sub DataSub()
	Dim myConnection as OleDbConnection
	Dim strConnectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mwt.mdb")
	
	dim OurCommand as OleDbCommand
	dim OurConnection as OleDbConnection
	dim OurDataAdapter as OleDbDataAdapter
	dim OurDataSet as New DataSet()

	OurCommand = New OleDbCommand("Select Name,Price from tbl_Menu_Item Where Category = 1;",OurConnection)
	OurDataAdapter = New OleDbDataAdapter(OurCommand)	
	OurDataAdapter.Fill(OurDataSet, "Categories")
	dim OurDataTable as New DataView(OurDataSet.Tables("Categories"))
	OurRepeater.DataSource = OurDataTable
	OurRepeater.DataBind()
end sub
</script>

If you can't tell I'm flying solo here learning .NET... Cut, Paste, Reverse Engineer, Cross finger's... That's the process. I am sure there is something simple here I am not seeing. I had just started to get into 3.0 when I decided to switch to .NET... Basically I am trying to call out all records from the tbl_Menu_Item table that match category 1.. I would then want to display them in my repeater. If anyone can help me out I would greatly appreciate it. Thanks in advance.
 
So I am both wondering and looking into the idea that .Fill is for interacting with a MS-SQL database instead of the access db I am using
 
Code Cleric said:
Fill: SelectCommand.Connection property has not been initialized.
Click to expand...

Try this:

Code: 
sub DataSub()
	Dim strConnectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("mwt.mdb")
	Dim strSQL as String = "Select Name,Price from tbl_Menu_Item Where Category = 1;"

	dim OurDataSet as New DataSet()
	dim OurDataTable as DataTable = OurDataSet.Tables.Add("Categories")
	dim OurDataAdapter as OleDbDataAdapter = New OleDbDataAdapter(strSQL, strConnectionString)
	OurDataAdapter.Fill(OurDataTable)

	OurRepeater.DataSource = OurDataTable
	OurRepeater.DataBind()
end sub

I didn't test this, but it should work. If not, I'll test/fix it tomorrow (it's too late right now).

And, in response to the second post, no, the OleDbDataAdapter and its Fill method work fine with Access. There is another DataAdapter in another namespace you could/would use to work with MS-SQL.

riley
 
Thanks for Replying Riley... I replaced my data sub with yours, my new error message is... Data type mismatch in criteria expression highlighting line 18 which is..
OurDataAdapter.Fill(OurDataTable) Any thoughts?

...

Few minutes later...

After thinking about it for a minute... Remember I'm a noob to databases... I started to wonder about somethings... In the database for this site, I am working with two tables. One is the category table called tbl_Menu_Cat it has two fields, Cat_ID, and Cat_Name... Cat_ID is the primary key... Now the tbl_Menu_Item table has the following fields... Item_ID, Category, Name, Desc, and Price. Both ID fields are autonumbers. Item_ID is the primary key for the Menu Item table, Cat_ID is the foreign key held in the category location of the Menu Item table.. I was originally trying to use the 'Where' command to call rows who's category was equal to 1; which in the Menu_Cat table is the ID for a category called 'Starters'. When I saw the new error message I thought maybe if I set 'Where Category = 'Starters'... This eliminated my error messages. The HeaderTemplate and the FooterTemplate of the Repeater display fine. The records that should be displayed by the ItemTemplate do not show up. Here is the code for my repeater, perhaps I have called the data improperly?
Code: 
<form runat="server">
<asp:Repeater id="OurRepeater" runat="server">
<HeaderTemplate>
	<u><b>Menu Items</b></u><br>
</HeaderTemplate>
<ItemTemplate>
	<%# DataBinder.Eval(Container.DataItem, "Name") %>:  
	<%# DataBinder.Eval(Container.DataItem, "Price") %><br>
</ItemTemplate>
<FooterTemplate>
End of the Menu Items
</FooterTemplate>
</asp:Repeater>
</form>
 
Here's another question that I was having... Am I not closing my connection to this db? I am assuming I am not, and also assuming I should be... What would the proper syntax be to close the connection to my db... Also could this be why I am not able to get any data from the db, perhaps too many open connections?
 
Code Cleric said:
Here's another question that I was having... Am I not closing my connection to this db? I am assuming I am not, and also assuming I should be... What would the proper syntax be to close the connection to my db... Also could this be why I am not able to get any data from the db, perhaps too many open connections?
Click to expand...

The DataAdaptor is opening and closing the connection -- no action is necessary on your part.

I suggest you place a message at the bottom of your page
<asp:Label id="OurMessage" runat="server"></asp:Label>
and load it with the results of the Fill method, which returns an integer value that indicates the number of rows returned.

For example:
Dim OurRowCount as Integer
OurRowCount = OurDataAdapter.Fill(OurDataTable)
OurMessasge.Text = OurRowCount & " rows returned"

This will tell you if any rows came back from the query. If the message says "0 rows returned" the problem is in the query. If the message says a non-zero number of rows were returned (eg.: "1 rows returned"), the problem is in the repeater binding. I betting on the query, as the ItemTemplate code looks syntactically correct.

riley
 
I implemented your idea of having a label show the number of rows returned... The label is telling me that 0 rows are being returned. I am assuming this means the data Table is not being populated by the db??
 
Code Cleric said:
I implemented your idea of having a label show the number of rows returned... The label is telling me that 0 rows are being returned. I am assuming this means the data Table is not being populated by the db??
Click to expand...

Right. So now you have to figure out why.

I suggest you create the query in the Access query wizard. Once you have it working, go to the SQL view and copy the text. You can then use that in your .Net application.

By the way, you can use the use the SQL view to paste in your current query text, and that will give you a head start in the wizard. Once it's pasted, go to the Design view and try to run it. You can play with it there until it does what you want. After you copy it from the SQL view, you can simply close the query wizard without saving the query.

riley
 
Everything is working just fine now... With the where command, since I am using the cat_id as the foreign key linking the two tables... The value of cat_id is stored in the category field of the menu table... I was originally trying to where = 1 which is the value of the cat_id.. Didn't work.. Then I tried where = "Starters" which is the name of the category.. Didn't work.. I removed the where and everything displayed no prob.. I then replaced the where command however this time instead of where = 1 I put where = '1' and only the records that contain the foreign key matching the category Starters were returned. Thank-you very much for the help and support Riley.
 
You must log in or register to reply here.
Back
Top