Saving information from .NET Listbox into SQL Server 2000

[Naters_uk]

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! :!:

[campkev]

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.

[f1 fan]

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.

[Naters_uk]

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.

[Naters_uk]

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

[campkev]

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
}

[Naters_uk]

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.

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
}

[campkev]

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

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.

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

[Naters_uk]

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

[Naters_uk]

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()

[campkev]

well, You are setting up the cmd, but I don't see you executing it anywhere

[f1 fan]

Can i also point out some problems/areas of improvement?
1. dont post your real connection string anywhere in public and especially not in forums :)
2. When you do implement the command execute only the first seat will be saved as you didnt loop through the others
3. Why do you iterate through all the selected items in the list, comma delimit them then go through that list pulling them out one by one again?
4. Please avoid writing sql queries in code. Use stored procedures. I dont know if you plan on using this in the real world or if it is just a project but if you have a webpage with sql code behind it from textboxes etc it takes me less than 10 seconds to get into your database and do whatever i want - read, write, delete and i could probably get into every database on the server from there. Its called SQL injection and it is #1 on hackers things to try on a website. Even if it is just a project, try and get into the habit of writing stored procs so you wont accidently leave sql code in your webpage on a live site one day.

[Naters_uk]

Thanks, i manage to execute it, cmd.ExecuteNonQuery() . Left it out. But now i got this error. What's the problem?

Server Error in '/WebApplication5' Application.
--------------------------------------------------------------------------------

String or binary data would be truncated. The statement has been terminated.

[Naters_uk]

Ok resolved, thanks alot campkev. !!!!!!!!!!!

[Naters_uk]

Campkev,

Once again thanks alot for your explanation and assistance with your postings. I am now stuck with another issue. Now since, i have saved the data into the table with commas. I would like to retrieve the data stored into the to a listbox which will display the seats that have already been booked. However, i am stuck with how can i remove the commas now and display it into the listbox as individual values.

I need to do this so i can disable buttons through the listbox so that tickets have been booked is unable to be rebooked

This is what i have written to retrieve it back to the listbox
Dim da As SqlDataAdapter = New SqlDataAdapter("Select SeatNum from Ticketing", Conn)
'Put user code to initialize the page here
Dim ds As DataSet = New DataSet
' Fill DataSet with the data
da.Fill(ds, "Ticketing")
' Set DataSource property of ListBox as DataSet’s DefaultView
listbox_seatsbooked.DataSource = ds.Tables("Ticketing").DefaultView

listbox_seatsbooked.SelectedIndex = 0
' Set Field Name you want to get data from
listbox_seatsbooked.DataTextField = "SeatNum"
' Bind the data
Page.DataBind()

[tgreer]

Please use code tags. It is very hard to read posts that mix the message with the code. That's what the code tags are for.