get date

**cmrhema** · Jun 5 '07, 03:34 AM

Originally posted by berry

Hi,
I want to sort all the data with the date which user key in. To store in the server, i use let the user to choose a date from the calendar. The format is 6/20/2007. But the date will be store in the server in format 2007-06-20.
When the user want to review his data, he choose from the calendar but the recordsource cannot find the data. What should I do?

Following is my code. Please have look on it. Thanks..

Code:

Private Sub Calendar1_DblClick()
txtDate = Calendar1.Value
End Sub

Private Sub savebtn_Click()
With rs
   .AddNew
   .Fields("Date") = txtDate.Text
   .Update
End With
Adodc1.RecordSource = "select *from downtime where Date = '"+txtdate.text+"'"
Adodc1.Refresh
DataGrid1.Refresh
End Sub

Are you saving the date in text format

**berry** · Jun 5 '07, 06:38 AM

Originally posted by cmrhema

Are you saving the date in text format

I store the date as "DATE" type in the database. But when the user choose the date, the date will place in a text box and will compare in the databases.

**cmrhema** · Jun 5 '07, 09:52 AM

Originally posted by berry

Hi,
I want to sort all the data with the date which user key in. To store in the server, i use let the user to choose a date from the calendar. The format is 6/20/2007. But the date will be store in the server in format 2007-06-20.
When the user want to review his data, he choose from the calendar but the recordsource cannot find the data. What should I do?

Following is my code. Please have look on it. Thanks..

Code:

Private Sub Calendar1_DblClick()
txtDate = Calendar1.Value
End Sub

Private Sub savebtn_Click()
With rs
   .AddNew
   .Fields("Date") = txtDate.Text
   .Update
End With
Adodc1.RecordSource = "select *from downtime where Date = '"+txtdate.text+"'"
Adodc1.Refresh
DataGrid1.Refresh
End Sub

You say that you have stored data in DATE FORMAT. But glancing through your code it doesn't seem so.
[HTML] .Fields("Date") = txtDate.Text[/HTML]
Replace txtDate by calendar.value

To answer your second part of question change the code from

[HTML]Date = '"+txtdate.text +"'"[/HTML]
to
[HTML]date='" & txtdate.Text & "'"[/HTML]

Try out and lets know
Good luck

**berry** · Jun 6 '07, 05:45 AM

Originally posted by cmrhema

You say that you have stored data in DATE FORMAT. But glancing through your code it doesn't seem so.
[HTML] .Fields("Date") = txtDate.Text[/HTML]
Replace txtDate by calendar.value

To answer your second part of question change the code from

[HTML]Date = '"+txtdate.text +"'"[/HTML]
to
[HTML]date='" & txtdate.Text & "'"[/HTML]

Try out and lets know
Good luck

Hi, I'd tried. I still can't get the answer. The datagrid shows nothing. I also tried to dim the text in txtdate as DATE. But it also can't work. Any other idea to get the specific date from the database and show in the datagrid?

**berry** · Jun 6 '07, 09:27 AM

Originally posted by cmrhema

You say that you have stored data in DATE FORMAT. But glancing through your code it doesn't seem so.
[HTML] .Fields("Date") = txtDate.Text[/HTML]
Replace txtDate by calendar.value

To answer your second part of question change the code from

[HTML]Date = '"+txtdate.text +"'"[/HTML]
to
[HTML]date='" & txtdate.Text & "'"[/HTML]

Try out and lets know
Good luck

Hi..I had tried the code u gave. But it still can't work. Any other idea to get a particular date from server and show in the datagrid?
Thanks..

**cmrhema** · Jun 6 '07, 09:31 AM

Originally posted by berry

Hi..I had tried the code u gave. But it still can't work. Any other idea to get a particular date from server and show in the datagrid?
Thanks..

Kindly Post your unedited code once again

**berry** · Jun 7 '07, 12:03 AM

Originally posted by cmrhema

You say that you have stored data in DATE FORMAT. But glancing through your code it doesn't seem so.
[HTML] .Fields("Date") = txtDate.Text[/HTML]
Replace txtDate by calendar.value

To answer your second part of question change the code from

[HTML]Date = '"+txtdate.text +"'"[/HTML]
to
[HTML]date='" & txtdate.Text & "'"[/HTML]

