Bit field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bit field

Author

Topic

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-08 : 11:53:52

Hi,

I have a stored procedure called from ASP code that, based on user input, updates several fields in an sql server table. All fields are updating with the exception of one, a data type bit field. Here is partial code form the SP. The field I'm trying to update (with a 1) is btComplete. The field updating it is @complete. Both fields are defined as a data type of bit. When I do an end around the ASP code by just executing the SP directly, it still does not update btComplete to 1.

Any ideas ?
Thanks,
Jeff

UPDATE table1
SET field1 = @field1,
field2 = @field2,
btComplete = @complete,
dtUpdateDate = GETDATE()
WHERE iNPIId = @npiID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-08 : 11:55:20

Showing us the entire stored proc and/or how you are calling it (in both cases) would be helpful.

- Jeff

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-08 : 12:04:18

What a co-incidence? One Jeff replying to other

BTW, I have couple of questions:
1. Is variable @complete set to 1 ?
2. Are other fields updated correctly?

Harsh Athalye
India.
"Nothing is Impossible"

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-08 : 13:03:08

OK, thanks to all. Here is the entire SP. I removed some of the fields being updated to shorten the listing. Harsh, to answer your question, all other fields update correctly. When I manually execute the SP from query analyzer I populate the @complete field with a 1.
Thanks again,
Jeff

ALTER PROCEDURE dbo.uspNPI_DataSave (
@field1 = NULL,
@field2 char(10),
@field3 char(9),
@complete bit = 0 )

AS

BEGIN
SET NOCOUNT ON
If @complete is null
Set @complete = 0

IF @field1 is null
BEGIN
INSERT INTO tblNPIData (
field1,
field2,
field3,
btComplete,
dtCreateDate)
VALUES(@field1,
@field2,
@field3,
@complete,
GETDATE())
SET @npiID = @@Identity
END
ELSE
BEGIN
UPDATE tblNPIData
SET field1 = @field1,
field2 = @field2,
field3 = @field3,
btComplete = @complete,
dtUpdateDate = GETDATE()
WHERE field1 = @field1

END

SET NOCOUNT OFF

END

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 13:17:28

Kinda redundant, isn't it?


   SET	  field1 	= @field1
	, field2 	= @field2 
	, field3 	= @field3 
	, btComplete 	= @complete 
	, dtUpdateDate  = GETDATE() 
  WHERE   field1 	= @field1

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-08 : 13:19:46

quote:

When I manually execute the SP from query analyzer I populate the @complete field with a 1.

Does it update correctly when executing it inside Query Analyzer? If not, please post the line of code for the exec.

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 13:25:30

And doesn't @field1 input parameter require a datatype?

CREATE PROCEDURE test (@x = Null, @y bit) AS SELECT @x

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 13:34:53

Can you post the ASP code that you use to call the sp.

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-08 : 13:56:39

I edited the code somewhat before posting. Field1 has a data type of int in the original code. I guess the bottom line is that all fields update correctly with the exception of the bit field and I was wondering if there is something unique about that data type in terms of updating ? Tara, at the moment, when I execute the SP in Query Analyzer, I simply right click on the SP in the object browser, click 'open', populate the fields, and click 'execute'.
Thanks
Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-08 : 13:59:42

Even if you right click on the objec to execute it, it still provides the exec code in the top right pane window. Please post that so that we can see it.

Tara Kizer

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-08 : 14:09:52

Ok, here is that exec code, thanks:
DECLARE @RC int
DECLARE @npiID int
DECLARE @npiNumber char(10)
DECLARE @taxID char(9)
DECLARE @tpID varchar(25)
DECLARE @taxonomy char(10)
DECLARE @medicareID char(10)
DECLARE @part char(1)
DECLARE @providerName varchar(255)
DECLARE @address1 varchar(255)
DECLARE @address2 varchar(255)
DECLARE @city varchar(255)
DECLARE @state char(2)
DECLARE @zipCode char(5)
DECLARE @zipFour char(4)
DECLARE @comments varchar(255)
DECLARE @complete bit
SELECT @npiID = 017401234
SELECT @npiNumber = NULL
SELECT @taxID = NULL
SELECT @tpID = NULL
SELECT @taxonomy = NULL
SELECT @medicareID = NULL
SELECT @part = NULL
SELECT @providerName = NULL
SELECT @address1 = NULL
SELECT @address2 = NULL
SELECT @city = NULL
SELECT @state = NULL
SELECT @zipCode = NULL
SELECT @zipFour = NULL
SELECT @comments = NULL
SELECT @complete = 1
EXEC @RC = [SybaseHDR_QA].[dbo].[uspNPI_DataSave] @npiID, @npiNumber, @taxID, @tpID, @taxonomy, @medicareID, @part, @providerName, @address1, @address2, @city, @state, @zipCode, @zipFour, @comments, @complete

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-08 : 14:15:38

The code looks correct. What may not be correct is this part in the stored procedure:

If @complete is null
Set @complete = 0

Try removing that part and send a 1 to the bit field. Does this work inside Query Analyzer? When I say work, I mean update the data as expected.

Tara Kizer

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-08 : 14:19:31

