I make the example of a table with 2 fields for semplicity: for the text fields use the type varchar which allows a better positioning of the cursor in edit mode.
First add to the project the "Firebirdclient-ADO NET xx Data Provider" reference.
In the connection string use servertype=1 for embedded server and servertype=0 for client server application.
The Dataset is a component of VB.net and is not typified. The field “Clienti” is a field varchar. For “Spesa” field I have chosen the type smallint. We'll use 3 winforms for the project: "MainForm", "NewForm", "EditForm".
The operations are there described with comments at the code:
Imports FirebirdSql.Data.FirebirdClient
__________________________________________________________________
Public Class MainForm
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim Fdataa As New FbDataAdapter("select*from New_table1", "servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\Giorgio.gdb")
DataSet1.Tables.Add("your_table")
Me.DataGridView1.DataSource = DataSet1
Me.DataGridView1.DataMember = "your_table"
Fdataa.Fill(DataSet1, "your_table")
End Sub
'To begin the change
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Open EditForm
EditForm.TextBox1.Text =
Microsoft.VisualBasic.Trim(DataGridView1.CurrentRow.Cells(0).Value)
EditForm.TextBox2.Text = DataGridView1.CurrentRow.Cells(1).Value
EditForm.Show()
End Sub
'To delete a record
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim mResult
Dim ObjConnection As New FbConnection()
mResult = MsgBox("Do you want to delete this row?", _
vbYesNo + vbQuestion, "Conferma eliminazione")
If mResult = vbNo Then
Exit Sub
End If
ObjConnection.ConnectionString = "servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "yourdb.gdb"
ObjConnection.Open()
Try
Dim ObjCommand As New FbCommand()
ObjCommand.Connection = ObjConnection
ObjCommand.CommandText = "delete from New_table1 where Clients='" & Me.DataGridView1.CurrentRow.Cells(0).Value & "'"
ObjCommand.ExecuteNonQuery()
Finally
ObjConnection.Close()
End Try
Me.DataGridView1.Rows.Remove(Me.DataGridView1.CurrentRow)
End Sub
End Class
'To update a record
Imports FirebirdSql.Data.FirebirdClient
______________________________________________________________
Public Class EditForm
Private Sub btnAggiorna_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAggiorna.Click
Dim customerRow() = MainForm.DataSet1.Tables("your_table").Select("Clients ='" & MainForm.DataGridView1.CurrentRow.Cells(0).Value & "'")
Dim ObjConnection As New FbConnection()
ObjConnection.ConnectionString = "servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\yourdb.gdb"
ObjConnection.Open()
Try
Dim ObjCommand As New FbCommand()
ObjCommand.Connection = ObjConnection
ObjCommand.CommandText = "update New_table1 set Clients='" & Me.TextBox1.Text & "', Spesa='" & Me.TextBox2.Text & "' where Clients='" &
MainForm.DataGridView1.CurrentRow.Cells(0).Value & "'"
ObjCommand.ExecuteNonQuery()
Dim customerRow() = Note.DataSet1.Tables("Note").Select("Contatore ='" & Note.DataGridView1.CurrentRow.Cells("Contatore").Value & "'")
customerRow(0)("Titolo") = Me.TextBox1.Text
customerRow(0)("nota") = Me.TextBox2.Text
Note.DataGridView1.Columns("titolo").HeaderText = yourtitle
Note.DataGridView1.Columns("Nota").HeaderText = yournote
Note.DataGridView1.Columns("contatore").Visible = False
ObjConnection.Close()
Me.Close()
MainForm.Show()
End Class
'To insert e new record
In MainForm:
Private Sub btnNuovo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNuovo.Click
NewForm.Show()
End Sub
Imports FirebirdSql.Data.FirebirdClient
____________________________________________________________
Public Class NewForm
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ObjConnection As New FbConnection()
ObjConnection.ConnectionString = "servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\yourdb.gdb"
ObjConnection.Open()
Try
Dim ObjCommand As New FbCommand()
ObjCommand.Connection = ObjConnection
ObjCommand.CommandText = "insert into New_table1 (Clients, Spending) values ('" & TextBox1.Text "','" & TextBox2.Text & "')"
& Replace(Me.TextBox2.Text, "'", "''") & "')"
command.ExecuteNonQuery()
command.CommandText = _
"select max(counter) as maxcount from note"
command.ExecuteNonQuery()
fbTran.Commit()
Dim newCustomersRow As DataRow = Note.DataSet1.Tables("yourtable").NewRow()
Dim dr As FbDataReader = command.ExecuteReader
dr.Read()
newCustomersRow("counter") = dr.Item("maxcount")
newCustomersRow("Title") = Me.TextBox1.Text
newCustomersRow("Note") = Me.TextBox2.Text
Note.DataSet1.Tables("yourtable").Rows.Add(newCustomersRow)
connection.Close()
Me.Close()
MainForm.Show()
End Sub
End Class
This code is worth for application not connected in net.
For an application connected in net only change the things about which we have before spoken.
Finally you can find in this blog a complete firebird-vb.net project at this address: http://galileo2007.altervista.org/downloads.htm. Search for 'Block Notes.zip' and download it. In this Project, in application folder, you'll find all libraries needed and FirebirdSql.Data.FirebirdClient.dll
No comments
Post a Comment