i m using vb.net with oledb connection
when retrieving data from EMP database i m getting error as syntax error(comma) in query expression 'eno='1',lname='',fname='' And FromPay=## And ToPay=##'
plz help...

Private Sub PopulateNameList()
        Dim Connection As New OleDbConnection(ACCESS_CONNECTION_STRING)

        lvName.Items.Clear()
        lvName.Sorting = SortOrder.Ascending

        Dim cmdfill As New OleDbCommand("SELECT eno, lname & ', ' & fname as Name FROM EMP", Connection)

        Try
            Connection.Open()
           Dim datareader As OleDbDataReader
            datareader = cmdfill.ExecuteReader

            While datareader.Read
                Dim lvi As New ListViewItem
                lvi.Text = CStr(datareader("eno"))
                lvi.SubItems.Add(CStr(datareader("Name")))
                lvName.Items.Add(lvi)
            End While

            If datareader.HasRows Then
                lvName.TopItem.Selected = True
                lvName.TopItem.Focused = True
                lvName.TopItem.EnsureVisible()
                lvName.Select()

                'lvName.Items(0).Selected = True
                'strSelectedeno = lvName.Items(0).SubItems(0).Text
                '    lvName_SelectedIndexChanged(Nothing, Nothing)

            End If

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
        Finally
            Connection.Close()
        End Try
    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
        mainform.Show()

    End Sub

    Private Sub lvName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvName.SelectedIndexChanged
        Dim cnOleDb As OleDbConnection
        Dim cmSQL As OleDbCommand
        Dim drSQL As OleDbDataReader
        Dim strSQL As String
        'Dim strID As String
        Dim ThisItem As ListViewItem

        If IsNothing(lvName.FocusedItem) Then Exit Sub

        For Each ThisItem In lvName.SelectedItems
            strSelectedeno = lvName.Items(ThisItem.Index).SubItems(0).Text

        Next
        strSelectedeno = lvName.FocusedItem.Text

        Try
            strSQL = "SELECT eno," & _
            "lname, " & _
            "fname, " & _
             "DailyRate, " & _
             "Allowance, " & _
             "Incentives " & _
             "FROM EMP " & _
             "WHERE eno = '" & strSelectedeno & "'"

            cnOleDb = New OleDbConnection(ACCESS_CONNECTION_STRING)
            cnOleDb.Open()

            cmSQL = New OleDbCommand(strSQL, cnOleDb)
            drSQL = cmSQL.ExecuteReader()

            drSQL.Read()

            txtDailyRate.Text = Format(Convert.ToDouble(drSQL.Item("DailyRate").ToString()), "0.00")
            dblAllowance = Format(Convert.ToDouble(drSQL.Item("Allowance").ToString()), "0.00")

            txtAllowance.Text = dblAllowance
            dblIncentives = Format(Convert.ToDouble(drSQL.Item("Incentives").ToString()), "0.00")
            txtIncentives.Text = dblIncentives

            ' Close and Clean up objects
            drSQL.Close()
            cnOleDb.Close()
            cmSQL.Dispose()
            cnOleDb.Dispose()

            GetPayslip()

            txtTotal.Text = Format(txtworkdays.Text * txtDailyRate.Text, "0.00")
            Calc_Gross()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub

in above code i m getting the error as
syntax error(comma) in query expression 'eno='1',lname='',fname='' And FromPay=## And ToPay=##'.
i m not getting from where this error is coming.
plz help.

Recommended Answers

All 25 Replies

I can't see the block of the code that you are referring to in the code that you have posted here.

i m using vb.net with oledb connection
when retrieving data from EMP database i m getting error as syntax error(comma) in query expression 'eno='1',lname='',fname='' And FromPay=## And ToPay=##'
plz help...

Private Sub PopulateNameList()
Dim Connection As New OleDbConnection(ACCESS_CONNECTION_STRING)

lvName.Items.Clear()
lvName.Sorting = SortOrder.Ascending

Dim cmdfill As New OleDbCommand("SELECT eno, lname & ', ' & fname as Name FROM EMP", Connection)

Try
Connection.Open()
Dim datareader As OleDbDataReader
datareader = cmdfill.ExecuteReader

While datareader.Read
Dim lvi As New ListViewItem
lvi.Text = CStr(datareader("eno"))
lvi.SubItems.Add(CStr(datareader("Name")))
lvName.Items.Add(lvi)
End While

If datareader.HasRows Then
lvName.TopItem.Selected = True
lvName.TopItem.Focused = True
lvName.TopItem.EnsureVisible()
lvName.Select()

'lvName.Items(0).Selected = True
'strSelectedeno = lvName.Items(0).SubItems(0).Text
' lvName_SelectedIndexChanged(Nothing, Nothing)

End If

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
Finally
Connection.Close()
End Try
End Sub

Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
Me.Close()
mainform.Show()

End Sub

Private Sub lvName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvName.SelectedIndexChanged
Dim cnOleDb As OleDbConnection
Dim cmSQL As OleDbCommand
Dim drSQL As OleDbDataReader
Dim strSQL As String
'Dim strID As String
Dim ThisItem As ListViewItem

