Showing posts with label nvarchar. Show all posts

Monday, March 26, 2012

error please help

any Idea why i am getting this error?

Server Error in '/endorse' Application.

Incorrect syntax near 'nvarchar'.

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: Incorrect syntax near 'nvarchar'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +890426 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +767130 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404 System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447 System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72 System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +390 System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +623 System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +109 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5104

Version Information: Microsoft .NET Framework Version:2.0.50727.1318; ASP.NET Version:2.0.50727.1318

My code is like........

<%@.PageLanguage="VB" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:LoginViewID="LoginView1"runat="server">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

DataKeyNames="id"DataSourceID="SqlDataSource1"Height="50px"Width="125px">

<asp:BoundFieldDataField="RoomNo"HeaderText="RoomNo"SortExpression="RoomNo"/>

<asp:BoundFieldDataField="Name MR"HeaderText="Name MR"SortExpression="Name MR"/>

<asp:BoundFieldDataField="Att Resi"HeaderText="Att Resi"SortExpression="Att Resi"/>

<asp:BoundFieldDataField="Age Sex"HeaderText="Age Sex"SortExpression="Age Sex"/>

<asp:BoundFieldDataField="Adm Date"HeaderText="Adm Date"SortExpression="Adm Date"/>

<asp:BoundFieldDataField="Diagnosis"HeaderText="Diagnosis"SortExpression="Diagnosis"/>

<asp:BoundFieldDataField="Diet IVF"HeaderText="Diet IVF"SortExpression="Diet IVF"/>

<asp:BoundFieldDataField="Medications"HeaderText="Medications"SortExpression="Medications"/>

<asp:BoundFieldDataField="Consults"HeaderText="Consults"SortExpression="Consults"/>

<asp:BoundFieldDataField="Followup"HeaderText="Followup"SortExpression="Followup"/>

<asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"ShowInsertButton="True"/>

</Fields>

</asp:DetailsView>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"

ConnectionString="<%$ ConnectionStrings:endorseConnectionString1 %>"DeleteCommand="DELETE FROM [endorse] WHERE [id] = @.original_id AND [RoomNo] = @.original_RoomNo AND [Name MR] = @.original_Name_MR AND [Att Resi] = @.original_Att_Resi AND [Age Sex] = @.original_Age_Sex AND [Adm Date] = @.original_Adm_Date AND [Diagnosis] = @.original_Diagnosis AND [Diet IVF] = @.original_Diet_IVF AND [Medications] = @.original_Medications AND [Consults] = @.original_Consults AND [Followup] = @.original_Followup"

InsertCommand="INSERT INTO [endorse] ([RoomNo], [Name MR], [Att Resi], [Age Sex], [Adm Date], [Diagnosis], [Diet IVF], [Medications], [Consults], [Followup]) VALUES (@.RoomNo, @.Name_MR, @.Att_Resi, @.Age_Sex, @.Adm_Date, @.Diagnosis, @.Diet_IVF, @.Medications, @.Consults, @.Followup)"

OldValuesParameterFormatString="original_{0}"SelectCommand="SELECT * FROM [endorse] WHERE ([id] = @.id)"

UpdateCommand="UPDATE [endorse] SET [RoomNo] = @.RoomNo, [Name MR] = @.Name_MR, [Att Resi] = @.Att_Resi, [Age Sex] = @.Age_Sex, [Adm Date] = @.Adm_Date, [Diagnosis] = @.Diagnosis, [Diet IVF] = @.Diet_IVF, [Medications] = @.Medications, [Consults] = @.Consults, [Followup] = @.Followup WHERE [id] = @.original_id AND [RoomNo] = @.original_RoomNo AND [Name MR] = @.original_Name_MR AND [Att Resi] = @.original_Att_Resi AND [Age Sex] = @.original_Age_Sex AND [Adm Date] = @.original_Adm_Date AND [Diagnosis] = @.original_Diagnosis AND [Diet IVF] = @.original_Diet_IVF AND [Medications] = @.original_Medications AND [Consults] = @.original_Consults AND [Followup] = @.original_Followup">

<asp:QueryStringParameterDefaultValue="0"Name="id"QueryStringField="id"Type="Int32"/>

</SelectParameters>

<asp:ParameterName="original_id"Type="Int32"/>

<asp:ParameterName="original_RoomNo"Type="Int32"/>

<asp:ParameterName="original_Name_MR"Type="String"/>

<asp:ParameterName="original_Att_Resi"Type="String"/>

<asp:ParameterName="original_Age_Sex"Type="String"/>

<asp:ParameterName="original_Adm_Date"Type="DateTime"/>

<asp:ParameterName="original_Diagnosis"Type="String"/>

<asp:ParameterName="original_Diet_IVF"Type="String"/>

<asp:ParameterName="original_Medications"Type="String"/>

<asp:ParameterName="original_Consults"Type="String"/>

<asp:ParameterName="original_Followup"Type="String"/>

</DeleteParameters>

<asp:ParameterName="RoomNo"Type="Int32"/>

