connection string in SQL Server

Hi Guys,

I have this query in my SQL Server:

 
set @connectionstring1  = '
declare @txtOutput varchar(50);
EXEC OPENDATASOURCE(''SQLOLEDB'', ''Data Source=' + @servername 
+ ';User ID=' + @userr +  ';Password=' + @paswerd + ''').[' + @dbname +  '].[dbo].[sp_web_updcustNew]  '''+ @txtMobileNoOld + ''', '''+ @txtMobileNoNew + ''', '''+ @txtPSTNOld + ''', '''+ @txtPSTNNew + ''', '''+ @txtFirstName + ''', '''+ @txtLastName + ''', '''+@dtBirthDate+''', '''+ @txtJenisKelamin + ''', '''+ @txtAlamat + ''', '''+@txtPropinsiID  + '', @txtKabKotaID , '''+ @txtEmailOld + ''', '''+ @txtEmailNew + ''', '''+ @txtWebSiteID + ''', '''+ @txtNoKTP + ''', '''+ @txtKontakID+ ''', '''+ @txtWebUID + ''', '''+ @txtPasswordNew + ''', @txtOutput output;insert into #myTempTable values(@txtOutput)'
exec (@connectionstring1);

Open in new window


The problem is, it's giving error because @txtMobileNoOld and @txtMobileNoNew is INT(integer), but that my code will send as '02143254435', it supposed to be 02143254435 (without single quote).

Please help changing my code, so the @txtMobileNoOld and @txtMobileNoNew  will be sent without single quote, and will still working properly.

Thanks.
softblessAsked:
Who is Participating?
 
Ephraim WangoyaCommented:

set @connectionstring1  = 
'declare @txtOutput varchar(50);
EXEC OPENDATASOURCE(''SQLOLEDB'', ''Data Source=' + @servername 
+ ';User ID=' + @userr +  ';Password=' + @paswerd + ''').[' + @dbname +  '].[dbo].[sp_web_updcustNew]  '+ @txtMobileNoOld + ', '+ @txtMobileNoNew + ', '''+ @txtPSTNOld + ''', '''+ @txtPSTNNew + ''', '''+ @txtFirstName + ''', '''+ @txtLastName + ''', '''+@dtBirthDate+''', '''+ @txtJenisKelamin + ''', '''+ @txtAlamat + ''', '''+@txtPropinsiID  + '', @txtKabKotaID , '''+ @txtEmailOld + ''', '''+ @txtEmailNew + ''', '''+ @txtWebSiteID + ''', '''+ @txtNoKTP + ''', '''+ @txtKontakID+ ''', '''+ @txtWebUID + ''', '''+ @txtPasswordNew + ''', @txtOutput output;insert into #myTempTable values(@txtOutput)'
exec (@connectionstring1);

Open in new window

0
 
cyberkiwiCommented:
Well.. If it's an int, then there is not supposed to be any leading 0's?  You could remove the quotes

'''+ @txtMobileNoOld + ''', '''+ @txtMobileNoNew + '''

to

'+ @txtMobileNoOld + ', '+ @txtMobileNoNew + '

But that means it is now just a number, for which the leading 0 will have no meaning.
0
 
cyberkiwiCommented:
It looks like you might need to check inside sp_web_updcustNew

Because the right approach could be to send in the STRING, including leading 0, then have it handle properly (it should NOT end up with the enclosing quotes in the db)
0
 
softblessAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.