Solved

DTPicker and DB datatype

Posted on 2014-04-27
9
286 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

617 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