10 Replies - 15681 Views - Last Post: 27 February 2011 - 09:10 PM Rate Topic: -----

#1 kojo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-February 11

vb6,sql date query

Posted 21 February 2011 - 11:52 AM

Hi,
I'm working on an sql data project in vb as a begiiner, and wanted to diplay products which has their expired dates falling between two given dates.
With my two text boxes for the dates is txtdateperiod1 and txtdateperiod2 respectively
the code was;


Private Sub cmbperiod_Click()
Dim tvssql As String
Dim txtdateperiod1 As Variant
Dim txtdateperiod2 As Variant

tvssql = "select * from products where P_expirydate= #txtdateperiod1# > , # txtdateperiod2 # " < ""

Adodc2CommandType = adCmdText
Adodc2.RecordSource = tvssql
Adodc2.Refresh
Set DataGrid1.DataSource = Adodc2
End Sub

I had an erro
please can you help meEmail me

Is This A Good Question/Topic? 0
  • +

Replies To: vb6,sql date query

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: vb6,sql date query

Posted 21 February 2011 - 12:29 PM

Hi and welcome to Dream.In.Code!

Yes we can help you, but:
1. Please don't put any email address in your request. We will respond here to ensure that everyone can learn something upon your question.
2. Please use code tags when posting code
3. Please give us all the details you can.

Quote

i had an erro
What error did you encounter? We can guess what goes wrong, but would be easier for everyone to know the exact problem.

Looking over your code, I saw a problem in sql statement.
tvssql = "select * from products where P_expirydate= #txtdateperiod1# > , # txtdateperiod2 # " < ""


I'm not sure if it is a valid Access query, but regarding SQL, correct would be
tvssql = "select * from products where P_expirydate>= #txtdateperiod1# and  P_expirydate <= #txtdateperiod2#"


Now, txtdateperiod1 and 2 are textboxes? string variables? If they are textboxes you should use .Value property(or .Text) and the values should be enclosed between apostrophes.

This post has been edited by Ionut: 21 February 2011 - 12:33 PM

Was This Post Helpful? 1
  • +
  • -

#3 BobRodes  Icon User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,069
  • Joined: 19-May 09

Re: vb6,sql date query

Posted 22 February 2011 - 06:40 PM

