How to Check and enter the data from "2 Textboxes" based on the availablity of the data into access from VB .Net

Question

I'm trying to insert some data into the access database but with some condition. Which is to enter the data by checking the 2 Textboxes and only then insert into access database.

To brief you more, please check out my code..

Imports System.Data.OleDb

Public Class Form1

Dim provider As String

Dim datafile As String

Dim connString As String

Dim myConnection As OleDbConnection = New OleDbConnection

Dim str As String

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="

    datafile = "D:\Database\samplech.accdb"

    connString = provider & datafile

    myConnection.ConnectionString = connString

    myConnection.Open()



    Dim chinsrt1 As String

    chinsrt1 = "Insert into bkCharacters([bkch01],[bkch02],[bkch03],[bkCh04]) Values (?,?,?,?)"

    Dim chcmd1 As OleDbCommand = New OleDbCommand(chinsrt1, myConnection)

    If (String.IsNullOrWhiteSpace(CH1.Text)) Then

        If (String.IsNullOrWhiteSpace(CHDP1.Text)) Then

            chcmd1.Parameters.Add(New OleDbParameter("bk01", CType(bkNmbr.Text, String)))

            chcmd1.Parameters.Add(New OleDbParameter("bk02", CType(CH1.Text, String)))

            chcmd1.Parameters.Add(New OleDbParameter("bk03", CType(CHDP1.Text, String)))

            chcmd1.Parameters.Add(New OleDbParameter("bk04", CType(CHSource.Text, String)))

        Else

            ' Insert an empty String

            chcmd1.Parameters.Add(New OleDbParameter("bk01", ""))

            chcmd1.Parameters.Add(New OleDbParameter("bk02", ""))

            chcmd1.Parameters.Add(New OleDbParameter("bk03", ""))

            chcmd1.Parameters.Add(New OleDbParameter("bk04", ""))

        End If

    End If





    Dim chinsrt2 As String

    chinsrt2 = "Insert into bkCharacters([bkch01],[bkch02],[bkch03],[bkCh04]) Values (?,?,?,?)"

    Dim chcmd2 As OleDbCommand = New OleDbCommand(chinsrt2, myConnection)

    If (String.IsNullOrWhiteSpace(CH2.Text)) Then

        If (String.IsNullOrWhiteSpace(CHDP2.Text)) Then

    chcmd2.Parameters.Add(New OleDbParameter("bk01", CType(bkNmbr.Text, String)))

            chcmd2.Parameters.Add(New OleDbParameter("bk02", CType(CH2.Text, String)))

            chcmd2.Parameters.Add(New OleDbParameter("bk03", CType(CHDP2.Text, String)))

            chcmd2.Parameters.Add(New OleDbParameter("bk04", CType(CHSource.Text, String)))

        Else

            ' Insert an empty String

            chcmd2.Parameters.Add(New OleDbParameter("bk01", ""))

            chcmd2.Parameters.Add(New OleDbParameter("bk02", ""))

            chcmd2.Parameters.Add(New OleDbParameter("bk03", ""))

            chcmd2.Parameters.Add(New OleDbParameter("bk04", ""))

        End If

    End If







    Try

        chcmd1.ExecuteNonQuery()

        chcmd2.ExecuteNonQuery()

        chcmd1.Dispose()

        chcmd2.Dispose()

        myConnection.Close()



    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

End Sub

End Class

What I'm trying to do here is to check the values of CH1.Text and CHDP1.Text and if there's some data in either one of those textboxes, i want the another field in access to be empty.

But If there's data in both of them just insert accordingly...

In both scenarios I want to also include bkNmbr.Text and ChSource.Text.

And If there's no data in both textboxes(CH1 and CHDP1), i just don't want to update the data at all (no update of the records)

Please help me out guys, I'm stuck here.

Thank You.

Answer 1

Check if one field is not empty and then insert, if both empty do nothing.

If (Not String.IsNullOrWhiteSpace(CH1.Text)) OR (Not String.IsNullOrWhiteSpace(CHDP1.Text)) Then 'check if at least one field is not empty
  Dim checkNullCH1 as String
  Dim checkNullCHDP1 as String

  If String.IsNullOrWhiteSpace(CH1.Text) then 'check if field is empty
    checkNullCH1 = "" 
  else 
    checkNullCH1 = CH1.Text
  end if

  If String.IsNullOrWhiteSpace(CHPD1.Text) then
    checkNullCHPD1 = ""
  else 
    checkNullCHPD1 = CHPD1.Text
  end if
  chcmd1.Parameters.Add(New OleDbParameter("bk01", CType(bkNmbr.Text, String)))
  chcmd1.Parameters.Add(New OleDbParameter("bk02", CType(checkNullCH1, String)))
  chcmd1.Parameters.Add(New OleDbParameter("bk03", CType(checkNullCHPD1, String)))
  chcmd1.Parameters.Add(New OleDbParameter("bk04", CType(CHSource.Text, String)))
  chcmd1.ExecuteNonQuery()
end if

Alternate you can check if both are empty.

If not ((String.IsNullOrWhiteSpace(CH1.Text)) AND (String.IsNullOrWhiteSpace(CHDP1.Text))) Then

This is equal to the other one.

How to Check and enter the data from "2 Textboxes" based on the availablity of the data into access from VB .Net

Sign up