ADO Parameters

[rbb]

I have a table that includes a bit column, and a stored procedure that inserts records into this procedure. I am now trying to write the code in VB.Net, but I am getting the following error every time I try to use a SQLDBType.Bit parameter.

 

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

 

Additional information: System error.

 

So I wrote some test code, and here it is.

 

VB CODE

  Private Sub TestBit()

     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_crap")
        cmd.Connection = cn
        Dim p As New SqlParameter("@crap", SqlDbType.Bit)
        p.Value = 1
        cmd.Parameters.Add(p)
        cmd.ExecuteNonQuery()
        cn.Close()
     End If

  End Sub

SQL SP

create procedure up_insert_crap
@crap bit as

insert into tblcrap(test) values(@crap)

 

The table is just an identity id field and a test field that is a bit.

 

Any Ideas as to what I am doing wrong?

 

 

Thanks,

 

Rob

[Joe Mamma]

I am just guessing, but dont sp's return a integer parameter (errocode, or sp defined result)?

 

try this

1. Set your command's command type to StoredProcedure

2. add an Int parameter, call it @result

3. set its direction to output

4. Add it to the Command

5. add your Bit input parameter

6. Call it @crap (or whatever the stored procedure expects)

7. set its direction to Input

8. set its value.

9. execute

[stustarz]

Just tried it with this, and it worked fine:

 

       Dim cn As SqlConnection = New SqlConnection
       cn.ConnectionString = "Data Source=SERVERNAME;" & _
                                     "Initial Catalog=DATABSE;" & _
                                     "User ID=USERID;Password=PASSWORD"

       Dim cmd As New SqlCommand
       cn.Open()
       If cn.State = ConnectionState.Open Then
           With cmd
               .CommandType = CommandType.StoredProcedure
               .CommandText = "up_insert_crap"
               .Connection = cn
'With the parameters just use the parameter name, 
'then the value for the parameter. From my understanding 
'the Stored procedure will determine the datatype during the 
'delcaration of the input parameter (DECLARE @crap BIT)
               .Parameters.Add("@crap", "1")
           End With
           cmd.ExecuteNonQuery()
           cn.Close()
       End If

 

I used the same stored procedure code, and had a table with the ID column (INT datatype, Identity set to Yes) and the test column (BIT Datatype).