Ошибка при преобразовании типа данных nvarchar к int - Решение и исправление самых разных ошибок на TopOshibok.ru

DECLARE @Count INTEGER
DECLARE @nSQL NVARCHAR(1000)
SET @nSQL = 'SELECT @Count = COUNT(*) FROM ' + @tablename
EXECUTE sp_executesql @nSQL, N'@Count INTEGER OUT', @Count OUT

-- Now check @Count

Be extra careful with dynamic sql like this, as you open yourself up to sql injection. So make sure @tablename is sanitized.

One check to be safe would be something like this, by making sure the table exists using a parameterised query before attempting the dynamic query:

DECLARE @Count INTEGER
DECLARE @nSQL NVARCHAR(1000)
SET @nSQL = 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TableName) 
    SELECT @Count = COUNT(*) FROM ' + @tablename + '
ELSE
    SELECT @Count = -1'

EXECUTE sp_executesql @nSQL, N'@TableName NVARCHAR(128), @Count INTEGER OUT', @TableName, @Count OUT

If @Count then comes out at -1, you know it’s because the tablename is invalid

Edit:
Reference to sp_executesql is here

Источник

I’m inserting data in two tables at once…

CREATE PROCEDURE inserttwo
(
     @BookID int,
     @BookName nvarchar(50),
     @DateIssue datetime,
     @ReturnDate datetime,
     @PersonID int
)
AS
    INSERT INTO tblReturn(BookID, BookName, DateIssue, ReturnDate, PersonID)
    VALUES(@BookID, @BookName, @DateIssue, @ReturnDate, @PersonID)

    INSERT INTO tblIssue(BookID, BookName, DateIssue, ReturnDate, PersonID)
    VALUES(@BookID, @BookName, @DateIssue, @ReturnDate, @PersonID)

then I’m updating and deleting these tables by stored procedure…

Delete query:

ALTER PROCEDURE [dbo].[Issuedelete]
(@BookID int)
AS
    DELETE FROM tblIssue
    WHERE BookID = @BookID

Update query:

ALTER PROCEDURE [dbo].[IssueUpdate]
   (@BookID int,
    @BookName nvarchar(50),
    @DateIssue datetime,
    @ReturnDate datetime,
    @PersonID int)
AS
    UPDATE tblIssue
    SET [BookID]     = @BookID , 
        [BookName]   = @BookName,
        [DateIssue]  = @DateIssue,
        [ReturnDate] = @ReturnDate,
        [PersonID]   = @PersonID
    WHERE BookID = @BookID

C# code to delete from tblIssue:

