Ray Tawil | sql server

Searching through all columns in datagridview

0

14 years

by Ray Tawil in Visual Basic.Net

Assuming you are searching for the name John, the following procedure will search all existing columns regardless

Source code

Dim x As Integer = 0
 While x < DataGridView1.Rows.Count
 Dim y As Integer = 0
 While y < DataGridView1.Rows(x).Cells.Count
 Dim c As DataGridViewCell = DataGridView1.Rows(x).Cells(y)
 If Not c.Value Is DBNull.Value Or Nothing Then
 If CType(c.Value, String) = "John" Then
 MessageBox.Show("Found!")
 End If
 End If
 System.Math.Min(System.Threading.Interlocked.Increment(y), y - 1)
 End While
 System.Math.Min(System.Threading.Interlocked.Increment(x), x - 1)
 End While
 MessageBox.Show("Search complete!")
 End Sub

Filtering a datagridview

0

14 years

by Ray Tawil in Visual Basic.Net

assuming you have a datagridview that has data in it.

you want to avoid recalling sql from the database & just want to filter the datagrid.

you can do the following, What you need (1 datagridview (data), 1 button (search), 1 textbox (string to search), 1 variable to precise the criteria your searching for)

Source code

Dim findcrit as string
Dim dt As DataTable = Ds_Pos.Employees
 Dim dv As New DataView(dt)
 
 Dim _RowFilter As String = ""
 Dim _FieldType = dt.Columns(findcrit).DataType.ToString
 
 Select Case _FieldType
 Case "System.Int32"
 _RowFilter = "convert(" & findcrit & ", 'System.String') like '" & Me.TextBox1.Text & "'"
 Case "System.Int64"
 _RowFilter = "convert(" & findcrit & ", 'System.String') like '" & Me.TextBox1.Text & "'"
 Case "System.Double"
 _RowFilter = "convert(" & findcrit & ", 'System.String') like '" & Me.TextBox1.Text & "'"
 Case "System.String"
 _RowFilter = findcrit & " like '" & TextBox1.Text & "*'"
 End Select
 dv.RowFilter = _RowFilter
 EmployeesDataGridView.DataSource = dv

The cases are to cast the type of variable from any type to string because you can only use the method on string data types.

the idea is to move the content to a data table then filter then return it back to the datatable.

database datagridview filter sql sql server Visual Basic.Net

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

Connect to SQL Server Database from Visual FoxPro 9 sql server visual foxpro 9