Firebird - VB.net

Refresh Datagridview after update, insert. Resolved

2011-10-30T11:47:00.001+01:00

All names are in italian language. As client you can choose an Oledb client instead of Fb client.
As connection string, for embedded server, you can use this one: strConn= "servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\YourDB.gdb"

We'll name the application main form "MainForm". First, in main form class of your project insert this routine:

    Public Sub GetData(ByVal strSQL As String, ByVal Table As String)

        Dim com As New FbCommand
        Dim adap As New FbDataAdapter
        Dim ds As New DataSet

        Dim connection As FbConnection = New FbConnection(strConn)
        connection.Open()

        adap = New FbDataAdapter(strSQL, strConn)
        adap.Fill(ds, Table)
        Me.DataGridView1.DataSource = ds.Tables(Table)
        connection.Close()
    End Sub

As edit routine use this code:

        ObjConnection.Open()
        Try
            Dim ObjCommand As New FbCommand()
            ObjCommand.Connection = ObjConnection

            ObjCommand.CommandText = "update YourTable set contesto='" & Replace(Me.txtDidascalia.Text, "'", "''") & "', Percorso='" & Replace(Me.txtPercorso.Text, "'", "''") & "'where contatore='" & MaiForm.DataGridView1.CurrentRow.Cells("Contatore").Value & "'"
            ObjCommand.ExecuteNonQuery()
        Catch
            MsgBox("Percorso del file non univoco", MsgBoxStyle.Critical, "Errore di immisione")
            Exit Sub
        Finally
MainForm.GetData("select*from YourTable", "YourTable")

' Columns names are in italian language

MainForm.DataGridView1.Columns("Contesto").HeaderText = "TITLE"
            MainForm.DataGridView1.Columns("Percorso").Visible = False
            MainForm.DataGridView1.Columns("Contesto").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill

        End Try
        Me.Close()

As insert routine we'll use code:

Dim ObjConnection As New FbConnection()
        Dim connection As FbConnection = New FbConnection("servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\YourDB.gdb")
        connection.Open()

        ' Start a local transaction.

        ' Enlist the command in the current transaction.
        Dim command As FbCommand = connection.CreateCommand()

        Try
            command.CommandText = _
              "insert into Immobili" _
            & " (Contesto, Percorso) values ('" & Replace(Me.txtDidascalia.Text, "'", "''") & "','" _
             & Replace(Me.txtPercorso.Text, "'", "''") & "')"
            command.ExecuteNonQuery()

        Catch
            MsgBox("Questo titolo di immagine esiste già. Cambia il titolo")

            Exit Sub
        End Try

        ObjConnection.Close()
        MainForm.GetData("Select*from YourTable order by contesto", "YourTable")

Me.Close()

Goodbye

Remove empty rows in text files? Solved

2011-10-29T11:07:00.000+01:00

A small code snippet may be very useful in many situations to remove empty rows in text files. Here is the code:
First: in Form1 insert a label by which you indicate to user the directory where is the converted file: ie C:\beta.tex
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim objFileToRead, objFileToWrite, objFSO, strPathWrite
        Try
            OpenFileDialog1.FileName = ""
            OpenFileDialog1.Filter = "Text files .txt|*.txt"
            Me.OpenFileDialog1.ShowDialog()
            strPathWrite = "C:\beta.txt"

            objFSO = CreateObject("Scripting.FileSystemObject")

            objFileToRead = objFSO.OpenTextFile(OpenFileDialog1.FileName, 1)
            objFileToWrite = objFSO.OpenTextFile(strPathWrite, 2, True)

            Dim strTemp
            Do While Not objFileToRead.AtEndOfStream
                strTemp = objFileToRead.ReadLine
                If strTemp <> "" Then
                    objFileToWrite.WriteLine(strTemp)
                End If
            Loop

            objFileToRead.Close()
            objFileToWrite.Close()
            objFileToRead = Nothing
            objFileToWrite = Nothing
            objFSO = Nothing

        Catch
            Me.Label1.Text = "You haven't chosed any file"
            Me.Label1.TextAlign = Drawing.ContentAlignment.MiddleCenter
            Me.Label1.Left = (Me.Width - Me.Label1.Width) / 2
        End Try

    End Sub

Can't install .net framework 3.5? Solved

2011-04-12T11:53:00.002+01:00

Updated

Many people can't install .net framework 3.5. And I was one of them. After many attempts I solved the issue.
I downloaded the updated Aaron Stebner "dotfix cleanup tool" from here in the page top left and launched it. This tool clean your system from any net. framework trace. After cleanup you can safely install the .net framework 3.5. Maybe the Aaron Stebner dotfix cleanup tool fails at the first attempt.
Then follow these steps:

Download "Free Uninstaller 1.1" form this link: http://www.anarchia.com/link_in_frame.php?link=4906&c=
Free Uninstaller 1.1 is a zip package. Doesn't need installation.
Double click on it
Will appear some registry keys red highlighted on the top of the window
The red highlighted registry keys are invalid registry keys. Remove them.
Retry with the Aaron Stebner dotfix cleanup.

Now of course you'll be able to install .net framework 3.5.

Update datagridview with numeric primary key. Solved

2011-04-05T15:16:00.000+01:00

The issue, typical in Firebird, consist in the fact that, after you insert a new record with an autoincrement field in a datagridview, we have to exit from the application to be able to edit the new inserted record.

To solve this issue you have to proceed this way:
1) Proceeding for semplicity with "SQL Manager 2010 lite" software, name the autoincrement field as "Counter"
2) In the database of interest create a new table called for example "Counter" with only a field named "memcont", type Integer.
3) To insert a record use this routine:
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ObjConnection As New FbConnection()
Dim connection As FbConnection = New
' If we assume an embedded server
FbConnection("servertype=1;username=sysdba;password=masterkey;database=" _

& My.Application.Info.DirectoryPath & "\mydb.gdb")
connection.Open()

' Starts a local transaction.
Dim fbTran As FbTransaction = connection.BeginTransaction()

' Enlist the command in the current transaction.
Dim command As FbCommand = connection.CreateCommand()
command.Transaction = fbTran

Try
command.CommandText = _
"insert into new_table1" _
& " (Clienti, spesa) values ('" & Me.TextBox1.Text & "','"

_
& Me.TextBox2.Text & "')"
command.ExecuteNonQuery()
command.CommandText = _
"delete from counter"
command.ExecuteNonQuery()
'There we make use of the generator GEN_ID function of Firebird with no increment
command.CommandText = _
"INSERT INTO counter(memcont)
VALUES(GEN_ID(new_table1_COUNTER_GEN, 0))"
command.ExecuteNonQuery()
fbTran.Commit()Catch ex As Exception
MsgBox("Insert a Integer number for field spesa")

fbTran.Rollback()Finally
ObjConnection.Close()
End Try

