?
Solved

How to use a listBox w/MSSQL database?

Posted on 2005-04-17
17
Medium Priority
?
186 Views
Last Modified: 2010-04-07
I am not sure the best way to go about this.
I have a listBox that I am using for days of the week.
My project is concerning classes, i.e. algebra, physics, P.E. etc.  
Each class is on certain days of the week.  I want to be able to select which ever days I want.  For instance P.E. is on Monday, Wednesday, and Friday.  So I would select Monday, Wednesday and Friday.
I always want the listBox to be populated with all seven days of the week.

What is the best way to store this in the database?
Should I be using something else instead like checkboxes?

Also, these values need to be read from the database at a later time to have these specific dates selected in this same listBox.
0
Comment
Question by:jettman26
  • 9
  • 8
17 Comments
 

Author Comment

by:jettman26
ID: 13807277
Maybe an increase in points will help get me a reply.  :)
0
 

Author Comment

by:jettman26
ID: 13810585
Lets make it 500.  I really need to get past this block.
0
 
LVL 18

Expert Comment

by:tusharashah
ID: 13811074
As the value of ListBox is alwayz going to be same write down 7 days in ListBox first... Now in your Database save SelectedDates as one string seperated by comma or semicolon..

--- To Store Value in Database
        string strDay = "";
        foreach (ListItem _li in ListBox1.Items)
        {
            if (_li.Selected)
            {
                strDay += _li.Value + ";";
            }
        }
 --> Now store strDay in your Database
 
--- Retrieve that field from Database and show selected values using following code:
        string[] strDays = strDay.Split(';');
        for (int i = 0; i < strDays.Length - 1; i++)
        {
            ListBox1.Items.FindByText(strDays[i]).Selected = true;
        }


-tushar
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jettman26
ID: 13811204
Looks good but I am having syntax errors all over the place.  I am using VB.NET if that makes any difference.
0
 
LVL 18

Expert Comment

by:tusharashah
ID: 13811266
A bit:

1)
Dim strDay As String = "" 
For Each _li As ListItem In ListBox1.Items
 If _li.Selected Then
   strDay += _li.Value + ";"
 End If
Next

2)
Dim strDays As String() = strDay.Split(";")
Dim i As Integer = 0
While i < strDays.Length - 1
 ListBox1.Items.FindByText(strDays(i)).Selected = True
 i = i+1
End While
0
 

Author Comment

by:jettman26
ID: 13811309
This is a lot better.  When I put in
Dim strDays As String() = strDays.Split(";")
        Dim i As Integer = 0
        While i < strDays.Length - 1
            daysListBox.Items.FindByText(strDays(i)).Selected = True
            i = i + 1
        End While

It doesn't like strDays.Split(";")

It says "'Split' is not a member of 'System.Array'"
0
 
LVL 18

Expert Comment

by:tusharashah
ID: 13811349
Modify it like:: strDays.Split(';')

0
 

Author Comment

by:jettman26
ID: 13811441
It likes that even worse.
A lot of blue wavy lines now.
0
 
LVL 18

Expert Comment

by:tusharashah
ID: 13811452
show me your code..
0
 

Author Comment

