Help with accessing a sql database in VB.NET 2005

I use the following code in my program.

        sQsT = "SELECT * FROM (Payroll) WHERE (FirstName  = '" & fN & "') AND (LastName = '" & lN & "') AND (dEnd >= #" & DateTimePicker1.Value & "#) AND (dEnd <= #" & DateTimePicker2.Value & "#)"

        On Error Resume Next
        'Declare SQL Connection, Data Adapter and Dataset
        Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Payroll.mdf;Integrated Security=True;User Instance=True")
        Dim da As New System.Data.SqlClient.SqlDataAdapter(sQsT, conn)
        Dim ds As New System.Data.DataSet
        Dim dr As DataRow

        da.Fill(ds)
        'Find out how many Rows are in the First table of the data Adapter
        Dim DCT As Integer = ds.Tables(0).Rows.Count

        For NM As Integer = 0 To DCT - 1
            dr = ds.Tables(0).Rows(NM)

            eiInsurance += dr.Item("HealthInsurance")
            eiTotald += dr.Item("TotalEmployerContributions")
            iGrossSalary += dr.Item("GrossSalary")
            iNetSalary += dr.Item("NetSalary")

        Next

although the data is in the database, it is not going into the loop to load the variables. I am not sure what I did wrong and any advice or assistance would be greatly appreciated.
OsirisJaAsked:
Who is Participating?
 
iHadiCommented:
Hi OsirisJa
There could be more than one reasons for your problem
-  use ' instead of # as Sancler suggested
-  Check the date format you are using: Try to format the date the same format as SQL Server so the server doesn't consider the month as day and the day as month
-  Your select statment is selecting a date with the time zero eg: 2005-05-08 00:00:00.000 so if the time in the stored datetime in the database is not zero eg: 2005-05-08 14:58:00.000 then the select statment will ignore it
    To correct this use the select statement as follows:
    SELECT * FROM (Payroll)
    WHERE dEnd >= '2005-05-08'
    AND dEnd <'2005-05-09'
0
 
Nandakumar SakthivelTechnology LeadCommented:
I think that the problem is in your select command.Everything looks fine except that
Can u check the select command(In query analyser) at runtime after the values has been replaced

Thanks,
Nanda
0
 
SanclerCommented:
Is # the right marker for dates in SQL Express?  It is in Access, but I think that's specific to Access.

I suggest you try it without that, or with ' instead (perhaps with DateTimePicker2.Value.ToString).

Roger
0
 
iHadiCommented:
Hi OsirisJa
There could be more than one reasons for your problem
-  use ' instead of # as Sancler suggested
-  Check the date format you are using: Try to format the date the same format as SQL Server so the server doesn't consider the month as day and the day as month
-  Your select statment is selecting a date with the time zero eg: 2005-05-08 00:00:00.000 so if the time in the stored datetime in the database is not zero eg: 2005-05-08 14:58:00.000 then the select statment will ignore it
    To correct this use the select statement as follows:
    SELECT * FROM (Payroll)
    WHERE dEnd >= '2005-05-08 '
    AND dEnd <'2005-05-09 '
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.