.NET using two parameters

getting no search results!!

[Andrew Thomas]

Im trying to search my SQL database using stored procedures. I tried it using one parameter and it worked fine, when i add another parameter i get no results but no errors, ive tried it in the SQL analyser and the SQL query is correct.

here is the full code, im pretty new to this so i may be making an obviouse mistake to all of you.

thanx

PS - took the connection out due to private passwords/logins etc..

Public Class WebForm3
Inherits System.Web.UI.Page
Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents dg_productsby As System.Web.UI.WebControls.DataGrid
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Txtbox_products As System.Web.UI.WebControls.TextBox
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Txtbox_Code As System.Web.UI.WebControls.TextBox
Protected WithEvents ds_Products As GPC.DataSet1

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
Me.ds_Products = New GPC.DataSet1()
CType(Me.ds_Products, System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "dbo.sp_Productsby", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("PK_ProductID", "PK_ProductID"), New System.Data.Common.DataColumnMapping("Code", "Code"), New System.Data.Common.DataColumnMapping("Name", "Name"), New System.Data.Common.DataColumnMapping("Description", "Description")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "dbo.[sp_Productsby]"
Me.SqlSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Prod_Name", System.Data.SqlDbType.NVarChar, 255))
Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Code", System.Data.SqlDbType.NVarChar, 255))
'
'SqlConnection1
'
'connection goes here

'ds_Products
'
Me.ds_Products.DataSetName = "ds_Products"
Me.ds_Products.Locale = New System.Globalization.CultureInfo("en-GB")
Me.ds_Products.Namespace = "http://www.tempuri.org/DataSet1.xsd"
CType(Me.ds_Products, System.ComponentModel.ISupportInitialize).EndInit()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

Me.SqlSelectCommand1.Parameters("@Prod_Name").Value = VariantType.Empty
Me.SqlSelectCommand1.Parameters("@Code").Value = VariantType.Empty
Me.SqlDataAdapter1.Fill(Me.ds_Products)

'If Not IsPostBack Then
Me.dg_productsby.DataBind()
'End If

End Sub

Private Sub SqlDataAdapter1_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.SqlSelectCommand1.Parameters("@Prod_Name").Value = Txtbox_products.Text
Me.SqlSelectCommand1.Parameters("@Code").Value = Txtbox_Code.Text
Me.SqlDataAdapter1.Fill(Me.ds_Products)
Me.dg_productsby.DataBind()
End Sub
End Class

it worked fine until i add the parameters and code for @code, im not sure if it is to do with the Me.SqlSelectCommand1.Parameters("@Code").Value = VariantType.Empty, ive also tried .NULL and vbnull but with no sucess

thanks again

[tomasz]

in your sp put loop

if @Code=''
begin
/* code without @Code param */
end
else
begin
/* code with @Code param */
end

and pass your code @code param as
Me.SqlSelectCommand1.Parameters("@Code").Value = Txtbox_Code.Text + ""

[Xoc]

tomasz-Welcome to WebmasterWorld! Thanks for helping with some of the questions that have come up.