I have two questions on this. One right answer gets both sets of points. What I'm asking seems so easy, I just want to look up a value in a SQL database with a read-only account! Yet for some reason, nothing is working. My SQL DB has 2 accounts- a full access 'admin' account and a read only account that has read access to all my tables.
In VB.NET i have this basic function to query a name that a user puts in a textbox to a SQL table.
Dim Finder as string = ("SELECT * FROM MyTable WHERE user_name= 'MyName'")
SqlDataAdapter1.SelectCommand.CommandText = Finder
Query = SqlDataAdapter1.Fill(DataSet11)
My SQL connection string:
The code works perfectly when I use my Admin account
Me.SqlConnection1.ConnectionString = "user id=<ID>;password=<PASSWORD>;data source=""<IP>"";initial catalog=MyDB"
HEre's what confuses me: The entire application works perfectly when I put my admin account info into the SQL connection string. If i put my readonly account info in there, it invariably gives me an error (shown below)
"Is your read only account info setup right?" Yes! I have verified this many times. In fact, in VB.NET I create a new sqlconnection and I can successfully test the connection and even fill in my dataset using the readonly credentials. SO i know the read only credentials are totally working. Its getting the code for VB.NET to query a table with readonly credentials that has me stumped.
"invalid object MyTable" (this is what a Sql catch exception returns)
I simply change the SQL string to my admin account and it works great. I know that my read only account works because in the form designer not only can I successfully create a SQL dataadapter, but i can also preview the data and the dataset! Its when I use the command 'Query = SqlDataAdapter1.Fill(DataSet11)' in my code that I get an error. So why in the world can I preview the data and fill the dataset in the form designer with the readonly account, but when i run the code, it bugs out?
SELECT * FROM dbo.MyTable WHERE dbo.MyTable.user_name= 'MyName'