DateTimePicker (VB6)

[david]

I m using two DTP controls in my VB app linked to Access 2000, one for Begin_Date and one for End_Date field. These two fields are grouped together with other input boxes (text boxes). The two DPTs and input boxes only link to DB when hit Update button, i.e. both DataSource and DataField are linked to an adodc by code when operate, not at design time. The two DPT work well when not linking and did work well when linking before.
However, now one of them doesnt work and always show msg "Unable to bind to field or DataMember: 'end_date' (Error 545)", when the code is linking and it stops at the line:
dtpED.DataField = "end_date". My relevant code as follows:

.....
Set txtSH.DataSource = adoSQL
Set dtpBD.DataSource = adoSQL
Set dtpED.DataSource = adoSQL '!!
Set txtComm.DataSource = adoSQL
.....
txtSH.DataField = "select_history"
dtpBD.DataField = "begin_date"
dtpED.DataField = "end_date" '!!!???
txtComm.DataField = "comments"
.........
txtSH.Refresh
dtpBD.Refresh
dtpED.Refresh
....

txtSH and txtComm are other text boxes, dptBD is another DPT for "Begin_Date", adoSQL has recordset retrieved from the DB by select statement. These is no spelling error or the like, e.g. I copied the Datafield name "end_date" from the select statement for adoSQL and the name is exactly same as its field name in the DB. As said, it worked before and I didnt change anything in this part of code or adoSQL statement, but it now stops working for some reason I really couldnt get idea after tried for two days. Appreciate if whoever knows the tricks related to DateTimePicker bounding to adodc or DB and let me know. By the way, another DTP control Begin_Date is set the same properties as End_Date DTP but receive no error msg.

David

[david]

It seems a hard question or rarely people use this control as nobody response the post for days.

I also found when delete all the records in the related table, the same msg occur too but at the line of "dtpBD.DataField = "begin_date", not the line of 'end_date". If I import some records that some of "begin_date" and "end_date" fields are empty, same error occurs too. If all the records have these two fields filled, no error, everything seems to work very well. However, during import the records, if try to convert the empty fields (date type) to, e.g. "1/1/9999" (first to string type then use CDate(string) to date type), it can only be converted when imported into a temp table. Then from the temp table move to the relevant db table "Main", the converted dates cannot be transferred into Main, or no "1/1/9999" in Main. All the field names and types are exactly same for both temp and Main tables and the two date fields are not primary or foreing keys.

Can these more infor give someone hints to see what tricks are for these DTP controls and/or date type fields problems?
david

[charlotte]

I've used the datetime picker a fair amount but only directly in Access, so I'm not sure the experience translates straight across to VB. Does the DTP work if you set the datafield in the properties window instead of from code?

[david]

Thanks Charlotte.

My input boxes including DTPs have to be dynamically bound, also ADODC's are connected by code as the db location may be changed later (cannot use udl file). So cannot set Datafield in design time for DTP.
Now I tried to test this anyway. Result: same error msg again for the two DTP's when the program starts. This means something wrong regardless setting in code or design time. The test conditions are: adodc created in design time by using the same RecordSource SQL. I used in the code; in the properties window both DTP's are set the same except one is False, another is True in UpDown property; DataFormat is set Date, Format is 1-dtpShortDate; DataSource is connected to that adodc and DataField linked to the relevant field of the adodc.
Can you see what is wrong with this?
David

[charlotte]

I wasn't suggesting you abandon your code. I was asking if they worked when you bound them through the properties window. If they did, you could exclude the ADODC and the DTPs themselves as problems and focus on the code issue. The fact that they don't work when you bind them directly means that there's something wrong with your connection I think.

I didn't see any code in your post to bind the ADODC to its connection string, so maybe something you're doing there is at fault. Here's an example of a Form_Load that works on my VB6 Pro. I created a connection to the Northwind.mdb and bound the ADODC to the Orders table. I bound the two datetimepickers to the RequiredDAte and ShippedDate fields.

<pre>Private Sub Form_Load()

With Adodc1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:Program FilesMicrosoft OfficeOffice9" _
& "OfficeSamplesNorthwind.mdb;Mode=ReadWrite|Sha re Deny None;" _
& "Persist Security Info=False"
.CommandType = adCmdTable
.CommandTimeout = 240
.RecordSource = "Orders"
.Mode = adModeReadWrite
.CursorType = adOpenKeyset

End With

Set DTPRequiredDt.DataSource = Adodc1
Set DTPShippedDt.DataSource = Adodc1
DTPRequiredDt.DataField = "RequiredDate"
DTPShippedDt.DataField = "ShippedDate"

DTPRequiredDt.Refresh
DTPShippedDt.Refresh
End Sub</pre>

[david]

It could be adodc's problem. But so far I failed to to find out where it is.

Comparing to your code, my connect properties are not much different. One thing is diff for sure is that you use one table and my one connects to three tables by SQL select clause.
According to your code, I changed my other three properties that diff from yours to the same ones as yours.
I.e. 1. in the ConnectionString, I added "Mode=ReadWrite|Share Deny None;", 2. adodc.mode = adModeReadWrite, 3. adodc.CursorType = adOpenKeyset. I still get the same error result as before.
Other adodc properties I didnt touch them, so as default.
I dont think my RecordSource SQL has problem that also serves for my Datagrid and other functions and works well, but I quote it below:

adodc.RecordSource = "select m.entry_order,m.expmnt_desc,m.trial_name,m.trial_l ocation,m.plot_num, m.line_name,m.Pedigree_Parentage,q.select_history, d.disease,d.rating, m.begin_date, m.end_date, d.comments
from Main m, QT_Num q, Disease d
where m.line_name = q.line_name and m.did = d.did and m.Pedigree_Parentage = q.Pedigree_Parentage;"

It is a one-line long string and I didnt let it change line for better performance. All the three fields in Where clause are PK/FK.

David

[charlotte]

Have you tried using explicit JOIN statements in your SQL instead of the implied joins? I haven't had any success with multi-table queries behind a datacontrol, but maybe someone else can help.

Edited

Actually, I got it to work with a 2-table query but I had to set the properties in the properties window in order to get the links to work. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>