Try out and lets know
Good luck

Hi..I had tried the code. But still can't get it. Any other idea to get a specific date from database and show in datagrid?
Thanks...

**berry** · Jun 7 '07, 12:12 AM

Originally posted by berry

Hi..I had tried the code. But still can't get it. Any other idea to get a specific date from database and show in datagrid?
Thanks...

This is the uneitted code. Please have a look.

Code:

Private Sub showDate()

Adodc1.RecordSource = "select * from date where dateNow =  '" & Text1 & "' "
Adodc1.Refresh
DataGrid1.Refresh
' where dateNow is field name and Text1 is the date key in by user

End Sub

Is it neccessary to dim the text as date?

**cmrhema** · Jun 7 '07, 03:08 AM

Originally posted by berry

This is the uneitted code. Please have a look.

Code:

Private Sub showDate()

Adodc1.RecordSource = "select * from date where dateNow =  '" & Text1 & "' "
Adodc1.Refresh
DataGrid1.Refresh
' where dateNow is field name and Text1 is the date key in by user

End Sub

Is it neccessary to dim the text as date?

Right click on Adodc1 go to properties
1.) In the general tabl Set the connectionstrin g to Provider Sql(The one you choose always)

2.) In the recordsource property select command type as 8-adCmdUnknown.

3.) In the Command Text Sql write down
select * from date

4.) Click on the control press F4

5.) Set the cursor location property to aduseclient

6.) Set the cursor type property to adopendynamic

7.) In the Datagrid properties set the datasource as Adodc1

8.) Write down the code as below

Code:

Private Sub showDate()
Adodc1.RecordSource = "select * from date where dateNow =  '" & CDate(Text1.Text) & "'"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
End Sub

I tried this and got. Let's hope and pray it works for you as well
Good luck

**berry** · Jun 7 '07, 06:57 AM

Originally posted by cmrhema

Right click on Adodc1 go to properties
1.) In the general tabl Set the connectionstrin g to Provider Sql(The one you choose always)

2.) In the recordsource property select command type as 8-adCmdUnknown.

3.) In the Command Text Sql write down
select * from date

4.) Click on the control press F4

5.) Set the cursor location property to aduseclient

6.) Set the cursor type property to adopendynamic

7.) In the Datagrid properties set the datasource as Adodc1

8.) Write down the code as below

Code:

Private Sub showDate()
Adodc1.RecordSource = "select * from date where dateNow =  '" & CDate(Text1.Text) & "'"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
End Sub

I tried this and got. Let's hope and pray it works for you as well
Good luck

Hi..I still can't get it. The datagrid shows empty. I don't know why. Here I attach my code and hope you can take a look.
In the form, I have:
2 comand buttons - saveBtn, selectDateBtn
1 text box - Text1
1 adodc - Adodc1
1 datagrid - DataGrid1
1 calendar - Calendar1

Please help me. Thanks...

Code:

Private conn As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub Calendar1_Click()
Text1 = Calendar1.Value
End Sub

Private Sub Form_Load()
Text1 = Date
Calendar1.Value = Text1

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=localhost;" _
    & "DATABASE=try;" _
    & "UID=root;" _
    & "PWD=1234;"
    conn.CursorLocation = adUseClient
    conn.Open
    
    'Open the to write the database
    Set rs = New ADODB.Recordset
    'Open the table
    rs.Open "date", conn, adOpenStatic, adLockOptimistic
    
Adodc1.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=localhost;" _
    & "DATABASE=try;" _
    & "UID=root;" _
    & "PWD=1234;"
    
End Sub

Private Sub saveBtn_Click()

With rs
    .AddNew
    .Fields("dateNow") = Text1
    .Update
End With
      
Adodc1.RecordSource = "select *from date order by datenow"
Adodc1.Refresh
DataGrid1.Refresh

End Sub

Private Sub selectDateBtn_Click()

Adodc1.RecordSource = "select * from date where dateNow =  '" & CDate(Text1.Text) & "'"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1

End Sub

**cmrhema** · Jun 7 '07, 07:39 AM

Originally posted by berry

