grid view data

I am working on a ticketing program and I am stuck. Let me tell you how it works so far. I have three screens the first is a ticket selection screen were there is a grid view display of all the events with a buy it now link. When you click the buy it now link it takes you to the second page and brings along the event name, location, and eventid. This info is used to fill a performances dropdown box on this screen were it displays mutlible performances for a event also on this page is a # of tickets textbox were the user can choose the # of tickets up to 9. Theses tickets have to be pull by tickets all together in a row (not spilt). This is were I am having trouble.

Now I need to pull the tickets Rowno, Section, and Seatno from a table based on the performs, Available, and priority ( which is a number I set to show the database which rows,seats,and section have what priority. I need to display this in to a gridview or something to just show what is available. Here is my code I am using.

Code:

---

Public Partial Class TicketOptions
    Inherits System.Web.UI.Page

    Dim perform2 As String

    Dim reader As String
    Dim seat As String
    Dim NumSeats As String
    Dim NotFound As String
    Dim perform As String
    Dim eID As String
   

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

     
        perform = DropDownList2.Text

        EventName.Text = Session("Etitle")
        Location.Text = Session("Epresenter")
        eID = Session("Eeventid")
    End Sub

    Protected Sub TicketNumber_TextChanged(ByVal sender As Object, ByVal e As EventArgs) Handles TicketNumber.TextChanged


        NumSeats = TicketNumber.Text

        Dim myConnection As SqlClient.SqlConnection

        Dim myCommand As New SqlClient.SqlCommand()

       



        seat = NumSeats


        myConnection = New SqlClient.SqlConnection("Data Source=SQLDEV.drake.ad;Initial Catalog=TicketTest;Integrated Security=True;User ID=TicketTestUser;Password=C8MT3wIkhWdapTm27UPR")

        myCommand.CommandText = "SELECT TOP(" + seat + ")[RowNo],[SeatNo],[Section],[priority] FROM [@perform] WHERE ([Available] = @Available) ORDER BY [Priority] DESC [RowNo]"
        GridView1 = myCommand.ExecuteScalar()
        GridView1.DataBind()

        myConnection.Open()
       

       
        myConnection.Close()


    End Sub


    Protected Sub DropDownList2_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DropDownList2.SelectedIndexChanged

              perform2 = DropDownList2.SelectedItem.Text

        Dim eID As String
        Dim myConnection As SqlClient.SqlConnection
        Dim myCommand As SqlClient.SqlCommand

        eID = Session("Eeventid")





        myConnection = New SqlClient.SqlConnection("Data Source=SQLDEV.drake.ad;Initial Catalog=TicketTest;        Integrated Security=True;User ID=TicketTestUser;Password=C8MT3wIkhWdapTm27UPR")



        myCommand.CommandText = "SELECT SeatingTable From Performances WHERE EventId ='" + eID + "' and Occurs='" + perform2 + "' ;"
        perform = myCommand.ExecuteScalar().ToString()

        myConnection.Open()



        myConnection.Close()

    End Sub


End Class

---

so where is the problem in Gridview or Query...?

I can not get it to display!

Try myCommand.ExcuteReader();

myCommand..ExcuteScalar(); why r u using this...?
u returning a single value..?

Quote:

---

Originally Posted by dnanetwork

Try myCommand.ExcuteReader();

myCommand..ExcuteScalar(); why r u using this...?
u returning a single value..?

---

Can you load a Gridview with a reader like that? I usually use DataAdapter.Fill and then set the datasource as the datatable then bind, e.g.:

Code:

---

m_SQLCommand.CommandText = "Select * FROM MyTable"

m_SQLDataAdapter.SelectCommand = m_SQLCommand

m_SQLDataAdapter.Fill(m_Dataset, "MyTable")

gvMyGridview.Datasource = m_Dataset.Tables("MyTable")

gvMyGridview.Databind()

---

Hey,

No, calling .ExecuteReader would require you to loop through the resulting IDataReader, and place the result into a format for binding to the GridView.

However, you don't always have to go to the extent of using an SqlDataAdapter either, it just depends on what you are doing.

dsherwood1204, you are going to want to avoid concatenating your SQL Query string like that, make sure that you always use Parameters when constructing your query. Have a look in my signature for more information on why you should do this.

Also, you are executing the query before you have opened your connection, and this isn't going to work.

Take a look at the ADO.Net links in my signature, especially the last two by jmcilhinney, they should give you an idea about the "correct" way to query the database.

Gary