OsirisJa
asked on
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.SqlC
Dim da As New System.Data.SqlClient.SqlD
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("TotalEmployerCont
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.
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.SqlC
Dim da As New System.Data.SqlClient.SqlD
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("TotalEmployerCont
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 '