ASP.net SQL statement not working

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

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
Alfred A.Commented:
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
 
Pratima PharandeCommented:
try thsi

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


what is exact school value ?
0
 
ramkihardyCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Easwaran ParamasivamCommented:
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
 
Easwaran ParamasivamCommented:
Please use special characters with char(code) function. Say for example aphastaphe can be replaced with Char(34).
0
 
Alfred A.Commented:
Oh, I didn't noticed you are accessing an Access Database, ignore my question in my previous post.
0
 
Alfred A.Commented:
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
 
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.

All Courses

From novice to tech pro — start learning today.