stored procedure in c# 'Invalid object name 'dbo.Users'

[IT_Techno]

hi
i have create database with tables using sql server 2008, having table named 'Users'
i have create this stored procedure :

USE [Licenses_DB]
GO
/****** Object:  StoredProcedure [dbo].[InsertUser]    Script Date: 02/17/2013 23:25:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertUser]
    @ID int ,
    @userName NVARCHAR(50),
    @password NVARCHAR(50)

AS
BEGIN
     SET NOCOUNT ON

    INSERT INTO dbo.Users
    (
        UserName,
        UserPassword
    )
    VALUES 
    (
        @userName,
        @password
    )
END
Go

then i used it inside C# Visual Studio 2010 to execute stored procedure with this code :

SqlConnection conn = new DBConnection().Conn();

                    try
                    {
                        SqlCommand sqlCMD = new SqlCommand("InsertUser", conn);

                        sqlCMD.CommandType = CommandType.StoredProcedure;
                        sqlCMD.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 50, "ID")).Value = 1;
                        sqlCMD.Parameters.Add(new SqlParameter("@userName", SqlDbType.NVarChar, 50, "UserName")).Value = userBox.Text.Trim();
                        sqlCMD.Parameters.Add(new SqlParameter("@password", SqlDbType.NVarChar, 50, "UserPassword")).Value = passwordBox.Text.Trim();

                        sqlCMD.ExecuteNonQuery();
                    }
                    catch(Exception exc)
                    {
                        throw new System.ArgumentException(exc.Message);
                    }

when i run it throw exception "Invalid object name 'dbo.Users'" ???

what is the problem in this code? how can i solve it ?

Best Regards,,

[Mike Askew]

Have you confirmed this table exists?

[IT_Techno]

yes it exists

[Ketsuekiame]

Try executing the stored procedure in SQL Studio and see if it gives you the same result.

It could be that;
A) You created the Users table on a different schema to dbo. (Kind of unlikely because you'd know about it...)
B) Your SQL connection string hasn't selected an initial database to use and is connected to 'master' by default.
C) Try using [dbo].[Users]. It shouldn't make a difference but consistency doesn't hurt.

[Cameronsmith63]

Could be that you not referencing your table properly.

But why not use something similar to this?

using (SqlCommand thisCommand = new SqlCommand(conn.ConnectionString))
{
    try
    {
        SqlCommand sqlCMD = new SqlCommand("InsertUser", conn);

        sqlCMD.CommandType = CommandType.StoredProcedure;

        sqlCMD.Parameters.Add(new SqlParameter(("@ID", SqlDbType.Int, 50, "ID")).Value = 1;
        sqlCMD.Parameters.Add(new SqlParameter("@userName", SqlDbType.NVarChar, 50, "UserName")).Value = userBox.Text.Trim();
        sqlCMD.Parameters.Add(new SqlParameter("@password", SqlDbType.NVarChar, 50, "UserPassword")).Value = passwordBox.Text.Trim();

        thisConnection.Open();
        object ret = command.ExecuteScalar();

        catch (Exception ex)
        {
            MessageBox.Show((ex.Message));
        }
    }
}

[Ketsuekiame]

To have using statements is better but not necessary. Also I presume you meant to include your connection in the using statement? The OP would also be able to put the SqlCommand into another using statement.

ExecuteScalar is unnecessary because his procedure returns no values.

[Cameronsmith63]

yeah i actually copied from my code and pasted his stuff here and there.

But executescalar is best used when you do not want to return any values in any case right?

[Ketsuekiame]

ExecuteScalar is used when you return a single value from your procedure. You can typically use this to return things such as the row identity if you're inserting or an aggregate value or something along those lines.

ExecuteNonQuery is used when you have nothing to return from the procedure. What you get as the result is the number of rows affected by your query. Importantly, you can use ExecuteNonQuery when you expect OUTPUT parameters, but no specific return value. Like a void method that takes a ref parameter argument.

[IT_Techno]

i have try execute stored procedure in sql studio but it's run well, then i run it again in visual studio it does not work. the same error
:(

and i have try all your suggestion, nothing work

[Ketsuekiame]

Can you post your ConnectionString please?

[IT_Techno]

this is my Connection Strign inside App.config:

    <add name="SQLConnectionString" connectionString="Data Source=MINFO-HP\SQLEXPRESS;Persist Security Info=true;User ID=sa; Password=root" providerName="System.Data.SqlClient" />

it's local server not remote server

[IT_Techno]

thank you for every one i have solve that problem, the problem was in Database Connection :

The old one is :

<add name="SQLConnectionString" connectionString="Data Source=MINFO-HP\SQLEXPRESS;Persist Security Info=true;User ID=sa; Password=root" providerName="System.Data.SqlClient" />

the new one is :

<add name="SQLConnectionString"
connectionString="Data Source=MINFO-HP\SQLEXPRESS;Initial Catalog=Licenses_DB;Persist Security Info=True;User ID=sa;Password=root"
providerName="System.Data.SqlClient" />

i have forget to put : "Initial Catalog=Licenses_DB"

[Ketsuekiame]

In my first post you will see that I gave you this as possible issue B. ;)