Click here to Skip to main content
15,891,204 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I can successfully login in and view, edit data and tables from SQL Management Studio from local. But when trying to do the same from any other application i.e. Visual Studio i can make a successful connection.. but can't retrieve data from the table.

In visual studio it shows
<OMSDB.dbo.Users>
Could not retrieve schema information for table or view OMSDB.dbo.Users.


What I have tried:

Previously i can access the same database without any problems. When i changed the OS i installed the SQL Server and attached the database... after that only this error is thrown.
Posted
Updated 9-Aug-17 12:53pm
Comments
RickZeeland 24-Jul-17 7:03am    
What kind of DataSource have you added? SQL Server, SQL Server CE or LocalDB ?
Can you show us the code you use to connect and open the table ?
Maybe you are closing the connection too early ?
Devil7DK 25-Jul-17 7:04am    
Sure...

Here it is...

Function LoadUsers(ByVal ConnectionString As String) As System.ComponentModel.BindingList(Of User)
Dim r As New System.ComponentModel.BindingList(Of User)
Dim conn As New SqlConnection(ConnectionString)
conn.Open()
Dim comm As New SqlCommand("SELECT * FROM Users", conn)
Dim reader As SqlDataReader = comm.ExecuteReader
Dim i As Integer = -1
While reader.Read()
Dim PH As Image = My.Resources.User_Default
Try
PH = Image.FromStream(New IO.MemoryStream(CType(reader.Item("Photo"), Byte())))
Catch ex As Exception

End Try
Dim Credentials_ As String = reader.Item("Credentials").ToString
Dim cls As System.ComponentModel.BindingList(Of Credential) = Nothing
If Credentials_ <> "" Then
cls = XMLParsers.Credentials.FromXML(Credentials_)
End If
Dim Permissions_ As String = reader.Item("Permissions").ToString
Dim Per As New List(Of String)
If Permissions_ <> "" Then
Per = XMLParsers.ListOfString.FromXML(Permissions_)
End If
r.Add(New User(reader.Item("ID").ToString, reader.Item("Username").ToString, reader.Item("Password").ToString, reader.Item("Desktop").ToString, reader.Item("Home").ToString, reader.Item("UserType").ToString, reader.Item("Address").ToString, reader.Item("Mobile").ToString, reader.Item("Email").ToString, Per.ToArray, reader.Item("Status").ToString, PH, cls))
End While
conn.Close()
Return r
End Function

and the connection string...

"Server=HP\SQLEXPRESS;Database=OMSDB;User Id=sa;Password=QW741erty;Application Name=Office Management System;Pooling=True;"
RickZeeland 25-Jul-17 10:18am    
And what happens if don't close the connection with conn.Close() or close it later on exit of the program ?
Devil7DK 26-Jul-17 3:36am    
"While reader.Read()" in this line i always get "false". even there is data in table (can be viewed using SSMS). and this line is before "conn.close()"

if there is problem in my code... why the same error occurring with Visual Studio....?
Devil7DK 25-Jul-17 7:06am    
@RickZeeland I dont this the problem is in the code as this same code has worked with the same database before...

1 solution

If I understand the issue correctly. You had an instance of SQL setup, on your computer. Replaced the OS, installed a fresh version of SQL Server, and attached the database to it. Now on connecting to the SQL server (That completes succesfully) however, when trying to access data in your OMSDB database, you get a denied message.

This seems to be a permissions issues. At the very least you will need to grant to the user account db_datareader. Though its also possible that if the user login was recreated at the SQL server level, its SID is out of sync with the database user. in which case the system stored procedure sp_change_users_login will need to be run on the user (in the OMSDB database).
SQL
EXEC sp_change_users_login 'Auto_Fix', 'user'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900