473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL STATE 22003: Numeric value out of range with TIME data type

Hi,

I am trying to insert a TIME value in a SQL Server 2008 database using
a simple ODBC C program. I follow the steps below:

1. Connect and Allocate Statement handle
2. Prepare a Statement as:
rc = SQLPrepare(hstmt, "insert into table1 values (?)" ,
SQL_NTS);
(NB: Table1 has only one column of type TIME in SQL Server 2008)
3. Bind the parameter:
rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,
SQL_C_BINARY,SQL_SS_TIME2, 16,7, &buffer,
sizeof(SQL_SS_TIME2_STRUCT ), 0);

(where, buffer is of type SQL_SS_TIME2_STRUCT and buffer.hour = 12;
buffer.minute=12; buffer.second =12; buffer.fraction = 0)

I also tried SQL_C_DEFAULT instead of SQL_C_BINARY and got the same
error.
4. rc = SQLExecute(htsmt)

Appreciate any help to resolve this.

Thanks in Advance,
Navneet
Nov 5 '08 #1
1 11979
Navs (na***********@gmail.com) writes:
I am trying to insert a TIME value in a SQL Server 2008 database using
a simple ODBC C program. I follow the steps below:

1. Connect and Allocate Statement handle
2. Prepare a Statement as:
rc = SQLPrepare(hstmt, "insert into table1 values (?)" ,
SQL_NTS);
(NB: Table1 has only one column of type TIME in SQL Server 2008)
3. Bind the parameter:
rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,
SQL_C_BINARY,SQL_SS_TIME2, 16,7, &buffer,
sizeof(SQL_SS_TIME2_STRUCT ), 0);

(where, buffer is of type SQL_SS_TIME2_STRUCT and buffer.hour = 12;
buffer.minute=12; buffer.second =12; buffer.fraction = 0)

I also tried SQL_C_DEFAULT instead of SQL_C_BINARY and got the same
error.
4. rc = SQLExecute(htsmt)
ODBC programming is nothing I normally do, but I've been reading the
manual, and it seems to me that the problem is the last parameter. I
this this needs to be address to some place where you have written
sizeof(SQL_SS_TIME2_STRUCT). In the documentatation for
SQLBindParameter, I found:

Note Application developers are strongly discouraged from specifying
a null pointer for StrLen_or_IndPtr when the data type of the parameter
is SQL_C_BINARY. To ensure that a driver does not unexpectedly truncate
SQL_C_BINARY data, StrLen_or_IndPtr should contain a pointer to a valid
length value.

I also found this note in SQL Server Books Online 2008 on:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_2devguide/html/7ac098db-9147-4883-8da9-a58ab24a0d31.htm

If the byte length of the data does not equal the size of the struct
required by the SQL type, a diagnostic record is generated with
SQLSTATE 22003 and the message "Numeric value out of range".


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 5 '08 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Luigi Ida' via SQLMonster.com | last post by:
Hi, I have a php application connected through odbc to a sqlserver database. When I try to execute select queries on a smalldatetime table field I receive this message: Warning: Numeric value...
30
by: Dr John Stockton | last post by:
It has appeared that ancient sources give a method for Numeric Date Validation that involves numerous tests to determine month length; versions are often posted by incomers here. That sort of code...
2
by: MrNobody | last post by:
I just noticed that a numeric up/down will let you manually type in any number even if it is beyond the min/max range you specified. The control will not actually return this value if it is...
2
by: Chad | last post by:
I have a textbox control, txtMeasurement, that I want to allow only numeric decimal input. I thought to use a client side validation control to ensure that the data entered is of type "Double". ...
7
by: Sheldon | last post by:
Hi, I have the following loop that I think can be written to run faster in Numeric. I am currently using Numeric. range_va = main.xsize= 600 main.ysize= 600 #msgva is an (600x600) Numeric...
4
by: JohnnyDeep | last post by:
Hi, I am getting this error while selecting some information from syscat.columns in DB2. DB2LEVEL is db2level DB21085I Instance "db2prod4" uses "32" bits and DB2 code release "SQL08021"
0
by: ronysk | last post by:
Hi, I am posting here to seek for help on type conversion between Python (Numeric Python) and C. Attachment A is a math function written in C, which is called by a Python program. I had...
2
by: avimel | last post by:
I have a CLOB generated by a call to DBMS_XMLGEN.NEWCONTEXT('SELECT ...') which contains data that needs to be parsed and output to file one row at a time; the row data in the CLOB is delimited by...
2
by: tron_23 | last post by:
hi, we use Toplink (TopLink - 4.6.0 (Build 417) with a DB2 Database 7.2. i know really old versions, but we could change to e newer one ;-) Sometimes we got some problems with update or insert...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.