GiantRobot
Posts: 2
|
| Posted: 10/19/2005, 4:29 PM |
|
I cannot get a boolean list box to update a stored procedure homed in SQL Server.I think I have everything configured properly. Update is successful me if I remove that one parameter and in the past my solution to this issue was to make my bit fields into integer fields but in this case that might break an existing application that relies on the field.
I have my project boolean publish setting set to 1/0
List box is based on a seperate 'pick_list' table and the CC Ctrl data type is set to boolean and the bound column is based on a bit value pulled from the SQL server table
Error I get is: 'Error converting data type nchar to bit. (Microsoft OLE DB Provider for SQL Server)' This error is fairly straight forward and is easily solved outside of CCS but I'd really like to understand what the issues are when attempting to this type of statement with CCS. I've never been able to get a bit field to update successfully with CCS...I always change them to int
The sp debugs fine is SQL server.
I'm using an SQLOLEDB connection string. Booelean Format set to 1;0
If it helps here's the sp: @Active is the input parameter and it hits a bit field.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc sp_ADV03_SITEINFO_UPD(
@pin nvarchar(255),
@pi_name nvarchar(60)= null,
@site_address_1 nvarchar(75) = null,
@site_address_2 nvarchar(75) = null,
@site_address_3 nvarchar(75) = null,
@site_address_4 nvarchar(75) = null,
@site_address_5 nvarchar(75) = null,
@fax1 nvarchar(50) = null,
@fax2 nvarchar(50) = null,
@email1 nvarchar(50) = null,
@email2 nvarchar(50) = null,
@email3 nvarchar(50) = null,
@email4 nvarchar(50) = null,
@email5 nvarchar(50) = null,
@CurrSiteNum nchar(2),
@siteId int,
@SiteNum nchar(2),
@Active bit,
@CountryNum int = null,
@CountryTxt nchar(30)
)as
declare @company nvarchar(255)
declare @study nvarchar(255)
declare @var nvarchar(255)
Set @company = 'ADVENTRX'
Set @study = 'ADVENTRX CoFactor-03'
Set @var = @SiteNum
Update pins Set
pin = @pin,
company = @company,
study = @study,
[var] = @var,
pi_name = @pi_name,
siteaddr1 = @site_address_1,
siteaddr2 = @site_address_2,
siteaddr3 = @site_address_3,
siteaddr4 = @site_address_4,
siteaddr5 = @site_address_5,
unblind_fax01 = @fax1,
unblind_fax02 = @fax2,
unblind_email01 = @email1,
unblind_email02 = @email2,
unblind_email03 = @email3,
unblind_email04 = @email4,
unblind_email05 = @email5
Where siteId = @siteId
Update [ADV03-SITES] Set
SiteNum = @SiteNum,
Active = @Active,
CountryNum = @CountryNum,
CountryTxt = @CountryTxt
Where SiteNum = @CurrSiteNum
if @@error !=0 return 99
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
 |
 |
Paul Shearing
|
| Posted: 11/14/2005, 7:15 AM |
|
I'm guessing that the object being passed via the @Active parameter is an nVarChar string '0' for False, '1' for true. To test this I would modify the stored procedure to have @Active nvarchar(1) = '0' as the parameter and when assigning use Active = CAST(@Active As Bit) or possibly:
Declare @Act Bit;
If @Active = '1'
Set @Act = 1
Else
Set @Act = 0;
Update ...
Active=@Act
...
According to the T-SQL documentation, the conversion of nvarchar to Bit is implicit and should not require the CAST function but the If...then...else logic above should work for all eventualities (although you may need to test separately for NULL). If this does not work I would create a temporary table with a variant field that I would update to hold the value of @Active so that I could see what is being passed through this parameter.
This is all a kludge to get round the original problem rather than to solve it but I hope that it helps.
Regards
Paul
|
|
|
|
Edd
Posts: 547
|
| Posted: 11/15/2005, 1:54 PM |
|
I agree with Paul, I have been working with CCS and products like Crystal and passing bit's is always an issue.
I personally always pass strings.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
|
|
|
|
