list box

[Maximus007]

I have a drop menu that has "All" as one of the options for useres to select. Therefore if the user selects "All" then it displays all the data for the date range.
part of the code:

strSelect = ("Select * from IT_Servers where DateEntered Between '" & strStart & "' AND '" & strEnd & "' And Tech='" & strTech & "'")


ObjCommand = New SqlCommand(strSelect, ObjConnection)
ObjAdapter = New SqlDataAdapter(ObjCommand)

objdataSet = New DataSet


ObjAdapter.Fill(objdataSet, "IT_Servers")



DataGrid1.DataSource = objdataSet

RdCount = objdataSet.Tables("IT_servers").Rows.Count

If RdCount = 0 Then
Label1.Text = "No record was found for " & strTech & "!"
DataGrid1.Visible = False
Else
Label1.Text = RdCount & " records were found for " & strTech & "!"
DataGrid1.Visible = True
DataGrid1.DataBind()
End If
ObjConnection.Close()
End Sub

 

[jbenson001]

Ok.. so what is your question?

 

[Maximus007]

right if I select "All" it will say no records where found. How can I get it to diplay all the records with those date range when a user select "All".

Thank you

 

[jbenson001]

so basically you want to keep the date range and remove the strTech parameter?

 

[Maximus007]

when the page first loads the user make a selection then select a start date and an end date. Then they click on the submit button. Now that first part works fine. However the area I am having a problem with is when the user select "All" from the drop down list. I comes up 0 with no record found. How can change it to where if a user selects "All" it displays all the data but if they select something else it displays just that record only.

 

[jbenson001]

Try:

If strTech <> "" Then
   strSelect = ("Select * from IT_Servers where  DateEntered Between '" & strStart & "' AND  '" & strEnd & "' And Tech='" & strTech & "'")
Else
   strSelect = ("Select * from IT_Servers where  DateEntered Between '" & strStart & "' AND  '" & strEnd & "')

End if

 

[Maximus007]

Here's the whole code:




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

If Not Page.IsPostBack Then



StartRadio.Checked = True
Calendar1.SelectedDate = Now()
txtStart.Text = Calendar1.SelectedDate
Calendar1.TodaysDate = Calendar1.SelectedDate

'fill the dropdown menu


Dim cmdTech As New SqlCommand("Select distinct(Tech) from It_Servers ", SqlConnection1)
Dim DrTech As SqlDataReader

SqlConnection1.Open()
DrTech = cmdTech.ExecuteReader()


dpName.DataSource = DrTech
dpName.DataTextField = "Tech"
dpName.DataBind()



DrTech.Close()
SqlConnection1.Close()
'Select first item in drop down menu
dpName.Items.Insert(0, New ListItem("All", -1))
'dpName.SelectedIndex = dpName.Items.Count = -1

End If


End Sub

Private Sub Calendar1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged

If StartRadio.Checked = True Then

txtStart.Text = Calendar1.SelectedDate

Else

txtEnd.Text = Calendar1.SelectedDate

End If

End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
reset()
BindData()

End Sub



Private Sub reset()
'reset page index to 0
DataGrid1.CurrentPageIndex = 0

'remove the selection from the datagrid
DataGrid1.SelectedIndex = -1
End Sub



Sub BindData()

Dim strTech = (dpName.SelectedItem.Text)
Dim strStart = (txtStart.Text)
Dim strEnd = (txtEnd.Text)

Dim ObjConnection As SqlConnection
Dim ObjCommand As SqlCommand
Dim ObjAdapter As SqlDataAdapter
DataGrid1.AllowPaging = True

Dim objdataSet As DataSet
Dim strSelect As String
Dim RdCount As Integer
Dim dr As SqlDataReader

'Connection()
ObjConnection = New SqlConnection("Server=Server;database=")

ObjConnection.Open()

'Querying the database
strSelect = ("Select * from IT_Servers where DateEntered Between '" & strStart & "' AND '" & strEnd & "' And Tech='" & strTech & "'")


ObjCommand = New SqlCommand(strSelect, ObjConnection)
ObjAdapter = New SqlDataAdapter(ObjCommand)

objdataSet = New DataSet


ObjAdapter.Fill(objdataSet, "IT_Servers")



DataGrid1.DataSource = objdataSet

RdCount = objdataSet.Tables("IT_servers").Rows.Count

If RdCount = 0 Then
Label1.Text = "No record was found for " & strTech & "!"
DataGrid1.Visible = False
Else
Label1.Text = RdCount & " records were found for " & strTech & "!"
DataGrid1.Visible = True
DataGrid1.DataBind()
End If
ObjConnection.Close()
End Sub
'Page throught data
Sub NewPage(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)
DataGrid1.CurrentPageIndex = e.NewPageIndex
BindData()
End Sub
End Class

 

[jbenson001]

Try using my code in your binddata() sub.

 

[Maximus007]

it gives me: No record was found for All!

 

[jbenson001]

Sorry, change this:

If strTech <> "" Then

to this:

If strTech <> "ALL" Then

 

[Maximus007]

It works like a charm!

Thank you jbenson001

 

[jbenson001]

glad to help