add a record to yes/no control form VB

carlos1972 · April 6th, 2006, 11:58 AM

I have a DB in Access wich has a yes/no fields, and I made a USP to add records from a VB app. but the problem is that I can't get my code right to do this any ideas my code look like this.

'Initialize the Command object and set its properties
            objCommand = New OleDbCommand("usp_test1", objConnection)
            objCommand.CommandType = Data.CommandType.StoredProcedure

            'Add the Parameters to the Parameters collection
            objCommand.Parameters.Add("@IDCorreoSAL", _
                OleDbType.Guid, 16).Value = Guid.NewGuid()

            objCommand.Parameters.Add("@NumeroSalida", _
            OleDbType.UnsignedTinyInt, 1).Value = _
            CType(txtNumero.Text, Byte)

            objCommand.Parameters.Add("@FechaSalida", _
             OleDbType.Date).Value = _
            CType(txtFechaSalida.Text, DateTime)

            objCommand.Parameters.Add("@Destinatario", _
            OleDbType.VarChar, 50).Value = txtDestinatario.Text

            objCommand.Parameters.Add("@FchaGuardia", _
             OleDbType.Date).Value = _
            CType(txtFechaGuardia.Text, DateTime)

            objCommand.Parameters.Add("@Asunto", OleDbType.LongVarChar, _
                    txtAsunto.Text.Length).Value = _
                   txtAsunto.Text

            objCommand.Parameters.Add("@clasificacion", _
             OleDbType.LongVarChar, txtClasificacion.Text.Length).Value = _
            txtClasificacion.Text

            MsgBox(chbEmail.Checked)

If chbEmail.Checked = True Then
                objCommand.Parameters.Add("@email", OleDbType.Integer).Value = "-1"
            Else
                objCommand.Parameters.Add("@email", OleDbType.Integer).Value = "0"
            End If

I bold what I is wrong

Thank you

Bob Bedell · April 6th, 2006, 09:16 PM

The Access (Jet) Yes/No data type maps to the .NET Framework OleDbType.Boolean enumeration member.

The Jet/.NET Framework data type mappings are as follows:

Text - OleDbType.WChar (130)
Memo - OleDbType.WChar (130)
Date/Time - OleDbType.Date (7)
Currency - OleDbType.Currency (6)
Yes/No - OleDbType.Boolean (11)
Number (Byte) - OleDbType.UnsignedTinyInt (17)
Number (Integer) - OleDbType.SmallInt (2)
Number (Long Integer) - OleDbType.Integer (3)
Number (Single) - OleDbType.Single (4)
Number (Double) - OleDbType.Double (5)
Number (Replication ID) - OleDbType.Guid (72)
Number (Decimal) - OleDbType.Numeric (131)

OLEObject fields map to OleDBType.Binary and HyperlinkFields are OleDbType.WChar.

VarWChar also works in place of WChar. And for your Guid parameter, try selecting the Number(ReplicationID) data type in Access.

Here's a VB.NET routine that will map your Access table to the OleDbType enumneration. Just pass it a connection string and a SQL statement like: SELECT * FROM YourTable which populates a DataReader. Import System.Data and System.Data.OleDb.

Code:

Public Sub m_GetSchemaTable(ByVal strConnectionString As String, ByVal strSqlStatement As String)
        Dim cnn As New OleDbConnection(strConnectionString)
        Dim cmd As New OleDbCommand(strSqlStatement, cnn)
        cnn.Open()
        Dim dr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Dim tbl As DataTable = dr.GetSchemaTable()
        Dim row As DataRow

        For Each row In tbl.Rows
            Console.WriteLine(row("ColumnName") & " - " & _
              CType(row("ProviderType"), OleDbType).ToString)
        Next row

        dr.Close()
    End Sub

HTH,

Bob

Bob Bedell · April 6th, 2006, 10:01 PM

And you can use both:

.Parameters(0).Value = False
.Parameters(0).Value = 0

and

.Parameters(0).Value = True
.Parameters(0).Value = -1

Here's a complete routine:

Code:

Public Sub CallStoredProcedureBoolean(ByVal strConnectionString As String, ByVal strStoredProcedure As String)
        Dim cnn As New OleDbConnection(strConnectionString)
        cnn.Open()

        Dim cmd As OleDbCommand = cnn.CreateCommand()
        With cmd
            .CommandText = strStoredProcedure
            .CommandType = CommandType.StoredProcedure
            .Parameters.Add("@YesNoField", OleDbType.Boolean)
            .Parameters(0).Value = False
        End With
        cmd.ExecuteNonQuery()
    End Sub

The Access "stored procedure" looks like:

Code:

PARAMETERS [@BooleanValue] Bit;
INSERT INTO tblBooleanField ( YesNoField )
SELECT [@BooleanValue] AS Expr1;

Notice the "Bit" parameter type.

HTH,

Bob