Integer Datatype And Null Value Error

May 2, 2006

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.

View 1 Replies


ADVERTISEMENT

Integer Datatype

Dec 4, 2000

Hi all,

I have a situation where I will have to insert a value(whole number) into the table where the the value is more than what the Integer can hold , I was wondering is there any other datatype which i can use other than integer


Thanks in advance.

Sanjeev Kumar

View 1 Replies View Related

Integer Datatype Question

Jun 4, 2004

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:

View 2 Replies View Related

Could A Datatype Of A Column Be Like Integer Array?

Aug 7, 2004

Hi everyone....
I need a column in my table that its DataType should be as integer arrays how can I implemant it in my sqlserver table.
Any help appreciated.

View 5 Replies View Related

Insert Integer Null Value

Jun 17, 2008

 Hi,I have SQL Server DB, and I tried to insert some data. I used StorProc to do the insertion as follows:ALTER PROCEDURE dbo.InsertPage        (    @PageID int,    @ParID int,    @ChiID int,    @PageContent ntext    )AS        INSERT INTO MP_Page                            (PageID, ParID, ChiID, PageContent)      VALUES     (@PageID,@ParID,@ChiID,@PageContent)    RETURNThe problem happened when I tired to insert int null value in field ChiID which is allow null and here is my codeprivate void InsertPage(PagesDB PageDB, out pagedetails pages)        {            if (txtChild.Text == "")            {                int varChildID;                varChildID = int.Parse(txtChild.Text.Trim());                pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), varChildID,                int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim());            }            else            {                pages = new pagedetails(int.Parse(ddlParent.SelectedValue.Trim()), int.Parse(txtChild.Text.Trim()),                int.Parse(txtPageID.Text.Trim()), FTB.Text.Trim());            }                PageDB.InsertPage(pages);        } the error message says Input string was not in a correct format.Any idea ??Thank you  

View 3 Replies View Related

Errors With Null Integer Values

Aug 3, 2006

I am new to SQL Server and am trying to figure out why it behaves the way it is regarding integers with null values.

I have a table that contains integer datatypes which can be null. If i insert the record with a blank field i receive an INCORRECT SYNTAX NEAR ',' error. If i surround the form value with '', it inserts a 0. Why does SQL Server behave like this? And whats the proper way to handle inserts such as this?

Thanks!

View 8 Replies View Related

How To Write A Function With Integer Parameter Accepting Null

Jan 19, 2007

I've got a function which inserts into a database, and has arguments for each item being inserted
A couple of the items are integer datatypes (in SQL), but they will accept nulls
When I add my parameters, it asks to explicitly use the SQL datatype (which is integer):.Add("@myParam", SqlDbType.Int).Value = myParam
In the header of the function, I assumed I could make the argument optional - Optional ByVal myParam as Integer=System.DBNull.Value
However, when the function runs, I always get an error:System.InvalidCastException was unhandled by user code  Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
I make them all Optional (which won't happen, but various arguments may be, at different timesand I get this error, with the last item (which is on a separate line), in red:Constant expression is required.
How can I get around this?

View 3 Replies View Related

Datatype Bit And Null

Jan 19, 2008

Hi,

I have two tables in one table's field I have the datatype bit and null.
In 2nd table's I have the datatype varchar 10.
Now I want to Select the value of 2nd table in my Controle that is Dropdown and insert this value in the 1st table like bit. as this possible.
Thanks for helping.


Navi

View 11 Replies View Related

Datatype Of NULL

Oct 17, 2007

Hello,

What is the Datatype for NULL ? How SQL Server stores NULL ? I have seen many discussions on this. Some say's that NULL will be considered as character type, then what about NULL value comes in a number column ?

Another article say's NULL is untyped. It don't have any type. Which one is correct ? Will there any difference for NULL in SQL Server and Oracle ?

Any help would be appreaciated

View 8 Replies View Related

Integration Services :: Replace Blank Strings Values To NULL And Convert To Integer Data Type

Oct 5, 2015

I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.

View 5 Replies View Related

Does NULL Inherit A Datatype After Convert

Jun 16, 2015

i have a little confusion...

DECLARE @name varchar(30)
SET @name = NULL
SELECT ISNULL(CONVERT(DATE,@name),'')
Result: 1900-01-01

But..

DECLARE @name varchar(30)
SET @name = NULL
SELECT ISNULL(@name,'')

Does NULL inherit a datatype after convert?

View 7 Replies View Related

How Much Storage Does A Null Xml Datatype Column Use If...

Sep 27, 2007


1) it is in the same filegroup as the rest of the table?
2) it is stored in a filegroup separately from the rest of the table (I think this is allowed)?

