IF statement not work - No Parameter error?!?

[yetiboy]

Hi guys,

Can anyone tell me where I am going wrong - I think I am very close but I have been working on this all day and still can't crack it. Thanks.


WHAT I AM TRYING TO ACHIEVE

1. I have a list of services, when I select a service I have a querystring pass the service category to the directory page.

2. The Directory page picks up if the querystring cat is equal to null or not. If it has a value it runs the 'businesses_service' sql statement fro cmd1. If not then it must be a standard directory viewing.


Please note I have already tested to see if any content is being passed throught the cat query by use the visibility function on a label.


Any ideas - as I'm going sightly mad ! thanks guys.




THE ERROR MESSAGE >

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 61: 'Create and fill the DataSet.
Line 62: Dim ds As New DataSet()
Line 63: cmd1.Fill(ds, "tbl_businesses")
Line 64:
Line 65:


Source File: C:\Inetpub\wwwroot\bourneuk.co.uk\sub_cat.aspx.vb Line: 63

Stack Trace:


[OleDbException (0x80040e10): No value given for one or more required parameters.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +91
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
sub_cat.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\bourneuk.co.uk\sub_cat.aspx.vb:63
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

MY CODE >

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.OleDb
'Imports System.Data.SqlClient

Partial Class sub_cat

    Inherits System.Web.UI.Page

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

		'Create the connection and DataAdapter for the biz_categories table. 
        Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("db/db_testing_dat.mdb"))



        Dim txt_businesses_id As String

        If Request.QueryString("id") = "" Then
            txt_businesses_id = ""
        Else
            txt_businesses_id = Request.QueryString("id")
        End If



        Dim txt_ser_category As String

        If Request.QueryString("cat") = "" Then
            txt_ser_category = ""
        Else
            txt_ser_category = Request.QueryString("cat")
        End If



        ' Text Business Listings

        '"server=(local);database=pubs;uid=sa;pwd=;")


        Dim cmd1 As OleDbDataAdapter

        If txt_ser_category = "" Then
            cmd1 = New OleDbDataAdapter("select * from tbl_businesses WHERE biz_sub_categories_id = " & txt_businesses_id & "", cnn)
         Else
            cmd1 = New OleDbDataAdapter("select * from tbl_businesses WHERE businesses_service = " & txt_ser_category & "", cnn)
        End If



        'Create and fill the DataSet. 
        Dim ds As New DataSet()
        cmd1.Fill(ds, "tbl_businesses")



        'Bind the tbl_biz_categories table to the parent Repeater control, and call DataBind. 
        businesses.DataSource = ds.Tables("tbl_businesses")
        Page.DataBind()


cnn.Close()



        lblServerDate.Text = "Today's Date: " & DateTime.Now.ToString("dd/MM/yyyy")


        about_menu.Visible = False
        history_menu.Visible = False
        gallery_menu.Visible = False
        news_menu.Visible = False
        events_menu.Visible = False
        sport_menu.Visible = False
        jobs_menu.Visible = False

        directory_menu.Visible = True
        services_menu.Visible = False

        'shopping_menu.Visible = False
        'foodndrink_menu.Visible = False
        'stay_menu.Visible = False
        'nightlife_menu.Visible = False




    End Sub
	


End Class

[darkwater23]

I'm kind of new to .Net, but I think that the problem is you have a string for the second argument.

I noticed something similar when I was using a method in C# to change views in a multiview control. I passed in the id of the view as ("myView") when it was actually supposed to be (myView). It didn't want the name of the object, but a reference to the object itself, I suppose.

Below is one link to the fill method. There's only one example where a string is allowed to be passed in and that was with 4 other variables.

Maybe try dropping the quotes on the second arg?

http://msdn2.microsoft.com/en-us/lib...pter.fill.aspx

[wwb_99]

^^^Good guess, but not quite. The error is coming from the database server, and it is saying that you don't have the expected parameters for that stored procedure. Double check the procedure and see what parameters it needs and provide them.