quote:
Originally posted by JeffT

Ok, here is that exec code, thanks:
DECLARE @RC int
DECLARE @npiID int
DECLARE @npiNumber char(10)
DECLARE @taxID char(9)
DECLARE @tpID varchar(25)
DECLARE @taxonomy char(10)
DECLARE @medicareID char(10)
DECLARE @part char(1)
DECLARE @providerName varchar(255)
DECLARE @address1 varchar(255)
DECLARE @address2 varchar(255)
DECLARE @city varchar(255)
DECLARE @state char(2)
DECLARE @zipCode char(5)
DECLARE @zipFour char(4)
DECLARE @comments varchar(255)
DECLARE @complete bit
SELECT @npiID = 017401234
SELECT @npiNumber = NULL
SELECT @taxID = NULL
SELECT @tpID = NULL
SELECT @taxonomy = NULL
SELECT @medicareID = NULL
SELECT @part = NULL
SELECT @providerName = NULL
SELECT @address1 = NULL
SELECT @address2 = NULL
SELECT @city = NULL
SELECT @state = NULL
SELECT @zipCode = NULL
SELECT @zipFour = NULL
SELECT @comments = NULL
SELECT @complete = 1
EXEC @RC = [SybaseHDR_QA].[dbo].[uspNPI_DataSave] @npiID, @npiNumber, @taxID, @tpID, @taxonomy, @medicareID, @part, @providerName, @address1, @address2, @city, @state, @zipCode, @zipFour, @comments, @complete

If you don't post the actual stored procedure, there is no way for us to verify that the parameters are being set properly when you are calling it.

- Jeff

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 14:49:12

You've never cleared up a critical question here - does this work when you run it in QA? If so, then the problem lies in the call from the ASP code - you need to post that.

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-08 : 15:10:24

snSQL,

The table field in question, btComplete, is not updated via a call from the ASP code or when I run the "called" stored procedure manually in query analyzer. Every other field can be updated with the exception of that field. I first wanted to get it working form query analyzer and then move on to the ASP code.

Thanks

Tara,

I removed those 2 lines of code and executed the SP again in QA but the bit table field still was not updated.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-08 : 15:15:53

We'll need to see the actual code then.

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 15:38:41

quote:
Originally posted by tkizer

We'll need to see the actual code then.

Tara Kizer

Sure....here you go...I don't have a problem...BUT I did have to make a lot of assumptions based on your "logic"


USE Northwind
GO

CREATE TABLE tblNPIData (
			  field1 int IDENTITY(1,1)
			, field2 char(10)
			, field3 char(9) 
			, complete bit 
			, dtCreateDate datetime
			, dtUpdateDate datetime)
GO



CREATE PROCEDURE dbo.uspNPI_DataSave (
	  @field1 int = NULL 
	, @field2 char(10)
	, @field3 char(9) 
	, @complete bit = 0)

AS 

SET NOCOUNT ON
IF @complete is null SET @complete = 0 

IF @field1 is null
    BEGIN
	INSERT INTO tblNPIData (
--			  field1, -- I'm assuming that this "Field" must be an identity due to your null logic  
			  field2  
			, field3  
			, Complete  
			, dtCreateDate)
	     VALUES (	  -- @field1,       --  So this has to come out as well  
			  @field2  
			, @field3 
			, @complete  
			, GETDATE())
--	SET @npiID = @@Identity		    -- Don't know what this is used for
    END
  ELSE
    BEGIN
	UPDATE tblNPIData 
	   SET	  
--		  field1 	= @field1,  -- This is not needed
		  field2 	= @field2 
		, field3 	= @field3 
		, Complete 	= @complete 
		, dtUpdateDate  = GETDATE() 
	  WHERE   field1 	= @field1 
    END
GO

EXEC uspNPI_DataSave
	  @field1= null
	, @field2= 'a'
	, @field3= 'b'
	, @complete= 0

SELECT * FROM tblNPIData

EXEC uspNPI_DataSave
	  @field1= 1
	, @field2= 'x'
	, @field3= 'y'
	, @complete= 1

SELECT * FROM tblNPIData
GO


DROP PROC uspNPI_DataSave
DROP TABLE tblNPIData
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-08 : 15:53:55

Not Brett's code, the actual code from JeffT!

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-08 : 15:56:48

quote:
Originally posted by tkizer

Not Brett's code, the actual code from JeffT!

Tara Kizer

I know, I know...but Jeff is speaking about Miracles...of course the bit "field" should occur if the other "fields" are being updated.

Jeff, what tara is asking for needs to be excatly like I posted....now it's your turn...

I think fresh mango margarita's tonight

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-09-08 : 16:33:27

Try count the number of parameters, maybe you are missing one ... making @complete NULL making it 0

quote:
I think fresh mango margarita's tonight

never have had that, are they good? strawbarry is my favorit

-- The Heisenberg uncertainty principle also applies when debugging

JeffT
Posting Yak Master

111 Posts

Posted - 2006-09-11 : 08:42:00

Hi,

Had to switch off to another task. I ended up finding the problem in the ASP code. The value being passed to the stored procedure was not being set properly.

Thanks to everyone for your help,
J.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources