Populating Web Form with Drop Down List Selection 1

[AugieDawgie]

Okay, I'm really new to this and am not sure how to get this to work, so here goes. I have a web form, User Administration. It contains a "search" button, which allows the user to search by first name or last name or both when clicked, depending on what the user has entered...it then populates the form with first name, last name, location, hardware & software for everyone that falls within the criteria. I've included the code below; however, I also have a databound location list box, which holds 42 of our stores. I can't figure out how to get a list of users at a specific location, if the location (no first or last name) is the only selected item prior to hitting the "search" button...I hope this makes sense and thanks in advance for any help you can give me.

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Fname = Trim(txtFnameAdd.Text)
Lname = Trim(txtLnameAdd.Text)

sql = "SELECT key_id, Fname, Lname, location, LEFT(Datemodified,11) as DateModified,"
sql = sql & "'<a href='"
sql = sql + "'Inventory Assign Hardware.aspx?ID='"
sql = sql & " + Cast(key_ID as Char) + "
sql = sql & "'''>Hardware Permissions"
sql = sql & "</a>' as Hardware,"
sql = sql & "'<a href='"
sql = sql + "'Inventory Assign Software.aspx?ID='"
sql = sql & " + Cast(key_ID as Char) + "
sql = sql & "'''>Software Permissions"
sql = sql & "</a>' as Software from Users "

If txtFnameAdd.Text <> "" And txtLnameAdd.Text = "" Then sql = sql & " WHERE Fname like '%' + '" & Fname & "' + '%'"
If txtLnameAdd.Text <> "" And txtFnameAdd.Text = "" Then sql = sql & " WHERE Lname like '%' + '" & Lname & "' + '%'"
sql = sql & BuildQuery()
daUsers = New System.Data.SqlClient.SqlDataAdapter(sql, SORCInventory)
daUsers.Fill(dsUser)
dgUsers.DataSource = dsUser
dgUsers.DataBind()
End Sub

Function GetLocations() As DataSet

Dim ddlDataSet As DataSet = New DataSet
Const strSQLDDL As String = _
"SELECT location FROM location ORDER BY location"

Dim myDataAdapter As SqlDataAdapter = New _
SqlDataAdapter(strSQLDDL, SORCInventory)

myDataAdapter.Fill(ddlDataSet, "Location")

Return ddlDataSet
End Function

 

[jbenson001]

You can use a required field validator on one or both of the name controls. Or you can do the validaton when they hit the submit button. You can check if both are blank and then display an error message.

Jim

 

[AugieDawgie]

Jim - thanks for the quick reply but -

I don't want to display an error message. I want to be able to pick Manhattan from the list without inputting a first or last name and get the form to populate with everyone in Manhattan at that location.

Example:

Right now I can enter just the letter L in the first name box, hit search, and it will give me back everyone in the database with the first name beginning with L regardless of where they are located.

Make sense?

 

[ca8msm]

This should be fairly straightforward - you have your current function set up to create a where clause if a user enters a forename or surname, so all you have to do is add another check to see if anything has been selected in the DropDownList.

Have a quick go at that (using DropDownList1.SelectedItem.Value to find out which value was entered) and if you have any trouble post back with what you are trying and we can show you a simple example.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jbenson001]

If you are looking for a way to construct your SQL to have a dynamic WHERE clause ie. They can enter either First, Last, Both or Neither, then I would create a stored procedure to do this. Then you can set up the sp to handle that.

From what I see in your code, it doesn't account for when the user enters both name fields. You may need something like this:

If txtLnameAdd.Text <> "" And txtFnameAdd.Text <> "" Then sql = sql & " WHERE Lname like '%' + '" & Lname & "' + '%' AND Fname like '%' + '" & Fname & "' + '%'"
[code]

When both are blank, try this:
[code]
If txtLnameAdd.Text = "" And txtFnameAdd.Text = "" Then sql = sql & " WHERE City = " & City 
[code]

I would suggest using a stored procedure though.

Jim

 

[AugieDawgie]

Thanks - I'll give it a go now

 

[jbenson001]

I suggest using a SP like this. Change your columns and table names accordingly:

CREATE PROCEDURE dbo.<procedure Name>
   @LName              VarChar(20),
   @FName              VarChar(20),
   @Office             VarChar(50)

AS

/*  Declare flag variables  */
Declare @LName_flg   int
Declare @FName_flg   int
Declare @Office_flg  int

/*  Initialize flag variables  */
Select @LName_flg = 1
Select @FName_flg = 1
Select @Office_flg = 1

IF @LName = ''
   Select @LName_flg = 0

IF @FName = ''
   Select @FName_flg = 0

IF @Office = ''
   Select @Office_flg = 0


Select
   <columns>
From
   <table name>
Where
   ( ( @FName_flg = 1 AND FName = @FName ) OR ( @FName_flg = 0 ) ) AND
   ( ( @LName_flg = 1 AND LName = @LName) OR ( @LName_flg = 0 ) ) AND
   ( ( @OfficeID_flg = 1 AND Office= @Office ) OR ( @Office_flg = 0 ) )

GO