Saving information from .NET Listbox into SQL Server 2000
 

I am doing a movie booking system, my booking page consists of buttons, representing as seats. Each time this seat is clicked, the button number is listed into a listbox.

For example, buttonA1, buttonA2 are clicked. The listbox display this two as A1 and A2. How can i save A1 & A2 into the SQL Database without selecting them with index.

Also, how can i save them as two separated records with commas in the same column in the SQL table known as SeatNo.

Is it possible? Help urgently! :!:

Re: Saving information from .NET Listbox into SQL Server 2000
 

I'm not real great with VB (I work in c#) but if your code for getting from the buttons into the select box, I may be able to help you figure out what you want to do.

Re: Saving information from .NET Listbox into SQL Server 2000
 

I already gave you the code to get the seat numbers and put them into a table. Why didnt you just bind this table to the listbox? Then your table is ready to be saved in the database.

Re: Saving information from .NET Listbox into SQL Server 2000
 

This is what you wrote, f1_fan

Re: URGENT!: Saving Database information from ASP.NET Button
Jan 20th 2006, 02:43 AM | Add to f1 fan's Reputation | Flag Bad Post | #11

im not doing your homework for you as i said... and you dont seem to want to help yourself.
To store information in the session you create a key for it and assign your information you want to store in there to it.

Session("theseats") = the_collection_you_store_your_seats_in

you have to get your collection out of the session to add your seats to it and then store it back in the session so you need

if (session("theseats") != null) then
the_collection_you_store_your_seats_in = Ctype(session("theseats"), thecollectiontype)
end if

then when you want to store in the database just store the collection



