Monday, January 8, 2007

How to insert a date field in a firebird database

Fist create a date field called "Today" in a firebird database. Then insert the following code in the inserting routine of a record in the database:

Dim ObjConnection As New FbConnection()
Dim connection As FbConnection = New FbConnection("servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\MyDb.gdb")
connection.Open()
' Start a local transaction.
Dim fbTran As FbTransaction = connection.BeginTransaction()
' Enlist the command in the current transaction.
Dim command As FbCommand = connection.CreateCommand()
Dim Sqlresult1 As Object
command.Transaction = fbTran
command.CommandText = "select cast('today' as date) from rdb$database"
Sqlresult1 = command.ExecuteScalar
command.CommandText = _
"insert into new_table1" _
& " (Today, Clienti, spesa, Domanda, Nota) values ('today','" & Replace(Me.TextBox1.Text, " '", "''") & "','" _
& Me.TextBox2.Text & ",'" & Replace(Me.TextBox3.Text, "'", "''") & "','" & _

Replace(Me.TextBox5.Text, "'", "''") & "')"
command.ExecuteNonQuery()
command.CommandText = _
"delete from contatore"
command.ExecuteNonQuery()
command.CommandText = _
"INSERT INTO Contatore(memcont) VALUES(GEN_ID(new_table1_CONTATORE_GEN, 0))"
command.ExecuteNonQuery()
fbTran.Commit()
Dim persRow As DataRow = Principale.DataSet1.Tables("contatore").NewRow
If IsDBNull(persRow("memcont")) Then
persRow("memcont") = Principale.DataSet1.Tables("New_table1").Rows.Count
End If
Principale.DataSet1.Tables("contatore").Rows.Add(persRow)
Dim CustomersRow As DataRow = Principale.DataSet1.Tables("contatore").Rows(0)
Dim newCustomersRow As DataRow = Principale.DataSet1.Tables("new_table1").NewRow()
newCustomersRow("contatore") = CustomersRow("MEMCONT") + 1
CustomersRow("MEMCONT") = newCustomersRow("contatore")
newCustomersRow("Today") = Sqlresult1
newCustomersRow("CLIENTI") = Me.TextBox1.Text
newCustomersRow("spesa") = Me.TextBox2.Text
newCustomersRow("Domanda") = Me.TextBox3.Text
newCustomersRow("Nota") = Me.TextBox5.Text
Principale.DataSet1.Tables("new_table1").Rows.Add(newCustomersRow)

No comments:

Post a Comment