Solved

DTPicker and DB datatype

Posted on 2014-04-27
9
281 Views
Last Modified: 2014-04-28
In my vb6 form I have a DTPicker field and I'm using it for tracking time.  The format is dtpTime and when I store the value in SQL server I chose the datatype to be time(7).

Is that a correct setup between the application and the DB
0
Comment
Question by:al4629740
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:ukerandi
ID: 40026210
See below example
The storage space taken by the TIME data type is as follows:
Specified Scale
Result (precision, scale)       Column length (bytes)       Fractional seconds precision
time       (16,7)       5       7
time(0)       (8,0)       3       0-2
time(1)       (10,1)       3       0-2
time(2)       (11,2)       3       0-2
time(3)       (12,3)       4       39145
time(4)       (13,4)       4       39145
time(5)       (14,5)       5       39209
time(6)       (15,6)       5       39209
time(7)       (16,7)       5       39209

time(7) is fine to track time more acuracy
0
 

Author Comment

by:al4629740
ID: 40026297
When I run the code in vb6, I get this error:

"multiple-step ole db operation generated errors. check each ole db status value if available. no work was done"

vb code where error is produced, see below on line 11


            esql = "select * from tblOrgActivities"
            rec.Open (esql), conn, adOpenDynamic, adLockOptimistic
            rec.AddNew
            
            
            If var4 = "Completing" Or var4 = "Participating" Then
            rec!Agency = var3
            rec!PIID = var2
            rec!Method = var4
            rec![Activity Name] = Text2
            rec![From] = DTPicker1  'error is here
            rec![To] = DTPicker2
            rec![Activity Desc] = Text5
            rec!EntryTime = ServerTime
            rec!FiscalHours = Text3
            rec!PrePostTestName = Text4
            rec!Location = Text1
            rec![Activity Outcome] = Text8
            rec![0 To 7] = Check1
            rec![8 To 12] = Check2
            rec![13 To 17] = Check3
            rec![18 To 20] = Check4
            rec![21 and over] = Check5
            End If

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40026395
check each ole db status value if available.
Have you done this?
What is your ADO provider ?  If you are not sure what I mean, post your connection string.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:al4629740
ID: 40026424
Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",1433;Network Library=DBMSSOCN;Initial Catalog= " & CAPDB & "; User ID=sa;Password=****"
0
 
LVL 10

Expert Comment

by:ukerandi
ID: 40026846
rec![From] = DTPicker1.Value.ToLongTimeString();

Or
DTPicker1.Value.ToShortTimeString();

and what is the value you are try to pass to rec![From]
0
 
LVL 10

Accepted Solution

by:
ukerandi earned 500 total points
ID: 40026870
rec![From] = DTPicker1.Value

or
rec![From] =Format$(DTPicker1.Value, "hh:mm AM/PM")
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40027429
sqloledb does not support the new data types (such as time) you need to use SQLNCLI10 instead.
0
 

Author Comment

by:al4629740
ID: 40027759
Do I simply just change it in the code and that's it?
0
 

Author Comment

by:al4629740
ID: 40027765
When I change it I get an error

Bad variable type
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question