Solved

ASP.net with Access SQL

Posted on 2011-03-23
3
259 Views
Last Modified: 2012-06-27
Hi

How do I update the SQL statement in the following code:
SELECT DISTINCT [Subject or activity] FROM [Table1]
so that nulls are not included.
Sub LoadSubjectDropDown()
        '// 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
            '// define the sql statement to execute
            Dim cmd As New OleDbCommand("SELECT DISTINCT [Subject or activity] FROM [Table1]", cn)
            '// open the connection
            cn.Open()
            '// execute the sql statement
            Using reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                DropDownList_Subject.DataSource = reader
                DropDownList_Subject.DataValueField = "Subject or activity"
                DropDownList_Subject.DataTextField = "Subject or activity"
                DropDownList_Subject.DataBind()
            End Using
        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
Comment
Question by:murbro
  • 2
3 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 35199281
try

"SELECT DISTINCT [Subject or activity] FROM [Table1] Where [Subject or activity] <> Null"

or

"SELECT DISTINCT [Subject or activity] FROM [Table1] Where [Subject or activity] is not Null"
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
ID: 35199290
or

"SELECT DISTINCT [Subject or activity] FROM [Table1] Where [Subject or activity] & "" <> """
0
 

Author Closing Comment

by:murbro
ID: 35199454
thanks very much
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

948 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now