Call SQL Stored procedure with Bit datatype

I am having difficulty calling a stored procedure from VB.Net that has a Bit
parameter. To try and figure it out, I have created some tests. The SQL SP
looks like this:
CREATE procedure up_insert_test
@value bit as
insert into tblTest(field1) values(@value)

And the table has two fields. The first is ID which is an indentity field,
and the second is Field1 that is just a nullable Bit field.

Here is my VB Code:
Dim cn As SqlConnection
Dim cmd As SqlCommand
cn = New SqlConnection(g_DBConnection)
cn.Open()
If cn.State = ConnectionState.Open Then
cmd = New SqlCommand("up_insert_test")
cmd.Connection = cn
Dim p As New SqlParameter("@value", SqlDbType.Bit)
p.Value = 1
cmd.Parameters.Add(p)
cmd.ExecuteNonQuery() 'Fails here!!!!!
cn.Close()
End If

Everytime I get to the ExecuteNonQuery I get the followning error:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.


If anyone can give me a hand, I would greatly appreciate it.

Thanks,

Rob

Mike
Fri Oct 22 09:55:34 CDT 2004

RBB wrote:
> I am having difficulty calling a stored procedure from VB.Net that has a Bit
> parameter. To try and figure it out, I have created some tests. The SQL SP
> looks like this:
> CREATE procedure up_insert_test
> @value bit as
> insert into tblTest(field1) values(@value)

> Dim p As New SqlParameter("@value", SqlDbType.Bit)
> p.Value = 1
> cmd.Parameters.Add(p)

As a guess, try "p.Value = True". Bit fields in SQL are interpreted as
booleans by ADO.NET. Technically anything non-zero should be "true" but
it's possible that you are causing the type of the parameter to be
changed by assigning an integer to it.

Also, if you have access to it, you can always run SQL Profiler and see
what the actual RPC call looks like. 99% of the time it will be an
invalid parameter value, and you can cut/paste from Profiler into Query
Analyzer to figure it out.

--Mike

Miha
Fri Oct 22 09:59:42 CDT 2004

Hi,

Try setting cmd.CommandType = CommandType.StoredProcedure;

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"RBB" <RBB@discussions.microsoft.com> wrote in message
news:C8204509-DE3E-4A38-BCD8-81DA279EEE68@microsoft.com...
>I am having difficulty calling a stored procedure from VB.Net that has a
>Bit
> parameter. To try and figure it out, I have created some tests. The SQL
> SP
> looks like this:
> CREATE procedure up_insert_test
> @value bit as
> insert into tblTest(field1) values(@value)
>
> And the table has two fields. The first is ID which is an indentity
> field,
> and the second is Field1 that is just a nullable Bit field.
>
> Here is my VB Code:
> Dim cn As SqlConnection
> Dim cmd As SqlCommand
> cn = New SqlConnection(g_DBConnection)
> cn.Open()
> If cn.State = ConnectionState.Open Then
> cmd = New SqlCommand("up_insert_test")
> cmd.Connection = cn
> Dim p As New SqlParameter("@value", SqlDbType.Bit)
> p.Value = 1
> cmd.Parameters.Add(p)
> cmd.ExecuteNonQuery() 'Fails here!!!!!
> cn.Close()
> End If
>
> Everytime I get to the ExecuteNonQuery I get the followning error:
> An unhandled exception of type 'System.Data.SqlClient.SqlException'
> occurred
> in system.data.dll
>
> Additional information: System error.
>
>
> If anyone can give me a hand, I would greatly appreciate it.
>
> Thanks,
>
> Rob

Sahil
Fri Oct 22 10:01:00 CDT 2004

That would fail too.

Here is the fixed code --

Sub Main()
Dim cn As SqlConnection
Dim cmd As SqlCommand
cn = New
SqlConnection("Server=Win2k-Smalik;Database=Northwind;uid=****;pwd=****")
cn.Open()
If cn.State = ConnectionState.Open Then
cmd = New SqlCommand("up_insert_test")
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
Dim p As New SqlParameter("@value", SqlDbType.Bit)
p.Value = True
cmd.Parameters.Add(p)
cmd.ExecuteNonQuery() 'Doesn't fail anymore !!!!!
cn.Close()
End If
End Sub

You forgot to ---
a) Set proper data type (value is object)
b) Set the command type

HTH :)
- Sahil Malik
http://www.dotnetjunkies.com/weblog/sahilmalik

