• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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