How to: Get list of table names from MS Access Database in ASP.NET 2.0

To get a list of table names from database use following code:

Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\sample.mdb"
Dim olecon As New OleDbConnection(sConn)
olecon.Open()
Dim tblrestrictions As String() = New String() {Nothing, Nothing, Nothing, "TABLE"}
Dim dt As DataTable = olecon.GetSchema("tables", tblrestrictions)
olecon.Close()
GridView1.DataSource = dt
GridView1.DataBind()

Restrictions are a string array in the following format:
{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}.

Using ADOX and Msysobjects works fine with windows applications but may problematic for web apps[permission related problems]. If you use Msysobjects directly in query, following error will be displayed:

Record(s) cannot be read; no read permission on 'MSysObjects'

So it's better to use GetSchema with restriction.

How To - ExecuteScalar ()

When To Use:

Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.

ExecuteScalar is a fast way to fetch one value.


Results:

If ExecuteScalar return 0 (zero) means no similar record found in database (According to condition is defined in WHERE clause).


Code Snippets:

Snippet 1:

Scenario: Avoid entry of duplicate user names by using ExecuteScalar method.

DBConnection.Open()
SQLString = "SELECT Count(*) FROM tblLogin " & _
WHERE username = '" & txtUserName.Text & "'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
If DBCommand.ExecuteScalar() <> 0 Then
lblError.Text = "Sorry. User Name Already Present."
End If
DBConnection.Close()


Snippet 2:

Scenario: Check whether User Name and Password is present in database by using ExecuteScalar method.

DBConnection.Open()
SQLString = "SELECT Count(*) FROM tblLogin " & _
WHERE username = '" & txtUserName.Text & "' AND Password='" & txtPassword.Text & "'"
DBCommand = New OleDbCommand(SQLString, DBConnection)
If DBCommand.ExecuteScalar() <> 0 Then
'IF condition satisfied means User Name and Password is correct.
' write code below here.
End If
DBConnection.Close()

Conclusion:

Use to get result of Queries like count, min, max...

Solution: AccessDataSource control generates error for autoincrement data type field while Inserting a record.

Following error you may faced:

"You tried to assign the Null value to a variable that is not a Variant data type. "


Solution:

When you use AccessDataSource control to insert a record in database. If one of field type is autoincrement then above error will shoot on the screen.

Normally you remove autoincrement data type fields from InsertCommand. This creates another problem. You don't face any error, but data is not saved in respective fields in database.

So here is a solution.

First, remove autoincrement field from InsertCommand.

Second, Important step, remove autoincrement field line from InsertParameters section of AccessDataSource control.