Ray Tawil | Programming

Validate data entered into textboxcolumn

0

15 years

by Ray Tawil in Visual Basic.Net

To validate the text entered into a datagrid add a handler to the DatagridTextboxColumn’s validating event. Here is some sample code.

Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As SqlConnection
Dim strConn As String
Dim strSQL As String
Dim da As SqlDataAdapter

strConn = “Server = (local);”
strConn &= “Database = NorthWind;”
strConn &= “Integrated Security = SSPI;”

conn = New SqlConnection(strConn)
da = New SqlDataAdapter(“Select * From Products”, conn)
da.Fill(ds, “Products”)

Dim ts As New DataGridTableStyle
ts.MappingName = ds.Tables(“Products”).TableName

Dim colDiscontinued As New DataGridBoolColumn
With colDiscontinued
.MappingName = “Discontinued”
.HeaderText = “Discontinued”
.Width = 80
End With

Dim colName As New DataGridTextBoxColumn
With colName
.MappingName = “ProductName”
.HeaderText = “Product Name”
.Width = 180
End With
AddHandler colName.TextBox.Validating, AddressOf CellValidating

ts.GridColumnStyles.Add(colName)
ts.GridColumnStyles.Add(colDiscontinued)

DataGrid1.TableStyles.Add(ts)

ts = Nothing
colDiscontinued = Nothing
colName = Nothing

DataGrid1.DataSource = ds.Tables(“Products”)
End Sub

Private Sub CellValidating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs)
Debug.WriteLine(DirectCast(sender, DataGridTextBox).Text)
End Sub

datagridview format input in datagridview

How to Connect to SQL Server Database from Visual FoxPro 9

12

15 years

by Ray Tawil in MS Sql Server

In Microsoft public newsgroups, I’ve noticed a recent increase in the number of questions that deal with how to connect from Visual Foxpro to SQL Server, and the problems related to making this connection. So I’ve decided to write this article to cover such an important topic.

There are two functions that can be used to establish a connection with the a remote SQL Server from Visual FoxPro:

SQLConnect()
SQLStringConnect()

The SQLConnect() Function

There are two ways to use the SQLConnect() function to connect to a remote data source, such as SQL Server. The first requires that you supply the name of a data source as defined in the ODBC Data Source Administrator applet of the Control Panel.

The following example creates a connection to a remote server using the ODBCNorthwind DSN:

LOCAL hConn

hConn = SQLConnect(“ODBCNorthwind”, “sa”, “”)

The second way to use SQLConnect() is to supply the name of a Visual FoxPro connection that was created using the create connection command. The CREATE CONNECTION command stores the metadata that Visual FoxPro needs to connect to a remote data source.

The following example creates a Visual FoxPro connection named Northwind and then connects to the database described by the connection:

LOCAL hConn

CREATE DATABASE cstemp

CREATE CONNECTION Northwind ;

DATASOURCE “ODBCNorthwind” ;

USERID “sa” ;

PASSWORD “”

hConn = SQLConnect(“Northwind”)

SQLStringConnect() Function

The other function that can be used to establish a connection to a remote data source, such as SQL Server, is SQLStringConnect(). Unlike SQLConnect(), SQLStringConnect() requires a single parameter, a string of semicolon-delimited options that describes the remote data source and optional connections settings.

The valid options are determined by the requirements of the ODBC driver. Specific requirements for each ODBC driver can be found in that ODBC driver’s documentation.

The following table lists some commonly used connection string options for SQL Server:

Option	Description
DSN	References an ODBC DSN.
Driver	Specifies the name of the ODBC driver to use.
Server	Specifies the name of the SQL Server to connect to.
UID	Specifies the login ID or username.
PWD	Specifies the password for the given login ID or username.
Database	Specifies the initial database to connect to.
APP	Specifies the name of the application making the connection.
WSID	The name of the workstation making the connection.
Trusted_Connection	Specifies whether the login is being validated by the Windows NT Domain.

Not all of the options listed in the above table have to be used for each connection.

For instance, if you specify the Trusted_Connection option and connect to SQL Server using NT Authentication, there is no reason to use the UID and PWD options since SQL Server would invariably ignore them. The following code demonstrates some examples of using SQLStringConnect().

Note: You can use the name of your server instead of the string.

SQL Server 2000 code example:

LOCAL hConn

hConn = SQLStringConnect(“Driver=SQL Server;Server=<SQL2000>;”+ ;

UID=sa;PWD=;Database=Northwind”)

