Retrieving data from Access database based on Windows Authenticated

[sfchan86]

Hi

I am trying to retrieve data from an Access Database based on the Windows Authenticated username, specifically the "UserRole".

Below is my code:


protected void Page_Load(object sender, EventArgs e)
{

OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\domain\folder\folder\folder\folder\database.mdb");

OleDbCommand thisCommand = new OleDbCommand();

OleDbDataReader reader;

string userName = User.Identity.Name.ToString().Replace("domain\\", "");
userName = userName.Replace(

" ", "");
thisCommand.CommandText =

"SELECT UserRole FROM Table WHERE UserName='userName'";
thisCommand.CommandType =

CommandType.Text;
thisCommand.Connection = thisConnection;


thisConnection.Open();


reader = thisCommand.ExecuteReader();

label_test.Text = reader.GetString(0);


//label_test.Text="'"+thisCommand.CommandText+"'";

thisConnection.Close();


}



..but when I try to view it on my browser, I get the following message:

Exception Details: System.InvalidOperationException: No data exists for the row/column.

Stack Trace:



[InvalidOperationException: No data exists for the row/column.]
System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal) +1044679
System.Data.OleDb.OleDbDataReader.GetString(Int32 ordinal) +12
Default.Page_Load(Object sender, EventArgs e) in \\domain\folder\folder\folder\folder\Default.aspx.cs:70 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627


Can anyone please tell me where I am going wrong here?

The Select statement definitely works as I tried in Access itself and it brought up what I wanted.

Thank you very much.
.

 

[lameid]

I would open the connection before assigning it to a command object... Not sure if there are any syntax differences between what you are using and VBA but try your statements in the below order.

thisConnection.Open();
thisCommand.Connection = thisConnection;
thisCommand.CommandType = CommandType.Text;

thisCommand.CommandText = "SELECT UserRole FROM Table WHERE UserName='userName'";