Hi..I still can't get it. The datagrid shows empty. I don't know why. Here I attach my code and hope you can take a look.
In the form, I have:
2 comand buttons - saveBtn, selectDateBtn
1 text box - Text1
1 adodc - Adodc1
1 datagrid - DataGrid1
1 calendar - Calendar1

Please help me. Thanks...

Code:

Private conn As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub Calendar1_Click()
Text1 = Calendar1.Value
End Sub

Private Sub Form_Load()
Text1 = Date
Calendar1.Value = Text1

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=localhost;" _
    & "DATABASE=try;" _
    & "UID=root;" _
    & "PWD=1234;"
    conn.CursorLocation = adUseClient
    conn.Open
    
    'Open the to write the database
    Set rs = New ADODB.Recordset
    'Open the table
    rs.Open "date", conn, adOpenStatic, adLockOptimistic
    
Adodc1.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=localhost;" _
    & "DATABASE=try;" _
    & "UID=root;" _
    & "PWD=1234;"
    
End Sub

Private Sub saveBtn_Click()

With rs
    .AddNew
    .Fields("dateNow") = Text1
    .Update
End With
      
Adodc1.RecordSource = "select *from date order by datenow"
Adodc1.Refresh
DataGrid1.Refresh

End Sub

Private Sub selectDateBtn_Click()

Adodc1.RecordSource = "select * from date where dateNow =  '" & CDate(Text1.Text) & "'"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1

End Sub

Do away the adodc control
remove all the properties set to datagrid1 by you if any
Check out the below code
Further I came to know that you are using MySQL an not MS-SQL SERVER

Code:

Private conn As ADODB.Connection
Private rs As ADODB.Recordset
 
Private Sub Calendar1_Click()
Text1 = Calendar1.Value
'This will write the data in Calendar to the text box
'Is it so? Did you check this properly. There should be an error in this code
'how come you did not encounter it
End Sub
 
Private Sub Form_Load()
Text1 = Date
'This will write the current date to text box
Calendar1.Value = Text1
 '????What is this for
'Infact this should result in an error

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=localhost;" _
    & "DATABASE=try;" _
    & "UID=root;" _
    & "PWD=1234;"
    conn.CursorLocation = adUseClient
    conn.Open
    
    'Open the to write the database
    Set rs = New ADODB.Recordset
    
    
End Sub
 
Private Sub saveBtn_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from date", conn, adOpenDynamic, adLockOptimistic
With rs
    .AddNew
    .Fields("dateNow") = CDate(Text1.Text)
'Hope you are adding other details as well
    .Update
End With
MsgBox "Details Updated"
rs.Close
End Sub
 
Private Sub selectDateBtn_Click()
If rs.State = adStateOpen Then rs.Close
'For checking purpose first check the below code and comment the second code and later comment the  first code and use the second code
'When you use the first code all the details should be displayed
'When you use the second code it will filter the required details
rs.Open "select * from date", conn,  adOpenDynamic, adLockOptimistic
rs.Open "select * from date where dateNow =  '" & CDate(Text1.Text) & "'", conn,  adOpenDynamic, adLockOptimistic
rs.CursorLocation = adUseClient

Set DataGrid1.DataSource = rs
 
End Sub

**berry** · Jun 7 '07, 09:03 AM

Originally posted by cmrhema

Do away the adodc control
remove all the properties set to datagrid1 by you if any
Check out the below code
Further I came to know that you are using MySQL an not MS-SQL SERVER

Code:

Private conn As ADODB.Connection
Private rs As ADODB.Recordset
 
Private Sub Calendar1_Click()
Text1 = Calendar1.Value
'This will write the data in Calendar to the text box
'Is it so? Did you check this properly. There should be an error in this code
'how come you did not encounter it
End Sub
 
Private Sub Form_Load()
Text1 = Date
'This will write the current date to text box
Calendar1.Value = Text1
 '????What is this for
'Infact this should result in an error

    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
    & "SERVER=localhost;" _
    & "DATABASE=try;" _
    & "UID=root;" _
    & "PWD=1234;"
    conn.CursorLocation = adUseClient
    conn.Open
    
    'Open the to write the database
    Set rs = New ADODB.Recordset
    
    
End Sub
 
Private Sub saveBtn_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "select * from date", conn, adOpenDynamic, adLockOptimistic
With rs
    .AddNew
    .Fields("dateNow") = CDate(Text1.Text)
