Kyle-Brown.net

OLEDB and SQL Connection Strings

• OLE DB Provider for SQL Server

For Standard Security

oConn.Open "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User;" & _
           "Password=myPassword"

For a Trusted Connection

oConn.Open "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "Integrated Security=SSPI"

To connect to a “Named Instance”

oConn.Open "Provider=sqloledb;" & _
           "Data Source=myServerName\myInstanceName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User;" & _
           "Password=myPassword"

Note: In order to connect to a SQL Server 2000 “named instance”, you must have MDAC 2.6 (or greater) installed.

To Prompt user for username and password

oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName" 

To connect to SQL Server running on the same computer

oConn.Open "Provider=sqloledb;" & _
           "Data Source=(local);" & _
           "Initial Catalog=myDatabaseName;" & _
           "User;" & _
           "Password=myPassword"

To connect to SQL Server running on a remote computer (via an IP address)

oConn.Open "Provider=sqloledb;" & _
           "Network Library=DBMSSOCN;" & _
           "Data Source=xxx.xxx.xxx.xxx,1433;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User;" & _
           "Password=myPassword"

Where:
- “Network Library=DBMSSOCN” tells OLE DB to use TCP/IP rather than
Named Pipes (Q238949)
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.  Q269882 and Q287932
- You can also add “Encrypt=yes” for encryption

For more information, see: Microsoft OLE DB Provider for SQL Server

To view Microsoft KB articles related to OLE DB Provider for SQL Server, click here

• OLE DB Provider for SQL Server via SQLXMLOLEDB

The SQLXMLOLEDB provider is an OLE DB provider that exposes the Microsoft SQLXML functionality through ADO. The SQLXMLOLEDB provider is not a rowset provider; it can only execute commands in the “write to an output stream” mode of ADO.

oConn.Open "Provider=SQLXMLOLEDB.3.0;" & _
           "Data Provider=SQLOLEDB;" & _
           "Data Source=mySqlServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User;" & _
           "Password=myUserPassword"

Getting data from a dropdownlist and a textbox in a sql statement

I recently created a program for work that allowed us to search our SQL database.  I had envisioned a simple C# program with a dropdownlist, textbox and a gridview that allowed the user to choose which table they wanted to search from and enter their search string in the textbox.  While this sounded easy, for a beginner programmer it all was except the damn dropdownlist.  I was having problems getting a dropdownlist populated with the tables in my SQL database.  I asked on forums, i searched forums, i exhausted Google – so i thought and camecould not figure this out.  Well first of all I was using the SqlConnection, SqlCommand, etc when i should have been using OleDbConnection, OleDbCommand, etc..  So to populate a dropdownlist with the list of tables from SQL the code was:

OleDbConnection con;
OleDbCommand cmd = new OleDbCommand();

string s = @”Provider=SQLOLEDB; Data Source=NAMED\INSTANCE; Initial Catalog=DatabaseName; Integrated Security=SSPI;”;

con = new OleDbConnection(s);
cmd.Connection = con;
try
{
con.Open();
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, “TABLE” });
DropDownList1.DataSource = dt.DefaultView;
DropDownList1.DisplayMember = “TABLE_NAME”;
con.Close();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message.ToString());
}

This populated the dropdownlist with all the tables from the Initial Catalog, aka database, using “GetOleDbSchemaGuid”.  Now to search the database using the dropdownlist and a textbox i used the following SQL syntax:

OleDbCommand command = new OleDbCommand(“SELECT * FROM ” + DropDownList1.Text + ” WHERE ColumnName LIKE ‘” + txtFind.Text + “‘”, objOleDbConnection);

Now the whole button event is as follows:

private void txtFind_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == (char)Keys.Enter)
{
OleDbConnection objOleDbConnection = new OleDbConnection();
string Connection = @”Provider=SQLOLEDB; Data Source=NAMED\INSTANCE; Initial Catalog=Database; Integrated Security=SSPI;”;

try
{

objOleDbConnection.ConnectionString = Connection;
objOleDbConnection.Open();

if (txtFind.Text != “”)
{
OleDbCommand command = new OleDbCommand(“SELECT * FROM ” + DropDownList1.Text + ” WHERE ColumnName LIKE ‘” + txtFind.Text + “‘”, objOleDbConnection);
OleDbDataAdapter objOleDbDataAdapter = new OleDbDataAdapter(command);
OleDbCommandBuilder objOleDbCommandBuilder = new OleDbCommandBuilder(objOleDbDataAdapter);
DataTable objDataTable = new DataTable();
objOleDbDataAdapter.Fill(objDataTable);
BindingSource objBindingSource = new BindingSource();
objBindingSource.DataSource = objDataTable;
DataGridView.DataSource = objBindingSource;
command.Dispose();
objOleDbConnection.Close();
txtStatus.Text = “SQL command executed successfully and the database connection is now closed.” +
“You are ready to type another ID and search”;
lblRowsReturned.Text = DataGridView1.RowCount.ToString();

}

}

catch (OleDbException ex)
{
// Print error message in txtStatus textbox
txtStatus.Text = “SQL error:” + ex.Message;
}
finally
{
// Close data reader object and database connection
if (objOleDbConnection != null)
objOleDbConnection.Close();

if (objOleDbConnection.State == ConnectionState.Open)
objOleDbConnection.Close();

}
}

}

So as you can see it is pretty simple to get a dropdownlist and a textbox to search a sql database based off the values either selected or entered on the form.  I hope this helps someone out since I could not find anywhere that specified how to accomplish this.  Maybe I was looking int he wrong areas or searching the wrong terms but god i could not find an example or answer anywhere.  My answers on forums went unanswered as well.

K