newbie sql server bit to c# bool question.

G

Guest

This should be easy but... I'm trying to get a simple bool written into a bit
column type using a stored procedure (VS2003 with SQL Server 2000). My
understanding where that they were equivalents.

------------
CREATE PROCEDURE sp_UpdateFPRFByVP
@fGuid uniqueidentifier,
@vpResponse bit,
@vpComments nvarchar(3000)

AS

Update tblFPRF
SET VPResponse = @vpResponse, VPComments = @vpComments, VPResponseDate =
getdate()
Where KGuid = @fGuid
GO
------------

In query analyzer this works perfectly well
Exec sp_UpdateFPRFByVP @fGUID='203765A0-E3F3-4161-A117-8E6013004A50',
@vpResponse="1",@vpComments="Abe's test"

In query analyzer this fails (true wo quotes is interpreted as a column)
Exec sp_UpdateFPRFByVP @fGUID='203765A0-E3F3-4161-A117-8E6013004A50',
@vpResponse="true",@vpComments="Abe's test"


I'm using a C# routine to do the update:
public void updateVPResponse()
{
Guid _guid = new Guid(_fGuid); //_fGuid is a string
SqlConnection conn = new SqlConnection(connectString);
SqlCommand cmd = new SqlCommand("sp_UpdateFPRFByVP",conn);

cmd.Parameters.Add("@fGuid", SqlDbType.UniqueIdentifier);
cmd.Parameters.Add("@vpResponse", SqlDbType.Bit);
cmd.Parameters.Add("@vpComments", SqlDbType.NVarChar, 3000);
cmd.Parameters["@fGuid"].Value = _guid;
cmd.Parameters["@vpResponse"].Value = _authorization;
cmd.Parameters["@vpComments"].Value = _vpComments;

conn.Open();

cmd.ExecuteNonQuery();
conn.Close();
}
--------------------------------------------------------------

What value can _authorization be to be valid. I tried "1"(string), true
(bool), 1 (int) and have gotten type errors for each.

Thanks
 
B

Bob

Hi,
I have always tested and set.
cmdConn.Parameters.Add(New SqlParameter("@active", SqlDbType.Bit))

If lglActive Then

cmdConn.Parameters("@active").Value = 1

Else

cmdConn.Parameters("@active").Value = 0

End If

HTH
 
G

Guest

Thanks for the reply. If I change the variable to _athorization to a 1 or a 0
then the procedure kicks out an exception "Line 1:Incorrect syntax near
'sp_UpdateFPRFByVP'"

I guess I'm doing something else stupid, but I don't see it yet.

Best

Bob said:
Hi,
I have always tested and set.
cmdConn.Parameters.Add(New SqlParameter("@active", SqlDbType.Bit))

If lglActive Then

cmdConn.Parameters("@active").Value = 1

Else

cmdConn.Parameters("@active").Value = 0

End If

HTH

AbeR said:
This should be easy but... I'm trying to get a simple bool written into a bit
column type using a stored procedure (VS2003 with SQL Server 2000). My
understanding where that they were equivalents.

------------
CREATE PROCEDURE sp_UpdateFPRFByVP
@fGuid uniqueidentifier,
@vpResponse bit,
@vpComments nvarchar(3000)

AS

Update tblFPRF
SET VPResponse = @vpResponse, VPComments = @vpComments, VPResponseDate =
getdate()
Where KGuid = @fGuid
GO
------------

In query analyzer this works perfectly well
Exec sp_UpdateFPRFByVP @fGUID='203765A0-E3F3-4161-A117-8E6013004A50',
@vpResponse="1",@vpComments="Abe's test"

In query analyzer this fails (true wo quotes is interpreted as a column)
Exec sp_UpdateFPRFByVP @fGUID='203765A0-E3F3-4161-A117-8E6013004A50',
@vpResponse="true",@vpComments="Abe's test"


I'm using a C# routine to do the update:
public void updateVPResponse()
{
Guid _guid = new Guid(_fGuid); //_fGuid is a string
SqlConnection conn = new SqlConnection(connectString);
SqlCommand cmd = new SqlCommand("sp_UpdateFPRFByVP",conn);

cmd.Parameters.Add("@fGuid", SqlDbType.UniqueIdentifier);
cmd.Parameters.Add("@vpResponse", SqlDbType.Bit);
cmd.Parameters.Add("@vpComments", SqlDbType.NVarChar, 3000);
cmd.Parameters["@fGuid"].Value = _guid;
cmd.Parameters["@vpResponse"].Value = _authorization;
cmd.Parameters["@vpComments"].Value = _vpComments;

conn.Open();

cmd.ExecuteNonQuery();
conn.Close();
}
--------------------------------------------------------------

What value can _authorization be to be valid. I tried "1"(string), true
(bool), 1 (int) and have gotten type errors for each.

Thanks
Click to expand...
Click to expand...
 
G

Guest

Knowing that I did something dumb, I went back to check my code and realized
that I did not define the SqlCommandType as a stored procedure. Ugh! Me Bad.

All better now. Thanks for the ear!

Bob said:
Hi,
I have always tested and set.
cmdConn.Parameters.Add(New SqlParameter("@active", SqlDbType.Bit))

If lglActive Then

cmdConn.Parameters("@active").Value = 1

Else

cmdConn.Parameters("@active").Value = 0

End If

HTH

AbeR said:
This should be easy but... I'm trying to get a simple bool written into a bit
column type using a stored procedure (VS2003 with SQL Server 2000). My
understanding where that they were equivalents.

------------
CREATE PROCEDURE sp_UpdateFPRFByVP
@fGuid uniqueidentifier,
@vpResponse bit,
@vpComments nvarchar(3000)

AS

Update tblFPRF
SET VPResponse = @vpResponse, VPComments = @vpComments, VPResponseDate =
getdate()
Where KGuid = @fGuid
GO
------------

In query analyzer this works perfectly well
Exec sp_UpdateFPRFByVP @fGUID='203765A0-E3F3-4161-A117-8E6013004A50',
@vpResponse="1",@vpComments="Abe's test"

In query analyzer this fails (true wo quotes is interpreted as a column)
Exec sp_UpdateFPRFByVP @fGUID='203765A0-E3F3-4161-A117-8E6013004A50',
@vpResponse="true",@vpComments="Abe's test"


I'm using a C# routine to do the update:
public void updateVPResponse()
{
Guid _guid = new Guid(_fGuid); //_fGuid is a string
SqlConnection conn = new SqlConnection(connectString);
SqlCommand cmd = new SqlCommand("sp_UpdateFPRFByVP",conn);

cmd.Parameters.Add("@fGuid", SqlDbType.UniqueIdentifier);
cmd.Parameters.Add("@vpResponse", SqlDbType.Bit);
cmd.Parameters.Add("@vpComments", SqlDbType.NVarChar, 3000);
cmd.Parameters["@fGuid"].Value = _guid;
cmd.Parameters["@vpResponse"].Value = _authorization;
cmd.Parameters["@vpComments"].Value = _vpComments;

conn.Open();

cmd.ExecuteNonQuery();
conn.Close();
}
--------------------------------------------------------------

What value can _authorization be to be valid. I tried "1"(string), true
(bool), 1 (int) and have gotten type errors for each.

Thanks
Click to expand...
Click to expand...
 
Top