<asp:ParameterName="Name_MR"Type="String"/>

<asp:ParameterName="Att_Resi"Type="String"/>

<asp:ParameterName="Age_Sex"Type="String"/>

<asp:ParameterName="Adm_Date"Type="DateTime"/>

<asp:ParameterName="Diagnosis"Type="String"/>

<asp:ParameterName="Diet_IVF"Type="String"/>

<asp:ParameterName="Medications"Type="String"/>

<asp:ParameterName="Consults"Type="String"/>

<asp:ParameterName="Followup"Type="String"/>

<asp:ParameterName="original_id"Type="Int32"/>

<asp:ParameterName="original_RoomNo"Type="Int32"/>

<asp:ParameterName="original_Name_MR"Type="String"/>

<asp:ParameterName="original_Att_Resi"Type="String"/>

<asp:ParameterName="original_Age_Sex"Type="String"/>

<asp:ParameterName="original_Adm_Date"Type="DateTime"/>

<asp:ParameterName="original_Diagnosis"Type="String"/>

<asp:ParameterName="original_Diet_IVF"Type="String"/>

<asp:ParameterName="original_Medications"Type="String"/>

<asp:ParameterName="original_Consults"Type="String"/>

<asp:ParameterName="original_Followup"Type="String"/>

</UpdateParameters>

<asp:ParameterName="RoomNo"Type="Int32"/>

<asp:ParameterName="Name_MR"Type="String"/>

<asp:ParameterName="Att_Resi"Type="String"/>

<asp:ParameterName="Age_Sex"Type="String"/>

<asp:ParameterName="Adm_Date"Type="DateTime"/>

<asp:ParameterName="Diagnosis"Type="String"/>

<asp:ParameterName="Diet_IVF"Type="String"/>

<asp:ParameterName="Medications"Type="String"/>

<asp:ParameterName="Consults"Type="String"/>

<asp:ParameterName="Followup"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

</LoggedInTemplate>

you are not logged in.

</AnonymousTemplate>

</asp:LoginView>

</div>

</form>

</body>

</html>

Here is a wild shot in the dark...

I notice there is a field called Age_Sex. Is it possible the data being inserted has a comma between the two data items?

Even if it isn't causing the problem, having two data items in one field certainly will raise some eyebrows, if not some havoc.

|||

Thanks for your reply steve,

I tried to remove the comma from the data fields and still the same error. I don't want to many columns in the table thats why i am combining the fields, I know database design is not efficient in that way but it is surving the purpose for me.

If you could help further I will appriciate it.

Thanks again.

Friday, March 23, 2012

Error on SQL .mdf update and edit - nText?

Hello anyone know how to fix this error?

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

Exception Details:System.Data.SqlClient.SqlException: The data types ntext and nvarchar are incompatible in the equal to operator.

Source Error:

Thanks , John

An nText field can not be used in optimisitic updates. Your update statement that was generated looks something like:

UPDATE SomeTable
SET {Stuff}
WHERE nTextField=old_nTextField

And old_nTextField is defined as a nvarchar. You can't really define it as nText as nText is just a very long nvarchar field, and even if you COULD get it defined as a nText, it would still be invalid, because you can't use nText= anything, even another nText in a WHERE clause.

|||What should it be? I am now using a text , but it still does not work.|||

TEXT/NTEXT/IMAGE data in SQL Server are considered as BLOB (Binary Large Object) data and can not be used with "=" or in sorting. You may need "Text and Image Functions" to manipulate these kinds of data:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_0gs3.asp

Or you can convert the TEXT data (less than 8000 chars) into VARCHAR data to use it in comparation, for example:

select * from test_txt where convert(varchar(8000),t)='The index position of the value parameter if that string is found, or -1 if it is not. If value is Empty, the return value is 0.'

Or use LIKE operator:

select * from test_txt where t like 'The index position of the value parameter if that string is found, or -1 if it is not. If value is Empty, the return value is 0.'

Sunday, February 19, 2012

Error Message conversion failed when converting the nvarchar value to data type int?

Hi,

I can't seem to fix the following error in my stored procedure.

Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.

The line of code in my stored procedure that seems to be the problem is the following:

CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END

Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.

hi,

Aston35 wrote:

Hi,
I can't seem to fix the following error in my stored procedure.
Error Message: Conversion failed when converting the nvarchar value '1007-001' to data type int.
The line of code in my stored procedure that seems to be the problem is the following:
CASE WHEN [Order Details].[Job No] IS NULL THEN [Orders].[Order No] ELSE [Order Details].[Job No] END
Order No has a data type of INT and Job No has a data type NVARCHAR(8). In the above case statement i'm not trying to convert anything but just display a column depending on the out come of the case statement. If anyone knows how to get around this error you help would be very welcome.

as you pointed out, the 2 columns are of a different data type, so you have to deal accordingly.. as you can not convert 'abc' to an integer data type, you have to perform the opposite, convert the int to a varchar...

SET NOCOUNT ON;

DECLARE @.b int;

SET @.b = 0;

PRINT 'works';