'There we create an instance of the new row in the datatable
Dim persRow As DataRow = Principale.DataSet1.Tables("counter").NewRow
If IsDBNull(persRow("memcont")) Then
persRow("memcont") = Principale.DataSet1.Tables("New_table1").Rows.Count
End If
Principale.DataSet1.Tables("counter").Rows.Add(persRow)
'There we create an instance of the row 0 of the "counter" datatable

Dim CustomersRow As DataRow = Principale.DataSet1.Tables("counter").Rows(0)
'There we create an instance of the new row of the "New_table1" datatable
Dim newCustomersRow As DataRow = Principale.DataSet1.Tables("new_table1").NewRow()
' The autoincremnt field value musts reflect the "memcont" value +1
newCustomersRow("counter") = CustomersRow("MEMCONT") + 1
' there we assign to the unique record in the field "memcont" of the datatable "counter" the
new value of the autoincrement field
CustomersRow("MEMCONT") = newCustomersRow("counter")
newCustomersRow("CLIENTI") = Me.TextBox1.Text
newCustomersRow("spesa") = Me.TextBox2.Text

Principale.DataSet1.Tables("new_table1").Rows.Add(newCustomersRow)

Me.Close()

End Sub

Vb.net shows error but application works? How to solve the problem

2010-11-27T10:09:00.002+01:00

Sometimes the datagridview control show error but the application works normally.
in fact in these cases is sufficient to close the error message and the application works normally.
However one wants that the error message isn't showed.
To eliminate the error message:

Call the datagridview dataerror event
Insert in the event routine this code: e.Cancel = True

Goodbye

How to manage a firebird database by fbembed.dll

2010-11-13T17:07:00.001+01:00

There is around a very good tool to manage firebird databases. Its name is SQL Manager 2010 Lite for Interbase and Firebird. You can downdoad it for free from http://www.softpedia.com/progDownload/EMS-IB-FB-Manager-Lite-Download-22779.html
To manage any database install SQL Manager 2010 Lite for Interbase and Firebird.
Now register a host:

I the toolbar click on "Register host" icon or from "Database" menu click on "Register host"
Will appear the "Register Host wizard"
From the "Host name" dropdown select "embedded" item. Insert username and password
In the "Client library" dropdown browse for the fbembed.dll and select it. Next.
Skip "Path to database utility". Click "Finish"

Now you can create databases, tables, execute sql scripts, etc.. in visual mode

Using the MS ReportViewer control. Code. Displaiing search results

2010-10-29T12:16:00.004+01:00

Private Sub AnteRicercaToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RicercaTipoAnteToolStripMenuItem.Click

If Me.txtTipo.Text = "" Or Me.txtRicerca.Text = "" Then
MsgBox("Effettuare prima la ricerca", MsgBoxStyle.Critical)
Exit Sub

End If

Report(My.Application.Info.DirectoryPath & "\AnteRicercaTipo.rdl", "Biblioteca")

RepTipo.ReportViewer1.Refresh()

RepTipo.Show()

End Sub

Private Sub Report(ByVal filename As String, ByVal table As String)

Dim RepDS As New ReportDataSource

Dim dv As New System.Data.DataView

dv = DataSet1.Tables(table).DefaultView

RepDS.Name = "DataSource1"
RepDS.Value = dv
RepTipo.ReportViewer1.LocalReport.DataSources.Clear()
RepTipo.ReportViewer1.LocalReport.DataSources.Add(RepDS)

RepTipo.ReportViewer1.LocalReport.ReportPath = filename

End Sub

Trying in google you will be able to find much documentation on how creating a report. In particular I council this link for customizining layout of report and the this link for a practice with ReportViewer.
If you have of the problems with these articles you can contact me obviously.
N.B.
Currently my acquaintances are limited to the type of report which I have created. Perhaps in future I will widen my acquaintances.

VC 2008. Api win32. Errors with winbase.h? Problem solved

2010-06-05T19:07:00.000+01:00

Hello friends,
this morning it happened a strange thing with all my win32 Api projects. Earlier they worked, but this morning the Visual C++ 2008 compiler was getting about a hundred of errors. Then I reinstalled Visual C++ 2008, but the same errors.
Eventually I have seen that the WinBase.h in ..\Program Files\Microsoft SDKs\Windows\v6.0A\Include\ directory was corrupted. Then I got the valid file WinBase.h. You download it by http://galileo2007.altervista.org/downloads.htm. The file is contained in WinBase.zip. Paste it just in ..\Program Files\Microsoft SDKs\Windows\v6.0A\Include\ directory.
Translated from: http://amici-di-galileo.blogspot.com/2010/03/api-win32-vc-2008-errori-dovuti.html

Api win32. "Application has failed to start because the applicati..

2010-06-05T16:47:00.007+01:00

Hello friends,
sometimes I need light applications which, say, must start at windows startup. In these cases I recur to the win32 api . But, with Visual c++ 2008 I encountered a difficulty which appeared to be very hard to resolve: when I launched an api win32 application on a machine without Visual c++ 2008, the application showed error and I was requested of MSVCR90D.dll and MSVCR90.dll. On a winxp machine the message was "Application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem."
Finally I solved the issue this way:
In release mode:
1)Project menu>Project properties>(if you don't see tree menu scroll toward left)Linker>Link Library Dependencies>No
2)Project menu>Project properties>C/C++>Code Generation>Runtime Library>Multithread (/MT). (This way ). This setting produces a very light executable working in all machines
You have to work always in release mode, because the application in release mode may crash whereas in debug mode it compiles.
Rarely may occur that, in release mode, the application compiles but crashes without
noticing any error. In this case switch to debug mode and you will have more information about the errors. After the debug, switch over again to release mode.

Api win32. Problem solved with MSVCR90D.dll and MSVCR90.dll

2010-02-23T11:38:00.009+01:00

Hello friends,
sometimes I need light applications which, say, must start at windows startup. In thees cases I recur to the win32 apis . But, with Visual c++ 2008 I encountered a difficulty which appeared to be very hard to resolve: when I launched an api win32 application on a machine without Visual c++ 2008, the application showed error and I was requested of MSVCR90D.dll e MSVCR90.dll. Finally I solved the issue this way:
In release mode:
1)Project menu>Project properties>(if you don't see tree menu scroll toward left)Linker>Link Library Dependencies>No
2)Project menu>Project properties>C/C++>Code Generation>Runtime Library>Multithread (/MT). (This way ). This setting results a very light executable working in all machines
You have to work always in release mode, because the application in release mode may crash whereas in debug mode it compiles.
Rarely may occur that, in release mode, the application compiles but crashes without
noticing any error. In this case switch to debug mode and you will have more information about the errors. After the debug, switch over again to release mode.
Goodbye!
Translated from: http://amici-di-galileo.blogspot.com/2010/02/api-win32-problema-risolto-con-le_21.html

Getting started with Express Reporting Services 2005

2009-12-24T17:46:00.005+01:00

Downloading and installation

