none
insert CheckBox Value Into Database RRS feed

  • Question

  • hi

    i want to insert CheckBox Value Into Database


    try 1

       Dim com As New OleDbCommand("INSERT INTO TB_WEBSERVERPAYED (WEBSERVERPAYED_CODE,WEBSERVERPAYED_DOLAR,WEBSERVERPAYED_CHECK) VALUES (@WEBSERVERPAYED_CODE,@WEBSERVERPAYED_DOLAR,@WEBSERVERPAYED_CHECK)", con)
    
            Try
                con.Open()
    
    
                com.Parameters.AddWithValue("@WEBSERVERPAYED_CODE", SERVERMEADD.TEXT_WEBCODE.Text)
                com.Parameters.AddWithValue("@WEBSERVERPAYED_DOLAR", SERVERMEADD.TEXT_COSTDOLAR.Text)
                com.Parameters.AddWithValue("@WEBSERVERPAYED_CHECK", SERVERMEADD.CheckBox1.Checked)
    
                com.ExecuteNonQuery()

    try 1

     Dim com As New OleDbCommand("INSERT INTO TB_WEBSERVERPAYED (WEBSERVERPAYED_CODE,WEBSERVERPAYED_DOLAR,WEBSERVERPAYED_CHECK) VALUES (@WEBSERVERPAYED_CODE,@WEBSERVERPAYED_DOLAR,@WEBSERVERPAYED_CHECK)", con)
    
            Try
                con.Open()
    
                Dim check As String
                If SERVERMEADD.CheckBox1.Checked = True Then
                    check = "Yes"
                Else
                    check = "No"
                End If
    
    
                com.Parameters.AddWithValue("@WEBSERVERPAYED_CODE", SERVERMEADD.TEXT_WEBCODE.Text)
                com.Parameters.AddWithValue("@WEBSERVERPAYED_DOLAR", SERVERMEADD.TEXT_COSTDOLAR.Text)
                com.Parameters.AddWithValue("@WEBSERVERPAYED_CHECK", check)
    
                com.ExecuteNonQuery()
    

    Monday, October 1, 2018 10:11 PM

Answers

  • If you have the field as a Yes/No field in MS-Access then the value passed in must be a Boolean, not a String.

    Example

        Public Function UpdateRow(pDataRow As DataRow) As Boolean
    
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
    
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                UPDATE 
                                    Customer 
                                SET 
                                    Process = @Process,
                                    CompanyName = @CompanyName, 
                                    ContactName = @ContactName,
                                    ContactTitle = @ContactTitle
                                WHERE Identifier = @Identifier
                            </SQL>.Value
    
                        Dim processParameter As New OleDbParameter With
                                {
                                .DbType = DbType.Boolean,
                                .ParameterName = "@Process",
                                .Value = pDataRow.Field(Of Boolean)("Process")
                                }
                        cmd.Parameters.Add(processParameter)
                        Dim companyNameParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "@CompanyName",
                                .Value = pDataRow.Field(Of String)("CompanyName")
                            }
    
                        cmd.Parameters.Add(companyNameParameter)
    
                        Dim contactNameParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "@ContactName",
                                .Value = pDataRow.Field(Of String)("ContactName")
                            }
    
                        cmd.Parameters.Add(contactNameParameter)
    
                        Dim contactTitleParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "@ContactTitle",
                                .Value = pDataRow.Field(Of String)("ContactTitle")
                            }
    
                        cmd.Parameters.Add(contactTitleParameter)
    
                        Dim identifierParameter As New OleDbParameter With
                            {
                                .DbType = DbType.Int32,
                                .ParameterName = "@Identifier",
                                .Value = pDataRow.Field(Of Int32)("Identifier")
                            }
    
                        cmd.Parameters.Add(identifierParameter)
    
                        Try
                            cn.Open()
    
                            Dim affected = cmd.ExecuteNonQuery
                            Return affected = 1
    
                        Catch ex As Exception
    
                            mHasException = True
                            mLastException = ex
    
                            Return IsSuccessFul
    
                        End Try
                    End Using
                End Using
    
            Catch ex As Exception
                mHasException = True
                mLastException = ex
    
                Return IsSuccessFul
    
            End Try
        End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Monday, October 1, 2018 10:20 PM
    Moderator

All replies

  • If you have the field as a Yes/No field in MS-Access then the value passed in must be a Boolean, not a String.

    Example

        Public Function UpdateRow(pDataRow As DataRow) As Boolean
    
            Try
                Using cn As New OleDbConnection(Builder.ConnectionString)
    
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                UPDATE 
                                    Customer 
                                SET 
                                    Process = @Process,
                                    CompanyName = @CompanyName, 
                                    ContactName = @ContactName,
                                    ContactTitle = @ContactTitle
                                WHERE Identifier = @Identifier
                            </SQL>.Value
    
                        Dim processParameter As New OleDbParameter With
                                {
                                .DbType = DbType.Boolean,
                                .ParameterName = "@Process",
                                .Value = pDataRow.Field(Of Boolean)("Process")
                                }
                        cmd.Parameters.Add(processParameter)
                        Dim companyNameParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "@CompanyName",
                                .Value = pDataRow.Field(Of String)("CompanyName")
                            }
    
                        cmd.Parameters.Add(companyNameParameter)
    
                        Dim contactNameParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "@ContactName",
                                .Value = pDataRow.Field(Of String)("ContactName")
                            }
    
                        cmd.Parameters.Add(contactNameParameter)
    
                        Dim contactTitleParameter As New OleDbParameter With
                            {
                                .DbType = DbType.String,
                                .ParameterName = "@ContactTitle",
                                .Value = pDataRow.Field(Of String)("ContactTitle")
                            }
    
                        cmd.Parameters.Add(contactTitleParameter)
    
                        Dim identifierParameter As New OleDbParameter With
                            {
                                .DbType = DbType.Int32,
                                .ParameterName = "@Identifier",
                                .Value = pDataRow.Field(Of Int32)("Identifier")
                            }
    
                        cmd.Parameters.Add(identifierParameter)
    
                        Try
                            cn.Open()
    
                            Dim affected = cmd.ExecuteNonQuery
                            Return affected = 1
    
                        Catch ex As Exception
    
                            mHasException = True
                            mLastException = ex
    
                            Return IsSuccessFul
    
                        End Try
                    End Using
                End Using
    
            Catch ex As Exception
                mHasException = True
                mLastException = ex
    
                Return IsSuccessFul
    
            End Try
        End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Monday, October 1, 2018 10:20 PM
    Moderator
  • Don't know if it is a correct way , but i use this

    ''To Database
     
    Dim nValue As String
            If Zout.Checked = True Then
                nValue = 1
            Else
                nValue = 0
            End If
    
    
     cmd.Parameters.Add("@Zout", SqlDbType.NVarChar).Value = nValue
    
    
    
    
    ''Back from database
    
         Zout.CheckState = sdr.Item("Zout")

    It works fine.

    Checkstate  nValue 1 from database is checked 


    • Edited by KeesBlunder Tuesday, October 2, 2018 9:32 AM
    Tuesday, October 2, 2018 9:31 AM