Call SQL Stored procedure with Bit datatype

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
RBB said:
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)
Click to expand...
Dim p As New SqlParameter("@value", SqlDbType.Bit)
p.Value = 1
cmd.Parameters.Add(p)
Click to expand...

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
 
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
 
MIha & Sahil - Thank you!

Setting the command type fixed the problem.

Thanks,

Rob

Miha Markic said:
Hi,

Try setting cmd.CommandType = CommandType.StoredProcedure;

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

RBB said:
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
Click to expand...
Click to expand...
 
Sahil said:
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
Click to expand...
You forgot to ---
a) Set proper data type (value is object)
b) Set the command type
Click to expand...

I *always* forget that. ADO.NET's should default to
CommandType.StoredProcedure, that's all you're "supposed" to use anyway :)
 
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.
 
Back
Top