Improve company productivity with a Business Account.Sign Up

x
?
Solved

DTPicker and DB datatype

Posted on 2014-04-27
9
Medium Priority
?
297 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a 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.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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