Kojo, I wouldn't use Variant unless I had to. You're much better off figuring out what the actual type you're trying to use is. As you can see, we have no idea how you're getting dates into those variables. (Ionut, TextBox doesn't have a Value property, just a Text property.)
Was This Post Helpful? 1
  • +
  • -

#4 Vishu Sukhdev  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 150
  • Joined: 19-February 10

Re: vb6,sql date query

Posted 22 February 2011 - 10:51 PM

Hi ,
try this
tvssql= "SELECT * FROM Products WHERE P_expirydate BETWEEN #" & txtdateperod1 &"# AND #" & txtdateperiod2 &"#"


This post has been edited by Vishu Sukhdev: 22 February 2011 - 10:52 PM

Was This Post Helpful? 0
  • +
  • -

#5 kojo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-February 11

Re: vb6,sql date query

Posted 23 February 2011 - 04:16 PM

View PostBobRodes, on 22 February 2011 - 06:40 PM, said:

Kojo, I wouldn't use Variant unless I had to. You're much better off figuring out what the actual type you're trying to use is. As you can see, we have no idea how you're getting dates into those variables. (Ionut, TextBox doesn't have a Value property, just a Text property.)

I have been trying, all I want to do is to create a query in vb, where using two text boxes and a command button I can get all the products that has their expiry date between the dates given in the two text boxes appearing in the data grid
Was This Post Helpful? 0
  • +
  • -

#6 kojo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-February 11

Re: vb6,sql date query

Posted 23 February 2011 - 04:23 PM

View PostIonut, on 21 February 2011 - 12:29 PM, said:

Hi and welcome to Dream.In.Code!

Yes we can help you, but:
1. Please don't put any email address in your request. We will respond here to ensure that everyone can learn something upon your question.
2. Please use code tags when posting code
3. Please give us all the details you can.

Quote

i had an erro
What error did you encounter? We can guess what goes wrong, but would be easier for everyone to know the exact problem.

Looking over your code, I saw a problem in sql statement.
tvssql = "select * from products where P_expirydate= #txtdateperiod1# > , # txtdateperiod2 # " < ""


I'm not sure if it is a valid Access query, but regarding SQL, correct would be
tvssql = "select * from products where P_expirydate>= #txtdateperiod1# and  P_expirydate <= #txtdateperiod2#"


Now, txtdateperiod1 and 2 are textboxes? string variables? If they are textboxes you should use .Value property(or .Text) and the values should be enclosed between apostrophes.

The erro was 'Could not find stored procedure 'false'
Was This Post Helpful? 0
  • +
  • -

#7 BobRodes  Icon User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,069
  • Joined: 19-May 09

Re: vb6,sql date query

Posted 24 February 2011 - 04:20 PM

well, you absolutely don't have to use variants to do that, or any other variable type for that matter. You can access the contents of your text boxes directly.

Could not find stored procedure 'false' means that you are trying to call a stored procedure with the name 'false'.
Was This Post Helpful? 0
  • +
  • -

#8 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: vb6,sql date query

Posted 25 February 2011 - 08:17 AM

Private Sub cmbperiod_Click()
Dim tvssql As String
Dim txtdateperiod1 As Variant
Dim txtdateperiod2 As Variant

tvssql = "select * from products "

Adodc2CommandType = adCmdText
Adodc2.RecordSource = tvssql
Adodc2.Refresh
Set DataGrid1.DataSource = Adodc2
End Sub



first give me a hint is this procedure load you correctly on the datagrid
if it is not first try to do it first
if it load correctly then use the following code

Private Sub cmbperiod_Click()
Dim tvssql As String
Dim txtdateperiod1 As Variant
Dim txtdateperiod2 As Variant
Dim Sdate as Date
Dim Edate as Date 

sdate = cdate(txtdateperiod1)
Edate = cdate(txtdateperiod2)
msgbox(format(sdate,"dd-MMM-yyyy"))
msgbox(format(Edate,"dd-MMM-yyyy"))

tvssql = "select * from products where  P_expirydate between '" & format(sdate,"dd-MMM-yyyy") &"' and '" & format(Edate,"dd-MMM-yyyy") &"'" 

Adodc2CommandType = adCmdText
Adodc2.RecordSource = tvssql
Adodc2.Refresh
Set DataGrid1.DataSource = Adodc2
End Sub



verify that the date are correct in the msgbox if it is correc then the result will load correct else
validate the date first and use it
Was This Post Helpful? 1
  • +
  • -

#9 kojo  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-February 11

Re: vb6,sql date query

Posted 26 February 2011 - 04:28 PM

View Postthava, on 25 February 2011 - 08:17 AM, said:

Private Sub cmbperiod_Click()
Dim tvssql As String
Dim txtdateperiod1 As Variant
Dim txtdateperiod2 As Variant

tvssql = "select * from products "

Adodc2CommandType = adCmdText
Adodc2.RecordSource = tvssql
Adodc2.Refresh
Set DataGrid1.DataSource = Adodc2
End Sub



first give me a hint is this procedure load you correctly on the datagrid
if it is not first try to do it first
if it load correctly then use the following code

Private Sub cmbperiod_Click()
Dim tvssql As String
Dim txtdateperiod1 As Variant
Dim txtdateperiod2 As Variant
Dim Sdate as Date
Dim Edate as Date 

sdate = cdate(txtdateperiod1)
Edate = cdate(txtdateperiod2)
msgbox(format(sdate,"dd-MMM-yyyy"))
msgbox(format(Edate,"dd-MMM-yyyy"))

tvssql = "select * from products where  P_expirydate between '" & format(sdate,"dd-MMM-yyyy") &"' and '" & format(Edate,"dd-MMM-yyyy") &"'" 

Adodc2CommandType = adCmdText
Adodc2.RecordSource = tvssql
Adodc2.Refresh
Set DataGrid1.DataSource = Adodc2
End Sub



verify that the date are correct in the msgbox if it is correc then the result will load correct else
validate the date first and use it

I used it, thank you but why is it giving a date to be 31-Dec-1899. can you give me an idea
Was This Post Helpful? 0
  • +
  • -

#10 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: vb6,sql date query

Posted 27 February 2011 - 07:06 PM

how did you implement this code in your form, i need that code,
and moreover this is why datetimepicker is for.
Was This Post Helpful? 0
  • +
  • -

#11 BobRodes  Icon User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,069
  • Joined: 19-May 09

Re: vb6,sql date query

Posted 27 February 2011 - 09:10 PM

That's the default date, the equivalent of CDate(0). It's because you haven't assigned any value to your txtdateperiod variables. I still wouldn't use those...
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1