"Mike Edenfield" <kutulu@not.kutulu.not.org> wrote in message
news:uiJRwcEuEHA.3088@tk2msftngp13.phx.gbl...
> RBB wrote:
> > I am having difficulty calling a stored procedure from VB.Net that has a
Bit
> > parameter. To try and figure it out, I have created some tests. The
SQL SP
> > looks like this:
> > CREATE procedure up_insert_test
> > @value bit as
> > insert into tblTest(field1) values(@value)
>
> > Dim p As New SqlParameter("@value", SqlDbType.Bit)
> > p.Value = 1
> > cmd.Parameters.Add(p)
>
> As a guess, try "p.Value = True". Bit fields in SQL are interpreted as
> booleans by ADO.NET. Technically anything non-zero should be "true" but
> it's possible that you are causing the type of the parameter to be
> changed by assigning an integer to it.
>
> Also, if you have access to it, you can always run SQL Profiler and see
> what the actual RPC call looks like. 99% of the time it will be an
> invalid parameter value, and you can cut/paste from Profiler into Query
> Analyzer to figure it out.
>
> --Mike

RBB
Fri Oct 22 10:15:04 CDT 2004

MIha & Sahil - Thank you!

Setting the command type fixed the problem.

Thanks,

Rob

"Miha Markic [MVP C#]" wrote:

> Hi,
>
> Try setting cmd.CommandType = CommandType.StoredProcedure;
>
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
>
> "RBB" <RBB@discussions.microsoft.com> wrote in message
> news:C8204509-DE3E-4A38-BCD8-81DA279EEE68@microsoft.com...
> >I am having difficulty calling a stored procedure from VB.Net that has a
> >Bit
> > parameter. To try and figure it out, I have created some tests. The SQL
> > SP
> > looks like this:
> > CREATE procedure up_insert_test
> > @value bit as
> > insert into tblTest(field1) values(@value)
> >
> > And the table has two fields. The first is ID which is an indentity
> > field,
> > and the second is Field1 that is just a nullable Bit field.
> >
> > Here is my VB Code:
> > Dim cn As SqlConnection
> > Dim cmd As SqlCommand
> > cn = New SqlConnection(g_DBConnection)
> > cn.Open()
> > If cn.State = ConnectionState.Open Then
> > cmd = New SqlCommand("up_insert_test")
> > cmd.Connection = cn
> > Dim p As New SqlParameter("@value", SqlDbType.Bit)
> > p.Value = 1
> > cmd.Parameters.Add(p)
> > cmd.ExecuteNonQuery() 'Fails here!!!!!
> > cn.Close()
> > End If
> >
> > Everytime I get to the ExecuteNonQuery I get the followning error:
> > An unhandled exception of type 'System.Data.SqlClient.SqlException'
> > occurred
> > in system.data.dll
> >
> > Additional information: System error.
> >
> >
> > If anyone can give me a hand, I would greatly appreciate it.
> >
> > Thanks,
> >
> > Rob
>
>
>

Mike
Fri Oct 22 11:08:03 CDT 2004

Sahil Malik wrote:

> cmd.Connection = cn
> cmd.CommandType = CommandType.StoredProcedure

> You forgot to ---
> a) Set proper data type (value is object)
> b) Set the command type

I *always* forget that. ADO.NET's should default to
CommandType.StoredProcedure, that's all you're "supposed" to use anyway :)

Sahil
Fri Oct 22 12:22:23 CDT 2004

Thats not a bad idea, but then think about it, the way ADO.NET is designed
from ground up, the easy entry, easy to implement things are more
straightforward, and the better to do stuff is not as straightforwad. The
idea being (I think), to get people introduced to ADO.NET, and realize OH
MAN THIS IS COOL !!! Once they have their feet wet, they realize .. that you
can't see heaven without dying, so then they do the right thing.

You wouldn't have a chance to do the right thing, if your start was so
complicated that you gave up :) .. just my views, but I think it's a wise
strategy.

- Sahil Malik
http://www.dotnetjunkies.com/weblog/sahilmalik
Please reply to the newsgroups instead of email so everyone can benefit from
your reply.


"Mike Edenfield" <kutulu@not.kutulu.not.org> wrote in message
news:uMlJQFFuEHA.3872@TK2MSFTNGP15.phx.gbl...
> Sahil Malik wrote:
>
> > cmd.Connection = cn
> > cmd.CommandType = CommandType.StoredProcedure
>
> > You forgot to ---
> > a) Set proper data type (value is object)
> > b) Set the command type
>
> I *always* forget that. ADO.NET's should default to
> CommandType.StoredProcedure, that's all you're "supposed" to use anyway :)