question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked Viorel-1 edited

Conversion failed when converting the varchar value 'BB-2' to data type int.

I am inserting BB-1 ,then again ,BB-2 is generating , it is giving error.

Conversion failed when converting the varchar value 'BB-2' to data type int.

 CREATE TABLE #tbl_Sell_M(Bill_No varchar(10),E_Date date)
        
    
    
     
 WITH cte AS (SELECT 1 AS Bill_No
                           UNION ALL
                           SELECT TOP (1) Bill_No + 1 AS Bill_No
                           FROM     dbo.tbl_Sell_M AS t
                           ORDER BY Bill_No DESC)
     SELECT TOP (1)  CONCAT('BB-', Bill_No)  AS Bill_No
     FROM     cte
     ORDER BY Bill_No DESC



sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Is this the full query that gives error? Show how do you insert.

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered Viorel-1 edited

If tbl_Sell_M is empty then it works. But if it contains something like ‘BB-1’ or ‘BB-2’, then it is not possible to calculate Bill_No + 1.

Consider using integers (int column) without ‘BB-‘ (which can be prepended later), or try this workaround, which is probably less recommended:

 WITH cte AS (SELECT 1 AS Bill_No
                            UNION ALL
                            SELECT TOP (1) stuff(Bill_No, 1, 3, '') + 1 AS Bill_No
                            FROM     tbl_Sell_M AS t
                            ORDER BY Bill_No DESC)
      SELECT TOP (1)  CONCAT('BB-', Bill_No)  AS Bill_No
      FROM     cte
      ORDER BY Bill_No DESC



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered akhterhussain-3167 commented

Please post table design of the table as DDL, some sample data as DML and the expected result.
.
By the error message you try to mix varchar & int values; that don't work in SQL, you have to convert the values to same data type.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

here is table

 CREATE TABLE #tbl_Sell_M(Bill_No varchar(10),E_Date date)


0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

CREATE TABLE #tbl_Sell_M( Bill_No varchar(10) ,E_Date date)

As expected, Bill_No is of type varchar, you can not "add" numeric 1 on the values. How does the existing data (=samples, as requested) look like?


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.