How to INSERT UPDATE DELETE Rows from MS-Access Database

How to INSERT UPDATE  DELETE Rows from MS-Access Database

Here is the source code for all

 Imports System.Windows.Forms  
 Public Class NewRequestFrm  
   Dim EditReqNo As Long  
   Dim IsEditMode As Boolean = False  
   Dim RequestStatus As String = ""  
   Dim RequestCrDate As New DateTimePicker  
   Dim RequestAcceptDate As New DateTimePicker  
   Sub New(Optional ByVal editrequestno As Long = -1)  
     ' This call is required by the designer.  
     InitializeComponent()  
     ' Add any initialization after the InitializeComponent() call.  
     If editrequestno >= 0 Then  
       IsEditMode = True  
       EditReqNo = editrequestno  
     End If  
   End Sub  
   Sub LoadRequestValues()  
       Dim SQLstr As String = ""  
       SQLstr = "select * from requests where reqid=" & EditReqNo  
       Dim SqlConn As New OleDb.OleDbConnection  
       Try  
         Dim Sqlcmmd As New OleDb.OleDbCommand  
         SqlConn.ConnectionString = ConnectionStrinG  
         SqlConn.Open()  
         Sqlcmmd.Connection = SqlConn  
         Sqlcmmd.CommandText = SQLstr  
         Sqlcmmd.CommandType = CommandType.Text  
         Dim Sreader As OleDb.OleDbDataReader  
         Sreader = Sqlcmmd.ExecuteReader  
         While Sreader.Read()  
           TxtAssetName.Text = Sreader("AssetName").ToString  
           TxtDepartment.Text = Sreader("department")  
           TxtDateFrom.Value = Sreader("datefrom")  
           TxtDateTo.Value = Sreader("dateto")  
           TxtNotes.Text = Sreader("notes")  
           RequestStatus = Sreader("status")  
           RequestCrDate.Value = Sreader("reqdate")  
           RequestAcceptDate.Value = Sreader("aptdate")  
           TxtReqNo.Text = Sreader("reqid")  
           TxtReqRef.Text = Sreader("reqref")  
         End While  
         Sreader.Close()  
         Sreader = Nothing  
       Catch ex As Exception  
         MsgBox(ex.Message)  
       Finally  
         SqlConn.Close()  
         SqlConn.Dispose()  
       End Try  
    
   End Sub  
   Sub LoadDefaultvalues()  
     TxtAssetName.Text = ""  
     TxtDepartment.Text = ""  
     TxtReqNo.Text = ""  
     TxtReqRef.Text = ""  
     TxtDateFrom.Value = Today  
     TxtDateTo.Value = Today  
     TxtNotes.Text = ""  
     Dim astid As Long = 0  
     astid = SQLGetNumericFieldValue("select max(reqid)+1 as tot from requests", "tot")  
     LoadDataIntoComboBox("Select distinct department from assets ", TxtDepartment, "department")  
     LoadDataIntoComboBox("Select distinct assetname from assets ", TxtAssetName, "assetname")  
     TxtReqNo.Text = astid  
   End Sub  
   Private Sub BtnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClose.Click  
     Me.Close()  
   End Sub  
   Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click  
     If TxtAssetName.Text.Length = 0 Then  
       MsgBox("Please Select the Asset Name      ", MsgBoxStyle.Information)  
       TxtAssetName.Focus()  
       Exit Sub  
     End If  
     If TxtDepartment.Text.Length = 0 Then  
       MsgBox("Please Select the Department Name      ", MsgBoxStyle.Information)  
       TxtDepartment.Focus()  
       Exit Sub  
     End If  
     Dim StrValue As String = ""  
     If IsEditMode = True Then  
       If MsgBox("Do you want to Alter the Request ?   ", MsgBoxStyle.YesNo) = MsgBoxResult.No Then  
         Exit Sub  
       End If  
       If My.Settings.Issql = "Yes" Then  
         StrValue = "UPDATE requests SET assetid=@assetid,assetname=@assetname,department=@department,datefrom=@datefrom,dateto=@dateto,notes=@notes,status=@status,reqdate=@reqdate,aptdate=@aptdate,datefromvalue=@datefromvalue,datetovalue=@datetovalue,reqid=@reqid,reqref=@reqref, ReqDateValue=@ReqDateValue " _  
        & " where reqid=" & EditReqNo  
         SaveRequests(StrValue, EditReqNo)  
       Else  
         StrValue = "UPDATE requests SET assetid=" & EditReqNo & ",assetname='" & TxtAssetName.Text & "',department='" & TxtDepartment.Text & "',datefrom='" & TxtDateFrom.Value.Date & "',dateto='" & TxtDateTo.Value.Date & "'" _  
           & ",notes='" & TxtNotes.Text & "',status='" & RequestStatus & "',reqdate='" & RequestCrDate.Value.Date & "',aptdate='" & RequestAcceptDate.Value.Date & "'" _  
           & ",datefromvalue=" & TxtDateFrom.Value.Date.ToOADate & ",datetovalue=" & TxtDateTo.Value.Date.ToOADate & ",reqid=" & EditReqNo.ToString _  
           & ",reqref='" & TxtReqRef.Text & "',ReqDateValue=" & RequestCrDate.Value.Date.ToOADate & " where reqid=" & EditReqNo  
         ExecuteSQLQuery(StrValue)  
         Me.Close()  
       End If  
     Else  
       If MsgBox("Do you want to Submit the Request ?   ", MsgBoxStyle.YesNo) = MsgBoxResult.No Then  
         Exit Sub  
       End If  
       RequestStatus = "Pending"  
       RequestCrDate.Value = Today  
       RequestAcceptDate.Value = Today  
       Dim astid As Long = 0  
       astid = SQLGetNumericFieldValue("select max(reqid)+1 as tot from requests", "tot")  
       StrValue = "INSERT INTO [requests] (assetid,assetname,department,datefrom,dateto,notes,status,reqdate,aptdate,datefromvalue,datetovalue,reqid,reqref,ReqDateValue) values (@assetid,@assetname,@department,@datefrom,@dateto,@notes,@status,@reqdate,@aptdate,@datefromvalue,@datetovalue,@reqid,@reqref,@ReqDateValue)"  
       If My.Settings.Issql = "Yes" Then  
         SaveRequests(StrValue, astid)  
       Else  
         StrValue = "INSERT INTO [requests] (assetid,assetname,department,datefrom,dateto,notes,status,reqdate,aptdate,datefromvalue,datetovalue,reqid,reqref,ReqDateValue) values" _  
       & "(" & astid & ",'" & TxtAssetName.Text & "','" & TxtDepartment.Text & "','" & TxtDateFrom.Value.Date & "','" & TxtDateTo.Value.Date & "','" & TxtNotes.Text _  
       & "','" & RequestStatus & "','" & RequestCrDate.Value.Date & "','" & RequestAcceptDate.Value.Date _  
       & "'," & TxtDateFrom.Value.Date.ToOADate & "," & TxtDateTo.Value.Date.ToOADate & "," & astid & ",'" & TxtReqRef.Text & "'," & RequestCrDate.Value.Date.ToOADate & ")"  
         ExecuteSQLQuery(StrValue)  
       End If  
       LoadDefaultvalues()  
     End If  
   End Sub  
   Private Sub NewRequestFrm_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated  
     TxtReqNo.Focus()  
   End Sub  
   Sub SaveRequests(ByVal SqlString As String, ByVal AssetId As Integer)  
     If My.Settings.Issql = "Yes" Then
            Try
                MainSqlConn.ConnectionString = ConnectionStrinG
                MainSqlConn.Open()
                Dim DBF As New SqlClient.SqlCommand(SqlString, MainSqlConn)
                With DBF.Parameters
                    .AddWithValue("assetid", AssetId)
                    .AddWithValue("AssetName", TxtAssetName.Text)
                    .AddWithValue("department", TxtDepartment.Text)
                    .AddWithValue("datefrom", TxtDateFrom.Value.Date)
                    .AddWithValue("dateto", TxtDateTo.Value.Date)
                    .AddWithValue("notes", TxtNotes.Text)
                    .AddWithValue("status", RequestStatus)
                    .AddWithValue("reqdate", RequestCrDate.Value.Date)
                    .AddWithValue("ReqDateValue", RequestCrDate.Value.Date.ToOADate)
                    .AddWithValue("aptdate", RequestAcceptDate.Value.Date)
                    .AddWithValue("datefromvalue", TxtDateFrom.Value.Date.ToOADate)
                    .AddWithValue("datetovalue", TxtDateTo.Value.Date.ToOADate)
                    .AddWithValue("reqid", AssetId)
                    .AddWithValue("reqref", TxtReqRef.Text)


                End With
                DBF.ExecuteNonQuery()
                DBF = Nothing
                MainSqlConn.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Else
            Try
                MainSqlConnOLE.ConnectionString = ConnectionStrinG
                MainSqlConnOLE.Open()
                Dim DBF As New OleDb.OleDbCommand(SqlString, MainSqlConnOLE)
                With DBF.Parameters
                    .AddWithValue("assetid", AssetId)
                    .AddWithValue("AssetName", TxtAssetName.Text)
                    .AddWithValue("department", TxtDepartment.Text)
                    .AddWithValue("datefrom", "'" & FormatDateTime(TxtDateFrom.Value.Date, DateFormat.ShortDate).ToString & "'")
                    .AddWithValue("dateto", "'" & FormatDateTime(TxtDateTo.Value.Date, DateFormat.ShortDate).ToString & "'")
                    .AddWithValue("notes", TxtNotes.Text)
                    .AddWithValue("status", RequestStatus)
                    .AddWithValue("reqdate", "'" & FormatDateTime(RequestCrDate.Value.Date, DateFormat.ShortDate).ToString & "'")
                    .AddWithValue("ReqDateValue", RequestCrDate.Value.Date.ToOADate)
                    .AddWithValue("aptdate", "'" & FormatDateTime(RequestAcceptDate.Value.Date, DateFormat.ShortDate).ToString & "'")
                    .AddWithValue("datefromvalue", TxtDateFrom.Value.Date.ToOADate)
                    .AddWithValue("datetovalue", TxtDateTo.Value.Date.ToOADate)
                    .AddWithValue("reqid", AssetId)
                    .AddWithValue("reqref", TxtReqRef.Text)
                End With
                DBF.ExecuteNonQuery()
                DBF = Nothing
                MainSqlConnOLE.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If
   End Sub  
   Private Sub NewRequestFrm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
     LoadDefaultvalues()  
     If IsEditMode = True Then  
       LoadRequestValues()  
     End If  
   End Sub  
 End Class