by:jettman26
ID: 13811512
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            If Not (Request.QueryString("class_name") Is Nothing) Then
                GetInfo(Request.QueryString("class_name").ToString)
            End If
        End If

    End Sub
   


    Private Sub GetInfo(ByVal strClassName As String)
        strClassName = Request.QueryString("class_name").ToString

        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        Dim strDays As String() = strDays.Split(";")
        Dim i As Integer = 0
        While i < strDays.Length - 1
            daysListBox.Items.FindByText(strDays(i)).Selected = True
            i = i + 1
        End While

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT * FROM CLASSES " & _
        "WHERE class_name = '" & strClassName & "'"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                classText.Text = myReader("class_name").ToString.Trim
                professorText.Text = myReader("professor").ToString.Trim
                startText.Text = myReader("time_start").ToString
                endText.Text = myReader("time_end").ToString
                daysListBox.DataTextField = myReader("days").ToString
                roomText.Text = myReader("room").ToString.Trim

            End While
            lblMessage.Text = "Record Successfully Read"
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String
        Dim Item As ListItem
        Dim myString As String

        Dim strDay As String = ""
        For Each _li As ListItem In daysListBox.Items
            If _li.Selected Then
                strDay += _li.Value + ";"
            End If
        Next

        'For Each Item In daysListBox.Items

        'If (Item.Selected) Then
        'strDay += Item.Value + ";"
        'End If
        'Next



        'For Each Item In daysListBox.Items
        'If Item.Selected = True Then
        'myString += Item.Text         '<-------OR Item.Value whichever the case may be
        'End If
        'Next


        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand

        If Not (Request.QueryString("class_name") Is Nothing) Then    '<---------******* Modified
            myCommand.CommandText = "UPDATE CLASSES SET professor = '" & professorText.Text & "', time_start = '" & startText.Text & "', time_end = '" & endText.Text & "', room = '" & roomText.Text & "', days = '" & strDay & "' WHERE Class_Name='" & Request.QueryString("class_name").ToString & "'"

        Else
            myCommand.CommandText = "INSERT INTO CLASSES VALUES ('" & classText.Text & "', '" & professorText.Text & "', '" & startText.Text & "', '" & endText.Text & "', '" & roomText.Text & "', '" & strDay & "')"
        End If

        myConn.Open()
        Try
            myCommand.ExecuteNonQuery()
            lblMessage.Text = "Record Successfully Added"
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()                  '<------------------------Make sure to close your connection.

    End Sub
0
 
LVL 18

Expert Comment

by:tusharashah
ID: 13811548
First Change this and let me know where are you getting Errors when you compile:
   Dim strDays As String() = strDays.Split(";")

Change this to:
  Dim strDays As String() = strDays.Split(';')    

-tushar
0
 

Author Comment

by:jettman26
ID: 13811586
strDays.Split(';')  'says "Expression expected."  The blue wavy line is between the t and the (
While i < strDays.Length - 1  'says "Name 'strDays' is not Declared"
daysListBox.Items.FindByText(strDays(i)).Selected = True   'says "Name 'strDays' is not Declared"
0
 
LVL 18

Accepted Solution

by:
tusharashah earned 2000 total points
ID: 13811655
There's some Re-Ordering required in GetInfo Function try this:

-------------------------------------------------------------------------------------------
    Private Sub GetInfo(ByVal strClassName As String)
        strClassName = Request.QueryString("class_name").ToString

        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        Dim strDays As String()

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT * FROM CLASSES " & _
        "WHERE class_name = '" & strClassName & "'"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                classText.Text = myReader("class_name").ToString.Trim
                professorText.Text = myReader("professor").ToString.Trim
                startText.Text = myReader("time_start").ToString
                endText.Text = myReader("time_end").ToString
                daysListBox.DataTextField = myReader("days").ToString().Split(';')
                roomText.Text = myReader("room").ToString.Trim

            End While

            Dim i As Integer = 0
            While i < strDays.Length - 1
                daysListBox.Items.FindByText(strDays(i)).Selected = True
                i = i + 1
             End While

            lblMessage.Text = "Record Successfully Read"
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub
-------------------------------------------------------------------------------------------

-tushar
0
 
LVL 18

Expert Comment

by:tusharashah
ID: 13811667
Still one more modification in above code:

Change: daysListBox.DataTextField = myReader("days").ToString().Split(';')

To: strDays = myReader("days").ToString().Split(';')
0
 

Author Comment

by:jettman26
ID: 13811703
strDays = myReader("days").ToString().Split(';')
It says "Expression expected."  with the wavy blue line between Split and (';')
0
 
LVL 18

Expert Comment

by:tusharashah
ID: 13811816
bit modification in syntax in split (vb..)

strDays = myReader("days").ToString().Split(";"c)
0
 

Author Comment

by:jettman26
ID: 13811837
Works good.
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Suggested Courses
Course of the Month9 days, 13 hours left to enroll

609 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