Re: simple vb.net database sample

• • Reply

  12 years ago

  by Judau_Ashta

  R. N. Infante

  Manila, Philippines

  Joined 14 years ago

   And Heres my code using Datatable, bindingsource and binding navigator control.

  Public Class frmCustomer
      Private Sub getData()
          If CreateDataTable("Select * from P3_CUSTOMER").Rows.Count = 0 Then
            'No Record
          Else
              bs.DataSource = CreateDataTable("Select * from P3_CUSTOMER")
              myGrid.DataSource = bs
              BN_Customer.BindingSource = bs
              txtCustomerID.DataBindings.Add("text", bs, "CustomerID")
              txtName.DataBindings.Add("text", bs, "BillToName")
              txtContact.DataBindings.Add("text", bs, "Contact")
              txtAddress.DataBindings.Add("text", bs, "BillToAddress1")
              txtTelephone.DataBindings.Add("text", bs, "Telephone1")
              txtFax.DataBindings.Add("text", bs, "Fax")
              txtEmail.DataBindings.Add("text", bs, "Email")
              BN_Customer.Enabled = True
              myGrid.Enabled = True
          End If
          myGrid.DefaultCellStyle.BackColor = Color.WhiteSmoke
          myGrid.AlternatingRowsDefaultCellStyle.BackColor = Color.White
      End Sub

  ' I use SQL query to save/update/Delete my Table. Heres my example to save another record

      Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
                  If ExecQuery("Insert Into P3_CUSTOMER Values('" + txtCustomerID.Text + "','" + txtName.Text + "', " & _
                  "'" + txtContact.Text + "','" + txtAddress.Text + "','" + txtTelephone.Text + "','" + txtFax.Text + "', " & _
                  "'" + txtEmail.Text + "')") = True Then
                      Call getData() 'To refresh Grid
                      MsgBox("You Have Succesfully Save New Record!", MsgBoxStyle.Information, "Customer Record")
                  Else
                      MsgBox("Error Saving!!", MsgBoxStyle.Exclamation, "Error!")
                  End If
      End Sub
  

  End Class 

   Module Module1
      Public Const ConnString As String = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=sa;Initial Catalog=P3_SAMPLE;Data Source=039-ERP-ROBERT"
      Public bs As New BindingSource
      Public Function CreateDataTable(ByVal xSQL As String) As DataTable
          Dim dt As DataTable = New DataTable
          Try
              Dim oConn As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConnString)
              Dim Adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(xSQL, ConnString)
              Adapter.Fill(dt)
              Adapter.Dispose()
              oConn.Close()
          Catch ex As Exception
              MsgBox("Error In DataBase Connection!", MsgBoxStyle.Critical, "Error In Database Connection!")
          End Try
          Return dt

      Public Function ExecQuery(ByVal xsql As String) As Boolean
          Try
              Using connection As New OleDb.OleDbConnection(ConnString)
                  Dim myCommand As New OleDb.OleDbCommand(xsql)
                  myCommand.Connection = connection
                  Try
                      connection.Open()
                      myCommand.ExecuteNonQuery()
                      Return True
                  Catch ex As Exception
                      Return False
                  End Try
              End Using
          Catch ex As Exception
              MsgBox("Error In Database Connection!", MsgBoxStyle.Critical, "Error!")
          End Try

      End Function

  End Module 

   

  I hope this can help you.. GoodLuck 

  Report abuse