Integer datatype question

I have a column setup using the Int datatype and a length of 4. First: does this mean that the allowable range is from -9999 to 9999? Second: I can't seem to change the 4 to anything else, how can I modify the length :confused:That's the internaly stored length...

Look up datatypes in books online...

int is actually

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).|||no, the 4 indicates the amount of bytes used. It's a fixed size, you can't change it. As specified by BOL, integer values '(...)from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).'. If this won't do use a bigint instead.

EDIT: Brett, I'll quote you on this: Damn...sniped again

integer datatype confusion, signed vs unsigned

Hi Group
Transact SQL defines that int is:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
1 (2,147,483,647). Storage size is 4 bytes.
This implies that only SIGNED integer values are possible with
SQL-Server. I'm aware that from a data conversion point of view this
is no problem in that a singed integer can be interpreted as unsigned
or signed.
Then, there is a (c) datatype definition SQLUINTEGER as well as
SQLINTEGER, so unsigned integers seem to be suported conversion wise.
However, when it comes to sorting or using select, things are IMHO
different in that -1 is smaller than '0' if interpreted as signed, but
obviousely the biggest possible value interpreted as unsigned etc. I
therefore somehow miss the possibility to declare an integer collumn
to be "unsigned" so as sorting etc. is made the right way.
The same problem obviousely exists with small integers except that
their range is limitted to what can be expressed with 16 bits.
Could someone sheed some light on this?
TIA
MarkusThe INT datatype is signed, as are BIGINT and SMALLINT. TINYINT is
the only one not signed. This is can not be changed.
If negative numbers are not valid for a column, enforce that with a
CHECK constraint:
CHECK (IntCol >= 0)
Roy Harvey
Beacon Falls, CT
On Wed, 19 Apr 2006 11:26:57 +0200, Markus Zingg <m.zingg@.nct.ch>
wrote:

>Hi Group
>Transact SQL defines that int is:
>Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
>1 (2,147,483,647). Storage size is 4 bytes.
>This implies that only SIGNED integer values are possible with
>SQL-Server. I'm aware that from a data conversion point of view this
>is no problem in that a singed integer can be interpreted as unsigned
>or signed.
>Then, there is a (c) datatype definition SQLUINTEGER as well as
>SQLINTEGER, so unsigned integers seem to be suported conversion wise.
>However, when it comes to sorting or using select, things are IMHO
>different in that -1 is smaller than '0' if interpreted as signed, but
>obviousely the biggest possible value interpreted as unsigned etc. I
>therefore somehow miss the possibility to declare an integer collumn
>to be "unsigned" so as sorting etc. is made the right way.
>The same problem obviousely exists with small integers except that
>their range is limitted to what can be expressed with 16 bits.
>Could someone sheed some light on this?
>TIA
>Markus|||Markus Zingg (m.zingg@.nct.ch) writes:
> Transact SQL defines that int is:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 -
> 1 (2,147,483,647). Storage size is 4 bytes.
> This implies that only SIGNED integer values are possible with
> SQL-Server. I'm aware that from a data conversion point of view this
> is no problem in that a singed integer can be interpreted as unsigned
> or signed.
> Then, there is a (c) datatype definition SQLUINTEGER as well as
> SQLINTEGER, so unsigned integers seem to be suported conversion wise.
I'm not really sure where you find this SQLUINTEGER type, but if the
type is in C, I presume that the type collection has been defined for
more engines than SQL Server in mind, and some of those engines may
support an unsigned integer type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

integer datatype and null value error

I'm getting a datatype error: "Application uses a value of the wrong type for the current operation" when executing the following stored procedure:

CREATE PROCEDURE dbo.Insert_Temp_ContactInfo
@.sessionid varchar(50),
@.FirstName varchar(50) = NULL,
@.LastName varchar(50) = NULL,
@.SchoolName varchar(50) = NULL,
@.address varchar(50) = NULL,
@.City varchar(50) = NULL,
@.State int = NULL,
@.Zip varchar(5) = NULL,
@.Phone varchar(10) = NULL,
@.Email varchar(50) = NULL,
@.CurrentCustomer varchar(3) = NULL,
@.ImplementationType int = NULL,
@.ProductType int = NULL,
@.Comment varchar(500) = NULL
AS
--check if a current record exists
SET NOCOUNT ON
begin
UPDATE dbo.Temp_ContactInfo
SET
FirstName = @.FirstName,
LastName = @.LastName,
SchoolName = @.SchoolName,
Address = @.address,
City = @.City,
State = @.State,
Zip = @.Zip,
Phone = @.Phone,
Email = @.Email,
CurrentCustomer = @.CurrentCustomer,
ImplementationType = @.ImplementationType,
ProductType = @.ProductType,
Comment = @.Comment
WHERE
sessionid = @.sessionid

If @.@.Rowcount = 0
INSERT INTO dbo.Temp_ContactInfo
(sessionid,
FirstName,
LastName,
SchoolName,
address,
City,
State,
Zip,
Phone,
Email,
CurrentCustomer,
ImplementationType,
ProductType,
Comment)
VALUES
(@.sessionid,
@.FirstName,
@.LastName,
@.SchoolName,
@.address,
@.City,
@.State,
@.Zip,
@.Phone,
@.Email,
@.CurrentCustomer,
@.ImplementationType,
@.ProductType,
@.Comment)
end
GO

This is code I'm using to call the procedure:

set InsertTempInfo = Server.CreateObject("ADODB.Command")
With InsertTempInfo
.ActiveConnection = MM_DBConn_STRING
.CommandText = "dbo.Insert_Temp_ContactInfo"
.CommandType = 4
.CommandTimeout = 0
.Prepared = true
.Parameters.Append .CreateParameter("@.sessionid", 200, 1,50, usrid)
.Parameters.Append .CreateParameter("@.FirstName", 200, 1,50,fname)
.Parameters.Append .CreateParameter("@.LastName", 200, 1,50,lname)
.Parameters.Append .CreateParameter("@.SchoolName", 200, 1,50,schoolname)
.Parameters.Append .CreateParameter("@.address", 200, 1,50,address)
.Parameters.Append .CreateParameter("@.City", 200, 1,50,city)
.Parameters.Append .CreateParameter("@.State", 3, 1,4,state)
.Parameters.Append .CreateParameter("@.Zip", 200, 1,5,zip)
.Parameters.Append .CreateParameter("@.Phone", 200, 1,10,phone)
.Parameters.Append .CreateParameter("@.Email", 200, 1,50,email)
.Parameters.Append .CreateParameter("@.CurrentCustomer", 200, 1,3,currentcustomer)
.Parameters.Append .CreateParameter("@.ImplementationType", 3, 1,4,implementationtype)
.Parameters.Append .CreateParameter("@.ProductType", 3, 1,4,producttype)
.Parameters.Append .CreateParameter("@.Comment", 200, 1,500,comment)
.Execute()
End With
Set InsertTempInfo = Nothing

the error is thrown on the following line:

.Parameters.Append .CreateParameter("@.State", 3, 1,4,state)

I'm using a table to hold data that I can pass back to the original form page and re-populate the fields that were not validated correctly. The stored procedure either inserts or updates the record in the temp table I've created.

So, currently, as I'm testing, I'm just passing empty values to all the parameters and the @.state parameter is failing and throwing the error.

I've double checked that the table has the state column set to integer datatype

The column is set as follows:

Name datatype length Allow Nulls
--------------
State int 4 checked

I have tried setting the default value for every column to Null in the table and then also not using a default value. Either way, I still recieve the same error?

Not sure what else to look at?

It seems the problem might be that instead of a null value being passed to the parameter that it is actually empty. Can passing an empty value to a column of datatype integer cause this problem? If so, is there a way to correct it?

Thanks for any help.Basic thing to check: Make sure the data type you are passing to the stored procedure is of the same type as the stored procedure is expecting