This is what i try to write with what you said
Private Sub a1_Command(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles a1.Command
If (a1.BackColor.Equals(System.Drawing.Color.Red)) Then
TextBox2.Text = e.CommandArgument
End If

I am not a programming expert. I can't understand persudo codes at one go. You said there's a table. I dont know what you are referring to. On top of that, since i put everything into a listbox. I just want to know how to bind the information of the seatbuttons from the listbox into the database, with a separate commas in between using Split function which i am still figuring out. For example, Seat A1 A2 etc will be save as A1,A2,A3 in the database.

Re: Saving information from .NET Listbox into SQL Server 2000
 

campkev: Thanks, i appreciate it. I manage to find a C# to VB converter.

I'm not real great with VB (I work in c#) but if your code for getting from the buttons into the select box, I may be able to help you figure out what you want to do.

This is how i have written for the button to be place as an information into the listbox. Basically, this button represents seat A3 and it is added to the listbox as A3 once clicked. I would like to save this from the list box into the database. For instance, A3,A4,A5 with a comma to separate the seats. I read up about the split function but i am not sure if its the right one to use.
Sub a3_Click(ByVal sender As Object, ByVal e As EventArgs)

If (a3.BackColor.Equals(System.Drawing.Color.Red)) Then

a3.BackColor = System.Drawing.Color.Black
listbox_seatselected.Items.Remove("A3")

Else

a3.BackColor = System.Drawing.Color.Red
listbox_seatselected.Items.Add("A3")

End If

Re: Saving information from .NET Listbox into SQL Server 2000
 

then you want something like

string newValue = "";

for(int  = 0, i < listbox_seatselected.Items.Count; i++){

newValue = newValue + ", " + listbox_seatselected.Items[i].ToString();

}

//newValue should now look like ", A1, A3, A5"

if(newValue.Length > 2){

newValue = newValue.Substring(2);  //removes the first ", " from the list 

                                                  //newValue should now look like "A1, A3, A5"

//then just update the database with this string

}

Re: Saving information from .NET Listbox into SQL Server 2000
 

I have tried rewriting this part into vb.net codes.

'To create an array of the seat names in the listbox as i have 152 buttons for seats
Dim nameArray(151) As String 'make room for 151 elements in the array
Dim i As Integer

For i = 0 To listbox_seatselected.Items.Count - 1
'assign a name to each indea in the array and should looke like "A1,A2,A3"
nameArray(i) = nameArray(151) + ", " + listbox_seatselected.Items(i).ToString()
Next
I do not understand the if(newValue.Length > 2){
newValue = newValue.Substring(2); //removes the first ", " from the list. I would appreciate if u can explain this section to me. I appreciate all your responses for trying to help me out.

Re: Saving information from .NET Listbox into SQL Server 2000
 

no no no.
you aren't making an array of strings you are making one string called newValue

change this

'To create an array of the seat names in the listbox as i have 152 buttons for seats

Dim nameArray(151) As String 'make room for 151 elements in the array

Dim i As Integer



For i = 0 To listbox_seatselected.Items.Count - 1

'assign a name to each indea in the array and should looke like "A1,A2,A3"

nameArray(i) = nameArray(151) + ", " + listbox_seatselected.Items(i).ToString()

Next

to this

'To create an array of the seat names in the listbox as i have 152 buttons for seats

Dim seatListString As String 'make new string

Dim i As Integer

seatListString = ""     ' start off with an blank string

For i = 0 To listbox_seatselected.Items.Count - 1

seatListString= seatListString + ", " + listbox_seatselected.Items(i).ToString()

Next

as for your next question
when you are done with the for loop the seatListString will look like this:
", A1, A2, A3"
but we don't want that.
so we check that seatListString is longer than 2 characters (which indicates that the user actually select at least one seat. it also prevents the next step from causing an error)
we take the substring of that string starting after the second character;

which gives us a string that looks like "A1, A2, A3"
and we are ready to update our database with this new string

if  seatListString.Length > 2 then

seatListString.Length = seatListString.Length.Substring(2);  

// then update your database

else

//do whatever you want to do if they didn't select any seats

end if

and hey, I am learning VB

Re: Saving information from .NET Listbox into SQL Server 2000
 

I am getting an error that substring is not a member of integer
If seatListString.Length > 2 Then
seatListString.Length = seatListString.Length.Substring(2)
End If

Re: Saving information from .NET Listbox into SQL Server 2000
 

This is what i have written to save it into the database, but nothing was save into the table and no error was displayed. What's possible wrong??

Protected Sub btn_confirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_confirm.Click
Dim Conn As New SqlConnection
Conn = New SqlConnection("workstation id=""IBM-89118400585"";packet size=4096;integrated security=SSPI;data source=""IBM-89118400585\MSSQLSERVER1"";persist security info=True;initial catalog=Republic Theatre DataBase")


DropDownMovie.SelectedValue = Session("MovieSelected")
DropDown_Time.SelectedValue = Session("MovieTimeSelected")
DropDown_Date.SelectedValue = Session("MovieDate")
'listbox_seatselected.Rows = Session("SeatSelected")
Conn.Open()
'To create an array of the seat names in the listbox as i have 152 buttons for seats
Dim seatListString As String 'make new string
Dim i As Integer
seatListString = "" ' start off with an blank string
For i = 0 To listbox_seatselected.Items.Count - 1
seatListString = seatListString + ", " + listbox_seatselected.Items(i).ToString()
Next

If seatListString.Length > 2 Then
seatListString = seatListString.Substring(2) 'removes the first ", " from the list () As NewValue.Substring(2)
End If

Dim cmd As New SqlCommand("Insert into Ticketing (MemberUserName, MovieID, MovieTitle, MovieDate, MovieTime, NRIC, SeatNum, MemberEmail, Amount) Values (@MemberUserName, @MovieID, @MovieTitle, @MovieDate, @MovieTime, @NRIC, @SeatNum, @MemberEmail, @Amount)", Conn)
With cmd.Parameters
.Add("@MemberUserName", Session("Username"))
.Add("@MovieID", txtbox_movieid.Text.Trim)
.Add("@MovieTitle", DropDownMovie.SelectedValue)
.Add("@MovieDate", DropDown_Date.SelectedValue)
.Add("@MovieTime", DropDown_Time.SelectedValue)
.Add("@NRIC", lblnric.Text.Trim)
.Add("@SeatNum", seatListString)
.Add("@MemberEmail", lblemail.Text.Trim)
.Add("@Amount", lbl2.Text)

End With
Conn.Close()