How To Handle ISNUMERIC() Function ?
Aug 31, 2006
I have a task (Derived Column Task) and I want to write something like this :
IsNumeric(aColumnOfString) == true ? "All numbers" : "there are some characters"
Here aColumnOfString can be something like "123a5" or 12345". I do not want to simply check if the left-most character is a number or not. I want to check the entire expression and return me a TRUE or false.
A TRUE is returned if the entire expression contains ONLY numbers, and FALSE otherwise.
I read some posting using regular expression. But that is not a solution for this situation.
Anyone knows how to accomplish this, please help!
View 3 Replies
ADVERTISEMENT
Mar 4, 2002
The isnumeric function returns 1 in examples where it should not:
select isnumeric('1D9') returns 1.
Can anyone explain this?
View 1 Replies
View Related
Nov 28, 2007
Hi, I have the follow function:
CREATE FUNCTION [dbo].[ToTime]
(
@intHora int, --A valid hour
@intMin int -- A valid minute
)
RETURNS smalldatetime
AS
BEGIN
declare @strTime smalldatetime
declare @errorvar int
select @strTime=cast(convert(varchar,cast((cast(@intHora as varchar) +':'+ cast(@intMin as varchar)) as smalldatetime),108) as varchar)
return @strTime;
END
the function works perfect but when the parameter for the hour is a negative number (for example -1), or a number > 23
and the parameter for the minute is an negative number (-1) or a number > 59, the function produce an error.
I need handle this error converting the wrong value in 0, but i don't want to do this using "if statement". for example
if @intHora < 0 or @intHora >23
begin
set @intHora = 0
end
if @intMin <0 or @intMin>59
begin
set @intMin = 0
end
please, If someone know some sql function (try - catch doesn't work) to handle this kind of error or some good way to do it, please help me.
View 4 Replies
View Related
May 2, 2015
I have a holding tree with more than 200 companies, and several layers. Each company is defined by:
- a unique ID
- a direct parent ID --> the company immediately above in the tree
- an ultimate parent ID --> the company that owns the entire tree, it is the same for all
Now I am looking for a function that returns the list of all companies below a given one in the tree, and there could be several layers underneath. Doing that for the ultimate parent is easy because all companies now who their ultimate parent is, but I am struggling to build a function that works for an intermediary parent in a dynamic way.
View 8 Replies
View Related
Apr 13, 2004
strange thing I just ran into, not sure if this is a bug or what ... but pretty annoying.
In MS SQLServer 2000 :
SELECT (ISNUMERIC('0E010101'))
returns "1"
but
SELECT CAST ('0E010101' AS numeric)
returns "Error converting data type varchar to numeric"
any idea?
View 14 Replies
View Related
Mar 27, 2006
hi,
i am migrating data from a legacy system with a not nice front-end.
as a result, i have all sorts of garbage stored on the tables.
i
am trying to convert values from varchar(12) to float, but i have an
error during selecting data that says that data can not be converted
eventhough i am using the ISNUMERIC() function to check.
case when
isNumeric( myCol01 ) = 0 then null
else
convert( float , myCol01 )
end
but my error occours when ISNUMERIC() encounters the value '. ' ; that is a dot with spaces after it.
try the expression below;
SELECT
myValue = '. '
, is_numeric = ISNUMERIC('. ')
, converted = CONVERT( FLOAT , '. ')
has anyone got any idea how to work around this?
nicolas
View 5 Replies
View Related
Feb 27, 2008
Hi,
I have a table described as follows:
TableA
GrpDate DateTime;
grpMiscError varchar(1);
Ex Data:
2/1/2008 1
2/1/2008 1
2/1/2008 0
2/1/2008 x
2/1/2008 0
The grpMiscError can contain 0, 1 or x only. I need to sum up this column for all the zeros by a particular date.
I have the following but doesn't work:
SELECT
SUM(CASE ISNUMERIC(grpMiscError) WHEN 0 THEN 1 ELSE 0 END)AS MiscError
FROM TableA
WHERE GrpDate = '2/1/2008'
I get back an answer of 1 MiscError instead of 2
What am I doing wrong here?
Thanks,
J
View 6 Replies
View Related
Feb 5, 2001
Hi all,
I have never seen this problem in SQL Server 7. The isnumeric() returns true for non-numeric data, as show in the following example:
declare @x as varchar(5)
select @x = '00d01'
select isnumeric(@x)
I would expect the isnumeric() function to return false. Can anyone give a reason why this should occur.
Does SQL Server think that this is a hex value and is performing an implicit conversion? If so how horrible...
Nick
View 2 Replies
View Related
Feb 23, 2005
Hello,
I would like to perform a delete statement on one of my tables.
I have a code column that has data in the form 011-234-12
and at some point in this column the data is like R0 or D1
I want to delete the rows where the code data is R0 or D1, E3, etc...
I would like to know how to create an SQL Command for Sql Server 7 that would delete from Soumission_detail where:
first left char is not Numeric in the code column.
Thanks.
View 4 Replies
View Related
Jan 10, 2007
HiHere's the problem:I need to search a postcode database by the first one or two letters.Problems occur for example when i want to search north London postcodes (N) when using:postcode LIKE @postcode + '%' As this picks up everything beginning with N, eg, NG for Nottingham, or NE for Newcastle. So i need a like statement which searches for the first one or two digits followed by a number!I've found the ISNUMERIC() function but not sure what the best way to use it with the like statement - or even if there is a better way altogether - can you use regular expressions in MSSQL?thanks
View 4 Replies
View Related
May 18, 2004
sneaky, sneaky, sneaky
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.
thanks, but which one??
numeric as far as float is concerned, is not the same thing as numeric as far as money is concerned
create table isnumerics
( id integer not null identity
, txtfld varchar(11)
)
insert into isnumerics (txtfld) values ( '1' )
insert into isnumerics (txtfld) values ( '937' )
insert into isnumerics (txtfld) values ( '937.0' )
insert into isnumerics (txtfld) values ( '$937' )
insert into isnumerics (txtfld) values ( '$937.00' )
insert into isnumerics (txtfld) values ( 'free' )
insert into isnumerics (txtfld) values ( '.50' )
insert into isnumerics (txtfld) values ( '1,000' )
insert into isnumerics (txtfld) values ( '' )
select id
, txtfld
, isnumeric(txtfld)
from isnumerics
111
29371
3937.01
4$9371
5$937.001
6free0
7.501
81,0001
90
select id
, txtfld
, isnumeric(txtfld)
, case when isnumeric(txtfld) = 1
then cast(txtfld as money)
else cast(null as money)
end as case1
from isnumerics
1111.0000
29371937.0000
3937.01937.0000
4$9371937.0000
5$937.001937.0000
6free0
7.501.5000
81,00011000.0000
90
select id
, txtfld
, isnumeric(txtfld)
, case isnumeric(txtfld)
when 1
then cast(txtfld as money)
else cast(null as money)
end as case2
from isnumerics
1111.0000
29371937.0000
3937.01937.0000
4$9371937.0000
5$937.001937.0000
6free0
7.501.5000
81,00011000.0000
90
select id
, txtfld
, isnumeric(txtfld)
, case isnumeric(txtfld)
when 1
then cast(txtfld as float)
else cast(null as float)
end as case2
from isnumerics
1111.0
29371937.0
3937.01937.0
6free0
7.5010.5
the others got "Error converting data type varchar to float"
no, there wasn't a question here, but yes, i'd love to hear your comments
View 6 Replies
View Related
Aug 28, 2007
Hi,
I'm casting a varchar field to a decimal field using the format
CASE ISNUMERIC(GrossMktCapGbp)
WHEN 1 THEN CONVERT(DECIMAL(18,6),GrossMktCapGbp)
ELSE NULL
end
Thinking this would ensure that any spurious rows got set to null.
However I had a problem with some values that were set to '.', it seems that isnumeric thinks these are numbers but casting them to decimal produces an error.
SELECT ISNUMERIC('.')
SELECT CAST('.' AS DECIMAL(18,6))
Should I have been doing something different in my check possibly.
Sean
View 10 Replies
View Related
Nov 29, 2007
=iif(
IsNumeric(Fields!Accreditation.Value),
Int(Fields!Accreditation.Value),
Fields!Accreditation.Value
)
The above expression seems to work fine if Fields!Accreditation.Value is a number. However, if Fields!Accreditation.Value is not a number, it gives an #Error. Why is the true part evaluated when the expression is false?
View 6 Replies
View Related
Dec 13, 2005
Been meaning to post this for a while. It does a very limited job of only allowing [0-9], but could be extended to allow negative numbers, or numeric values that are suitable for numeric types other than INT, but avoiding the pitfalls of IsNumeric() which might allow through data not suitable for some of the numeric datatypes
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsINT]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.kk_fn_UTIL_IsINT
GO
CREATE FUNCTION dbo.kk_fn_UTIL_IsINT
(
-- String to be tested - Must only contain [0-9], any spaces are trimmed
@strINTvarchar(8000)
)
RETURNS int-- NULL = Bad INT encountered, else cleanedup INT returned
/* WITH ENCRYPTION */
AS
/*
* kk_fn_UTIL_IsINTCheck that a String is a valid INT
*SELECT dbo.kk_fn_UTIL_IsINT(MyINTColumn)
*IF dbo.kk_fn_UTIL_IsINT(MyINTColumn) IS NULL ... Bad INT
*
* Returns:
*
*int valueValid integer
*NULLBad parameter passed
*
* HISTORY:
*
* 30-Sep-2005 Started
*/
BEGIN
DECLARE@intValueint
SELECT@strINT = LTRIM(RTRIM(@strINT)),
@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%'
THEN CONVERT(int, @strINT)
ELSE NULL
END
RETURN @intValue
/** TEST RIG
SELECTdbo.kk_fn_UTIL_IsINT('123'),IsNumeric('123')
SELECTdbo.kk_fn_UTIL_IsINT(' 123 '),IsNumeric(' 123 ')
SELECTdbo.kk_fn_UTIL_IsINT('123.'),IsNumeric('123.')
SELECTdbo.kk_fn_UTIL_IsINT('123e2'),IsNumeric('123e2')
SELECTdbo.kk_fn_UTIL_IsINT('XYZ'),IsNumeric('XYZ')
SELECTdbo.kk_fn_UTIL_IsINT('-123'),IsNumeric('-123')
SELECTdbo.kk_fn_UTIL_IsINT('-'),IsNumeric('-')
**/
--==================== kk_fn_UTIL_IsINT ====================--
END
GO
Kristen
View 15 Replies
View Related
Feb 14, 2008
I would like to validate datatype using Derived Column.My data type are such as numeric(X,X),datetime,int, and varchar.How do I do this using Derived Column.Example if row does not qualify as ISNUMERIC()...throw it in ERROR table else send it to SUCCESS table.Any Idea ?
View 4 Replies
View Related
Dec 18, 2007
Hello,
I have searched the forum, and have discovered that the DTS method using IsNumeric to check for numierc values (ActiveX) is not valid in SSIS. Most of what I have seen prescribes using the script component to handle this.
So formerly, I checked to see if a column was numeric. If it was, then I needed to use the numeric value as is, or in some cases, I needed to perform a calculation on the value and use the result. If the value was not numeric, then whatever the value was needed to be changed to zero.
Here is an example of how I would use the current value, or set the value to zero:
If IsNumeric(DTSSource("Col003")) Then DTSDestination("ADepTrnx") = CLng(DTSSource("Col003")) Else DTSDestination("ADepTrnx") = 0 End If
This is an example of how I would use the current value in a calculation, or set the value to zero:
If IsNumeric(DTSSource("Col012")) Then DTSDestination("AlliStdFee") = CLng(DTSSource("Col012"))/100 Else DTSDestination("AlliStdFee") = 0 End If
Does anyone have an example of how I would handle both situations in a script component?
Thank you for your help!
cdun2
View 3 Replies
View Related
Sep 10, 2007
Hello all!
I've wrote a small query for SQL 2005 and it's doesn't seem to work.
I have a table that contains two columns (X and Y), X is an int and Y is an nvarchar(50). I've populated this table with some data where Y contains numbers and some strings (e.g. "1", "2", "foo", etc). I've then got a view which only returns the rows where Y is numeric - now, I then query this table stating I only want numbers greater than 0 (i've casted the column) but this throws an error stating "foo" can't be casted. This is strange because the view doesn't return that.
What's going on? All of this works fine in SQL 2000 but not in SQL 2005 - looks like it's looking at the underlying table rather than the view. Sample code below to help you all out: -
Create Table
============
CREATE TABLE [dbo].[tblTest](
[X] [int] NOT NULL,
[nvarchar](50) NOT NULL
) ON [PRIMARY]
Insert Data
===========
INSERT INTO tblTest(X, Y) VALUES(1, '1')
INSERT INTO tblTest(X, Y) VALUES(1, '2')
INSERT INTO tblTest(X, Y) VALUES(2, 'foo')
INSERT INTO tblTest(X, Y) VALUES(2, 'bar')
Create View
===========
CREATE VIEW [dbo].[vwTest]
AS
SELECT X, Y
FROM dbo.tblTest
WHERE (ISNUMERIC(Y) = 1)
Finally
=======
SELECT X, Y
FROM dbo.vwTest
WHERE (CONVERT(int, Y) >= 0)
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'foo' to data type int.
Thanks in advance!
View 10 Replies
View Related
Aug 20, 2007
Does anyone else get the following result when running this query?
Select isnumeric('4D7')
-----------
1
(1 row(s) affected)
Does anyone know why this would return true for numeric?
Thanks,
Ray
View 4 Replies
View Related
Dec 3, 2007
Hi
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
thanks.
View 10 Replies
View Related
Mar 25, 2003
Hi - Please excuse me if this is really simple, but I'm fairly new to this lark.
My (made up) code is below... I'd be grateful for any pointers.
insert into [tblInvoices]
(full_period,
supplier_no,
account_code,
tran_amount,
function)
select
full_period,
supplier_no,
account_code,
tran_amount
case
when substring(account_code,1,2) = 'FY' then '-'
when isNumeric(account_code) then left(account_code, 2)
when not isNumeric(substring(account_code,1,2)) then left(account_code, 1)
else 'oops'
end as function,
from
tblLoadMSV900_i
end
Is this even close?
I'm using a stored proc to insert the data from tblLoadMSV900_i into tblInvoices and at the same time insert some data into the function field.
In plain english I want make sure that:
If the first 2 chars of account_code are 'FY' then function='-',
If the first char of account_code is numeric then function=left(account_code, 2),
If the the first char is not numeric (and if first two chars are not 'FY' i.e. first char could be 'F') then function=left(account_code, 1)
And there's plenty more where this came from! But if I can crack this with your help then I should have a better idea about the rest of the proc.
Thanks
Sara
View 2 Replies
View Related
Apr 14, 2008
There is a MSSQL function that check the value. Like ISNULL(), ISDATE() and ISNUMERIC(). I don't see a function that check for decimal. If there isn't any then is there an user-defined function for it? I need to be able to validate the string value for decimal before it get assigned to a decimal datatype or T-SQL will run into an error.
I'm using MS-SQL 2000...
Thanks...
View 3 Replies
View Related
Aug 27, 2015
I found this to work:
SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
[Code] ....
It Returns:
uri
evFieldUri
evFieldVal
(No column name)
224016 3267
+000089243829 89243829
224019 2717
+000089243825 89243825
224472 3333
+000000000000000000000017 17
225052 3267
+000089243829 89243829
225055 2717
+000089243825 89243825
So, then I went back to:
SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
[Code] ....
And it returns this error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
So, I tried again, and this worked…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT),
ISNUMERIC(evFieldVal)
FROM TSEXFIELDV WHERE URI
> 0 AND evFieldUri
IN ( SELECT URI
FROM TSEXFIELD WHERE exFieldFormat
IN (1,11))
I logged out and came back and tried again, and it still worked. So then I tried…
SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
WHERE URI
> 0
[Code] ...
And it fails.
View 5 Replies
View Related
Jan 18, 2008
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;
View 2 Replies
View Related
Jan 3, 2008
Hi,
I have another issue. I have an excel file that I pipe through a "data conversion" task. I have set all the column data types to strings, because there's no way to know beforehand if a particular column will be number or text because the file is very non-standard (it looks more like a formatted report).
After the data conversion, I send all the rows to a script task. In the script task, I do a check on the numeric fields.
for example:
If Not IsNumeric(Row.Price) Then
Row.Price_IsNull = True
End If
However, this check fails each and every time, even if the field contains a number! I don't have this problem when using flat file sources.
So, none of my numeric fields are getting loaded to my ole db destination.
Help, is there a way around this? Or am I forced to just skip this number check altogether? I'd prefer not to.
Thanks
View 10 Replies
View Related
Mar 30, 2004
Do I have other option beside using IF..ELSE IF? TIF
-- GET INFORMATION OF THE JOB
DECLARE @JOBIDAS Char(10)
DECLARE @VRUSERVICEHRSAS Decimal(18,2)
DECLARE @VRUSERVICEMINAS Decimal(18,2)
DECLARE @BILLEDFLATAS Decimal(18,2)
DECLARE @BILLREGRATEAS Decimal(18,2)
DECLARE @MIN_HRSAS Decimal(18,2)
DECLARE @COUNT_GREATER_MINTinyInt
DECLARE @COUNT_LESS_MINTinyInt
SET @VRUSERVICEMIN = 46
SET @BILLEDFLAT = 0
-- PROCESS ONLY RECORDS WHERE THERE IS NO FLAT FEE
IF @BILLEDFLAT = 0
BEGIN
IF @VRUSERVICEMIN BETWEEN 0 AND 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN = 15
BEGIN
SET @VRUSERVICEMIN = .25
END
ELSE IF @VRUSERVICEMIN BETWEEN 15 AND 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN = 30
BEGIN
SET @VRUSERVICEMIN = .5
END
ELSE IF @VRUSERVICEMIN BETWEEN 30 AND 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN = 45
BEGIN
SET @VRUSERVICEMIN = .75
END
ELSE IF @VRUSERVICEMIN > 45
BEGIN
SET @VRUSERVICEMIN = 1
END
END
PRINT @VRUSERVICEMIN
View 6 Replies
View Related
Apr 1, 2007
Is it possible to catch and error and then keep the process going in a stored procedure?
So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?
View 4 Replies
View Related
Oct 21, 2007
Hi! I have some try .. catch block trying to insert some data into database. During its action duplicate key row insert error could raise, for example. The question is how could I know distinguish it from other sql errors? Object ex (Catch ex As Exception) has only message property '{"Cannot insert duplicate key row in object 'dbo.Group_Courses' with unique index 'IX_Group_Courses'.The statement has been terminated."}' and type System.Data.SqlClient.SqlException. Knowing the type of error is not enough, because there are different SqlExceptions. Even the message is not unique for this error, because now i deal with 'dbo.Group_Courses' and then it could be other table. Is there something that unique identifies each error? For example error code. If it exists, where could I get it?
Thanks in advance!
View 2 Replies
View Related
Mar 13, 2004
I test my the now function and it is getting the right date. When I try to send that to the sql database I have it turns it into 1/1/1900. Does anyone know why this is happening, I have tried everything Here is my code:
sql = "Insert into tblguestbook(date, name, city, state, email, Url, Comments)Values ('"
sql = sql & Request.Form(Now) & "','"
sql = sql & Request.Form("nametxt") & "','"
sql = sql & Request.Form("citytxt") & "','"
sql = sql & Request.Form("statetxt") & "','"
sql = sql & Request.Form("emailtxt") & "','"
sql = sql & Request.Form("urltxt") & "','"
sql = sql & Request.Form("commentstxt") & "');"
View 1 Replies
View Related
Dec 31, 2004
Hi,
I have a table with one field set at nvarchar (4000)
This is sometimes not big enough and I get an error that the max row size has been reached.
How do people handle the error when the max row size is reached and gracefully inform the user?
Also, should I really be using Ntext instead, would this be better - is there a performance penalty?
Much obliged.
RG
View 1 Replies
View Related
Jan 13, 2006
Hi,
I would like to handle a sql error in t-sql and return a certain value in case error occurs. For example if I would like to add a record I want to return a certain identity value or maybe a status of transaction (0 for incomplete, 1 for succesfull trans).
If error occurs in sql I cannot return any values back to asp.net because of What I am doing at the moment is catching an error in asp.net and then displaying an error message. Is there a way to return only a return value to asp.net and somehow handle the error in t-sql?
Thanks
View 1 Replies
View Related
Feb 28, 2001
I don't know what's wrong with SQL2000 setup, the problem is:
whenever I execute a query with a date/time such as 02/02/200,
SQL give me an error message saying that the date is "Out of Range".
Any ideas, thanks in advance.
View 4 Replies
View Related
May 20, 2002
I'm having trouble with something and I was hoping that you could point me in the right direction.
Here's the scenario:
I have a VB application that clients use to add records to a SQK 2K DB. The info they have added that day is shown in a grid. They have the ability to edit items in the grid, and then update those changes to the database. The problem is that sometimes they change the values to something they shouldn't. To combat this I've started experimenting with check constraints. In query analyzer I test the constraint by trying to update an entry to an 'illegal' value. When I do this, I get an error saying: "Server: Msg 547, Level 16, State 1, Line 1" and the change is not made. What I'd like to do is to give the user a dialog box notifying him of the error. Is there a way to have a sub-routine or stored procedure be triggered when a message of this type is generated?
My specs are: W2K pro clients, SQL2K on an NT4 sp6a server. Application is written in VB6.
Any help is greatly appreciated.
thanks,
-scott
View 1 Replies
View Related
May 15, 2001
I have a select statement that works, but I know there has to be a better
way( I apologize for being TSQL brain dead today). Here is the statement
SELECT PATIENT_ACPT_STATUS, DISP_NOTES, RECORD_ID, modified_by, last_name, ddate
FROM PATIENT_MEDICATION_DISPERSAL_
Where (ddate = convert(char(10),getdate(),101) and
(MODIFIED_BY = CURRENT_USER) and
rec_status = 1) or
(ddate = convert(char(10),getdate(),101) and
(PATIENT_ACPT_STATUS = 1)) OR
(ddate = convert(char(10),getdate(),101) and
(MODIFIED_BY = 'open'))
View 1 Replies
View Related
