Connection Strings on Various Databases

MS_Access
ODBC
Standard Security:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"
Workgroup:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw;"
Exclusive:
"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="

OLE DB, OleDbConnection (.NET)
Standard security:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"

Workgroup (system database):
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;"

With password:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"

Oracle
ODBC
New version:
"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"
Old version:
"Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

OLE DB, OleDbConnection (.NET)
Standard security:
"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"
This one's from Microsoft, the following are from Oracle
Standard Security:
"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;"
Trusted Connection:
"Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;"

OracleConnection (.NET)
Standard:
"Data Source=MyOracleDB;Integrated Security=yes;"
This one works only with Oracle 8i release 3 or later
Specifying username and password:
"Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated Security=no;"
This one works only with Oracle 8i release 3 or later
Declare the OracleConnection:
C#: using System.Data.OracleClient;OracleConnection oOracleConn = new OracleConnection();oOracleConn.ConnectionString = "my connectionstring";oOracleConn.Open();

VB.NET:Imports System.Data.OracleClientDim oOracleConn As OracleConnection = New OracleConnection()oOracleConn.ConnectionString = "my connectionstring"oOracleConn.Open()
Missing the System.Data.OracleClient namespace? Download .NET Managed Provider for Oracle >>Great article! "Features of Oracle Data Provider for .NET" by Rama Mohan G. at C# Corner

Excel
ODBC
Standard:
"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;"
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

OLE DB
Standard:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

Text
ODBC
Standard:
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;"

OLE DB
Standard:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
"HDR=Yes;" indicates that the first row contains columnnames, not data

DBF/FoxPro
ODBC
standard:
"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\mydbpath;"

OLE DB, OleDbConnection (.NET)
standard:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;Password="

