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

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have a query below , in that with out count function .i got answer,if using count function ,i got this error ---->Conversion failed when converting the varchar value '')' to data type int.

Please crack me out..

SQL

select 'INSERT INTO CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount) 
      values ( '''+'Library'+''','''+isnull('CheckOut -'+cio_branchname,'')+''',
      '''+ISNULL(cio_membername,'')+''','''+ISNULL(mstmember.m_email1,'')+''',
      '''+ISNULL(mstmember.m_mobile,'')+''','''+count(trncheckinout.cio_bookname)+''')' 

     AS MAILQRY
     from 
    trncheckinout inner join
    mstmember on cio_mrecid=m_recid where CIO_CheckOutDt = 20141208
    group by m_email1,m_mobile, cio_branchname,cio_membername

Posted 8-Dec-14 20:09pm

Bns Vigneshwaran

Updated 8-Dec-14 21:59pm

Tomas Takac

v2

Add a Solution

Comments

syed shanu 9-Dec-14 2:15am

try this if this not works paste your insert and select query create and insert script so that other can find for the solution.

insert into CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount)
values
Select 'Library'
,'CheckOut -'+cio_branchname
,cio_membername
,mstmember.m_email1
,mstmember.m_mobile
,count(trncheckinout.cio_bookname)
from
trncheckinout inner join
mstmember on cio_mrecid=m_recid where CIO_CheckOutDt = 20141208
group by m_email1,m_mobile, cio_branchname,cio_membername

Bns Vigneshwaran 9-Dec-14 2:23am

I want o/p like this sir, please see Below .. In that at last you see 'THE SURVIVORS CLUB' <-- its a book, i want count of that .. so only i structured query like that,so i used count function there..Thank you

INSERT INTO CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount)
values ( 'Library','CheckOut -seevaram ',
'D253,RAMYA DEVI BRAMYA DEVI B','rami.rogfed@gmail.com',
'+919445354610','THE SURVIVORS CLUB')

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 9-Dec-14 3:30am

You should use Parameterized query.

2 solutions

Solution 1

exec the below query.

SQL

insert into CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount)
Select 'Library'
,'CheckOut -'+cio_branchname
,cio_membername
,mstmember.m_email1
,mstmember.m_mobile
,count(trncheckinout.cio_bookname)
from
trncheckinout inner join
mstmember on cio_mrecid=m_recid where CIO_CheckOutDt = 20141208
group by m_email1,m_mobile, cio_branchname,cio_membername

Posted 8-Dec-14 22:07pm

mudgilsks

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tomas Takac · Accepted Answer · 2014-12-08T22:10:00

The problem is string comcatenation is using the same operator as addition: +. If one of the operands is a number SQL server assumes it's addition and tries to convert the rest to numbers too. So what you need is to convert result of count to varchar:

SQL

select 'INSERT INTO CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount) 
      values ( ''Library'','''+isnull('CheckOut -'+cio_branchname,'')+''',
      '''+ISNULL(cio_membername,'')+''','''+ISNULL(mstmember.m_email1,'')+''',
      '''+ISNULL(mstmember.m_mobile,'')+''','''+cast(count(trncheckinout.cio_bookname) as varchar(100))+''')' 
 
     AS MAILQRY
     from 
    trncheckinout inner join
    mstmember on cio_mrecid=m_recid where CIO_CheckOutDt = 20141208
    group by m_email1,m_mobile, cio_branchname,cio_membername

I took the liberty to remove the concatenation around the first operand 'Library'.