View 1 Replies View Related

Insert Null Value In Column With Int Datatype

May 14, 2008



how do i insert null value to a column with 'int' datatype. This columnn is primary key. Please help.

Akhil

View 3 Replies View Related

Alter Table Datatype With Null Values

May 7, 2015

I am trying to change a column from a decimal(18,2) to a decimal(18,3). What is the SQL command to alter this table?

SQL Command:

alter table TableName
alter column ColumnName decimal(18,3) [null]

the above command is right ?

View 6 Replies View Related

Insert NULL Into Smalldatetime Datatype Field.

Mar 20, 2008

Hi,

I am facing problem while inserting a Null value into a smalldatetime datatype field in sql server 2000 using code in vb 6.0

Error as : Type mismatch.

Kindly let me know how to insert Null or blank (dtDate = "") into a column.

Regards,

Srinivas Alwala

View 1 Replies View Related

NULL Values To Sequential Number (The Field Is Nvarchar Datatype)

Jun 16, 2012

Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.

My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.

To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.

View 1 Replies View Related

Transact SQL :: Based On Data Convert Datatype And Make Wrong Date As NULL

Apr 21, 2015

In the below scenario we are inserting some time related fields in Temp table.But its data type is varchar. Once data loading is finished in the temp table (Data is loading by source team SQOOP and they are unable to load if the source datatype is having Date or datetime) we have to alter the column datatypes. somehow, some character data in inserted in date columns (look at into 3rd insert statement). while altering the table it is failing. Can we do any alternative for this (Means if any varchar data that is non convertible to date can we make as null)

INSERT INTO ##TEMP_TEST
SELECT '2014-09-30','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
UNION SELECT '2014-09-29','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
UNION SELECT '2014-09-28','2017-10-06','Nov  6 2014  6:11AM','Nov  6 2014  6:11AM'
GO
INSERT INTO ##TEMP_TEST SELECT NULL,NULL,NULL,NULL 

[Code] ....

View 6 Replies View Related

DB Design :: Convert Integer Date (MMDDYY) And Integer Time (HHMMSS) To DateTime Format?

Jul 20, 2015

Working on a new database where the Date and Time are stored in a Date Time Field.

Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:

transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)

How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?

This works well for converting the transDate Part in the select statement:

   dbo.IntegerToDate(at.transDate) as transDate

   * That returns: "2015-07-16 00:00:00.000"

* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!

Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.

View 3 Replies View Related

Validation Error When Converting Integer Zip To String

Sep 17, 2007

I am taking a five-digit zipcode from a database where it is stored as an int, and I would like to store it as a char(5).

I set my OLE DB source to pull from a proc as:




Code Snippet

SELECT

cust.CustomerID as CustomerID,
case

when cust.Zip > 99999 then Null
when cust.Zip < 0 then Null
else REPLICATE('0',5 - LEN(cast(cust.Zip as varchar(5))))

+ cast(cust.Zip as varchar(5))
end as CustomerZip


However, when I do that, SSIS sees the external column as a string of length 8000, and insists on giving me a truncation warning on validation.

I also tried bringing the Zip field in as an int and doing a data conversion in the SSIS data stream. This worked great until I added a Derived Column transform to do the REPLICATE function as above to add leading zeros to the zip code. Once I did that, SSIS decided that there was a chance of truncation again and started with the truncation warnings.

My derived column is:




Code Snippet

REPLICATE("0",5 - LEN(TRIM([CustomerZip]))) + TRIM([CustomerZip])




BTW - I don't think the TRIM() function is really necessary in the above, but I tried it out of desperation and it made no difference.

This is really starting to drive me nuts. Does anyone have any thoughts on how to convert an integer zipcode to a char(5) string with leading zeros without incurring these validation warnings?

TIA

- Steve

View 4 Replies View Related

Type Cast Error Converting From Integer To String

Oct 30, 2007

I am attempting to convert an integer value to a string using the Derived Column transformation with the following expression on the field:
(DT_STR,10,1252)prod_id

prod_id is an integer. I was able to do this before however, in the past couple of days, this has failed with the following error:
"An error occurred while attempting to perform a type cast."


To my knowledge, I have not changed anything about this particular data flow within the past couple of days. I have verified that the value is coming in as an integer.