Sybase
ODBC
Standard Sybase System 12 (or 12.5) Enterprise Open Client:
"Driver={SYBASE ASE ODBC Driver};Srvr=Aron1;Uid=username;Pwd=password"
Standard Sybase System 11:
"Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;"
Do you know a userguide for Sybase System 11, 12, 12.5? E-mail the URL to connectionstrings.com now!! >>
Intersolv 3.10:
"Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"
Sybase SQL Anywhere (former Watcom SQL ODBC driver):
"ODBC; Driver=Sybase SQL Anywhere 5.0; DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;Uid=username;Pwd=password;Dsn="""""
Note! The two double quota following the DSN parameter at the end are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax. The empty DSN parameter is indeed critical as not including it will result in error 7778. Read more in the Sybase SQL Anywhere User Guide >>

OLE DB
Adaptive Server Anywhere (ASA):
"Provider=ASAProv;Data source=myASA"
Read more in the ASA User Guide >>
Adaptive Server Enterprise (ASE) with Data Source .IDS file:
"Provider=Sybase ASE OLE DB Provider; Data source=myASE"
Note that you must create a Data Source .IDS file using the Sybase Data Administrator. These .IDS files resemble ODBC DSNs.
Adaptive Server Enterprise (ASE):
"Provider=Sybase.ASEOLEDBProvider;Srvr=myASEserver,5000;Catalog=myDBname;User Id=username;Password=password" - some reports on problem using the above one, try the following as an alternative -"Provider=Sybase.ASEOLEDBProvider;Server Name=myASEserver,5000;Initial Catalog=myDBname;User Id=username;Password=password"
This one works only from Open Client 12.5 where the server port number feature works, allowing fully qualified connection strings to be used without defining any .IDS Data Source files.

AseConnection (.NET)
Standard:
"Data Source='myASEserver';Port=5000;Database='myDBname';UID='username';PWD='password';"
Declare the AseConnection:
C#:using Sybase.Data.AseClient;AseConnection oCon = new AseConnection();oCon.ConnectionString="my connection string";oCon.Open();
VB.NET:Imports System.Data.AseClientDim oCon As AseConnection = New AseConnection()oCon.ConnectionString="my connection string"oCon.Open()

MYSQL
ODBC
ODBC 2.50 Local database:
"Driver={mySQL};Server=localhost;Option=16834;Database=mydatabase;"
ODBC 2.50 Remote database:
"Driver={mySQL};Server=data.domain.com;Port=3306;Option=131072;Stmt=;Database=my-database;Uid=username;Pwd=password;"
ODBC 3.51 Local database:
"DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=myDatabase;USER=myUsername;PASSWORD=myPassword;OPTION=3;"
ODBC 3.51 Remote database:
"DRIVER={MySQL ODBC 3.51 Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase; USER=myUsername;PASSWORD=myPassword;OPTION=3;"

OLE DB, OleDbConnection (.NET)
Standard:
"Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;"
MySqlConnection (.NET)
eInfoDesigns.dbProvider:
"Data Source=server;Database=mydb;User ID=username;Password=pwd;Command Logging=false"
This one is used with eInfoDesigns dbProvider, an add-on to .NET
Declare the MySqlConnection:
C#:using eInfoDesigns.dbProvider.MySqlClient;MySqlConnection oMySqlConn = new MySqlConnection();oMySqlConn.ConnectionString = "my connectionstring";oMySqlConn.Open();

VB.NET:Imports eInfoDesigns.dbProvider.MySqlClientDim oMySqlConn As MySqlConnection = New MySqlConnection()oMySqlConn.ConnectionString = "my connectionstring"oMySqlConn.Open()

SQL SERVER
ODBC
Standard Security:
"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"
Trusted connection:
"Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"
Prompt for username and password:
oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"

OLE DB, OleDbConnection (.NET)
Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"
(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
Prompt for username and password:
oConn.Provider = "sqloledb"oConn.Properties("Prompt") = adPromptAlwaysoConn.Open "Data Source=Aron1;Initial Catalog=pubs;"
Connect via an IP address:
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))

SqlConnection (.NET)
Standard Security:
"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;" - or -"Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False" (both connection strings produces the same result)

Trusted Connection:
"Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;" - or -"Server=Aron1;Database=pubs;Trusted_Connection=True;" (both connection strings produces the same result)
(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)
Connect via an IP address:
"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"
(DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))
Declare the SqlConnection:
C#:using System.Data.SqlClient;SqlConnection oSQLConn = new SqlConnection();oSQLConn.ConnectionString="my connectionstring";oSQLConn.Open();
VB.NET:Imports System.Data.SqlClientDim oSQLConn As SqlConnection = New SqlConnection()oSQLConn.ConnectionString="my connectionstring"oSQLConn.Open()

Poping new window and to pass variable value from one webform to another in ASP.NET

The code helps in poping new window and to pass variable value from one webform to another. Technology: ASP.NET Language: VB

'Response.Write("<'write script language= javascript here'>" & vbCrLf)' 'Response.Write("wndCht=window.open('http://blrkec32416d/naviwebpage/mainpage.aspx?arg='data' ','','width=650, height=400,toobar=no,addressbar=no,menubar=no,scrollbars=yes,resizable=yes');")' 'Response.Write("<" & "/" & "script" & ">" & vbCrLf)

'Dim str1 As String'
str1 = Request.QueryString("arg")'
TextBox1.Text = str1

Comments Please

Database Connectivity and Query commands to execute in ASP.NET

This is the conenction string for access stroed in a variable strconn. This variable is passed as argument to the function in the function call. Here it is GETHEADLINES.
Public strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("Database/naviwebpage.mdb")

The function returns a dataset of records of the query. the results should be stroed in the Dataset and can populated to any form.
Function fnGetHeadlines(ByVal strConn As String) As DataSet
Dim conn As New OleDbConnection(strConn)
conn.Open()
Dim cmd As New OleDbCommand("select Headlines,DetailNews from news", New OleDbConnection(strConn))
Dim myDataAdapter As New OleDbDataAdapter(cmd)
Dim myDataset As New DataSet
myDataAdapter.Fill(myDataset)
conn.Close()
Return myDataset
End Function

Comments Please

Link Button Event in a Datagrid in ASP.NET

This code gets the event of the Linkbutton placed in a Datagrid. make sure the datagrid is not autogenerates and the data should be bounded. In this code the linkbutton is placed in the first column of the Datagrrid using the datagrid properties.

Technology : ASP.NET
Language : VB
Code:

Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
TextBox1.Text = e.Item.Cells(1).Text
End Sub

The DataGrid1_itemcommand is the event handler for the Datagrid.

Comments Please.

Validating User and password from Database in ASP.Net

The code below helps in validating the user and his password maintained in the databse.
The language used is VB.

Dim objUser As New news
Dim user, pass As String
user = TextBox2.Text ' TEXTBOX TO GET USERNAME
pass = TextBox3.Text ' TEXTBOX TO GET PASSWORD
ds = objUser.fnGetuser(strConn, user, pass) ' FUNTION CALL TO DATABSE QUERY
If ds.Tables(0).Rows.Count = 0 Then
TextBox1.Text = "unsuccess" 'IF THE DATASET RETURNS NULL VALUE
Exit Sub
End If
If user = ds.Tables(0).Rows(0).Item(0) And pass = ds.Tables(0).Rows(0).Item(1) Then
TextBox1.Text = "successful"
End If


' FUNCTION GETUSER
Function fnGetuser(ByVal strConn As String, ByVal user As String, ByVal pass As String) As DataSet

Dim conn As New OleDbConnection(strConn)
conn.Open()
Dim cmd As New OleDbCommand("select username,password from login where username= '" & user & "' and password= '" & pass & "' ", New OleDbConnection(strConn))
Dim myDataAdapter As New OleDbDataAdapter(cmd)
Dim myDataset As New DataSet
myDataAdapter.Fill(myDataset)
conn.Close()
Return myDataset

End Function

Comments Please.

Excel to Datagrid Migration in ASP.Net

This code migrates all excel data to datagrid in asp.net using vb language

Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\Wordlist.xls; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)
DS = New System.Data.DataSet
MyCommand.Fill(DS)
MyConnection.Close()
DataGrid2.DataSource = DS
DataGrid2.DataBind()

Comments Please

Importing Excel data to Access Database in ASP.NET

The code below gives the solution for importing excel data to access database. I needed this when doing a tool on skill search of employees.


TO DOWNLAOD THE EXCEL SHEET TO PARTICULAR PATH

Dim savePath As String = "C:\Inetpub\wwwroot\naviwebpage\Database\"
Dim postedFile = UploadFile.PostedFile
Dim filename As String = Path.GetFileName(postedFile.FileName)
txtExportFilename.Text = filename 'ASSIGNING THE FILENAME TO A TEXTBOX
If Not (UploadFile.PostedFile Is Nothing) Then
Try
'Dim postedFile = UploadFile.PostedFile
' Dim filename As String = Path.GetFileName(postedFile.FileName)
Dim contentType As String = postedFile.ContentType
Dim contentLength As Integer = postedFile.ContentLength

postedFile.SaveAs(savePath & filename)
Label2.Text = postedFile.Filename &amp; " uploaded" & _
"
content type: " & contentType & _
"
content length: " & contentLength.ToString()
Catch exc As Exception
Label2.Text = "Failed uploading file"
End Try
End If

IMPORTING DATA FROM EXCEL
Dim filenameEx, path As String
filenameEx = txtExportFilename.Text 'ASSIGINING THE FILENAME TO THE VARIABLE FROM THE TEXTBOX
path = "C:\Inetpub\wwwroot\naviwebpage\Database\" & filenameEx


'to convert excel it intO access
Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Inetpub\wwwroot\naviwebpage\Database\naviwebpage.mdb")
Try
AccessConn.Open()

'FOR EXISTING TABLE
' Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Export](F1,F2) SELECT * from [Excel 8.0;DATABASE=" & path &amp; ";HDR=No;IMEX=1].[Sheet1$];", AccessConn)

'Working existing table for default path
'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Export](F1,F2) SELECT * from [Excel 8.0;DATABASE=C:\Inetpub\wwwroot\naviwebpage\Database\Wordlist.xls;HDR=No;IMEX=1].[Sheet1$];", AccessConn)


'FOR NEW TABLE CREATED EACH TIME
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [ALCON] FROM [Excel 8.0;DATABASE=" & path &amp; ";HDR=yes;IMEX=1].[Sheet1$];", AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Catch ex As Exception
Label2.Text = ex.Message
Finally
AccessConn.Close()
'Label2.Text = "Export Success"
End Try

Hope this is helpful to who ever in need of this. Comments Please. Enjoy