'Hope you are adding other details as well
    .Update
End With
MsgBox "Details Updated"
rs.Close
End Sub
 
Private Sub selectDateBtn_Click()
If rs.State = adStateOpen Then rs.Close
'For checking purpose first check the below code and comment the second code and later comment the  first code and use the second code
'When you use the first code all the details should be displayed
'When you use the second code it will filter the required details
rs.Open "select * from date", conn,  adOpenDynamic, adLockOptimistic
rs.Open "select * from date where dateNow =  '" & CDate(Text1.Text) & "'", conn,  adOpenDynamic, adLockOptimistic
rs.CursorLocation = adUseClient

Set DataGrid1.DataSource = rs
 
End Sub

Hi.. Thanks for your help. But there are some problem..

Code:

Private Sub Calendar1_Click()
Text1 = Calendar1.Value
'This will write the data in Calendar to the text box 
'Is it so? Did you check this properly. There should be an error in this code
'how come you did not encounter it
'Ya, this is to place the date into textbox to let the user saw the date that he press. cause after click, i will invisible the calendar. There is no error.
End Sub

Code:

Private Sub Form_Load()
Text1 = Date
'This will write the current date to text box
Calendar1.Value = Text1
 '????What is this for
'Infact this should result in an error
'These codes purpose to show the selected date in the calendar is current date.
'So there is also no error.

Code:

Private Sub selectDateBtn_Click()
If rs.State = adStateOpen Then rs.Close
'For checking purpose first check the below code and comment the second code and later comment the  first code and use the second code
'When you use the first code all the details should be displayed
'When you use the second code it will filter the required details
rs.Open "select * from date", conn,  adOpenDynamic, adLockOptimistic
rs.Open "select * from date where dateNow =  '" & CDate(Text1.Text) & "'", conn,  adOpenDynamic, adLockOptimistic
rs.CursorLocation = adUseClient

Set DataGrid1.DataSource = rs
 
End Sub

When I use either 1st or 2nd code, there is an error "Operation is not allowed when the object is opened."
When I comment rs.CursorLocati on = adUseClient, it only no problem.
It can shows all data if i use 1st code.
But nothing will be shown if 2nd code is used.
I wonder why...

**cmrhema** · Jun 7 '07, 09:53 AM

Originally posted by berry

Hi.. Thanks for your help. But there are some problem..

Code:

Private Sub Calendar1_Click()
Text1 = Calendar1.Value
'This will write the data in Calendar to the text box 
'Is it so? Did you check this properly. There should be an error in this code
'how come you did not encounter it
'Ya, this is to place the date into textbox to let the user saw the date that he press. cause after click, i will invisible the calendar. There is no error.
End Sub

Code:

Private Sub Form_Load()
Text1 = Date
'This will write the current date to text box
Calendar1.Value = Text1
 '????What is this for
'Infact this should result in an error
'These codes purpose to show the selected date in the calendar is current date.
'So there is also no error.

Code:

Private Sub selectDateBtn_Click()
If rs.State = adStateOpen Then rs.Close
'For checking purpose first check the below code and comment the second code and later comment the  first code and use the second code
'When you use the first code all the details should be displayed
'When you use the second code it will filter the required details
rs.Open "select * from date", conn,  adOpenDynamic, adLockOptimistic
rs.Open "select * from date where dateNow =  '" & CDate(Text1.Text) & "'", conn,  adOpenDynamic, adLockOptimistic
rs.CursorLocation = adUseClient

Set DataGrid1.DataSource = rs
 
End Sub

When I use either 1st or 2nd code, there is an error "Operation is not allowed when the object is opened."
When I comment rs.CursorLocati on = adUseClient, it only no problem.
It can shows all data if i use 1st code.
But nothing will be shown if 2nd code is used.
I wonder why...

Place
rs.CursorLocati on = adUseClient
after
If rs.State = adStateOpen Then rs.Close
Now there will be no error

Try out these
rs.Open "select * from date where dateNow> = '" & CDate(Text1.Tex t) & "'", conn, adOpenDynamic, adLockOptimisti c

See whether this works.
Also what are the other fields in the table

get date

get date

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment