none
Filter Datagridview using combobox selected Item and between two datetimepicker values in vb.net_ I am using access database and visual studio 2012 RRS feed

  • Question

  • Hi every body!

    When i run the programme its displaying "Syntax error in JOIN operation",OleDbException was unhandle.

    How can i correct it?

    Here are my codings

    To load the combobox

    Private Sub frmBank_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    lblTime.Text = Now

    Dim obj AsNewCLSMain

    obj.connect_me()'Connects to the database through mainclass

        If obj.con.State = ConnectionState.Open Then

           obj.con.Close()

           End If

         obj.con.Open()

         comboLoadFORMBANK("SELECT * FROM Bank ", 1, cboFORMBank)

         obj.con.Close()

        End Sub

    Sub comboLoadFORMBANK(ByVal sql As String, ByVal No As Integer, ByVal c As ComboBox)

            c.Items.Clear()

            obj.cmd.Connection = obj.con

            obj.cmd.CommandText = sql

            obj.dr = obj.cmd.ExecuteReader

            While obj.dr.Read

                c.Items.Add(obj.dr.GetValue(No))

            End While

            obj.cmd.Dispose()

        End Sub

    Private Sub btnView_Click(sender As Object, e As EventArgs) Handles btnView.Click

            obj.connect_me()

            Dim abc As String

            Dim ds As New DataSet

    abc = " SELECT Issued_Cheque_Details.Issued_Date,Issued_Cheque_Details.Bank,Issued_Cheque_Details.Receivers_Name,Issued_Cheque_Details.Cheque_Number, Issued_Cheque_Details.Cheque_Amount, Issued_Cheque_Details.Postdated_To" & _

    " FROM (Issued_Cheque_Details " & _

    " WHERE (((Issued_Cheque_Details.Bank)='" & cboFORMBank.SelectedItem & "') AND ((Issued_Cheque_Details.Issued_Date) Between #" & dtpStart_Date.Value.Date & "# And #" & dtpEnd_date.Value.Date & "#))"

     Dim ada As New OleDb.OleDbDataAdapter(abc, obj.con)

         ada.Fill(ds)

         DataGridView1.DataSource = ds

     End Sub

    It may be a simple mistake but i couldn't find it out.So kindly someone guide me to correct this mistake

    Thank You.

    Best Regards

    Noel

    Wednesday, August 14, 2013 9:02 AM

