ron nash

Hi,

I need some help with this please.

I have a database table which contains customer orders. I am trying to code my SQL select statement to:

1) Only return records where the record orderdate is within the last 30 days

2) Between two dates, selected from the datepicker control.

With regards to issue 1, I could fill a table with all the records for the account in question and then for each record do a datediff between the records order date and the current date to determine if the number of days is within 30 days. If yes then add this record to a temp table and then set this table as the datasource for the datagridview.

There must be a more efficient way

With regards to issue 2)



Re: Visual Basic Express Edition SQL querry between two dates

DMan1

Dim TheDate As Date = Now.AddDays(-30)

Dim SqlStatement As String = "Select * From TheTable Where DateField > @TheDate"

Dim SqlStatement As String = "SELECT * FROM TheTable Where DateField BETWEEN @StartDate AND @EndDATE"

The "@" represent SQL Variables which there values whould be passed in using sql command parameters






Re: Visual Basic Express Edition SQL querry between two dates

ron nash

DMan1,

Many Thanks

I did infact code it as per my post and it worked, however as I thought, your solution is more efficient

Thanks again

Ron





Re: Visual Basic Express Edition SQL querry between two dates

ron nash

Dman1,

After checking the returned records I found that the records returned are not correct, that is, not all records in the last 30 days are shown.

I have records with dates from 1st Jan - 15th Feb.

It will show records from 28th Jan - 9th Feb. Any records after the 9th are not shown.

I have used different date formats but with no resolve

Here is the SQL statement used

'\\ select by default records for last 30 days

Dim thedate As Date = Now.AddDays(-30)

Dim SearchQuery As String = " SELECT * FROM custorders where accountno= '" & m_account & "' and date > '" & thedate & "'"

i have just changed the database table date field from the 9thFeb to 10thFeb. This record is not displayed after search.

It seems to have an issue with 2digits for Feb

 





Re: Visual Basic Express Edition SQL querry between two dates

DMan1

First thing I would check is the format of the date you are passing against the format of the date stored in the db...make sure they are the same




Re: Visual Basic Express Edition SQL querry between two dates

ron nash

DMan1,

I have cleared the database table and set the column from string to timedate

I have added a reocrd to the table and the date format is displayed :02/09/2007 22:01:54

I now have an issue with the select statement : .....and date > '" & thedate & "' "

The following error is displayed :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I know it is the '" & thedate & "' " that is causing the problem.

I have tryed different syntax but no resolve

Could you please show me how this should be coded

Many Thanks





Re: Visual Basic Express Edition SQL querry between two dates

ron nash

Hi

can anyone please help with this issue

Thanks





Re: Visual Basic Express Edition SQL querry between two dates

ron nash

I would realy appreciate some help here fellow VB'ers!!





Re: Visual Basic Express Edition SQL querry between two dates

DMan1

How are you declaring the variable "TheDate" and how are you setting it's value

 

edit: are you still using:

Dim thedate As Date = Now.AddDays(-30)

and you have verified the DateTime value in the db

Is the error a db conversion error or a VB error you are getting when running your code






Re: Visual Basic Express Edition SQL querry between two dates

DMan1

AHHHHHHH...I....SEEEEEEE the problem...get rid of the apostrophees/quote delimeters

"...Date > " & TheDate ...or

"...Date >#" & TheDate &"#"

the pound sign is used in sql to delimit date types....but definately do NOT use the character delimeter " ' "






Re: Visual Basic Express Edition SQL querry between two dates

ron nash

Currently using the following

DB date column set to datetime  data =2007-02-16 20:13:51.000

Dim SearchQuery As String = " SELECT * FROM custorders where accountno= '" & m_account & "' and Date > " & thedate

 

CheckOrdersDataset = New DataSet

CheckOrdersTableAdapter = New SqlDataAdapter(SearchQuery, Form1.cnn1)

CheckOrdersTableAdapter.Fill(CheckOrdersDataset, "custOrders")  Errors here now :Incorrect syntax near '20'.

CheckOrdersTable = CheckOrdersDataset.Tables("custOrders")

I have tested both methods you have suggested:

...Date > " & thedate

..Date > & thedate"

..Date >#" & TheDate &"#"





Re: Visual Basic Express Edition SQL querry between two dates

ron nash

 

This was beginning to do my head in!  but it's now sorted

Once I sorted the correct format of the Thedate variable it now functions correctly

Dim SearchQuery As String = " SELECT * FROM custorders where accountno= '" & m_account & "' and Date > '" & thedate1 & "'"

Many Thanks to DMan1 for your replys.... Time for some Beers!!