winnu winnu - 5 months ago 12
Vb.net Question

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

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

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.