SQL Query problem

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
Building some queries for my ASP.NET 2.0 Dataset.

I'm new to this all but one of the queries is giving me an error, i'm learning from a tutorial on the ASP.net website so i'm stuck really.

Code: 
SELECT     ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM         Products
WHERE     CategoryID = @CategoryID

"Error in WHERE clause near '@'. Unable to parse query text.

Any help is appreciated, thanks :D
 
Access, could that be why?

I'll will be moving to using SQL Server, but I can't just yet. Please don't suggest just using SQL Server now, I simply can't

:D
 
Try this

Code: 
SELECT     ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM         Products
WHERE     CategoryID = ?

If that doesn't work try using brackets around the CategoryID = ? like this

WHERE (CategoryID = ?)
 
noob said:
Try this

Code: 
SELECT     ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM         Products
WHERE     CategoryID = ?

If that doesn't work try using brackets around the CategoryID = ? like this

WHERE (CategoryID = ?)
Click to expand...

No luck - "Enter value parameter for CategoryID"
This is for a DataSet in ASP.NET 2.0. I'm using Visual Web Developer to construct it.

Tutorial said:
The next step is to define the SQL query used to access the data. Since we want to return only those products that belong to a particular category, I use the same SELECT statement from GetProducts(), but add the following WHERE clause: WHERE CategoryID = @CategoryID. The @CategoryID parameter indicates to the TableAdapter wizard that the method we're creating will require an input parameter of the corresponding type (namely, a nullable integer).
Click to expand...
 
From an Access Database, in Table Products. I'm using another query on the same database that returns all data with no problems.

Code: 
SELECT     ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM         Products
 
Hi Wardie

You need to pass a value to (CategoryID =?) this could a value from a textbox on the page, a querystring value, a session value, a cookie value, etc.

In the following example I have a gridview and I can show particular students based on a textbox, session variable or whatever I want.



To be honest with you the tutorial you seem to be using doesn't seem good at all.
 
It's from the official ASP.NET website.

From what I can tell the point of the tutorial is to basically keep all the fetching of data and so on, seperate from the actual web design. It actually does make quite a lot of sense to be fair. Then when you want the data you simply call on the TableAdapter that you've created which contains the SQL Queries.

I'm stuck now though because what it's telling me to do isn't working ha-ha.

I guess I could just do all this using Access Data Sources? I'm new to this so i'm having to start somewhere ha-ha.

The eventual aim is to construct an application that will allow users to enter the number of hours they have spent on a task(s) each day of the week.

Cheers for trying to help :D:D
 
Last edited:
No luck - "Enter value parameter for CategoryID"
Click to expand...

The above error message, is that when you execute the ASP.NET page?

With the GetProductsByCategoryID(categoryID) method in our DAL, we can now create an ASP.NET page that displays only those products in a specified category. The following example shows all products that are in the Beverages category, which have a CategoryID of 1.
Click to expand...

If you look at the following code: -

Beverages.aspx.vb

Imports NorthwindTableAdapters

Partial Class Beverages
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Load

Dim productsAdapter As New ProductsTableAdapter
GridView1.DataSource =
productsAdapter.GetProductsByCategoryID(1)
GridView1.DataBind()
End Sub
End Class
Click to expand...

GridView1.DataSource = productsAdapter.GetProductsByCategoryID(1)

The above line of code is what is passing a CategoryID of 1 whatever is between the brackets is what you are passing to (CategoryID =?).
 
I hadn't actually tried building the page because it was failing when I was testing the query, I understand what you mean now though.

Bare with me I am new :)

I'll give it a go and let you know. Thanks again.
 
If you are finding this all a bit tricky I can show you a very quick way to acheive all this, using the drag and drop components in Visual Web Developer. I know it is good to understand the code but to be honest with you I think that tutorial chucks you right in the deep end.
 
Got it sorted now I was being silly, thanks :)

Well i'm always open to learning things different ways. I know you can just chuck on GridView components and pretty much configure them to do what you want really quickly, if that's what your talking about?

I'm guessing that tutorial is showing the way it SHOULD be done but not the easiest?

This is only for the sake of learning, i'm not actually developing the application I talked about yet because I don't have the know how lol :D
 
Wardie said:
Got it sorted now I was being silly, thanks :)

Well i'm always open to learning things different ways. I know you can just chuck on GridView components and pretty much configure them to do what you want really quickly, if that's what your talking about?

I'm guessing that tutorial is showing the way it SHOULD be done but not the easiest?

This is only for the sake of learning, i'm not actually developing the application I talked about yet because I don't have the know how lol :D
Click to expand...

Yeah using the components. The way they are teaching you is the 'very neat, using functions, etc' way hehe which is good but noobs can find it very overwhelming. In my job everything has to be banged out as fast as possible so I end up writing a lot of dirty code. There are times where I just cannot drag and drop because I tend to do a lot of stuff with Oracle and that can complicate things.
 
I'm not totally new to programming and do have some experience from college so it's not too bad. I was just getting confused about the right way to do it after reading a few tutorials and the set i've found and posted now seem to be the most comprehensive, so I decided to follow them.

Cheers for the help, no doubt expect more posts from me lol.

You a developer then by any chance?

I have to admit you could knock together applications pretty quickly compared to other languages if you knew what you were doing.
 
Well developer / lecturer. ;) I've got a niggly little problem I cannot bloody figure it out. I have a pass-through SQL query in Access which grabs data from our Oracle DB but when I try using the pass-through query in ASP.NET for some reason it will not show the primary key (StudentID) so if getting three fields StudentID, Firstname, Surname. It will not show the StudentID and I don't know why. I have feeling it's something to do with casting the type from Oracle. :mad:
 
You must log in or register to reply here.
Back
Top Bottom