Getting error with

Question

wenbnet 9 Practically a Posting Shark

13 Years Ago

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.

vb.net

Edited 13 Years Ago by kvprajapati because: Added [code] tags.

3 Contributors
25 Replies
315 Views
2 Weeks Discussion Span
Latest Post 13 Years Ago Latest Post by wenbnet

debasisdas 580 Posting Genius

13 Years Ago

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

sandeepparekh9 109 Posting Whiz

13 Years Ago

can u put a screen of error?

sandeepparekh9 109 Posting Whiz

13 Years Ago

when u reply to post .. use advanced editor.

u will find attachment option there

attach the screen shot of your error there.

sandeepparekh9 109 Posting Whiz

13 Years Ago

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

Edited 13 Years Ago by sandeepparekh9 because: n/a

sandeepparekh9 109 Posting Whiz

13 Years Ago

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

Reply to this topic

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.

wenbnet 9 Practically a Posting Shark · Answer 1 · 2011-04-09T11:04:42+00:00

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.

wenbnet 9 Practically a Posting Shark · Answer 2 · 2011-04-09T15:52:41+00:00

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

wenbnet 9 Practically a Posting Shark · Answer 3 · 2011-04-09T17:13:28+00:00

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.

wenbnet 9 Practically a Posting Shark · Answer 4 · 2011-04-18T14:13:16+00:00

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

wenbnet 9 Practically a Posting Shark · Answer 5 · 2011-04-18T14:28:59+00:00

wenbnet 9 Practically a Posting Shark

13 Years Ago

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

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

pay.zip (804.98 KB)

wenbnet 9 Practically a Posting Shark · Answer 6 · 2011-04-18T14:30:49+00:00

other forms r ok, bt payroll form getting this error

sandeepparekh9 109 Posting Whiz · Answer 7 · 2011-04-18T14:38:02+00:00

sandeepparekh9 109 Posting Whiz

13 Years Ago

ok let me see..

sandeepparekh9 109 Posting Whiz · Answer 8 · 2011-04-18T15:08:50+00:00

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...

wenbnet 9 Practically a Posting Shark · Answer 9 · 2011-04-18T16:01:43+00:00

thank u sir,
i will definitely correct my mistake.

sandeepparekh9 109 Posting Whiz · Answer 10 · 2011-04-18T16:11:16+00:00

sandeepparekh9 109 Posting Whiz

13 Years Ago

o.k. all the best

wenbnet 9 Practically a Posting Shark · Answer 11 · 2011-04-18T16:56:59+00:00

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

sandeepparekh9 109 Posting Whiz · Answer 12 · 2011-04-18T17:46:47+00:00

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

wenbnet 9 Practically a Posting Shark · Answer 13 · 2011-04-19T15:19:22+00:00

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.

wenbnet 9 Practically a Posting Shark · Answer 14 · 2011-04-20T16:14:18+00:00

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

sandeepparekh9 109 Posting Whiz · Answer 15 · 2011-04-20T17:48:30+00:00

http://www.codeproject.com/KB/cs/CreatingCrystalReports.aspx

http://www.codeproject.com/KB/cs/CrystalReportNDataTable.aspx

http://developer.emc.com/developer/downloads/CrystalReport_ADO_Dataset.pdf

wenbnet 9 Practically a Posting Shark · Answer 16 · 2011-04-23T17:11:15+00:00

http://www.codeproject.com/KB/cs/CreatingCrystalReports.aspx
http://www.codeproject.com/KB/cs/CrystalReportNDataTable.aspx
http://developer.emc.com/developer/downloads/CrystalReport_ADO_Dataset.pdf

Hello Sir,
i created Crystal Report bt it takes garbage values and not from my table,
how is this possible.
if u want i can paste my crystal report code

wenbnet 9 Practically a Posting Shark · Answer 17 · 2011-04-25T12:53:11+00:00

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?

sandeepparekh9 109 Posting Whiz · Answer 18 · 2011-04-25T14:30:55+00:00

sandeepparekh9 109 Posting Whiz

13 Years Ago

ok.. paste your code

wenbnet 9 Practically a Posting Shark · Answer 19 · 2011-04-25T15:31:33+00:00

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.

wenbnet 9 Practically a Posting Shark · Answer 20 · 2011-04-27T13:19:45+00:00

wenbnet 9 Practically a Posting Shark

13 Years Ago

thanx to all my problem get solved :-O