hConn = SQLStringConnect(“DSN=ODBCNorthwind;UID=sa;PWD=;Database=Northwind”)

hConn =
SQLStringConnect(“DSN=ODBCNorthwind;Database=Northwind;Trusted_Connection=Yes”)

Handling Connection Errors

Both the SQLConnect() and SQLStringConnect() functions return a connection handle. If
the connection is established successfully, the handle will be a positive integer. If Visual FoxPro failed to make the connection, the handle will contain a negative integer. A simple
call to the AERROR() function can be used to retrieve the error number and message. The following example traps for a failed connection and displays the error number and message using the Visual FoxPro MESSAGEBOX() function.

Visual FoxPro returns error 1526 for all errors against a remote data source. The fifth element of the array returned by AERROR() contains the remote data source-specific error.

#define MB_OKBUTTON 0

#define MB_STOPSIGNICON 16

LOCAL hConn

hConn = SQLConnect(“ODBCNorthwind”, “falseuser”, “”)

IF (hConn < 0)

LOCAL ARRAY laError[1]

AERROR(laError)

MESSAGEBOX( ;

laError[2], ;

MB_OKBUTTON + MB_STOPSIGNICON, ;

“Error ” + TRANSFORM(laError[5]))

ENDIF

Disconnecting From SQL Server

It is very important that a connection be released when it is no longer needed by the application because connections consume valuable resources on the server, and the number of connections may be limited by licensing constraints.

You break the connection to the remote data source using the SQLDisconnect() function. SQLDisconnect() takes one parameter, the connection handle created by a call to either SQLConnect() or SQLStringConnect(). SQLDisconnect() returns a 1 if the connection was correctly terminated and a negative value if an error occurred.

The following example establishes a connection to SQL Server, and then drops the connection:

LOCAL hConn,lnResult

*hConn = SQLStringConnect(“Driver=SQL Server;Server=<SQL2000>;”+ ;

UID=sa;PWD=;Database=Northwind”)

hConn = SQLConnect(“ODBCNorthwind”, “sa”, “”)

IF (hConn > 0)

MESSAGEBOX(“Connection has done”)

lnResult = SQLDisconnect(hConn)

IF lnResult < 0

MESSAGEBOX(“Disconnect failed”)

ENDIF && lnResult < 0

ENDIF && hConn > 0