Download SQLEXPR_TOOLKIT.EXE from this page after you selected the languag
Download and install ODBC Firebird driver from here.
Create a DSN: see here
After the SQLEXPR_TOOLKIT.EXE the start menu directory, between the others, the voice Microsoft Sql Server 2005> Sql Server Business Development Intelligence Studio. Clic on it.
Will be opened “Visual Studio 2005” IDE . Up in the Standard bar of the instruments clic on New Project, or in the Initial Page clic on Create Project. Give a name to the new project.
Will appear "New Project" window. In the left pane select "Business Intelligence Projects". In the right pane select "Server Report" wizard. Give e name tho the project. Ok. Now you'll see report wizard window. Clic next.
Now you have to set Data origin. Give it a name. From the combo "Type" select ODBC. Clic "edit". It will appear Connection properties window. Select "use connection string". Clic on "Generate" button. Ok. Will appear the connection string window. Next. Will appear Query project window. Write a simple query: select*from YourTable.
Next. In the window Select type of report select, for simplicity, Table. Next. In the window Planning Table the fields of the table will appear to you, on the left side. Leave all as it is. Next. In the window Chose table style you can choose between the types of style listed, but it is clear that if you will not follow the guided procedure you can personalize as you want the styles of the report. Next. In the window Completion wizard you may give a name to your report. Therefore the end.
To add a new report, in explorer solutions select Report folder and right clic Add report. you'll see report wizard window. Clic next. Now follow the steps from 9 to 10.

Therefore you have created your first one report with the Reporting Services. In the http://firebird-vbnet.blogspot.com/2010/10/using-ms-reportviewer-control-code.html argument we will see how to use the report with the control reportviewer.

Reporting Services 2008 don't support reportviewer add in

2009-12-18T17:13:00.003+01:00

I have realized that Reporting Services 2008 don't support reportviewer add in. Particularly isn't possible pass the report parameters to the reportviewer because arises an exception for the setparameters method.

using report parameters in SSRS 2005

2009-12-02T17:15:00.017+01:00

In this post we'll use report parameters to center an image in a reportviewer control.

