Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DTPicker and DB datatype

Posted on 2014-04-27
9
Medium Priority
?
293 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
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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

971 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