If IsNothing(lvName.FocusedItem) Then Exit Sub

For Each ThisItem In lvName.SelectedItems
strSelectedeno = lvName.Items(ThisItem.Index).SubItems(0).Text

Next
strSelectedeno = lvName.FocusedItem.Text

Try
strSQL = "SELECT eno," & _
"lname, " & _
"fname, " & _
"DailyRate, " & _
"Allowance, " & _
"Incentives " & _
"FROM EMP " & _
"WHERE eno = '" & strSelectedeno & "'"

cnOleDb = New OleDbConnection(ACCESS_CONNECTION_STRING)
cnOleDb.Open()

cmSQL = New OleDbCommand(strSQL, cnOleDb)
drSQL = cmSQL.ExecuteReader()

drSQL.Read()

txtDailyRate.Text = Format(Convert.ToDouble(drSQL.Item("DailyRate").ToString()), "0.00")
dblAllowance = Format(Convert.ToDouble(drSQL.Item("Allowance").ToString()), "0.00")

txtAllowance.Text = dblAllowance
dblIncentives = Format(Convert.ToDouble(drSQL.Item("Incentives").ToString()), "0.00")
txtIncentives.Text = dblIncentives

' Close and Clean up objects
drSQL.Close()
cnOleDb.Close()
cmSQL.Dispose()
cnOleDb.Dispose()

GetPayslip()

txtTotal.Text = Format(txtworkdays.Text * txtDailyRate.Text, "0.00")
Calc_Gross()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub

in above code i m getting the error as
syntax error(comma) in query expression 'eno='1',lname='',fname='' And FromPay=## And ToPay=##'.
i m not getting from where this error is coming.
plz help.

can u put a screen of error?

hw to put screen of error?, actually i m nw here.
plz help

when u reply to post .. use advanced editor.

u will find attachment option there

attach the screen shot of your error there.

here is screen shot of my error,
thnx for help.
actually want to fetch data(eno,lname & fname) from emp table to payroll table payroll there i m getting error in lvName_SelectedIndexChanged function.

Dim cmdfill As New OleDbCommand("SELECT eno, lname & ', ' & fname as Name FROM EMP", Connection)

change this to

Dim cmdfill As New OleDbCommand("SELECT eno, lname + ' ' +fname as Name FROM EMP", Connection)  ' if you want space (' ') between lname and fname

or

Dim cmdfill As New OleDbCommand("SELECT eno, lname + ',' +fname as Name FROM EMP", Connection)  'if you want , between lname and fname

and i think this will solve the problem

thank u Sir for reply,
i did changes as u said bt still getting d same error,
when i run the project first this error get shown then form get display
and when i click on the item from list again error get shown(screen shot error1) on each click
and data didn't fetch from table

if you dont mind give me the project.. i will take a look at it.. and send u back.. if possible..

yes.
In this project i m debugging payroll form and getting this error.

other forms r ok, bt payroll form getting this error

i have seen your project... there were so many errors in your sql queries...

first of all when you specify where and there are more than one criteria then use AND between them.. but u used , .

i have also change you access database..nothing more just

specified DefaultValue 0.00 for DailyRate,Allowance,Incentives in EMP table..
also change type of eno from number to string

and yes another thing is that you should use Parameters for passing values to sql queries.

i have used it in you GEtpayslip() method.. please see it...

thank u sir,
i will definitely correct my mistake.

Hello Sir,
i want to make payslip for each employee using crystal report,
i know hw to prepare crystal report.

is it possible?
can u give me any suggestion,tutorial or any thing u suggest

check this link: http://www.vbforums.com/showthread.php?t=433644

hello sir, as u see my project,
when payslip get created for employee that payslip should also printed through report,
so is it possible to create report for each employee separately,?????

what should i do for that , plz help its urgent.
thanks in advance.

can anyone help in preparing crystal report????
its urgent
Thanx in advance

Hello Sir,
In my crystal report form in CrystalReportViewer it shows garbage value bt when i run program my payslip get generated with values from data table,

what mistakes i have made?

ok.. paste your code

here is my crystal report code, it shows garbage values but its working on run time

Dim rpt As New CrystalReport1() 'The report you created.

        Dim ACCESS_CONNECTION_STRING As String = cnsettings()

        '  Dim myconnection As New OleDbConnection(ACCESS_CONNECTION_STRING)

        Dim mycommand As New OleDbCommand
        Dim myDA As New OleDbDataAdapter
        Dim ds As New DBDataSet3
        Try
            Dim myconnection As New OleDbConnection(ACCESS_CONNECTION_STRING)
            mycommand.Connection = myconnection
            mycommand.CommandText = "select * from payroll"
            mycommand.CommandType = CommandType.Text
            myDA.SelectCommand = mycommand
            myDA.Fill(ds, "payroll")
            rpt.SetDataSource(ds)
            CrystalReportViewer1.ReportSource = rpt

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

after solving this my project will complete.

thanx to all my problem get solved :-O

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.