In the report project create the file Picture.rdl (SSRS 2008 don't support any reportviewer add in with reference to report parameters, this is a bug). To create a file rdl connected to firbird ODBC let see: Getting started with Express Reporting Services 2005
In the rdl file create two controls: image control and textbox control.
From the menu choose Report > Report Parameters command. Press Add to add a parameter.The value of a parameter can be used in expressions as Parameters!Foo.Value where Foo is the name of the parameter.
To center an image in the report we'll make use of the image control "padding" property, because it lets use expressions.
The "source" property have to be set: external
The "sizing" property have to be set: fitProportional
In order to select and center the image in the image control we'll make use of three parameters:
P (text type)
PadLeft (Integer type in points)
PadRight (Integer type in points)

As value of the left padding we have: =Parameters!PadLeft.value & "pt"
As value of right padding we have: =Parameters!PadRight.value & "pt"
As value of Value property we have: "file:///" & Parameters!P.Value

In order to write a caption we'll add to the report a large textbox control
In order to write a caption in the textbox control we'll make use of a parameter:
Caption
As value of Value property we have: =Parameters!Caption.Value
to show the report in Visual Basic we'll make use of a form with a reportviewer control in it.

In the Fprincipale form the code to show the image and caption in a reportviewer control is:
Imports FirebirdSql.Data.FirebirdClient
Imports Microsoft.Reporting.WinForms

Private Sub Report(ByVal filename As String) ShowReport.ReportViewer1.LocalReport.EnableExternalImages = True
Dim strImage, strContesto As String, strPadleft, strPadRight As Integer
Dim params(3) As ReportParameter
strImage = Me.DataGridView1.CurrentRow.Cells("Percorso").Value
strContesto = Me.DataGridView1.CurrentRow.Cells("Contesto").Value
image1 = New Bitmap(strImage, True)
If (image1.Width / 72) * 2.54 > 17 Or (image1.Height / 72) * 2.54 > 12.25 Then
strPadleft = (17 / 2.54 - 12.25 / 2.54 * (image1.Width / image1.HorizontalResolution) / (image1.Height / image1.VerticalResolution)) * 72 / 2
in this case the left and right padding have to be set so that the new image.width is resized by the ratio (12.25 / 2.54)/(image1.Height / image1.VerticalResolution).
strPadRight = strPadleft
Else
strPadleft = (17 / 2.54 - (image1.Width / 96)) * 72 / 2
in this case the image is resized with reference to screen resolution 96 DPI that is set by Windows OS. See Computer monitor DPI standards.
When th DPI image resolution is lower than the computer monitor DPI standard, the image appears smaller with reference to its printer sizes. Then, since the image control fits proportionally an image with a given DPI resolution, we, to see the real image size in reportviewer, need to refer to computer monitor DPI standard. Then say, 72*96/72=96, that is the number that apperars in the strPadLeft expression.

strPadRight = strPadleft
End If
params(0) = New ReportParameter("P", strImage, False)
params(1) = New ReportParameter("Didascalia", strContesto, False)
params(2) = New ReportParameter("PadLeft", strPadleft, False)
params(3) = New ReportParameter("PadRight", strPadRight, False) ShowReport.ReportViewer1.LocalReport.ReportPath = filename ShowReport.ReportViewer1.LocalReport.SetParameters(params)
Dim RepDS As New ReportDataSource
Dim dv As New System.Data.DataView
dv = DataSet1.Tables("Immobili").DefaultView
RepDS.Name = "DataSource1" RepDS.Value = dv
ShowReport.ReportViewer1.LocalReport.DataSources.Clear() ShowReport.ReportViewer1.LocalReport.DataSources.Add(RepDS) ShowReport.ReportViewer1.LocalReport.ReportPath = filename
End Sub

Private Sub btnReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReport.Click
Try
Report(My.Application.Info.DirectoryPath & "\Picture.rdl")
Catch
MsgBox("Select a record")
Exit Sub
End Try
ShowReport.Show()
End Sub
The related rdl report (Picture.zip) can be dowlaoded here

IIS show error running ASP.net pages? Solved

2009-10-21T12:18:00.002+01:00

Hello friends,
I noticed my 5.1 IIS on windows xp not showing aspx pages. After some troubling, I found the cause of the issue: I needed uninstall and reinstall the .net framework.
If you have many versions of framework installed on your machine you need a working unistalling freeware tool: dotnetfx cleanup. Here's the download page: http://www.filestube.com/b7b587c2f858fd0203e9,g/dotnetfx-cleanup-tool.html

Howto create a DSN

2009-10-19T11:13:00.015+01:00

Follow the next procedure: close VWD if opened.
First case. Client Server structure:
First install a Firebird ODBC build from Firebirdsql.org.
Start>Settings>control Panel>Administrative tools>Data source OBDC>"User DSN" tab>Add button>Select Firebird/Interbase(r) driver>in the textbox "Data source name" insert a cutom name>In Database textbox browse your database>In client DLL browse fbclient.dll from your Firebird Application folder>In user database textbox insert database admin username>in "password" textbox insert admin password>In "character set" combobox select NONE. Now test the connectiion>Press the confirm button.
Second case. Embedded server:
First install a Firebird ODBC build from Firebirdsql.org.
Seconf doowload embedded server package from Firebirdsql.org. Unpack it in a custom folder.
Start>Settings>control Panel>Administrative tools>Data source OBDC>"User DSN" tab>Add button>Select Firebird/Interbase(r) driver>in the textbox "Data source name" insert a cutom name>In Database textbox browse your database>In client DLL browse for fbembed.dll from the custom folder in which the embedded server has been unpacked>In user database textbox insert database admin username>in "password" textbox insert admin password>In "character set" combobox select NONE. Now test the connection>Press the confirm button.

Goodbye.

Establishing a data connection for Asp.net page

2009-10-15T09:46:00.007+01:00

With Visual Web Developer, from Microsoft:

Create an ODBC data source for the database
In default.aspx web form drag and drop a Gridview control
Click on the top right of the Gridview control. Select "new data source". On the page will appear a window with various icons. Select "Database". Click OK.
Now 'll appear SqlDataSource1 control. Click on "New connection" button.
It'll appear a window. Click on Edit button and select "Other". In the label It'll appear ".Net Framework Data Provider for ODBC"
Below click the option button "Use connection string". Click on "Generate.." button, and select the tab "Computer data source". Then select your data source name. Then OK. Next.
Click on the "Specify a stored procedure or a customized SQL statement" option button. Otherwise you may get a token error message because of the square brackets for a string without white spaces.
You may use "select" statement. Write the select statement. Next. Click on "Test query" button.
Now you should see your table.
For further test start the debug.

Resize form, controls, fonts

2009-05-21T09:33:00.030+01:00

Updated.
You have to design the form with its size relative to minimum user working area, because, at resolutions lower than this minimum, as you can see, the form may be forcedly resized and the code cannot yet work.
At bottom of the form load event you have to put: ResizeFormClass.SubResize(Me, (form width value/Screen.PrimaryScreen.WorkingArea.Width value) * 100, (form height value / Screen.PrimaryScreen.WorkingArea.Height value) * 100), at minimum user working area.
For example, with minimum working area width=1024 ad minimum working area height=738 the code resizes right at 1152x864, 1280x1024 and at larger resolutions.

If the form is maximized, below "ResizeFormClass.SubResize(Me, form width value / Screen.PrimaryScreen.WorkingArea.Width), form height value / Screen.PrimaryScreen.WorkingArea.Height)" put "Me.WindowState = FormWindowState.Maximized", only at runtime.

For an example see Resize.zip at http://galileo2007.altervista.org/downloads.htm
In this example the minumum resolution assumed is 1024x780
To resize, in a module paste the code below:

Module Module1

Public Class ResizeFormClass
'Original form width.
Private Shared m_FormWidth As Long
Private Shared m_FormHeight As Long

Public Shared Sub SubResize(ByVal F As Form, ByVal percentW As Double, ByVal percentH As Double)
Dim FormHeight As Long
Dim FormWidth As Long
Dim HeightChange As Double, WidthChange As Double

Call SaveInitialStates(F)

'Calculate the new height and width the form needs to be resized to, based on the current avaible screen area.
FormHeight = Int((Screen.PrimaryScreen.WorkingArea.Height) * (percentH / 100))
FormWidth = Int((Screen.PrimaryScreen.WorkingArea.Width) * (percentW / 100))

'Use the Form that is to be resized.
With F
'Change the demensions and position of the form.

.Height = FormHeight
.Width = FormWidth

HeightChange = .ClientSize.Height / m_FormHeight
WidthChange = .ClientSize.Width / m_FormWidth

End With
'Calculate ratio current avaible screen area/form size

'Notify the class that the form has been resized.
SubChangeWithRatio(F, WidthChange, HeightChange)

End Sub

Private Shared Sub SaveInitialStates(ByVal F As Form)

'Use the form that is being resized.
With F
'Check if the form is a MDI form.

'Set the FormWidth and FormHeight variables to the Form's Scale Width and Height.
m_FormWidth = .ClientSize.Width
m_FormHeight = .ClientSize.Height

End With

End Sub

Public Shared Sub SubChangeWithRatio(ByVal F As Form, ByVal RapportoW As Single, ByVal RapportoH As Single)
'uses a recursive routine
For Each ctl As Control In F.Controls
ResizeControlAndIncludedControls(ctl, RapportoW, RapportoH)
Next

End Sub

Private Shared Sub ResizeControlAndIncludedControls(ByRef ctl As Control, ByVal RapportoW As Single, ByVal RapportoH As Single)

Dim ChildCtl As Control

For Each ChildCtl In ctl.Controls

ResizeControlAndIncludedControls(ChildCtl, RapportoW, RapportoH)

Next
ResizeControl(ctl, RapportoW, RapportoH)
End Sub

Private Shared Sub ResizeControl(ByRef ctl As Control, ByVal RapportoW As Single, ByVal RapportoH As Single)
Dim lb As New ListBox, intlH As Boolean
Try
If TypeOf ctl Is ListBox Then

lb = CType(ctl, ListBox)
intlH = lb.IntegralHeight
lb.IntegralHeight = False

ctl.Left = CInt(ctl.Left * RapportoW)
ctl.Top = CInt(ctl.Top * RapportoH)
ctl.Width = CInt(ctl.Width * RapportoW)
ctl.Height = CInt(ctl.Height * RapportoH)

Else

ctl.Left = CInt(ctl.Left * RapportoW)
ctl.Top = CInt(ctl.Top * RapportoH)
ctl.Width = CInt(ctl.Width * RapportoW)
ctl.Height = CInt(ctl.Height * RapportoH)

End If

lb.IntegralHeight = intlH
If TypeOf ctl Is ListView Then
Try
ResizeColumns(ctl, RapportoW, RapportoH)
Catch ex As Exception
End Try
End If
Try
ResizeControlFont(ctl, RapportoW, RapportoH)

Catch ex As Exception
End Try
Catch ex As Exception
End Try

End Sub

Private Shared Sub ResizeControlFont(ByRef Ct As Control, ByVal RapportoW As Single, ByVal RapportoH As Single)

'Resizes the control font and, in the case of some controls, as the listview
' resizes the columns also

Try

Dim FSize As Double = Ct.Font.Size
Dim FStile As FontStyle = Ct.Font.Style
Dim FNome As String = Ct.Font.Name
Dim NuovoSize As Double = FSize

NuovoSize = FSize * Math.Sqrt(RapportoW * RapportoH)
Dim NFont As New Font(FNome, CSng(NuovoSize), FStile)
Ct.Font = NFont

Catch

End Try

End Sub

Private Shared Sub ResizeColumns(ByRef ct As Control, ByVal RapportoW As Single, ByVal RapportoH As Single)

Dim c As ColumnHeader
For Each c In CType(ct, ListView).Columns
c.Width = CInt(c.Width * RapportoW)
Next

End Sub
End Class
End Module

Found two bugs in Visual Basic express + SP1

2007-03-13T11:17:00.000+01:00

The properties rows of datagridview and visiblerows.count of datagrid
mark 1 plus record. Done my signal to Microsoft.

Dbms Firebird and Vb.net

2007-03-10T13:00:00.012+01:00

Warning: this article has been modified on 17 February 2007

Dbms the Firebird offers excellent performances also regarding other famous dbms. Moreover it is free, it's wheight (very light) is around 14 mega. It has a client-server structure and as far as instructions SQL they follow the international standards.
For a comparison with others dbms see here
The last setup for the classic server and superserver can be dowloaded here.
Firebird database can work also without main Server installed. In fact there is the embedded version of the server which you can download from here. The embedded version (very light) is particularly suitable for standalone applications. For the libraries and folder you which have to put in the target application folder see http://firebird-vbnet.blogspot.com/2007/02/solved-issue-unable-to-load-dll-fbembed.html
To work with .net applications Firebird needs the .net provider which you can download here.
Now is more easy to insert indexes and primary keys in the tables.
As editors of SQL there are free applications. One freeware is SQL Manager 2010 lite for InterBase/Firebird. You can download it for free from: from http://www.softpedia.com/progDownload/EMS-IB-FB-Manager-Lite-Download-22779.html. The other is IBEasy+ 1.5.2 from here. Once installed IBEasy+ 1.5.2 I recommend to put in its application directory fbembed.dll, icuu30.dll, icuin30.dll and the icudt30.dll of an updated embedded firebird package.
IBEasy+ 1.5.2 may is useful to import database from other dbms. I council to import from Excel stylesheets. But we'll see how in this post: http://firebird-vbnet.blogspot.com/2007/02/from-mdf-to-gdb-with-free-software.html.
SQL Manager 2010 lite is more complete, even if it doesn't allow to import databases from other dbms.

-The connection strings for Server and Superserver is: "servertype=1;username=sysdba;password=masterkey;database=" _& My.Application.Info.DirectoryPath & "\mydb.gdb"

-The connection strin for embedded server is: "servertype=0;username=sysdba;password=masterkey;database=" _& My.Application.Info.DirectoryPath & "\mydb.gdb"

Firebird in applications shared in LAN net

2007-03-07T12:00:00.000+01:00

In the client applications in which more computer are connected in network

In the application project use a connection string like this “server=computer name where the server resides; username=user name ; password=user password; database= explicit path of the database in the server machine\mydb.gdb"
You have to install in the client machine the .net framework.
You have also to copy on the in the client machine only the escecutable of the application and to install the FirebirdClient-2.0.1.
You don't need install neither the Server of Firebird neither the client library in the client machine.
If the server machine is firewalled let open the port n. 3050 in the firewall.
There's no other you have to do.

Firebird in applications not connected in network

2007-03-04T11:15:00.005+01:00

In order to distribute applications not connected in network but like installabile program on a whichever computer, use the following procedure:

Download ADO NET Provider FirebirdClient-2.0.1 which installs the net classes of Firebird on every computer which must hold a VB.net application with Firebird.
Download the package Embedded Firebird
Unzip this file.
In the folder of the application “\bin\debug” copy from the previously unzipped package the libraries:
- fbembed.dll
- icuu30.dll
- icuin30.dll
- icudt30.dll
- the folder “intl” and the folder "udf"
In the application project use a connection string like this: “servertype=1; username=user name; password=user password; database=” & My.Application.Info.DirectoryPath &” \mydb.gdb "
In the installation package insert the executable, the database, the libraries and the folder intl, which will be placed in the same target folder of the application.

As installer I council CIS downloadable from http://66.249.93.104/translate_c?hl=en&ie=UTF-8&amp;amp;amp;amp;amp;amp;amp;amp;amp;oe=UTF-8&langpair=it%7Cen&u=http://www.silvercybertech.com/italian/index.html&prev=/language_tools

Solved the issue "unable to load DLL fbembed .dll"

2007-03-01T11:13:00.005+01:00

If you install Firebird server and open a database by Firebird server, download Fb embed package having the same release number as Firebird server. If you don't install a new Firebird server you don't have to download a new Fb embedded release for that database. Anyway in application directory you have to copy:
- fbembed.dll
- icuu30.dll
- icuin30.dll
- icudt30.dll
Also you have to copy the folders "intl" and "udf".

Examples of code

2007-02-26T11:00:00.006+01:00

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("New_table1")
Me.DataGridView1.DataSource = DataSet1
Me.DataGridView1.DataMember = "New_table1"

Fdataa.Fill(DataSet1, "New_table1")

End Sub

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
NewForm.Show()
End Sub
'To begin the change
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

'I used the trim function because would be problems with positioning of the cursor if you will pass from varcahr type to char type and vice versa.
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("Si desidera eliminare questo cliente?", _
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 & "\miodb.gdb"
ObjConnection.Open()
Try
Dim ObjCommand As New FbCommand()
ObjCommand.Connection = ObjConnection

ObjCommand.CommandText = "delete from New_table1 where Clienti='" & 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("New_table1").Select("Clienti ='" & MainForm.DataGridView1.CurrentRow.Cells(0).Value & "'")

Dim ObjConnection As New FbConnection()

ObjConnection.ConnectionString = "servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\miodb.gdb"
ObjConnection.Open()
Try
Dim ObjCommand As New FbCommand()
ObjCommand.Connection = ObjConnection

ObjCommand.CommandText = "update New_table1 set Clienti='" & Me.TextBox1.Text & "', Spesa='" & Me.TextBox2.Text & "' where Clienti='" &
MainForm.DataGridView1.CurrentRow.Cells(0).Value & "'"
ObjCommand.ExecuteNonQuery()
Finally
ObjConnection.Close()
End Try

customerRow(0)("Clienti") = Me.TextBox1.Text
customerRow(0)("Spesa") = Me.TextBox2.Text

Me.Close()
MainForm.Show()
End Sub
End Class

'To insert e new record

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 & "\miodb.gdb"
ObjConnection.Open()
Try
Dim ObjCommand As New FbCommand()
ObjCommand.Connection = ObjConnection
ObjCommand.CommandText = "insert into New_table1 (Clienti, Spesa) values ('" & TextBox1.Text "','" & TextBox2.Text & "')"
ObjCommand.ExecuteNonQuery()
Finally
ObjConnection.Close()
End Try

Dim newCustomersRow As DataRow = MainForm.DataSet1.Tables("New_table1").NewRow()
newCustomersRow("Clienti") = TextBox1.Text
newCustomersRow("Spesa") = TextBox2.Text
MainForm.DataSet1.Tables("New_table1").Rows.Add(newCustomersRow)

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.

The autoincrement field

2007-02-23T10:45:00.002+01:00

If you are setting an autoincrement field I council to do this with SQL Manager 2005 Lite for InterBase/Firebird. Edit the autoincrement field and in edit window, on thew left, at the voice Autoincrement>Initial value> put 0.

If the table contains data follow this procedure:

Open the table with IBEasy
In the window which appears clic on "data export "
Export the table in XML or Excel format with IBEasy.
Put the autoincrement field at the bottom of the other fields.
Import with IBEasy the previous mentioned XML or Excel file.
Now put the autoincrement field in the position which you want in the table.

At this point your table will have an autoincrement field with the records numbered.

Also the eye wants its part

2007-02-17T08:00:00.005+01:00

Warning: this article has been modified on 7 February 2007.
You will have noticed that the fields of database Firebird 1.5.3 must have the form my_field or MYField and without accents or apex, or space as in "My Field".
We can instead give to the field the form which we want: that is "My Field" or "Citta'" or other type, if we set for the database, as character set, charset UTF8, and we can make it if we use an other free software of management for Firebird called FlameRobin 0.7.6 from SourgeForge.net here. Pay attention, in the query in VB.net, to the way as the fields of which has been spoken over, are written in a SQL statement: in order to make an instruction update example , the instruction must be written therefore: “update table set ""MY FIELD"" = 'Roma'", which is the field which must be all in capital, and this works even if the last letters of the field are accented or with apostrophe. FlameRobin is useful also because it gives, more of the other software, one information detailed on the errors in the query. It remains plain which, for the other uses are convenient the other software of management which I have indicated before.

From mdf to gdb with free software

2007-02-14T07:01:00.004+01:00

You need MS Reporting Services Express. First download an d install SQLEXPR_TOOLKIT.EXE from this page after you selected the language.
Follow this way: Start>Programs>Microsoft Visual Studio 2005>Microsoft Visual Studio 2005. Will appear the initial page of Visual Studio 2005. In the initial page, if does exist a project, go on. If does not exist a project create a new project. In solution explorer right clic on the project and select add>new element. In the window which appear select wizard for new report. Next. Select new data origin>Type=Microsoft Sql server>Connection string="Data Source=.\SQLEXPRESS;AttachDbFilename=path of the database;Connect Timeout=30;User Instance=True">Window query project">select*from YourTable>Window Select Report type>Table>Window table project>Will appear all fields of your table>leave all as it is>Next>Window "Select table style">Select a style>Window completion wizard>textbox Report name>give a name>End>Clic preview>The table will appear>Export>Excel.
IBEasy 1.5.2
When you open IBEasy 1.5.2 it shows error, but after it opens correctly. If you want to remove the error message, put in this application directory:
- fbembed.dll
- icuu30.dll
- icuin30.dll
- icudt30.dll
Now open IBEasy 1.5.2>Open your database>Create a new table>Create corresponding fields>Follow the instruction for import a table Excel by IBeasy guide.

Firebird and the problem of the apex

2007-02-08T11:05:00.000+01:00

Can exist in Firebird records in which the apex appears: for example in CITTA'. In these cases Firebird does not accept the field. In order to avoid the problem you must double the apex. The function Replace for ex. can for this purpose be used in this way: “update Table set Field='" & Replace (Textbox1.text, "'", "''") & "'"

Reporting Services: presentation

2007-02-05T13:00:00.002+01:00

Already long time ago Reporting Services has been released in free version from Microsoft.

From a article of David De Giacomi on dotnethell.it (if you want to read the entire article see here)
... Crystal Reports and its limits
Since many yaears in this field operates a very famous company wich produces a product called Crystal Reports. This product has been inserted in the suite Visual Studio.net of Microsoft for the development. In effects since from the first moment it has been an optimal movement, but in th utilize we notice limits of this product...
Crystal is however an optimal product because it constructs Report of WinForms applications inside, WebForms and quite export given through Web Services. Already some time ago I wrote an article which showed some of its potentialities.
Constructing a Report with ASP.NET and Crystal Reports
Some of its limits are as an example not complete integration with .NET, in fact from the point of view Globalization/Internationalization personally I have found various difficulties.
Moreover also triviality as export of the report in pdf demands a enough tortuous path…

Perhaps the more interesting part for us is the Report Designer, is the tool integrated in Visual Studio .NET which allows to design us the Report and to connect them to our given bases (.NET DataSets)....
Very mach important is the use of Reporting Services in Web Services.

Exporting a report without preview in Excel format

2007-01-27T13:00:00.000+01:00

Imports FirebirdSql.Data.FirebirdClient
Imports Microsoft.Reporting.WinForms
Imports System.IO
Private Sub EsportaExcelToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EsportaExcelToolStripMenuItem.Click
If Me.TextBox1.Text = "" Or Me.TextBox2.Text = "" Then
MsgBox("Pereform before the search", MsgBoxStyle.Critical)
Exit Sub
End If

Label1.Visible = True
'To visualize the label
Me.Refresh()
Report(My.Application.Info.DirectoryPath & "\EsportaExcel.rdl", "Biblioteca")
EsportaExcel()
'This statement is necessary to display e new report after precedent displaying
RepTipo.ReportViewer1.Reset()
Label1.Visible = False
End Sub

Private Sub Report(ByVal filename As String, ByVal table As String)

Dim RepDS As New ReportDataSource

Dim dv As New System.Data.DataView

dv = DataSet1.Tables(table).DefaultView

RepDS.Name = "DataSource1"
RepDS.Value = dv
RepTipo.ReportViewer1.LocalReport.DataSources.Clear()
RepTipo.ReportViewer1.LocalReport.DataSources.Add(RepDS)

RepTipo.ReportViewer1.LocalReport.ReportPath = filename

End Sub

Private Sub EsportaExcel()
Dim warnings As Warning() = Nothing
Dim streamids As String() = Nothing
Dim mimeType As String = Nothing
Dim encoding As String = Nothing
Dim extension As String = Nothing
Dim bytes As Byte()

bytes = RepTipo.ReportViewer1.LocalReport.Render("Excel", _
Nothing, mimeType, _
encoding, extension, streamids, warnings)

Dim fs As New FileStream("c:\outputvb.xls", FileMode.Create)
fs.Write(bytes, 0, bytes.Length)
fs.Close()

End Sub

Exporting a report without preview in PDF format

2007-01-27T10:29:00.000+01:00

Here is the code to export a report in PDF format without preview:
Imports FirebirdSql.Data.FirebirdClient
Imports Microsoft.Reporting.WinForms
Imports System.IO
Private Sub EsportaPDFToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EsportaPDFToolStripMenuItem.Click
If Me.TextBox1.Text = "" Or Me.TextBox2.Text = "" Then
MsgBox("Perform before the search", MsgBoxStyle.Critical)
Exit Sub
End If
Label1.Visible = True
Me.Refresh()
Report(My.Application.Info.DirectoryPath & "\AnteRicercaTipo.rdl", "Biblioteca")
EsportaPDF()
RepTipo.ReportViewer1.Reset()
Label1.Visible = False

End Sub

Private Sub Report(ByVal filename As String, ByVal table As String)

Dim RepDS As New ReportDataSource

Dim dv As New System.Data.DataView

dv = DataSet1.Tables(table).DefaultView

RepDS.Name = "DataSource1"
RepDS.Value = dv
RepTipo.ReportViewer1.LocalReport.DataSources.Clear()
RepTipo.ReportViewer1.LocalReport.DataSources.Add(RepDS)

RepTipo.ReportViewer1.LocalReport.ReportPath = filename

End Sub

Private Sub EsportaPDF()

Dim warnings As Warning() = Nothing
Dim streamids As String() = Nothing
Dim mimeType As String = Nothing
Dim encoding As String = Nothing
Dim extension As String = Nothing
Dim bytes As Byte()

bytes = RepTipo.ReportViewer1.LocalReport.Render("PDF", _
Nothing, mimeType, _
encoding, extension, streamids, warnings)

Dim fs As New FileStream("c:\outputvb.pdf", FileMode.Create)
fs.Write(bytes, 0, bytes.Length)
fs.Close()
End Sub

MS reportviewer. Printing a report without preview

2007-01-27T09:00:00.001+01:00

Imports FirebirdSql.Data.FirebirdClient
Imports Microsoft.Reporting.WinForms
Imports System.IO
Imports System.Threading
Private Sub StampaRicercaToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RicercaTipoStampaToolStripMenuItem.Click
If Me.TextBox1.Text = "" Or Me.TextBox2.Text = "" Then
MsgBox("Perform before the search", MsgBoxStyle.Critical)
Exit Sub
End If

Label1.Text = "Attendere," & vbCr & "stampa in corso..."
Label1.Visible = True

Label2.Visible = True
Me.Refresh()
Report(My.Application.Info.DirectoryPath & "\AnteRicercaTipo.rdl", "Biblioteca")
EsportaPDF()
Dim process As New Process()
process.StartInfo.FileName = "C:\outputvb.pdf"
process.StartInfo.CreateNoWindow = True
process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
process.StartInfo.Verb = "Print"
process.Start()

process.WaitForInputIdle()

Thread.Sleep(5000)
If Not process.CloseMainWindow() Then
process.Kill()
End If

Label2.Visible = False

Label1.Visible = False
Me.WindowState = FormWindowState.Maximized
RepTipo.ReportViewer1.Reset()
End Sub

Private Sub Report(ByVal filename As String, ByVal table As String)

Dim RepDS As New ReportDataSource

Dim dv As New System.Data.DataView

dv = DataSet1.Tables(table).DefaultView

RepDS.Name = "DataSource1"
RepDS.Value = dv
RepTipo.ReportViewer1.LocalReport.DataSources.Clear()
RepTipo.ReportViewer1.LocalReport.DataSources.Add(RepDS)

RepTipo.ReportViewer1.LocalReport.ReportPath = filename

End Sub

Private Sub EsportaPDF()

'L'istruzione si mette altrimenti la label non viene visualizzata
Dim warnings As Warning() = Nothing
Dim streamids As String() = Nothing
Dim mimeType As String = Nothing
Dim encoding As String = Nothing
Dim extension As String = Nothing
Dim bytes As Byte()

bytes = RepTipo.ReportViewer1.LocalReport.Render("PDF", _
Nothing, mimeType, _
encoding, extension, streamids, warnings)

Dim fs As New FileStream("c:\outputvb.pdf", FileMode.Create)
fs.Write(bytes, 0, bytes.Length)
fs.Close()
End Sub

Installation of applications which use ReportViewer

2007-01-26T12:00:00.005+01:00

In all the installation which use roportviewer, in the folder destination of the following application you must install the DLLs: Microsoft.ReportVierer.Winforms.dll, Microsoft.ReportVierer.ProcessingObject.Model.dll, Microsoft.ReportViewer.Common.dll. Thoose DLLs can be installed installing the file ReportViewer.exe here from the site of Microsoft
I know a free software installer which allows to make installations in sequence mode with a single installation package. The free software which creates this package is called Cyberinstaller, downloadable from here.
If you are using other software for the installation which does not possess this functionality then you must execute this procedure in order to gain the DLLs about what I were speaking before:

download indicated ReportVierewer.exe file like before indicated.
open it with Winrar or softer similar. In Winrar you will find the ReportV1.cab file. Open it.
extract the file in a folder to pleasure.
In this folder you will find 4 files. Rinominate the FL_Microsoft_ReportViewer_Common_dll_117718_____X86.3643236F_ file in Microsoft.ReportViewer.Common.dll, the FL_Microsoft_ReportViewer_ProcessingObject_125592_____X86.3643 file in Microsoft.ReportVierer.ProcessingObject.Model.dll, the FL_Microsoft_ReportViewer_WebForms_dll_117720_____X86.364323 file in Microsoft.ReportVierer.Winforms.dll.
These three rinominated files you will insert, as I said, in the istallation folder of target application.

Good bye and good job.

The issue of centering a report in the page in excel format

2007-01-25T12:00:00.003+01:00

When you are exporting a report in excel format and want to center a table in the page, could find difficult to do it, because the width of the table in Excel change against the width which you have setted for the table when you created the report.
I have notice that the ratio "width of table of excel/width whic you have setted for the table in the project" is nearest to 1.076. Therefore you must divide the width which you want for the table in excel format by this constant to obtain the width to set for of the table in the project, considering obviously the width of the margins of the report. After you have divided by 1.076 let have the shrewdness to reduce the width found for the table in the project by 1/10 of a millimetre because of the inevitable imprecision of the printer.
Goodby and god job.

How to populate a Listbox control by a table data

2007-01-23T10:30:00.001+01:00

Guide MSDN does not propose one acceptable solution for this argument. With Firebird as database the things are different. The populate code for a Listbox is this (the names of the database and of the table or other it is clear that they may be personalized from you). The datasets are components of VB.net and are not typifyed:

Imports FirebirdSql.Data.FirebirdClient
_____________________________________________________________
Dim Fdataa1 As New FbDataAdapter("select*from tipo_di_opera", "servertype=1;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\Biblioteca.gdb")
DataSet2.Tables.Add("tipo_di_opera")
Fdataa1.Fill(DataSet2, "tipo_di_opera")
For i As Integer = 0 To DataSet2.Tables("tipo_di_opera").Rows.Count - 1
ListBox1.Items.Add(DataSet2.Tables("tipo_di_opera").Rows(i).Item(0))
Next i
ListBox1.Sorted = True

Deleting multiple selected rows in a datagridview with only a clic

2007-01-20T10:00:00.001+01:00

With this routine we can delete multiple selected rows with only a clic maybe after a search in the table. We have to clic on the tooths in the leftmost of the datagridview.

Private Sub DeleteSelecedRows()
Dim ObjConnection As New FbConnection()
Dim i As Integer
Dim mResult
mResult = MsgBox("Want you really delete the selected records?", _
vbYesNo + vbQuestion, "Removal confirmation")
If mResult = vbNo Then
Exit Sub
End If
ObjConnection.ConnectionString = _ "servertype=0;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\mydb.gdb"
Dim ObjCommand As New FbCommand()
ObjCommand.Connection = ObjConnection
For i = Me.DataGridView1.SelectedRows.Count - 1 To 0 Step -1
ObjCommand.CommandText = "delete from mytable where counter='" & _ DataGridView1.SelectedRows(i).Cells("counter").Value & "'"
ObjConnection.Open()
ObjCommand.ExecuteNonQuery()
ObjConnection.Close()

Me.DataGridView1.Rows.Remove(Me.DataGridView1.SelectedRows(i))
Next

End Sub

Firebird and the boolean data type

2007-01-17T11:00:00.001+01:00

We know that Firebid hasn't a boolean data type. But with VB.net this isn't a problem.
Now we'll make an axample of how manage a checkbox column in VB.net with Firebird.
First we create a field smallint called for example "P" not null with field default=0 in the table.
Next we cerate a new boolean column in a datgridview and assign to it the properties of visualization an resizing mode, proceeding in this way:

Dim checkboxColumn As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn
With checkboxColumn
.HeaderText = "P"
.Name = "P"
.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
.FlatStyle = FlatStyle.Standard
.CellTemplate = New DataGridViewCheckBoxCell()
.CellTemplate.Style.BackColor = Color.Beige
.DataPropertyName = "P"
'There we assign the numeric value corresponding to true and false value
.TrueValue = 1
.FalseValue = 0
End With
'There we assign the position of the boolean column in the datagridview
Me.DataGridView1.Columns.Insert(1, checkboxColumn)
Next we use the DataGridView1_CellContentClick event of the datagridview to edit the boolean field:

Private Sub DataGridView1_CellContentClick(ByVal sender As Object, _
ByVal e As DataGridViewCellEventArgs) _
Handles DataGridView1.CellContentClick
Dim ObjConnection As New FbConnection()
Dim checkboxColumn As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn
ObjConnection.ConnectionString = "servertype=0;username=sysdba;dialect=3;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\Biblioteca.gdb"
ObjConnection.Open()

Try
Dim ObjCommand As New FbCommand()
ObjCommand.Connection = ObjConnection
ObjCommand.CommandType = CommandType.TableDirect
If DataGridView1.CurrentRow.Cells("P").Value = 0 Then

ObjCommand.CommandText = "update Biblioteca set P=1 where counter='" & DataGridView1.CurrentRow.Cells("Counter").Value & "' and P=0"

Else
ObjCommand.CommandText = "update Biblioteca set P=0 where counter='" & DataGridView1.CurrentRow.Cells("Counter").Value & "'"

End If
ObjCommand.ExecuteNonQuery()

Catch ex As FbException
MsgBox(ex.Message)
Finally
ObjConnection.Close()
End Try
End Sub

Database backup, restore, compact

2007-01-14T11:00:00.005+01:00

This is the code:

Imports FirebirdSql.Data.FirebirdClient
Imports FirebirdSql.Data.Services

Backup of a database: code

Dim n As FirebirdSql.Data.Services.FbBackup = New FirebirdSql.Data.Services.FbBackup()
Dim fl As FirebirdSql.Data.Services.FbBackupFile = New FirebirdSql.Data.Services.FbBackupFile(My.Application.Info.DirectoryPath & "\mydb.gdk", FileLen(My.Application.Info.DirectoryPath & "\mydb.gdb"))
n.BackupFiles.Add(fl)
n.ConnectionString = "servertype=0;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\mydb.gdb"
n.Execute()

Restore a database: code

Dim n1 As FirebirdSql.Data.Services.FbRestore = New FirebirdSql.Data.Services.FbRestore()
Dim fl1 As FirebirdSql.Data.Services.FbBackupFile = New
'These statements are necessary because on restart of the computer a runtime exception appear
'The try-catch-finally block, otherwise, seems not work
On Error GoTo 1
On Error Resume Next

Dim cs As FbConnectionStringBuilder = New FbConnectionStringBuilder
Dim restoreSvc As FbRestore = New FbRestore
cs.UserID = "SYSDBA"
cs.Password = "masterkey"
cs.Database = My.Application.Info.DirectoryPath & "\mydb.gdb"
restoreSvc.ConnectionString() = cs.ToString
restoreSvc.BackupFiles.Add(New _
FbBackupFile(My.Application.Info.DirectoryPath & "\mydb.gdk",FileLen(My.Application.Info.DirectoryPath & "\mydb.gdb")))
restoreSvc.Verbose = True
restoreSvc.PageSize = 4096
restoreSvc.PageBuffers = 2048
restoreSvc.Options = FbRestoreFlags.Replace
FbConnection.ClearAllPools()
restoreSvc.Execute()
exit sub
1:
restoreSvc.Execute()

Compact a database: code

Dim n As FirebirdSql.Data.Services.FbBackup = New FirebirdSql.Data.Services.FbBackup()
Dim fl As FirebirdSql.Data.Services.FbBackupFile = New FirebirdSql.Data.Services.FbBackupFile(My.Application.Info.DirectoryPath & "\mydb.gdk", FileLen(My.Application.Info.DirectoryPath & "\mydb.gdb"))
n.BackupFiles.Add(fl)
n.ConnectionString = "servertype=0;username=sysdba;password=masterkey;database=" & My.Application.Info.DirectoryPath & "\mydb.gdb"
n.Execute()
'These statements are necessary because on restart of the computer a runtime exception appear
'The try-catch-finally block, otherwise, seems not work On Error GoTo 1
On Error Resume Next

Dim cs As FbConnectionStringBuilder = New FbConnectionStringBuilder
Dim restoreSvc As FbRestore = New FbRestore
cs.UserID = "SYSDBA"
cs.Password = "masterkey"
cs.Database = My.Application.Info.DirectoryPath & "\mydb.gdb"
restoreSvc.ConnectionString() = cs.ToString
restoreSvc.BackupFiles.Add(New _
FbBackupFile(My.Application.Info.DirectoryPath & "\mydb.gdk", FileLen(My.Application.Info.DirectoryPath & "\mydb.gdb")))
restoreSvc.Verbose = True
restoreSvc.PageSize = 4096
restoreSvc.PageBuffers = 2048
restoreSvc.Options = FbRestoreFlags.Replace
FbConnection.ClearAllPools()
restoreSvc.Execute()
exit sub
1:
restoreSvc.Execute()

P.S.
To see the database resized go in the database folder>Menu on the top>Visualize>clic on "Update" item.

How to insert a date field in a firebird database

2007-01-08T11:00:00.002+01:00

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)