SELECT CASE WHEN @.b = 0 THEN 1 ELSE 'a' END AS [Result];

SET @.b = 1;

PRINT 'does not work';

SELECT CASE WHEN @.b = 0 THEN 1 ELSE 'a' END AS [Result];

DECLARE @.b int;

SET @.b = 1;

PRINT 'works';

SELECT CASE WHEN @.b = 0 THEN CONVERT(varchar, 1) ELSE 'a' END AS [Result];

--<

works

Result

does not work

Result

Msg 245, Level 16, State 1, Line 9

Conversion failed when converting the varchar value 'a' to data type int.

works

Result

a
regards|||Thanks Andrea, will give your suggestion ago and see how i get on.

Friday, February 17, 2012

Error Message 443

Hi,

I'm trying to create the following function:

CREATE FUNCTION dbo.get_id(@.name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @.id INT
SET @.id = (SELECT id FROM dbo.language WHERE name = @.name)
IF @.@.ROWCOUNT = 0
RAISERROR('Specified item does not exist', 16, 1)

RETURN @.id
END
GO

And I get the following error message:
Invalid use of side-effecting or time-dependent operator in 'RAISERROR' within a function.

I can't find any references to the use of RAISERROR within functions or stored procedures, so I have no idea why this is not working.
What I'm trying to do is check for the existence of the row inside the function and not outside, on all the places I need to call this function. I need to have an exception in my code if this happens, that's why I thought RAISERROR is what I needed...

Any help would be appreciated!

Thanks,
Florin

You cannot use RAISERROR from within TSQL UDFs. You need to put this logic in a stored procedure or use a SQLCLR UDF. Please take a look at http://msdn2.microsoft.com/en-us/library/ms191320(en-US,SQL.90).aspx topic also.|||

Thanks, I eventually figured out that I can't use RAISERROR within a function, so I changed it to a stored procedure. My problem now is that RAISERROR doesn't stop the execution of my stored procedure, but continues with the next statement. Is there a way to achieve the same kind of functionality as with primary key or constraint violations? I'm testing some conditions inside my stored procedure and I want to stop the execution under certain circumstances and get an exception in my .NET calling code. Is that at all possible with RAISERROR?
I noticed that when an error is produced by the SQL Server engine, besides the red error message (which I can also create using RAISERROR) there is another message saying "The statement has been terminated". How can I achieve the same? :-)

Thanks, Florin

|||While you can't raise an error in a udf. . .

this function:

CREATE FUNCTION dbo.get_id(@.name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @.id INT
SELECT @.id = id FROM dbo.language WHERE name = @.name
RETURN @.id
END
GO

will return a null if name is not found.

Now, I contend, trying to select an id for a name that doesn't exist shouldn't be an error. . . It should return a null. Trying to use the null in a place that requires non-null will raise the error. . . and that might make perfect logical sense.

Just a thought.

On the other hand, if you want to use a procedure, look up @.@.ERROR in the help file.|||

Well, in the application logic, the foreign key can be null, which means that record has no references, but in this particular case I need to throw an error because the calling client has given a foreign key value that does not exist. Null is a valid case, non-existing referenced record is not. That's why I need the error. I want to do as much as I can on the database in one stored procedure call, to avoid multiple database calls/roundtrips...

Florin

|||

huh? I think you misunderstood. . .

run this script. . .

-- BEGIN SCRIPT =========================

create table Song(ID int not null identity(1,1) primary key, name varchar(255))

create table Band(ID int not null identity(1,1) primary key, name varchar(255))

create table Record(BandID int not null references Band(ID), SongID int not null references Song(ID) )

CREATE FUNCTION dbo.getSongID(@.name NVARCHAR(50)) RETURNS INT AS

BEGIN

DECLARE @.id INT

SELECT @.id = id FROM dbo.Song WHERE name = @.name

RETURN @.id

END

CREATE FUNCTION dbo.getBandID(@.name NVARCHAR(50)) RETURNS INT AS

BEGIN

DECLARE @.id INT

SELECT @.id = id FROM dbo.Band WHERE name = @.name

RETURN @.id

END

Insert into Song(Name) values('Love Will Tear Us Apart')

Insert into Band(Name) values('Joy Division')

-- This works

Insert into Record values(dbo.getBandID('Joy Division'), dbo.getSongID('Love Will Tear Us Apart'))

-- This doesn't and the error is on the database

Insert into Record values(dbo.getBandID('Captain and Tennille'), dbo.getSongID('Love Will Tear Us Apart'))

-- This doesn't and the error is on the database

Insert into Record values(dbo.getBandID('Joy Division'), dbo.getSongID('Love Will Keep Us Together'))

-- END SCRIPT =========================

Monday, March 26, 2012

error please help

Server Error in '/endorse' Application.

Incorrect syntax near 'nvarchar'.

Friday, March 23, 2012

Error on SQL .mdf update and edit - nText?

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

Sunday, February 19, 2012

Error Message conversion failed when converting the nvarchar value to data type int?

Friday, February 17, 2012

Error Message 443

Sql Error message

Blog Archive

About Me