SQL query returning wrong value

[poojavb]

Hello Friends,

I am messed up with the below query

Try
    Dim myCommand As OleDbCommand
    myCommand = New OleDbCommand("select sum(tmt.total) as Total from Appointment appt, TreatmentMaster tmt where appt.patientid='" + txtPatIDBill.Text + "' and [tmt].[treatid]=[appt].[treatmentid] and (Format([appt.ApptDate], 'Short Date')) between '" + dtpFromBill.Value.Date + "' and  '" + dtpToBill.Value.Date + "'", Connection)
    Dim reader As OleDbDataReader = myCommand.ExecuteReader
    Debug.Print("Query output: " + myCommand.CommandText)
    If reader.Read = True Then
       Debug.Print("True")
    Else
       Debug.Print("False")
    End If
Catch ex As Exception
    MsgBox("Error Connecting to Database: " & ex.Message)
End Try

I have to select the patientid, from date and the to date from the GUI part....

I have values in Database for date 2/20/2012 and 2/22/2012

whenever I try to run the query in Access it is returning the correct data...

but when i run the query in my project the reader in not reading correctly...even if values are present is returns false
and if values are not there then it returns true....

I am not getting where I am going wrong...
Please help me...

[thines01]

Do you need () on the Read()?
http://msdn.microsoft.com/en-us/library/haa3afyz.aspx#Y608

[poojavb]

Do you need () on the Read()?
http://msdn.microsoft.com/en-us/library/haa3afyz.aspx#Y608

no the () are not necessary....i have tried with it also...

[M.Waqas Aslam]

hello !
do you try dtpFromBill.Value.Date.tostring() instead of this dtpFromBill.Value.Date .

Regards

[M.Waqas Aslam]

and please try this query , may be this will solve your prob .

"select sum(tmt.total) as Total from Appointment appt inner join  TreatmentMaster tmt on  [tmt].[treatid]=[appt].[treatmentid] where appt.patientid='" + txtPatIDBill.Text + "' and [appt.ApptDate] between '" + dtpFromBill.Value.Date.tostring() + "' and  '" + dtpToBill.Value.Date.tostring() + "'"

[poojavb]

and please try this query , may be this will solve your prob .

"select sum(tmt.total) as Total from Appointment appt inner join  TreatmentMaster tmt on  [tmt].[treatid]=[appt].[treatmentid] where appt.patientid='" + txtPatIDBill.Text + "' and [appt.ApptDate] between '" + dtpFromBill.Value.Date.tostring() + "' and  '" + dtpToBill.Value.Date.tostring() + "'"

I got the answer...but I need help on how to proceed....

Actually when I take some other date that is not there in database the 'sum(tmt.total) as Total ' returns a null value and therefore the reader takes it as true...

but if I remove the sum and just write '(tmt.total) as Total' then no records are retrieved and it returns false that is correct...but I need the sum for calculation....

so I need to know how shud I proceed....

[poojavb]

and please try this query , may be this will solve your prob .

"select sum(tmt.total) as Total from Appointment appt inner join  TreatmentMaster tmt on  [tmt].[treatid]=[appt].[treatmentid] where appt.patientid='" + txtPatIDBill.Text + "' and [appt.ApptDate] between '" + dtpFromBill.Value.Date.tostring() + "' and  '" + dtpToBill.Value.Date.tostring() + "'"

I tried this also using

Debug.Print("Date: " + dtpToBill.Value.Date.ToString)

If I use the to String method then I will also get the time along with the date
Date: 2/24/2012 12:00:00 AM

[M.Waqas Aslam]

try this may be this will helps you

If reader.Read = True Then
dim a as integer
 a = val(reader.read).tostring()
if a <> null then 
   Debug.Print("True")
endif
    
    Else
       Debug.Print("False")
    End If

this is just an idea , so please check it by your self .

Regards

[poojavb]

try this may be this will helps you

If reader.Read = True Then
dim a as integer
 a = val(reader.read).tostring()
if a <> null then 
   Debug.Print("True")
endif
    
    Else
       Debug.Print("False")
    End If

this is just an idea , so please check it by your self .

Regards

I used formatting for datetimepicker....my issue has been solved...

[M.Waqas Aslam]

if your prob is solved then please mark this thread solved :)
Regards

[poojavb]

if your prob is solved then please mark this thread solved :)
Regards

Ya sure....thank u....