Invalid object name 'User'.

I have a datagrid that I am trying to populate when I press a button.  I know I have connectivity to the database becasue my dropdowns pull data from the database... does any one know why I get this error and how to remedy it ?

Thanks




Invalid object name 'User'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'User'.

Source Error:


Line 189:                  SqlDataAdapter ad = new SqlDataAdapter(sql.ToString(), cnFriends);
Line 190:                  dsResults = new DataSet();
Line 191:                  ad.Fill(dsResults, "User");
Line 192:
Line 193:                  if (dsResults.Tables["User"].Rows.Count <
 
jimwal1940Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PoeticAudioCommented:
i'm  not sure if this is the solution, but user is a reserved word in SQL, so maybe do

ad.Fill(dsResults, "tblUser")
if(dsResults.Tables["tblUser"]...etc
PoeticAudioCommented:
and if your tables name in SQL is user then I would change that to, just to be safe.
AdGrootCommented:
Are your sure that the the command reurns a result

if not you don't get a table

See the help of SqlDataAdapter.Fill

If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.

Ad


PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

jimwal1940Author Commented:
How can I check to see I'm getting returned rows ?
jimwal1940Author Commented:
What does it mean in a SQL statement when you have this [User].UserID ..... I'm not sure what the brackets are for...
PoeticAudioCommented:
User is a reserved word (a word that SQL uses, such as SELECT, JOIN...etc) so you can either change it (which is what I would do) or you can us brackets.

PoeticAudioCommented:
you can check to see if you're returning rows by using autos, or a watch. I like using watches. To do that, while running set a break point right after your fill. Then go to the menu up top and click debug -> Windows -> Watch -> (select a watch, such as watch 1) and then in the watch. Under name (in the watch window) type dsResults.Tables.List. Click on the + sign and you can select the table you are after (such as 0). From there you can go to Rows, then List and it will list all of your rows (it will be indexed, so it will be like 0, 1, 2, 3...etc) That will tell you if you have rows. You can then click on the row you want, and select ItemArray to see what's in those rows.

There are other ways of seeing if there are rows (such as set some sort of arbitrary integer to rows.count) but I like to use the debugging tools because you can see everything you would ever want to know about your objects.
jimwal1940Author Commented:
ok I changed it to [tblUser] ..same issue ....

PoeticAudioCommented:
Also I fogot to mention:

With brackets they can also be handy if you want to use spaces. For example if you're selecting a column named FirstName but you want it to show "First Name" in your datagrid you can use brackets. When you change the name of the returned column this is called aliasing, here's a typical example...

SELECT FirstName AS [First Name] -- this is aliasing, and using brackets to add a space in the returned column
FROM tblUsers
WHERE UserLevel > 1
PoeticAudioCommented:
and you renamed your actual table? I try to never name my tables with reserved words.
jimwal1940Author Commented:
I was following this example from a book .. These things never work right..  Yes... I renamed the actual table also...

I tried to do the break points but I don't see the WATCH in the debug... ...
quoclanCommented:
How can I check to see I'm getting returned rows ?
--> ad.Fill(dsResults,"User") return number of rows is inserted into table "User"
Hope this help you.

PoeticAudioCommented:
you have to go to Debug -> windows -> watches
PoeticAudioCommented:
btw, you can only use watches during run-time
quoclanCommented:
Your dsResult is null when you debug, right ??
Did you try to test your command in Query Analyzer ? It worked ?
jimwal1940Author Commented:
So this table USER is it one I can query ?  the dsResults is actually a dataset linked to the datagrid... I think

Poet ... how do I know I'm in run time... ?


Maybe its easy if I just tell you what I'm trying to accomplish.  I want to basically fill in a text box and search a database for matches or like matches and display in a datagrid... Does anyone have a working example of this ?
jimwal1940Author Commented:
Guys .. If you can't tell I'm new to this.. so debugging is not really my strong points at this point... ;-)   whats the best way to debug this and determine what a variable is now equal to ...
quoclanCommented:
It's easy ... You can do this ... I want to know more about your code and your database.
quoclanCommented:
So ... if you can tell me more about your code, i think i can help you ....
jimwal1940Author Commented:
well the original code I copied out of a book called Beginning C# Web Applications by Daniel Cazzulino ( its red.. in case you have it )... so I think maybe its easier to just start over using on datagrid and one search window.  

The database can simple be a table called tblUsers with UserId, firstname, Lastname....

jimwal1940Author Commented:
This is the original code.......for the Search Button...

private void btnSearch_Click(object sender, System.EventArgs e)
            {
                  StringBuilder sql = new StringBuilder();

                  sql.Append(@"SELECT TOP ");
                  sql.Append(ConfigurationSettings.AppSettings["searchLimit"]);
                  sql.Append(@"
                        [tblUser].tblUserId, [tblUser].FirstName, [tblUser].Lastname,
                        Place.PlaceID, Place.Name AS PlaceName,
                        PlaceType.Name AS PlaceType, PlaceType.TypeID,
                        TimeLapse.Name AS LapseName, TimeLapse.YearIn,
                        TimeLapse.MonthIn, TimeLapse.YearOut, TimeLapse.MonthOut
                        from [tblUser]
                        LEFT OUTER JOIN TimeLapse ON
                              TimeLapse.tblUserID = [tblUser].tblUserID
                        LEFT OUTER JOIN Place ON
                              Place.PlaceID = TimeLapse.PlaceID
                        Left OUTER JOIN PlaceType ON
                              Place.TypeID = PlaceType.TypeID
                        ");

                  StringBuilder qry = new StringBuilder();

                  if (txtFirstName.Text != String.Empty)
                  {
                        qry.Append("[tbltblUser].FirstName LIKE '%");
                        qry.Append(txtFirstName.Text).Append("%' and ");
                  }

                  if (txtLastName.Text != String.Empty)
                  {
                        qry.Append("[tbltblUser].LastName LIKE '%");
                        qry.Append(txtLastName.Text).Append("%' and ");
                  }
                  if (cbPlace.SelectedItem.Value != "0")
                  {
                        qry.Append("[Place].PlaceID = '");
                        qry.Append(cbPlace.SelectedItem.Value).Append("' AND ");
                  }
                  if (cbType.SelectedItem.Value != "0")
                  {
                        qry.Append("[PlaceType].PlaceID = '");
                        qry.Append(cbType.SelectedItem.Value).Append("' AND ");
                  }
                  if (txtYearIn.Text != String.Empty )
                  {
                        qry.Append("TimeLapse.YearIn = ");
                        qry.Append(txtYearIn.Text).Append(" AND ");
                  }
                  if (txtYearOut.Text != String.Empty )
                  {
                        qry.Append("TimeLapse.YearOut = ");
                        qry.Append(txtYearOut.Text).Append(" AND ");
                  }

                  string filter = qry.ToString();
                  if (filter.Length != 0)

                  {
                        sql.Append(" WHERE");
                        sql.Append(filter.Remove(filter.Length - 4, 4));
                  }
                  SqlDataAdapter ad = new SqlDataAdapter(sql.ToString(), cnFriends);
                  dsResults = new DataSet();
                  ad.Fill(dsResults, "tblUser");

                  if (dsResults.Tables["tblUser"].Rows.Count <
                        Convert.ToInt32(ConfigurationSettings.AppSettings["searchLimit"]))
                  {
                        lblLimit.Text = " Found " +
                              dsResults.Tables["tblUser"].Rows.Count.ToString() +
                              " tblUsers matching your criteria on initial search.";
                  }
                  else
                  {
                        lblLimit.Text = " You're working with the first " +
                              ConfigurationSettings.AppSettings["searchLimit"] +
                              @" results. If you're looking for someone who's not in this list,
                              please search again with a more precise search criteria.";
                  }

                  Session["search"] = dsResults;

                  BindFromSession();


            }
quoclanCommented:
Maybe, i post my code about your request ... this's simple ...
I have textbox named txt1, button named btnSearch, and a datagrid named DataGird1 ... I used table Products of Northwind database ... This is code when i clicked btnSearch :
                        DataSet ds = new DataSet();
                  String strSelect = "Select * from Products where ProductName like '%" + txt1.Text + "%'";
                  SqlConnection con = new SqlConnection("server=.;database=Northwind;uid=sa;pwd=quoclan");
                  SqlDataAdapter da = new SqlDataAdapter(strSelect,con);
                  int k = da.Fill(ds,"User");
                  if (k==0)
                  {
                        DataGrid1.Visible = false;
                  }
                  else
                  {
                        DataGrid1.Visible = true;
                        DataGrid1.DataSource = ds.Tables["User"];
                        DataGrid1.DataBind();
                  }
quoclanCommented:
I don't have this book :) ... your code is not simple :) ... it's so confused ... you can simple it step by step to find your problem .... First, you comment all your code about StringBuilder qry (from StringBuilder qry = new .... to before SqlDataAdapter ad = ...). Hope this will work ...
jimwal1940Author Commented:
Ok.. let me try and build this... and see if I can get it to work ...
jimwal1940Author Commented:
I agree it is confusing... for a beginner
jimwal1940Author Commented:
ok .. this is what I get... I added the system.  Do I need to do anything to the datagrid..etc... ?

Server Error in '/demo' Application.
--------------------------------------------------------------------------------

Shared Memory Provider: The system cannot find the file specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Shared Memory Provider: The system cannot find the file specified.

Source Error:


Line 57:                   SqlConnection con = new SqlConnection("server=.;database=Northwind;uid=sa;pwd=admin");
Line 58:                   SqlDataAdapter da = new SqlDataAdapter(strSelect,con);
Line 59:                   int k = da.Fill(ds,"User");
Line 60:                   if (k==0)
Line 61:                   {
 
---- My Code ---
jimwal1940Author Commented:
This was my code ----

using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace demo
{
      /// <summary>
      /// Summary description for WebForm1.
      /// </summary>
      public class WebForm1 : System.Web.UI.Page
      {
            protected System.Web.UI.WebControls.Button Button1;
            protected System.Web.UI.WebControls.TextBox txt1;
            protected System.Web.UI.WebControls.DataGrid DataGrid1;
      
            private void Page_Load(object sender, System.EventArgs e)
            {
                  // Put user code to initialize the page here
            }

            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                  //
                  // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                  //
                  InitializeComponent();
                  base.OnInit(e);
            }
            
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {    
                  this.Button1.Click += new System.EventHandler(this.Button1_Click);
                  this.Load += new System.EventHandler(this.Page_Load);

            }
            #endregion

            private void Button1_Click(object sender, System.EventArgs e)
            {
                  DataSet ds = new DataSet();
                  String strSelect = "Select * from Products where ProductName like '%" + txt1.Text + "%'";
                  SqlConnection con = new SqlConnection("server=.;database=Northwind;uid=sa;pwd=admin");
                  SqlDataAdapter da = new SqlDataAdapter(strSelect,con);
                  int k = da.Fill(ds,"User");
                  if (k==0)
                  {
                        DataGrid1.Visible = false;
                  }
                  else
                  {
                        DataGrid1.Visible = true;
                        DataGrid1.DataSource = ds.Tables["User"];
                        DataGrid1.DataBind();
                  }
            }
      }
}
quoclanCommented:
You can send me your connection string of your SqlConnection cnFriends ?
jimwal1940Author Commented:
workstation id=L05WALKJH;packet size=4096;user id=sa;password=admin;data source="l05walkjh\laptop_sql";persist security info=False;initial catalog=Friendsdata
quoclanCommented:
How did you config about "data source" ?

And, you can try this connection string :
workstation id=L05WALKJH;packet size=4096;user id=sa;password=admin;data source=l05walkjh;persist security info=False;initial catalog=Northwind
jimwal1940Author Commented:
well I conntected to the Server Explorer and connected that way ....

Do you have a better way...

quoclanCommented:
So, you can get the connection string to Northwind with that way (use Server Explorer)
jimwal1940Author Commented:
Can you give me an example of what the SqlConnection command will look like ?
quoclanCommented:
MSDN : A SqlConnection object represents a unique session to a SQL Server data source.
So, when you create a SqlConnection object, you create a link from your program to Sql Server. With this link, you can access, excute command in Sql Server .... In SqlConnection, the property ConnectionString is important ... Because, you can have many ways to connect to Sql Server (via tcp/ip (default), pipe ...) and ConnectionString decide use which method to connect to Sql Server.
Simple ConnectionString will have some important parameter like this:
"server=localhost;uid=sa;pwd=quoclan;database=Northwind" (order is not important)
1. server (workstation id): your server name or server address (in your case, server=L05WALKJH)
2. uid (user id) : your username to access SqlServer
3. pwd (password) : password of username
4. database (initial catalog) : database in Sql Server you want to access

So, your SqlConnection is :
SqlConnection con = new SqlConnection("server=L05WALKJH;uid=sa;pwd=admin;database=Northwind")

When you use Server Explorer, it has GUI for you to easy set up ConnectionString (with many unnecessary parameters) and will auto generate SqlConnection object for you.

My English is not good .. Hope you understand this.
jimwal1940Author Commented:
ok .. thats what I thought... so I'm good there...

so the line    ...... int k = da.Fill(ds,"tblUser");  That is basically filling the dataset of 'ds'... but what is the tblUser for ?
jimwal1940Author Commented:
and you're english is actually very good..
jimwal1940Author Commented:
do I need to place any DataMembers or anything in the datagrid ?
jimwal1940Author Commented:
Waht about assigning the DataSet to the DataGrid1 ?
quoclanCommented:
and you're english is actually very good..
--> Thanks very much ;)

"tblUser" is a name of DataTable you put data into DataSet ds.
ds can have more DataTable, and you use name for easy memorize, easy understand ...
when access data, with name, you can use :
ds.Tables["tblUser"].Rows.Count ....
(I think "tblUser" don't relate with Sql Server, so, you can name this freely, such as "User" ...)

Good practice to use name to access DataTable in DataSet.

You can use index to access DataTable in DataSet.
Suppose your DataSet ds has 2 DataTables:
ds.Tables.Add("tblUser");
ds.Tables.Add("tblProduct");

You can access tblUser DataTable by:

ds.Tables[0].Rows.Count ....
(or ds.Tables["tblUser"].Rows.Count as above)

and tblProduct :
ds.Tables[1].Rows.Count ...
( or ds.Tables["tblProducts"].Rows.Count as above)
...
You can see, name is easier than index :)

do I need to place any DataMembers or anything in the datagrid ?
---> just DataSource and DataBind() is enough.
      
      
jimwal1940Author Commented:
So this is my error.....


Shared Memory Provider: The system cannot find the file specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Shared Memory Provider: The system cannot find the file specified.

Source Error:


Line 60:
Line 61:                   SqlDataAdapter da = new SqlDataAdapter(strSelect,con);
Line 62:                   int k = da.Fill(ds,"tblUser");
Line 63:                   if (k==0)
Line 64:                   {
 

Source File: c:\inetpub\wwwroot\demo\webform1.aspx.cs    Line: 62

Stack Trace:


[SqlException (0x80131904): Shared Memory Provider: The system cannot find the file specified.
]
   System.Data.ProviderBase.DbConnectionPool.GetConnection(Object owningObject) +317
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnectionBase owningConnection) +90
   System.Data.ProviderBase.DbConnectionClosed.Open(DbConnectionBase outerConnection) +189
   System.Data.ProviderBase.DbConnectionBase.Open() +62
   System.Data.SqlClient.SqlConnection.Open() +168
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +86
   demo.WebForm1.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\demo\webform1.aspx.cs:62
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +232
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +5
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +31
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5157

 
jimwal1940Author Commented:
I just can't believe I cant get this to work... I would think its easy...
jimwal1940Author Commented:
How can I make sure I am getting connectity with my SQL connection ?
quoclanCommented:
This indicate your ConnectionString is not correct ...
You can use Server Explorer to auto generate ConnectionString to Northwind ?
jimwal1940Author Commented:
I'm trying but it doesn't seem to work ....
jimwal1940Author Commented:
I don;t think I'm doing the auto generate right /....
quoclanCommented:
Use your database Friendsdata and your old ConnectionString :
workstation id=L05WALKJH;packet size=4096;user id=sa;password=admin;data source="l05walkjh\laptop_sql";persist security info=False;initial catalog=Friendsdata

Create table named Products in Friendsdata have the same structure of Products of Northwind. Input some value ... and test.
I think your Sql Server is configed not by default. I don't understand more about Sql Server ...

(use your old SqlConnection cnFriends)
               DataSet ds = new DataSet();
               String strSelect = "Select * from Products where ProductName like '%" + txt1.Text + "%'";
//place declare of cnFriends

               SqlConnection cnFriends = ......
               SqlDataAdapter da = new SqlDataAdapter(strSelect,cnFriends);
               int k = da.Fill(ds,"User");
               if (k==0)
               {
                    DataGrid1.Visible = false;
               }
               else
               {
                    DataGrid1.Visible = true;
                    DataGrid1.DataSource = ds.Tables["User"];
                    DataGrid1.DataBind();
               }
jimwal1940Author Commented:
How can I test to see if the DAMN SqlConnection works....

This is so frustrating...
jimwal1940Author Commented:
Ok I'm using the cnFriends I made before and dropped your code in the page and reworked it.....

This its the NEW error....

The IListSource does not contain any data sources.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: The IListSource does not contain any data sources.

Source Error:


Line 134:                        grdResults.Visible = true;
Line 135:                        grdResults.DataSource = dsResults.Tables["User"];
Line 136:                        grdResults.DataBind();
Line 137:                  }
Line 138:                  
 
quoclanCommented:
Please show me the code when button is clicked.
jimwal1940Author Commented:
ok quoclan .. I think I figured it out... but I have another question for you...

If I want to search on a firstname that starts with a 'J' and also a Last name that starts with a 'S'  ... how do I include both of those in the search... ?

I used the same code that you gave me earlier and got the datagrid to actually write... I'm givin gyou all my points...
quoclanCommented:
I think your question is to add MORE criterial like "firstname that starts ..... and lastname starts with a 'S'" ...

Here are more code for select string :
//Your strSelect first has WHERE clause
strSelect += " and firstname like 'J%' and lastname like 'S%'";

If i misunderstand, please tell me.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jimwal1940Author Commented:
Okkkk.. SO I FINALLY got this to work in the datagrid.. but now the next question....

How can I get the paging function to work on this datagrid ?  like Next Page /.... and Previous Page
quoclanCommented:
Sorry, recently, I have been busy ....
I will answer your question about paging function as soon as possible.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.