Answers

  • Hello Noel_,

    Welcome toMSDN Support Forum.

    From your description, I notice the issue you are experiencing is that when you run the program it occurs an exception.

    As far as I know when an exception occurs like that, it may be that the sql statement is written inappropriate.

    And I notice that the error description shows “Syntax error in JOIN operation”.

    But I have not seen any JOIN operation in your sql statement.

    So is it sure that the error occurs in the code provided by you.

    I recommend that you can use the try-catch to find out the wrong location.

    And I notice that there is a “(” in your sql statement near “Issued_Cheque_Details”, I do not know why do you write that because when I write the sql statement like that it will throw an exception.

    I made a sample with try-catch and please see the sample code below:

    Option Explicit On
    Option Strict On
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Public Class SqlHelper
        Function IsAnswer(answer As String) As Boolean
            Dim connectionString As String = "Data Source=(localdb)\v11.0;Initial Catalog=TestDataBase;Integrated Security=True"
            ' Provide the query string with a parameter placeholder.
            Dim queryString As String = "SELECT answer from answer"
            Dim flag As Boolean = False
            ' Create and open the connection in a using block. This
            ' ensures that all resources will be closed and disposed
            ' when the code exits.
            Using connection As New SqlConnection(connectionString)
                ' Create the Command and Parameter objects.
                Dim command As New SqlCommand(queryString, connection)
                ' Open the connection in a try/catch block.
                ' Create and execute the DataReader, writing the result
                ' set to the console window.
                Try
                    connection.Open()
                    Dim dataReader As SqlDataReader = _
                    command.ExecuteReader()
                    Do While dataReader.Read()
                        If answer.Equals(dataReader(0).ToString()) Then
                            flag = True
                            Exit Do
                        End If
                    Loop
                    dataReader.Close()
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
                Console.ReadLine()
            End Using
            Return flag
        End Function
    End Class

    If it has an error running the sql statement, it will be caught.

    If this is not helpful to you, could you please share your table structure and more codes?

    So that we can help you better.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 15, 2013 7:53 AM
    Moderator
  • Hello,

    A couple of things.

    • Best to use parameters as shown below, see also Creating SQL statement with parameters and more. Downside is now we can not see values in the CommandText but this article with code fixes that.
    • Alternate, calling SQL from MS-Access including passing parameters.
    • No need to use DataAdapter in your case.
    • Not seeing any reason to use a DataSet.
    • You should clean up your SQL along with comments above

    Bottom line is when there is clean statements, minimal code to get a task done you can easily locate issues.

    Conceptual example and best practice for reading data with parameters. Please note MS-Access could care less about the names or positions of parameters, we name parameters for us to understand our code later down the road when we have not worked with the code for a while.

    Special note on parameters, there is an AddWithValue method which is fine to use 99% of the time, when something like this is not 100% I advise not using it for anything other than super simple SQL statements.

    All code below is VS2012 compatible, VB.NET, Option Strict On, Option Infer On. My guess is the SQL is much easier for you to read.

    Public Function GetMyData(ByVal Bank As String, StartDate As Date, EndDate As Date) As DataTable
        Dim dt As New DataTable With {.TableName = "Results"}
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = "TODO"}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT 
                            Issued_Date,
                            Bank,
                            Receivers_Name,
                            Cheque_Number, 
                            Cheque_Amount, 
                            Postdated_To
                        FROM 
                            Issued_Cheque_Details
                        WHERE 
                            (Bank= @Bank) AND (Issued_Date Between @Start_Date And @End_date)
                    </SQL>.Value
                cmd.Parameters.AddRange(
                    New OleDb.OleDbParameter() _
                    {
                        New OleDb.OleDbParameter With {.ParameterName = "@Bank", .DbType = DbType.String},
                        New OleDb.OleDbParameter With {.ParameterName = "@Start_Date", .DbType = DbType.Date},
                        New OleDb.OleDbParameter With {.ParameterName = "@End_Date", .DbType = DbType.Date}
                    }
                )
                cmd.Parameters("@Bank").Value = Bank
                cmd.Parameters("@Start_Date").Value = StartDate
                cmd.Parameters("@End_Date").Value = EndDate
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
        Return dt
    End Function

    The following is demoing a Data Reader

    Public Function DemoUsingReader(ByVal SelectStatement As String, Index As Integer) As String()
        Dim ItemList As New List(Of String)
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = "TODO"}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = SelectStatement}
                cn.Open()
                Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader
                If Reader.HasRows Then
                    While Reader.Read
                        ItemList.Add(Reader.GetValue(Index).ToString)
                    End While
                End If
            End Using
        End Using
        Return ItemList.ToArray
    End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, August 15, 2013 1:25 PM

