Desperately need help in drop down list

[Joey Liang via DotNetMonster.com]

Hi all,
I have a drop down list which store all the different brands of
product.When i selected the particular brand from the drop down list, it
will display all the products with the selected brand in a datagrid. I have
this error when i select a brand from the drop down list. Blow is my
code,anyone can help me to solve my error,which part of my code went wrong?
Really thanx and very appreciate your help in advanced.. I have been
stucked for days regarding this error..

This is the error : No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for one
or more required parameters.

Source Error:


Line 208: objAdp = New OleDb.OleDbDataAdapter(strSqlCmd, con)
Line 209: dataTable = New DataTable
Line 210: objAdp.Fill(dataTable)
Line 211: dgProducts.DataSource = dataTable
Line 212: dgProducts.DataBind()

Here is the code :
Dim strSqlCmd As String
Dim objAdp As OleDb.OleDbDataAdapter
Dim dataTable As DataTable
Dim brand As String

brand = ddlBrand.SelectedItem.Value

strSqlCmd = "Select p.ProductID, p.ProductName, p.ProductBrand, " & _
"p.ProductImage, p.Price, p.Quantity " & _
"From Product p, CategoryProduct cp Where " & _
"p.ProductID= cp.productID " & _
"and cp.CategoryID=" & Request.QueryString.Get
("CategoryID") & _
"and p.ProductBrand=" & brand


objAdp = New OleDb.OleDbDataAdapter(strSqlCmd, con)
dataTable = New DataTable
objAdp.Fill(dataTable)
dgProducts.DataSource = dataTable
dgProducts.DataBind()


End Sub

 

[Guest]

Hi Joey,

I assume that the autopostback on the dropdown list is open.

What does Request.QueryString.Get ("CategoryID") return when you change the
dropdownlist item? Make sure it returns what you expect it to return. (i.e:
not empty string)

By the way, try to use paramters collection of the sql command object. At
the moment your code is vulnerable to SQL Injection.

Hope this helps,

Ethem Azun

 

[Joey Liang via DotNetMonster.com]

Hi Ethem Azun
Yup i hav set autoPostBack= true. As i am doing a e-commerce
web which have different categories.Example i have clicked on a category
"ink cartridges" it will show all the products in this category. When i
select a brand "HP" from the drop down list, it will only display all "HP"
brand ink cartridges.Hence i use Request.QueryString.Get ("CategoryID") to
display the category that the user clicked. You have any idea which part of
error wrong? is my Select statement wrong?I really need help in
these..thanx..

 

[Guest]

Hi Joey,

Why are you using the Query string for this purpose? Since you are using
postbacks, you should use the SelectedItem property of the drop down list to
get the selected value. Besides, if your parameter is a string value, you
should include it inside aphostrophes, ('') in your sql select statement.

There are several flaws in the code particle you have sent me. I would
recommend learning and doing the right way instead of writing fast code that
just works. It's out of scope for me to explain every detail here, but please
follow this path;

1) Use sqlparameters instead of string concatenations. This way, you can
make sure that nobody can use SQL Injection on your application and there are
no typo mistakes such as forgetting aphostrophes etc. Take a look the code in
this link, which includes how to use sql parameters with a sql command.

http://msdn.microsoft.com/library/d...guide/html/cpconupdatingdatainsqldatabase.asp

If you wonder about why I'm stressing on this point, check out this;
http://www.sitepoint.com/article/sql-injection-attacks-safe

2) Learn the correct way of using the web controls and the code behind model;

WebUI Control Reference:
http://samples.gotdotnet.com/quickstart/aspplus/doc/webcontrolsref.aspx
Code Behind Model:
http://msdn.microsoft.com/msdnmag/issues/01/08/cutting/default.aspx

Hope this helps,

Ethem Azun

 

[Me]

Ha! Ha! That is funny!

I remember back in the day when I first started
programming. It was about 3 years ago I think. I
always believed you should learn the right way and
get the job done correctly. I thought in the end
it serves everyone to do it well. Then the ugly
real world set in and I haven't had the
opportunity to do anything but hastily throw down
ad hoc code to just get the job done fast. The
customer never knows the nightmare I face when
they ask my boss for a new feature or a bug fix.
Ah... the idealic life. Wonderful memories.

 

[Phillip Ian]

I'm not sure I see anything glaringly wrong with your code as listed,
so I'd be tempted to suspect a malformed category name, especially
since, as someone pointed out, there are no apostrophes around the
CategoryID.

I just wanted to expand on what Ethem said about parameterized SQL.
This is about the most important thing you can do for your code in the
interest of security, the way I see it.

I'm also in the middle of writing a cart...here's the function I use to
do pretty much the same thing you're doing:

Public Function GetProductPageByCategory(ByVal ACategory As
cCategory, ByVal APage As Integer) As DataTable
Dim cn As New SqlConnection(ConnectionString)
Dim cmd As New SqlCommand("select ID, Name, Price from tblProducts
where ID in (select ProductID from tblProductPages where
CategoryID=@cat and Page=@page) order by Name", cn)
Dim da As New SqlDataAdapter(cmd)

Dim result As New DataTable

If ACategory Is Nothing Then Return result

cn.Open()
Try
cmd.Parameters.Add("@cat", ACategory.ID)
cmd.Parameters.Add("@page", APage)
da.Fill(result)
Catch ex As Exception
cn.Close()
Throw New DataException("Could not load the list of products for
category " & ACategory.FullName, ex)
Return Nothing
End Try

cn.Close()
Return result
End Function

Notice the parameters (@cat, @page) in the SQL. This guarrantees that
whatever I pass as @cat or @page will be considered one piece, not SQL
meant to be parsed. In your example, if I put something like the
following:

http://your.domain/your_app/your_page.aspx?cat=dummy;truncate table
Product;dummy

....I think you can see the fun you're in for in this case.

Good luck with the project!

-Phil

 

[Guest]

Well, then change your job. Stay cool, have fun.

And don't forget that in real life, there are real customers, real
strategies, real designs, real risks, real flaws, real laws and real audits
and real rewards. If you are not careful, you loose your job; in a very real
way. It is that simple. Unfortunately, sometimes you have to live through
that to get that experience, and I hope you will never have that
"opportunity".

If you don't know how to use a tool and don't want to learn how to in the
proper way, just don't use it. This way you are less dangerous to yourself as
well as to the society. You might be more talented in spending your energy
in smth else, so just do that. And before blaming everyone and everything
else, learn how to plan, and teach it also to your boss.