?
Solved

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

Posted on 2009-02-24
4
Medium Priority
?
186 Views
Last Modified: 2012-05-06
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
Comment
Question by:spaceneedlejumper
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Ramesh Srinivas
ID: 23720169
Hi,

ExecuteScalar() will only return a single value back.

thx.
0
 

Author Comment

by:spaceneedlejumper
ID: 23720191
That was quick!  Do you know the proper command?

Thank you very much.

0
 
LVL 11

Accepted Solution

by:
Ramesh Srinivas earned 2000 total points
ID: 23720210
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
 

Author Comment

by:spaceneedlejumper
ID: 23720269
Thank you very much again!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month16 days, 13 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question