All replies

  • Hello Noel_,

    Welcome toMSDN Support Forum.

    From your description, I notice the issue you are experiencing is that when you run the program it occurs an exception.

    As far as I know when an exception occurs like that, it may be that the sql statement is written inappropriate.

    And I notice that the error description shows “Syntax error in JOIN operation”.

    But I have not seen any JOIN operation in your sql statement.

    So is it sure that the error occurs in the code provided by you.

    I recommend that you can use the try-catch to find out the wrong location.

    And I notice that there is a “(” in your sql statement near “Issued_Cheque_Details”, I do not know why do you write that because when I write the sql statement like that it will throw an exception.

    I made a sample with try-catch and please see the sample code below:

    Option Explicit On
    Option Strict On
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Public Class SqlHelper
        Function IsAnswer(answer As String) As Boolean
            Dim connectionString As String = "Data Source=(localdb)\v11.0;Initial Catalog=TestDataBase;Integrated Security=True"
            ' Provide the query string with a parameter placeholder.
            Dim queryString As String = "SELECT answer from answer"
            Dim flag As Boolean = False
            ' Create and open the connection in a using block. This
            ' ensures that all resources will be closed and disposed
            ' when the code exits.
            Using connection As New SqlConnection(connectionString)
                ' Create the Command and Parameter objects.
                Dim command As New SqlCommand(queryString, connection)
                ' Open the connection in a try/catch block.
                ' Create and execute the DataReader, writing the result
                ' set to the console window.
                Try
                    connection.Open()
                    Dim dataReader As SqlDataReader = _
                    command.ExecuteReader()
                    Do While dataReader.Read()
                        If answer.Equals(dataReader(0).ToString()) Then
                            flag = True
                            Exit Do
                        End If
                    Loop
                    dataReader.Close()
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
                Console.ReadLine()
            End Using
            Return flag
        End Function
    End Class

    If it has an error running the sql statement, it will be caught.

    If this is not helpful to you, could you please share your table structure and more codes?

    So that we can help you better.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 15, 2013 7:53 AM
    Moderator
  • Hello,

    A couple of things.

    • Best to use parameters as shown below, see also Creating SQL statement with parameters and more. Downside is now we can not see values in the CommandText but this article with code fixes that.
    • Alternate, calling SQL from MS-Access including passing parameters.
    • No need to use DataAdapter in your case.
    • Not seeing any reason to use a DataSet.
    • You should clean up your SQL along with comments above

    Bottom line is when there is clean statements, minimal code to get a task done you can easily locate issues.

    Conceptual example and best practice for reading data with parameters. Please note MS-Access could care less about the names or positions of parameters, we name parameters for us to understand our code later down the road when we have not worked with the code for a while.

    Special note on parameters, there is an AddWithValue method which is fine to use 99% of the time, when something like this is not 100% I advise not using it for anything other than super simple SQL statements.

    All code below is VS2012 compatible, VB.NET, Option Strict On, Option Infer On. My guess is the SQL is much easier for you to read.

    Public Function GetMyData(ByVal Bank As String, StartDate As Date, EndDate As Date) As DataTable
        Dim dt As New DataTable With {.TableName = "Results"}
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = "TODO"}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT 
                            Issued_Date,
                            Bank,
                            Receivers_Name,
                            Cheque_Number, 
                            Cheque_Amount, 
                            Postdated_To
                        FROM 
                            Issued_Cheque_Details
                        WHERE 
                            (Bank= @Bank) AND (Issued_Date Between @Start_Date And @End_date)
                    </SQL>.Value
                cmd.Parameters.AddRange(
                    New OleDb.OleDbParameter() _
                    {
                        New OleDb.OleDbParameter With {.ParameterName = "@Bank", .DbType = DbType.String},
                        New OleDb.OleDbParameter With {.ParameterName = "@Start_Date", .DbType = DbType.Date},
                        New OleDb.OleDbParameter With {.ParameterName = "@End_Date", .DbType = DbType.Date}
                    }
                )
                cmd.Parameters("@Bank").Value = Bank
                cmd.Parameters("@Start_Date").Value = StartDate
                cmd.Parameters("@End_Date").Value = EndDate
                cn.Open()
                dt.Load(cmd.ExecuteReader)
            End Using
        End Using
        Return dt
    End Function

    The following is demoing a Data Reader

    Public Function DemoUsingReader(ByVal SelectStatement As String, Index As Integer) As String()
        Dim ItemList As New List(Of String)
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = "TODO"}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = SelectStatement}
                cn.Open()
                Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader
                If Reader.HasRows Then
                    While Reader.Read
                        ItemList.Add(Reader.GetValue(Index).ToString)
                    End While
                End If
            End Using
        End Using
        Return ItemList.ToArray
    End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Thursday, August 15, 2013 1:25 PM