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
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.