?
Solved

ASP.net SQL statement not working

Posted on 2011-05-10
8
Medium Priority
?
252 Views
Last Modified: 2012-05-11
Hi

In my ASP.net code I am getting data from a table but for some reason I'm getting nothing.
Is there something wrong with my syntax
Sub A

        '// define a connection to the database
        Dim cs As String = ConfigurationManager.ConnectionStrings("WhatEverNameYouWant").ConnectionString

        Dim oSchool As String = Me.ListBox_School.Text


        cs = cs.Replace("App_Data\GC.accdb", Server.MapPath("App_Data\GC.accdb"))

        Dim cn As New OleDbConnection(cs)

        '// define the sql statement to execute
     
        Dim sSQL As String
        'sSQL = "SELECT * FROM [Table1] WHERE [School] = '" & SQLConvert(oSchool) & "'"
        sSQL = "SELECT * FROM [Table1] WHERE [School] = 'St Stithian''s JP' And ([Applicable to] = 'PARENT')"
        Dim cmd As New OleDbCommand(sSQL, cn)

        Try

            '// open the connection
            cn.Open()

            '// execute the sql statement
            Using reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                ' While reader.Read()
                '// this loops through all of the returned records
                'Response.Write("blah")
                'End While

                GridView1.DataSource = reader
                GridView1.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:Murray Brown
8 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35727123
try thsi

 sSQL = "SELECT * FROM [Table1] WHERE [School] = 'St Stithian's JP' And ([Applicable to] = 'PARENT')"


what is exact school value ?
0
 
LVL 2

Expert Comment

by:ramkihardy
ID: 35727139
use this you will get the result..

 sSQL = "SELECT * FROM [Table1] WHERE [School] = 'St Stithian/'s JP' And ([Applicable to] = 'PARENT')"

If any error arrises...please post that error and the line in which it showing...

Regards
ramki...
0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 35727169
I belive the problem with the line

sSQL = "SELECT * FROM [Table1] WHERE [School] = 'St Stithian''s JP' And ([Applicable to] = 'PARENT')"

First of all Column name in database should not contain Space. I don't think so why did you use () in second condition?

Please try to correct it
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 35727190
Please use special characters with char(code) function. Say for example aphastaphe can be replaced with Char(34).
0
 
LVL 21

Accepted Solution

by:
Alfred A. earned 2000 total points
ID: 35727221
The query syntax is correct.  You need to escape the apostrophe with another apostrophe ('St Stithian''s JP').

Have you tried running your select query below in a query analyzer?

SELECT * FROM [Table1] WHERE [School] = 'St Stithian''s JP' And ([Applicable to] = 'PARENT')
0
 
LVL 21

Expert Comment

by:Alfred A.
ID: 35727381
Oh, I didn't noticed you are accessing an Access Database, ignore my question in my previous post.
0
 
LVL 21

Assisted Solution

by:Alfred A.
Alfred A. earned 2000 total points
ID: 35736042
OK.  I created my own Access Database and tested your code and it works just fine.  Note that I added the access db file doing a right-click on App_Data folder and selecting Add --> Existing Item and browsed the created file.  You might need to check your connection string.


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        
        Dim cs As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=App_Data\Test1.accdb;"
        cs = cs.Replace("App_Data\Test1.accdb", Server.MapPath("~\App_Data\Test1.accdb"))

        Dim cn As New OleDbConnection(cs)

        Dim sSQL As String

        sSQL = "SELECT * FROM [Tester] WHERE [School] = 'Test''s' And ([Applicable To] = 'PARENT')"
        Dim cmd As New OledbCommand(sSQL, cn)

        Try
            cn.Open()

            Using reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

                GridView1.DataSource = reader
                GridView1.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
 

Author Closing Comment

by:Murray Brown
ID: 35736076
Thanks very much
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Integration Management Part 2
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

621 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