7 Replies - 3077 Views - Last Post: 28 June 2012 - 09:28 AM Rate Topic: -----

#1 matt9537  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 08-May 12

Invalid Object Name when using SQL and C#

Posted 28 June 2012 - 03:03 AM

Been wrestling with this for hours but I cannot find a reason why this won't work. Knowing me, its probably obivious (so apologises lol)

             SqlConnection sql = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\UserAccounts.sql.mdf;Initial Catalog = UserAccounts; Integrated Security=True;Connect Timeout=30;User Instance=True"); 
            SqlDataAdapter sda = new SqlDataAdapter();

            sda.InsertCommand = new SqlCommand("INSERT INTO Details VALUES (@UserName, @Password)", sql);
            sda.InsertCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName.Text;
            sda.InsertCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = Password.Text;

            sql.Open();
            sda.InsertCommand.ExecuteNonQuery();
            sql.Close();



I'm working with WindowsForms, where the concept of the program is to allow the user to register as a user by entering a 'userName' and 'password and storing it into the database 'userAccounts' in the table 'Details'.
This code is executed when the user clicks onto the 'okay' button.

However, I keep getting the cannot find table 'Details'

thank you in advance

Is This A Good Question/Topic? 0
  • +

Replies To: Invalid Object Name when using SQL and C#

#2 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 486
  • Joined: 13-June 12

Re: Invalid Object Name when using SQL and C#

Posted 28 June 2012 - 05:00 AM

I think it's because you haven't associated the SQL Connection with the SQLDataAdapter. You can't just open a connection then run a SQLDataAdapter's query hoping it will work - the sda object doesn't have a connection to query.

Also, I believe you don't need to explicitly open and close then connection when using SQLDataAdapters, because you associate a connection and it is automatically opened and closed when the methods are invoked.

It doesn't seem like SQLDataAdapter has a constructor that only takes a SQLConnection as a parameter, nor does it have a member (according to MSDN. Further, I don't think you're doing it right - a SQLDataAdapter is supposed to bridge the gap between a DataSet and a database, and to send updates back to the DB you should use the .Update() method. I think what you need to use is a SQLDataReader and a SQLCommand(which has a SQLConnection member). There's a great ADO tutorial onC# Station.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#3 matt9537  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 08-May 12

Re: Invalid Object Name when using SQL and C#

Posted 28 June 2012 - 05:53 AM

Thanks for your help. It makes sense. :)

I followed the tutorial as mentionned at: http://www.csharp-st...DotNet/Lesson02

However, again, I'm having the same error. It can't find my table :/

SqlConnection sql = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\UserAccounts.sql.mdf;Initial Catalog = UserAccounts; Integrated Security=True;Connect Timeout=30;User Instance=True"); 
            sql.Open(); 
            SqlDataReader read = null;

            SqlCommand cmd = new SqlCommand("Select * from Details", sql);

            read = cmd.ExecuteReader();

            while (read.Read())
            {
                Console.WriteLine(read[0]);
            }
        
      
            if (read != null)
            {
                read.Close();
            }

            if (sql != null)
            {
                sql.Close();
            }


If it helps. I have been using the following websites for guidence:

http://www.youtube.c...h?v=OJfYSZzZOP8

http://www.homeandle...harp_s12p3.html

Thanks for help :)
Was This Post Helpful? 0
  • +
  • -

#4 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 486
  • Joined: 13-June 12

Re: Invalid Object Name when using SQL and C#

Posted 28 June 2012 - 06:19 AM

Where are you getting the Connection string from? There's nothing inherently wrong with the rest of the code (other than a few try...catch...finally statements for preference).
Was This Post Helpful? 0
  • +
  • -

#5 matt9537  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 08-May 12

Re: Invalid Object Name when using SQL and C#

Posted 28 June 2012 - 07:37 AM

I had tried many tutorials on getting the connection string but none work. The only way it seemed to "work" was using this website: http://www.homeandle...harp_s12p3.html

I have run a test and the connection can be openned and closed successfully (a message box is displayed confirming this). However, when it comes to finding the table it doesn't work :/

In SQL, the table can be accessed, modfied and viewed without problem :s
Was This Post Helpful? 0
  • +
  • -

#6 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 486
  • Joined: 13-June 12

Re: Invalid Object Name when using SQL and C#

Posted 28 June 2012 - 07:53 AM

This might be a stupid question, but are you certain there is a table in the DB called "Details"? Is it your own DB or has it come from elsewhere? You could benefit from manually exploring it.

Another thought is that you have \ in the string but in C# when you use \ in a string you are calling an escape sequence. Try and change line 1 to
SqlConnection sql = new SqlConnection(@"Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\UserAccounts.sql.mdf;Initial Catalog = UserAccounts; Integrated Security=True;Connect Timeout=30;User Instance=True"); 
- The @ symbol effectively tell the compiler to treat the \ as a literal \ and not call escape sequences.
Was This Post Helpful? 0
  • +
  • -

#7 matt9537  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 08-May 12

Re: Invalid Object Name when using SQL and C#

Posted 28 June 2012 - 08:54 AM

Hi,
Thanks a lot for your help MrShoes, I really appiricate your time and effort.

As I anticipated, the error was simple - the table wasn't there.
I therefore, remade the database, this time within Visual Studio (as opposed to the previous one created in microsofts SQL server manager).
Now, the table is there and that errors gone - however, anothers appeared. An SQL exception regarding an error with the syntax around 'Table'

thanks a lot for your help :)

 System.Data.SqlClient.SqlConnection sql = new System.Data.SqlClient.SqlConnection();
    
            sql.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\user\\Documents\\Visual Studio 2008\\Projects\\Database\\Database\\Database.mdf; Initial Catalog= Database; Integrated Security=True;Connect Timeout=30;User Instance=True";
            sql.Open();
            SqlDataReader read = null;
            
            SqlCommand cmd = new SqlCommand("SELECT * FROM Table", sql);
           
            read = cmd.ExecuteReader();

            while (read.Read())
            {
                Console.WriteLine(read[0]);
            }


            sql.Close();
            read.Close();  

Was This Post Helpful? 0
  • +
  • -

#8 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 5960
  • View blog
  • Posts: 23,238
  • Joined: 23-August 08

Re: Invalid Object Name when using SQL and C#

Posted 28 June 2012 - 09:28 AM

Quote

However, I keep getting the cannot find table 'Details'


Quote

As I anticipated, the error was simple - the table wasn't there.


Lesson learned, read your errors? I mean, it was right there in your original post.

Quote

An SQL exception regarding an error with the syntax around 'Table'


So, do you actually have a table named Table? If so,

1. That is an inordinately stupid name for a table containing data. The table name should be a name which describes the data contained therein.
2. It's a SQL Reserved Word, so you SHOULDN'T use it as a name for that reason as well.

Quote

Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, you can do this only by using delimited identifiers.

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1