private void btnDelete_Click(object sender, EventArgs e)
{
    try
    {
        string c = ConfigurationManager.ConnectionStrings["LMS"].ConnectionString;

        SqlConnection con = new SqlConnection(c);
        con.Open();

        SqlCommand cmd = new SqlCommand("Issuedelete", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BookID", ComBox1BookID.Text);

        cmd.ExecuteNonQuery();
        con.Close();
        storedproc();
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

C# code to update tblIssue:

private void btnupdate_Click(object sender, EventArgs e)
{
    try
    {
        string c = ConfigurationManager.ConnectionStrings["LMS"].ConnectionString;

        SqlConnection con = new SqlConnection(c);
        con.Open();

        SqlCommand cmd = new SqlCommand("IssueUpdate", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BookID", ComBox1BookID.Text);
        cmd.Parameters.AddWithValue("@BookName", ComBox2BName.Text);
        cmd.Parameters.AddWithValue("@DateIssue", IssueDate.Value.ToString());
        cmd.Parameters.AddWithValue("@ReturnDate", ReturnDate.Value.ToString());
        cmd.Parameters.AddWithValue("@PersonID", CBox3PerID.Text);

        cmd.ExecuteNonQuery();

        con.Close();
        storedproc();
    }
    catch (Exception ex)
    {
        Console.WriteLine("SqlError" + ex);
    }
}

After compiling, I get an error

Can’t convert datatype into int

I tried other ways by changing the parameters with OleDB and SqlDbType… But it’s not deleting and updating records… And also having same problem when I’m updating and deleting into tblReturn… Please, help me???

Источник

See more:

The error occurs when executing my queries

error like(

Conversion failed when converting the nvarchar value to data type int.

Can anyone help me

Comments

Solution 1

Acting entirely blindfolded, your error message is telling you that you are trying to use a character string as if it was an integer. It may appear to the naked eye like you have only integers in that column, but nvarchars can hold spaces, carriage returns, tabs, etc. all of which will cause this error. Seeing the table structure and your query would help to pinpoint the issue.

Good Luck

Solution 2

Hi try this,

CAST(@your_param AS nvarchar(15))

Solution 4

Hi,

Check out whether your column is numeric data.

select ISNUMERIC(ColumnName) from TableName

If it is a non numeric value take action based on your value

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrs	This month

CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900

Источник

Remove From My Forums

Question
Hello guys,

I have a problem.

How to solve this conversion error running the query?

Conversion failed when converting the nvarchar value ‘xx.xxx’ to data type int.

Many Thanks

Answers

Hi guys,

I solved the problem by converting the nvarchar in float by using CAST function.
- Marked as answer by
  fasttrack
  Thursday, April 21, 2011 7:51 PM

All replies

Can you post the SQL? Based on what you have given me I would bet that you are trying to put a decimal number into an integer (XX.XXX) which does not work. integer will only take whole numbers. Try using the data type decimal or money whichever
is more correct.
Hello,

You try to convert a non-numeric nvarchar value to int. You could check the values with
IsNumeric() if it’s numerical, but even this wouldn’t prevent error for all cases e.g. if the numeric value of the varchar is to high for integer.

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog
Xing
You cannot convert a nvarchar to an int. You need to check if the value is numeric before converting it to int. Something like
```
DECLARE @tbl TABLE(id int, val nvarchar(10))

INSERT INTO @tbl
SELECT 1,'ID1' UNION 
SELECT 2, '2099'

SELECT ID, CONVERT(INT, CASE WHEN ISNUMERIC(VAL) = 1 THEN VAL ELSE 0 END)VAL
FROM @tbl
```
Regards, Deven —————————————— Please vote if you find any of my post helpful.
- Proposed as answer by
  Kent Waldrop
  Tuesday, April 19, 2011 3:00 PM
A same error post has been answered
here

Please look into it…might solve your error as well.

-Vinay Pugalia
If a post answers your question, please click «Mark As Answer» on that post and
«Mark as Helpful».
Web : Inkey Solutions

Blog : My Blog
Email : Vinay Pugalia
The query is very easy: retrive the value from the field (Fld1) and then divided by 3600

Fld1 nvarchar(4000)

SELECT

SUM((Fld1)/3600)
AS SumFld1

FROM table1

———-

The suggestion

SELECT ID, CONVERT(INT,
CASE WHEN
ISNUMERIC(VAL) = 1 THEN VAL
ELSE 0 END)VAL
does not work, it produces the same error.

———-

Any help will be very appreciated
You cannot convert a nvarchar to an int. You need to check if the value is numeric before converting it to int. Something like
```
DECLARE @tbl TABLE(id int, val nvarchar(10))



INSERT INTO @tbl

SELECT 1,'ID1' UNION 

SELECT 2, '2099'



SELECT ID, CONVERT(INT, CASE WHEN ISNUMERIC(VAL) = 1 THEN VAL ELSE 0 END)VAL

FROM @tbl
```
Regards, Deven —————————————— Please vote if you find any of my post helpful.
It might be better to leave out the ELSE 0 for this and just let the invalid values return as null; obviously, no requirements were given so I really cannot say. Please give a look at these guidelines for posting questions in the transact SQL forum:

MSDN Transact SQL Forum Posting Guidelines:
The query is very easy: retrive the value from the field (Fld1) and then divided by 3600

Fld1 nvarchar(4000)

SELECT

SUM((Fld1)/3600)
AS SumFld1

FROM table1

———-

The suggestion

SELECT ID, CONVERT(INT,
CASE WHEN
ISNUMERIC(VAL) = 1 THEN VAL
ELSE 0 END)VAL
does not work, it produces the same error.

———-

Any help will be very appreciated

There is a good chance that the isNumeric function isn’t going to cut the mustard. You might want to give a look at this article about the problems with the isNumeric function:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

There are other articles that might be more appropriate for this issue.

Somebody help please? Other references for the issues with «isNumeric»?
Hi guys,

I solved the problem by converting the nvarchar in float by using CAST function.
- Marked as answer by
  fasttrack
  Thursday, April 21, 2011 7:51 PM
Answer:
SELECT REPLACE(REPLACE(REPLACE(CAST(@YourVariable AS VARCHAR(MAX)), CHAR(9), »), CHAR(10), »), CHAR(13), »)

Select all invisible characters:

DECLARE @nstring NVARCHAR(100)

SET @nstring =(select var from table (nolock) )

DECLARE @position INT

SET @position = 1

DECLARE @CharList TABLE (

Position INT,

UnicodeChar NVARCHAR(1),

UnicodeValue INT

)

WHILE @position <= DATALENGTH(@nstring)

BEGIN

INSERT @CharList

SELECT @position as Position

,CONVERT(nchar(1),SUBSTRING(@nstring, @position, 1)) as UnicodeChar

,UNICODE(SUBSTRING(@nstring, @position, 1)) as UnicodeValue

SET @position = @position + 1

END

Источник

In this guide, we will walk you through the process of troubleshooting and fixing the Error converting data type NVARCHAR to Numeric issue. This error usually occurs when you attempt to convert an NVARCHAR column to a numeric data type in SQL Server. The error occurs when the NVARCHAR column contains a value that cannot be converted to a numeric data type.

Understanding the Error
Common Causes
Step-by-Step Solution
FAQ
Related Links

Understanding the Error

The Error converting data type NVARCHAR to Numeric issue occurs when SQL Server tries to convert a non-numeric value in an NVARCHAR column to a numeric data type. For instance, when you attempt to update or insert data into a table that contains an NVARCHAR column with a non-numeric value, and a constraint or trigger exists that requires the column to have a numeric value, the error occurs.

SQL Server supports various data types for storing different types of data. NVARCHAR is a variable-length Unicode character data type, while numeric is a data type that can store exact numeric values with a fixed number of decimal places.

Common Causes

Non-numeric characters in the NVARCHAR column
Incorrect data type conversion function
Implicit data type conversion

Step-by-Step Solution

Step 1: Identify the Non-Numeric Values

The first step in fixing the error is identifying the non-numeric values in the NVARCHAR column. You can use the ISNUMERIC function to filter out non-numeric values in the column. Here’s an example query to find non-numeric values in the YourColumnName column of the YourTableName table:

SELECT *
FROM YourTableName
WHERE ISNUMERIC(YourColumnName) = 0

Step 2: Update or Remove Non-Numeric Values

Once you have identified the non-numeric values, you can either update them with numeric values or remove them altogether. To update a specific row, you can use the following query:

UPDATE YourTableName
SET YourColumnName = 'YourNewNumericValue'
WHERE YourPrimaryKeyColumn = 'PrimaryKeyValue'

To remove a specific row, you can use the following query:

DELETE FROM YourTableName
WHERE YourPrimaryKeyColumn = 'PrimaryKeyValue'

Step 3: Use the Correct Data Type Conversion Function

When converting an NVARCHAR column to a numeric data type, make sure you are using the correct data type conversion function. For instance, use the CAST or CONVERT function to explicitly convert the column to a numeric data type:

SELECT CAST(YourColumnName AS NUMERIC(18, 2))
FROM YourTableName

SELECT CONVERT(NUMERIC(18, 2), YourColumnName)
FROM YourTableName

FAQ

What is the difference between NVARCHAR and VARCHAR data types?

NVARCHAR is a Unicode variable-length character data type that can store both Unicode and non-Unicode characters.
VARCHAR is a non-Unicode variable-length character data type that can store only non-Unicode characters.

How can I avoid implicit data type conversion?

To avoid implicit data type conversion, always use the correct data type for your columns when creating or altering tables. Also, use the CAST or CONVERT functions when you need to explicitly convert one data type to another.

What is the difference between CAST and CONVERT functions?

Both CAST and CONVERT functions are used to convert one data type to another. The main difference between the two is the syntax. The CAST function uses the ANSI SQL-92 syntax, while the CONVERT function uses the SQL Server-specific syntax.

Can I convert an NVARCHAR column to an INT data type?

Yes, you can convert an NVARCHAR column to an INT data type using the CAST or CONVERT functions. However, before converting, make sure that the NVARCHAR column contains only integer values, otherwise, an error will occur.

Can I use the TRY_CONVERT function to avoid the error?

Yes, you can use the TRY_CONVERT function to avoid the error. The TRY_CONVERT function returns a NULL value if the conversion fails, instead of raising an error. Here’s an example:

SELECT TRY_CONVERT(NUMERIC(18, 2), YourColumnName)
FROM YourTableName

Data Types (Transact-SQL)
CAST and CONVERT (Transact-SQL)
ISNUMERIC (Transact-SQL)
TRY_CONVERT (Transact-SQL)

Источник

Solution 1

Solution 2

Solution 4

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="RRS feed" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001" />

Question

Answers

All replies

Table of Contents

Understanding the Error

Common Causes

Step-by-Step Solution

Step 1: Identify the Non-Numeric Values

Step 2: Update or Remove Non-Numeric Values

Step 3: Use the Correct Data Type Conversion Function

FAQ

What is the difference between NVARCHAR and VARCHAR data types?

How can I avoid implicit data type conversion?

What is the difference between CAST and CONVERT functions?

Can I convert an NVARCHAR column to an INT data type?

Can I use the TRY_CONVERT function to avoid the error?