Any help would be greatly appreciated.

View 7 Replies View Related

Datatype Error

May 23, 2008

Here a4e the values I am passing in
articleID = 17
Keywords = car|boat
categoriesRemove = 1,4,14


ALTER PROCEDURE [dbo].[GetArticlesByKeywords]
@articleID int,
@Keywords VARCHAR(MAX),
@categoriesRemove nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @MySQLPart varchar(4000)

SET @MYSQLPART = 'SELECT DISTINCT articleKeywords.articleID,
articles.articleTitle,
articles.articleSummary
FROM articleKeywords
LEFT OUTER JOIN articles
ON articleKeywords.articleID = articles.articleID
WHERE (articleKeywords.articleKeyword IN (SELECT data
FROM dbo.FUNCTION_STRING_TO_TABLE (''' + @Keywords + ''',''|'' ) AS function_string_to_table_1))
AND (articleKeywords.articleID <> ' + @articleID + ')
AND (NOT (articles.categoryID IN (' + @categoriesRemove + ')))'
EXEC (@MYSQLPART)
END




************ERROR*********************

Conversion failed when converting the varchar value 'SELECT DISTINCT articleKeywords.articleID,
articles.articleTitle,
articles.articleSummary
FROM articleKeywords
LEFT OUTER JOIN articles
ON articleKeywords.articleID = articles.articleID
WHERE (articleKeywords.articleKeyword IN (SELECT data
FROM dbo.FUNCTION_STRING_TO_TABLE ('car|boat|van','|' ) AS function_string_to_table_1))
AND (articleKeywords.articleID <> ' to data type int.

View 2 Replies View Related

Getting Error While Using Text Datatype

Oct 25, 2006

Hello, I am writing a sproc and am getting this error: Any ideas? Thanks!!Msg 402, Level 16, State 1, Procedure InsertUserPreferences, Line 18The data types text and text are incompatible in the equal to operator.-------------------------------------------------------------------------------------------------------------------create procedure InsertUserPreferences(@PublisherServer text)asbeginif exists(Select Preference_StringList from USER_Preference where Preference_StringList = @PublisherServer)begin--UPDATEexec dbo.uProc_USER_Preference end

View 3 Replies View Related

Error With Using Nvarchar Datatype

Sep 10, 2007

Hi, I have created a database using VWD to keep values of urls and have structured it as...
Prefix (http://, network name), address(www.name.com), and name (name of address), the address field has been defined as a nvarchar(MAX).
Most of the addresses updated into the address field work, except something like: www.java-scripts.net/javascripts/Image-Rollover-Script.phtml.
I get this error:
Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.Login failed for user 'NETWORKNAMEASPNET'.Source Error:



Line 1176: if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)
Line 1177: != System.Data.ConnectionState.Open)) {
Line 1178: this.Adapter.InsertCommand.Connection.Open();
Line 1179: }
Line 1180: try {
I can insert something like www.google.com into the addresses field without any errors. Any ideas why?If it is a nvarchar type it should be able to except all sorts of characters??

View 11 Replies View Related

Date - Datatype Error

Jun 1, 2000

In Ms-Access table I have the field called
date which yyyymmdd

the data I have in Ms-Access is

19940101
year month day.

Ms-Access data type is date and ms-Sql datatype is datatime. but still i'm getting error any help

What should be the Ms-Sql data type filed ?

When I try to conver from Ms-ACCESS to MS-SQL Server it give me this error


Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime

View 2 Replies View Related

Datatype Conversion Error In DTS

Aug 25, 2000

I am using DTS to import a DB2 table from the mainframe and export the table in text format to a shared folder. I want to convert two fields to a date format yyyy-mm-dd. RELSE_Date is in this format and Updtts is a timestamp coming from the mainframe. The text file is all vchar. In dts here is my query pulling from the mainframe
SELECT A.PROD_ORDER_NUMBER, A.DYE_SEQUENCE, A.STYLE,
A.COLOR, A.SIZE, A.STATUS_IND, A.STATUS_CODE,
A.QUANTITY_REC_DC, B.SHIP_OTFQ_QTY,
A.MRP_PACK_CODE, A.LABELS_PRINTED,
date(A.RELSE_DATE) as RELSE_DATE,
date(A.UPDTTS) as UPDtts, A.LOCATION
FROM DB2.WP1_PO_CUST_REQ A,
DB2.WP1_DYE_LOT_REQ B
WHERE A.PROD_ORDER_NUMBER = B.PROD_ORDER_NUMBER
AND A.DYE_SEQUENCE = B.DYE_SEQUENCE
AND A.STYLE = B.STYLE
AND A.COLOR = B.COLOR
AND A.SIZE = B.SIZE
AND (A.PROD_ORDER_NUMBER LIKE '__K____')
When I parse the query it accepts it. When I run the DTS I get an error stating (SQL STATE 220077 SQLCODE -180) The sting representation of a datetime value has invalid syntax. Does anyone have a suggestion on how to convert these fields before the text file is exported or another output format that is similar to .dat in comma delimited format? Thank you.

View 1 Replies View Related

Error With Datetime Datatype

Oct 31, 2006

Hi Guys,

I'm having problem in loading textfile into the database. One of the columns in the textfile has a datetime datatype.

Here is a sample of the text file. All the other columns are string except for the datetime: "5/4/2006"

"1","1","ITEM","5/4/2006","10:05:04","11110",10004,"Regular Half Chicken",1,130.00,0,0


Error is shown below.

Error: 0xC0202009 at Data Flow Task, OLE DB Destination [154]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

Error: 0xC020901C at Data Flow Task, OLE DB Destination [154]: There was an error with input column "Transaction_Date" (1233) on input "OLE DB Destination Input" (167). The column status returned was: "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, OLE DB Destination [154]: The "input "OLE DB Destination Input" (167)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (167)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (154) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.



Please help!!!

Thanks in advance,

Larry

View 7 Replies View Related

Getting Error While Using Text Datatype

Oct 25, 2006

Hello, I am writing a sproc and am getting this error: Any ideas? Thanks!!

Msg 402, Level 16, State 1, Procedure InsertUserPreferences, Line 18

The data types text and text are incompatible in the equal to operator.

-------------------------------------------------------------------------------------------------------------------

create procedure InsertUserPreferences

(

@PublisherServer text

)

as

begin



if exists(Select Preference_StringList from USER_Preference where Preference_StringList = @PublisherServer)

begin

--UPDATE

exec dbo.uProc_USER_Preference

end

View 4 Replies View Related

T-SQL (SS2K8) :: Varchar Datatype Field Will Ignore Leading Zeros When Compared With Numeric Datatype?

Jan 28, 2015

Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?

create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp

[Code] .....

View 4 Replies View Related

Numeric Datatype To Ssis Variable Datatype Conversion Problem

Apr 24, 2008



Good afternoon,

I have an issue with an ssis variable datatype.

The scenario is as follows:

I have a stored procedure:


PROCEDURE [dbo].[sp_newTransaction]



@sourceSystem varchar(50),

@txOut NUMERIC(18,0) OUTPUT

AS

insert into scn_transaction (sourceSystemName) values(@sourceSystem);

SELECT @txOut = @@identity


Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).

