• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

Why is this query only getting the first value in the database?

I don't know much about SQL - sorry.

Attached are two screenshots.
Screenshot 1)   The page the user searches from
Screenshot 2)   The search results page

I checked only the topmost checkbox to get the result I got on the second page.

There are two Response.Writes on the search results page.  One shows the contents of the query passed in the session variable.  The other shows the contents of the session variable containing the results of the query ("5", the first JobID with "Full Time Job" as the JobType in the database).

Am I doing something wrong?
VB CODE FOR THE SEARCH FILE THAT BUILDS THE SQL QUERY
 
Partial Class StudentSearchJobs
    Inherits System.Web.UI.Page
 
    Protected Sub btn_Search_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Search.Click
        Dim JobCommand As String = "SELECT JobID, JobTitle FROM [Jobs] "
        Dim TypeCheck As Boolean = False
 
        '   Check every checkbox and build the query with all that are checked
 
        '   Build the JobCommand
        If CBT_FullTimePermanent.Checked = True Then
            JobCommand += "WHERE ([JobType] = 'Full Time Job'"
            TypeCheck = True
        End If
        JobCommand += ")"
 
        Session("MyQuery") = JobCommand
 
        Server.Transfer("~/StudentSearchResults.aspx")
 
    End Sub
End Class
 
 
*****************************************************************************
VB CODE FOR THE SEARCH RESULTS FILE
Imports System.Data
Imports System.Data.SqlClient
 
Partial Class StudentSearchResults
    Inherits System.Web.UI.Page
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
        Dim myConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\CAC.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        Using myConnection As New SqlConnection(myConnectionString)
 
            Dim myCommand As New SqlCommand()
            myCommand.Connection = myConnection
            myConnection.Open()
            myCommand.CommandText = Session("MyQuery")
            Session("TryThis") = myCommand.ExecuteScalar()
            myConnection.Close()
 
        End Using
 
    End Sub
End Class

Open in new window

SearchJobs.gif
SearchResults.gif
0
spaceneedlejumper
Asked:
spaceneedlejumper
  • 2
  • 2
1 Solution
 
Ramesh SrinivasTechnical ConsultantCommented:
Hi,

ExecuteScalar() will only return a single value back.

thx.
0
 
spaceneedlejumperAuthor Commented:
That was quick!  Do you know the proper command?

Thank you very much.

0
 
Ramesh SrinivasTechnical ConsultantCommented:
Try the following...

Dim myCommand As New SqlCommand()
Dim da As SqlDataAdapter = New SqlDataAdapter
Dim ds As DataSet = New DataSet
myCommand.Connection = myConnection
myCommand.CommandText = Session("MyQuery")
da.SelectCommand = myCommand
da.Fill(ds)
For Each dr As DataRow In ds.Tables(0).Rows
  Response.Write(dr("JobID") & "<BR>" & dr("JobTitle"))
Next

This will output all matching rows.

regards.
0
 
spaceneedlejumperAuthor Commented:
Thank you very much again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now