Solved

DTPicker and DB datatype

Posted on 2014-04-27
9
285 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 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