AdventureWorks, invalid object name error

Hi, I will start step by step:

1. a new web site with VS 2005.

2. I added a sqldatasouce and connect with AdventureWorks sample database ,which comes with sql server 2005 developer edition, selected by drop-down list. [ server name:(local) ]

3. Test connection. It is OK.

4. Saved as 'AdventureWorksConnectionString'.

5. Some columns are selected in the 'product' table.

6. At the end while testing query with 'test query' button it gives:

"There was an error executing the query. Please check the syntax of the command and if present, the types and values of parameters and ensure the are correct.

Invalid object name 'Product'. "

7. However when I choice NorthWind database sample I installed externally, there is no problem.Moreover, when I choice AWBuildVersion table in the AdventureWorks, and it's columns, there is also no problem.

8. I compared NorthWind and AdventureWorks security properties in the SQL server managment studio, but can't find any differences.

9. I have been searching all the web since two days.

10. Thanks.

can you paste the SQL command that fails...

|||

There are two problems.

The AdventureWorks database uses a "Schema.Table" naming convention that the SQLDataSource Wizard doesn't pickup.

Table Names are Case-Sensitive in the AdventureWorks database.

Workaround:

Instead of "Specify columns from table or view", select "Specify a custom SQL statement..."

On the next screen, use the "Query Builder" to generate the SQL statement. It will pickup the schema name.

PS: After I post this message, I'm going to move this thread to the SQL database forum. I think it's more appropriate there.

|||

Yeah, it works... Thank you very much... But,I couldn't figure out why this happened to me, because I have started to read"Addison Wesley ASP NET .2.0 Illustrated". In that book, it is theprocedure how to implement AdventureWorks database in Chapter 2, and does normention any info of "Schema.Table" and SQLDataSouce relation. Ihave tried some method to accomplish that with "Specify columns from tableor view" method:

Source Code of"Specify columns from table or view" method which gives error:

<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
ProviderName="<%$ConnectionStrings:AdventureWorksConnectionString.ProviderName %>"
SelectCommand="SELECT [ProductID], [Name], [ProductNumber], [ListPrice],[Color], [SafetyStockLevel], [StandardCost], [Size] FROM[Product]">
</asp:SqlDataSource>

Source Code of "Specify a custom SQL statement..."and applying "Query Builder" method which gives NO error:

<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
SelectCommand="SELECT ProductID, Name, ProductNumber, ListPrice, Color,SafetyStockLevel, StandardCost, Size FROMProduction.Product">
</asp:SqlDataSource>

What I could try as a beginner to get a validoutput is changing[Product] as Production.Product.Of course, it gives the same 'invalid object name' error.

As I told you before, AWBuildVersion tablegives no error with "Specify columns from table or view" method:

<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
ProviderName="<%$ ConnectionStrings:AdventureWorksConnectionString.ProviderName%>"
SelectCommand="SELECT [SystemInformationID], [Database Version] ASDatabase_Version, [VersionDate], [ModifiedDate] FROM [AWBuildVersion]">
</asp:SqlDataSource>

I have understood from your note theonly way to implement "Schema.Table" s is use the "QueryBuilder", right?

And for your consideration here is Stack Trace:

------

SqlException (0x80131904): Invalid object name 'Product'.]
  System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
  System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2406
  System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
  System.Data.SqlClient.SqlDataReader.get_MetaData() +62
  System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294
  System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1038
  System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
  System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20
  System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107
  System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10
  System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
  System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
  System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
  System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
  System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1657
  System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13
  System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140
  System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68
  System.Web.UI.WebControls.GridView.DataBind() +5
  System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61
  System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67
  System.Web.UI.Control.EnsureChildControls() +97
  System.Web.UI.Control.PreRenderRecursiveInternal() +50
  System.Web.UI.Control.PreRenderRecursiveInternal() +171
  System.Web.UI.Control.PreRenderRecursiveInternal() +171
  System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731

------

 Again, Thank you very much to help and save my time.

sql

ADODB.Recordset Invalid Column Name VB6

I'm trying to limit the recordset retuned in the sub below by a
variable called vcnumber. I'm not sure what the problem is but I keep
getting the error "Invalid Column Name" when I set sqlstring to "select
* from vc_names where vc_case_number =" & vcnumber. If I don't try to
limit the recordset returned and just set sqlstring to "select * from
vc_names", it works. If there is another\better way to do this, I am
open to suggestions. Thanks!
========================================
===================
Public Sub populatename()
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectString As String
Dim sqlstring As String
Set adoConnection = CreateObject("ADODB.Connection")
Set adoRecordset = CreateObject("ADODB.Recordset")
connectString = "Provider=SQLOLEDB.1;Password=password;Persist Security
Info=True;User ID=user;Initial Catalog=db;Data Source=server"
adoConnection.Open connectString
sqlstring = "select * from vc_names where vc_case_number =" & vcnumber
adoRecordset.Open sqlstring, adoConnection, adOpenDynamic,
adLockOptimistic
Do Until adoRecordset.EOF
List2.AddItem adoRecordset!first_name & " " & adoRecordset!middle_name
& " " & adoRecordset!last_name
adoRecordset.MoveNext
Loop
adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing
End Sub
========================================
========================if vs_case_number is a char field, remember to add quotes i.e.
sqlstring = "select * from vc_names where vc_case_number = '" &
vcnumber & "'"

ADO error: Invalid object name <object name>.

I tried to create a report that is based on a stored procedure and I get this error when I try to verify the SQL.

ADO error: Invalid object name '#MatPatAbsence'.

If I click on the RUN exclaimation mark, I get no data, but do get the column names.

#MatPatAbsence is a temporary table created in the stored procedure and deleted once used.

I have also created another similar report from a stored procedure that has temporary tables and all are working fine.

also this stored procedure runs fine in query analyzer.

Can any one help please?

paste your query, I will take a look