9

I have an old asp.net 1 project (it works fine on old server, mytable exist in db. Now I am trying to upgrade it to asp.net 4

My connection string is:

<add key="SqlConnection" 
     value="DRIVER={SQL Server};SERVER=bel\SQLEXPRESS;Trusted_connection=yes;DAT­ABASE=mydb;option=3;"/>

I get error

ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'mytable'.

OdbcCommand dataCommand = new OdbcCommand("select * from mytable", dataConnection);
dataCommand.CommandTimeout = 900;
OdbcDataReader dataReader = dataCommand.ExecuteReader(CommandBehavior.CloseConnection);

When I wrote SQL as select * from mydb.dbo.mytable all works fine

What I should change in db settings (security, schema, dbo) or in connection string?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John
  • 864
  • 1
  • 11
  • 26
  • Just always specify the schema - e.g. use `dbo.MyTable` instead of just `MyTable` - so try `select * from dbo.mytable` - does that work? – marc_s Sep 21 '11 at 21:21

1 Answers1

25

My guess, seeing that you're using ODBC, is that your ODBC connection doesn't specify a default database, and so it's using master.

You can either:

  • specify the database in your connection string using "Database=myDBname" or "InitialCatalog=myDBname"
  • change the default database in your ODBC connection, as shown here in XP/Server 2003 SQL Server ODBC
John N
  • 1,755
  • 17
  • 21
  • another thing worth trying is toggling b/w `Database` and `InitialCatalog`. I was able to connect to Azure Synapse with `InitialCatalog` but couldn't actually query until I switched the param to `Database`. – Anders Swanson Jul 14 '20 at 16:36