Integers, Money values becoming zeros

Hi,

When I reset the SQL query in Database ->Show Sql Query, all integer and money type values are becoming zeros. When I execute the same query in the SQL Query Analyzer I am getting the values with same no. of records in the report. Can anybody suggest me the solution.

The only difference before resetting, the query doesn't have "WHERE" clause.

Thanks in advance.Post the query you used and the table structure

Integer Parameter

I have a parameter field with a data type of int. I want the users to be able to select one or 'all' from the dropdown list. Is there a wildcard for a integer datatype?

Dropdown list example:

All Project Ids

1001

1002

1003

1004

In cases like this I will frequently use a NULL parameter to designate 'all'; can that work for you in this case? An example of this would be something like this:

declare @.example table (integerCol integer)
insert into @.example values (1001)
insert into @.example values (1002)
insert into @.example values (1003)
insert into @.example values (1004)

declare @.anIntegerParm integer
set @.anIntegerParm = null
set @.anIntegerParm = 1002

if @.anIntegerParm is null

select * from @.example

else

select * from @.example
where integerCol = @.anIntegerParm

-- When @.anIntegerParm is null:

-- integerCol
-- --
-- 1001
-- 1002
-- 1003
-- 1004

-- When @.anIntegerParm = 1002:

-- integerCol
-- --
-- 1002

select * from @.example
where @.anIntegerParm is null
or @.anIntegerParm is not null
and integerCol = @.anIntegerParm

-- integerCol
-- --
-- 1002

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

Installing SQL Express 2K5 on a domain controller?

I want to install SQL Express 2K5 on a lightly used second Windows 2003 SP1 DC. I know about the warning not to do this type of install. However, the group is small and their budget does not allow for another server just for one application. With that being said, the error message I keep getting is "can't create local groups......" (I know that you do not have local groups with AD). So, is there a work around to this issue or can someone point me in the right direction as how to install on a domain controller? Thanks in advance for your time!!!

hi,

please have a look at http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/ReadmeSQLEXP2005.htm, paragraph 3.10

regards

|||

Andrea,

Thanks for the link, however that is the reason for the post. Paragrph 3.10 states "

3.10 Installing SQL Server Express on a Windows Domain Controller

Security Note:

We recommend against running SQL Server Express on a domain controller.

It is possible to install SQL Server Express on a Windows domain controller; however, it cannot run on a Windows Server 2003 domain controller as Local Service or Network Service. SQL Server service accounts should run as Windows domain user accounts. It is also possible to install SQL Server service accounts to run as Local System, but this option is not recommended."

I just need to know how to get SQLExpress to install without trying to create local accounts and use a specific network user account. Again thanks for your time and effort.

|||

You will have to create specific domain accounts for the SQL Server Services -BEFORE you attempt to install. Here are some articles with information about what accounts are needed and why.

Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Configuration -Service Accounts, SQL Server or SQL Server Agent service account
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx

Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
http://support.microsoft.com/kb/907557