• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

ASP.net SQL problem get rid of spaces

Hi

I am using the code below to populate a DropDownList in ASP.net with distinct items from an Access column. The problem that I am getting is that there is a space in one of the distinct items.
How do get rid of the space? eg " Parents" should be "Parents"
Sub LoadApplicableToListBox()
        '// define a connection to the database
        Dim cs As String = ConfigurationManager.ConnectionStrings("WhatEverNameYouWant").ConnectionString
        cs = cs.Replace("App_Data\GC.accdb", Server.MapPath("App_Data\GC.accdb"))

        Dim cn As New OleDbConnection(cs)
        Try
            Dim oSchool As String = Me.ListBox_School.SelectedValue
            '// define the sql statement to execute

            Dim cmd As New OleDbCommand("SELECT DISTINCT [Applicable to] FROM [Table1] Where [School] = '" & SQLConvert(oSchool) & "'", cn)
            '// open the connection
            cn.Open()
            '// execute the sql statement
            Using reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                Me.ListBox_ApplicableTo.DataSource = reader
                Me.ListBox_ApplicableTo.DataValueField = "Applicable to"
                Me.ListBox_ApplicableTo.DataTextField = "Applicable to"
                Me.ListBox_ApplicableTo.DataBind()
                Me.ListBox_ApplicableTo.Items.Remove(Me.ListBox_ApplicableTo.Items.FindByValue("")) 'delete any superfluous blank
            End Using
            Me.ListBox_ApplicableTo.Items.Remove(Me.ListBox_ApplicableTo.Items.FindByValue("")) 'delete any superfluous blank
            Me.ListBox_ApplicableTo.Items.Insert(0, New ListItem("-", "-"))
            ListBox_ApplicableTo.SelectedValue = "-"
        Catch ex As Exception
            Response.Write(ex.Message)

        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If
        End Try
    End Sub

Open in new window

0
Murray Brown
Asked:
Murray Brown
  • 2
1 Solution
 
Pratima PharandeCommented:
use this


            Dim cmd As New OleDbCommand("SELECT DISTINCT Trim([Applicable to]) FROM [Table1] Where [School] = '" & SQLConvert(oSchool) & "'", cn)
0
 
Pratima PharandeCommented:
Sub LoadApplicableToListBox()
        '// define a connection to the database
        Dim cs As String = ConfigurationManager.ConnectionStrings("WhatEverNameYouWant").ConnectionString
        cs = cs.Replace("App_Data\GC.accdb", Server.MapPath("App_Data\GC.accdb"))

        Dim cn As New OleDbConnection(cs)
        Try
            Dim oSchool As String = Me.ListBox_School.SelectedValue
            '// define the sql statement to execute

            Dim cmd As New OleDbCommand("SELECT DISTINCT Trim([Applicable to]) FROM [Table1] Where [School] = '" & SQLConvert(oSchool) & "'", cn)
            '// open the connection
            cn.Open()
            '// execute the sql statement
            Using reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                Me.ListBox_ApplicableTo.DataSource = reader
                Me.ListBox_ApplicableTo.DataValueField = "Applicable to"
                Me.ListBox_ApplicableTo.DataTextField = "Applicable to"
                Me.ListBox_ApplicableTo.DataBind()
                Me.ListBox_ApplicableTo.Items.Remove(Me.ListBox_ApplicableTo.Items.FindByValue("")) 'delete any superfluous blank
            End Using
            Me.ListBox_ApplicableTo.Items.Remove(Me.ListBox_ApplicableTo.Items.FindByValue("")) 'delete any superfluous blank
            Me.ListBox_ApplicableTo.Items.Insert(0, New ListItem("-", "-"))
            ListBox_ApplicableTo.SelectedValue = "-"
        Catch ex As Exception
            Response.Write(ex.Message)

        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If
        End Try
    End Sub

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now