If the parameter supplied to SQLDisconnect() is not a valid connection handle, Visual FoxPro will return a run-time error (#1466). Currently there is no way to determine whether a connection handle is valid without attempting to use it.

To disconnect all SQL pass through connections, you can pass a value of zero to SQLDisconnect().

source: Sayed Geneidy

Format input date in datagridview

0

15 years

by Ray Tawil in Visual Basic.Net

A common problem faced in datagridview is how to format the date in the grid, here is a small way to do it:

Private Sub dataGridView1_CellParsing(ByVal sender As Object, ByVal e As DataGridViewCellParsingEventArgs)

If e.ColumnIndex = 0 Then

Dim [date] As String = DirectCast(e.Value, String)

Try

Dim month As String = [date].Substring(0, 2)

Dim day As String = [date].Substring(2, 2)

Dim year As String = [date].Substring(4, 4)

e.Value = New DateTime(Convert.ToInt32(year), Convert.ToInt32(month), Convert.ToInt32(day))

e.ParsingApplied = True
Catch ex As Exception

MessageBox.Show(“parsing error!”)

End Try

End If
End Sub

datagridview Format input date in datagridview Visual Basic.Net

Hacking the Report Viewer Redistributable

0

15 years

by Ray Tawil in Visual Basic.Net

We always face a problem when we want to publish an application, we want to make sure not to make the customer go through Library installation & dll’s & ocx’s registration, the ideal scenario is to give the customer a portable application that does not need any configuration of course that is hard to achieve especially when you have databases that need servers but you can do that with small to medium sized applications.

error

Today i’m going to address a different problem i faced on one of the projects i’m working on currently. I thought of using report viewer included in visual studio 2008 & 2005 keeping away from crystal reports, thinking that microsoft report viewer is included in the .net framework which turned out to be wrong.

Microsoft includes the pack for this as a standalone installation found here:

Microsoft Report Viewer Redistributable 2005 Service Pack 1:

http://www.microsoft.com/downloads/details.aspx?FamilyID=82833F27-081D-4B72-83EF-2836360A904D&displaylang=en

Microsoft Report Viewer Redistributable 2008 Service Pack 1:

http://www.microsoft.com/downloadS/details.aspx?familyid=BB196D5D-76C2-4A0E-9458-267D22B6AAC6&displaylang=en

Next use any of your zip extraction program to extract the ReportViewer.exe

You will see a CAB file named vb_ros.cab also extract it.

————————————————————————————————————————————–

Then do as following:

Rename: FL_Microsoft_ReportViewer_Common_dll_117718_____X86.3643236F_FC70_11D3_A536_0090278A1BB8 To Microsoft.ReportViewer.Common.dll
Rename: FL_Microsoft_ReportViewer_ProcessingObject_125592_____X86.3643236F_FC70_11D3_A536_0090278A1BB8 To Microsoft.ReportViewer.ProcessingObjectModel.dll
Rename: FL_Microsoft_ReportViewer_WebForms_dll_117720_____X86.3643236F_FC70_11D3_A536_0090278A1BB8 To Microsoft.ReportViewer.WebForms.dll
Rename: FL_Microsoft_ReportViewer_WinForms_dll_117722_____X86.3643236F_FC70_11D3_A536_0090278A1BB8 To Microsoft.ReportViewer.WinForms.dll

Now go to your project.

copy paste the renamed dlls, next to your exe file for exapmple “Myproject\bin\..”

now right click on your project, press properties.

go to references tab

press add -> reference -> browse -> choose the 4 dll’s

That’s it, now your project can work as a portable standalone project with report viewer support.

Thank you for reading.

Please check the youtube video if you like a more step by step tutorial

Hacking the Report Viewer Redistributable Ms Access portable application programming tricks Report Viewer Report Viewer Redistributable Visual Basic.Net

Managing BindingSource Position in Data Driven WinForms

0

15 years

by Ray Tawil in Visual Basic.Net

If you are working with a Windows Form where a user can create and edit data in the same form you may run into the problem of trying to set the BindingSource to a given record in a DataSet you want to work with.

For example let’s say you have a DataGridView in a Smart Client. When the user double clicks a given row in the DataGridView a form opens so they can edit the data. More times than not (no matter how many demos you watch) user’s do not edit data values in DataGridViews. Although this is a supported feature, business objects are too complex. Imagine having to edit a contact in a single row in Outlook for example, not fun and not very user friendly. Why every Microsoft demo I watch shows editing in DataGridViews is beyond me. I digress though.

When the new form is instantiated we need to pass two things: the dataset we are working with, and the IDof the item we want to edit. We can pass this information into the constructor of the form without any trouble as seen below. By passing in the DataSet we can add new records, delete records or whatever we need to do. The other benefit is since the dataset is passed by reference, any changes to it will automatically appear in the row the user just double clicked on. In order for the form fields to be auto bound to the record the user clicked on we need to set the DataBinding of the controls in the form to a BindingSource. The BindingSource serves as the go between between the UI and data store. Here’s a sample “CellDoubleClick” event that would happen in the DataGridView:

1 int id = Convert.ToInt32(DataGridView.Rows[e.RowIndex].Cells[0].Value);

2 MyForm myForm = new MyForm(dataSet, id);

3 myForm.Show();

As the form loads the problem we face is having to tell the BindingSource in the form which record we want to set as the BindingSource.Current item. At first glance it looks like we would just get the DataRow and then set it as the Current property. Wrong. The BindingSource.Current property has only a get accessor. Instead we need to use the BindingSource.Position property. Note: Any change to the position property updates the current property used by the bindingsource. The problem we face now is the BindingSource.Position property wants to know the index of the item in the underlying DataSource.

We don’t know the index value used by the BindingSource but we do know the ID we want to use. This is where the BindingSource.Find() method comes in. Using the find method we can basically search the underlying datasource and retrieve the index of a given item. Here’s an example:

contactBindingSource.Position = contactBindingSource.Find(“Id”, 2);

Once the line above runs, it is going to search the column “Id” in the DataSource associated with the BindingSource and return the index of that item.

What if you use the same form to create a new item? Simple, call the BindingSource.AddNew() method which will add a new item to the underlying DataSource. Then move the index to the last item. Here’s a sample:

bindingSource.AddNew();
bindingSource.MoveLast();

Since items are always added at the end of the associated data source the MoveLast() method will set the index to the last item in the underlying datasource.

I hope this makes sense because it is an easy way to have a form bound to a DataSet and use the same form to perform “CRUD” operations with.

binding BindingSource.Find BindingSource.Position data datagridview win form