I execute the stored proc with the following sql with an OLE DB connection manager:

exec sp_newTransaction ?, ?

The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:

User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1

The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.

At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.

I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.

Thanks much,

B

View 6 Replies View Related

Derived Column Error:can't Change Datatype

Aug 31, 2007

I am trying to replace the value of a column in a derived column component, but it will not let me change the datatype.

It has decided that the column is a float, which is wrong.

How can I change it to the correct type?

View 8 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off


go

declare

@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)

AND DR.[ID]= @ID

)

go

set ansi_nulls on

View 3 Replies View Related

Error When Convert Or Cast Functions From Varchar To XML Datatype

Dec 29, 2007

Hi I have a varchar(8000) and currently XML files are stored in varchar(8000).Some times when i am doing manuplactions in my varchar column i am getting with special characters error. so now i want to keep my column varchar(MAX) and when i am doing calculations i will convert my varchar datatype to xml datatype. By doing this i hope there wont be any special character problems.
When i am doing calculations with the wellformed xml i am getting error for both convert and cast methods as below 
I am trying to do convert(xml,MyVarcharColumn)
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Also i tried with casting and getting same problme. is there any way to convert
 
please suggest me
 
Thanks
Dilip

View 1 Replies View Related

Syntax Error Converting Varchar Value '/' To A Column Of Datatype Int

Aug 31, 2007

Hi,
Can anyone please help me with the syntax for this query please. Error is "syntax error converting varchar value '/' to a column of datatype int"
Query:

Code:

select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label,((select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ) + ' /' + (
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0)) as Countrec from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID


Thanks...

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved