Member Avatar for 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,,

  • 4 Contributors
  • 12 Replies
  • 2K Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Ketsuekiame

Dani AI

Generated

That error usually means the procedure is running, but in a database where the table does not exist. If you were calling the wrong proc name you would get “Could not find stored procedure…”, so “Invalid object name ‘dbo.Users’” points to database context, not the proc name. As a quick sanity check in SSMS, run:

SELECT DB_NAME() AS current_db, OBJECT_ID('dbo.Users','U') AS users_id;

If users_id is NULL, you are not in the database that owns the table. Also confirm the table’s schema really is dbo (SQL Server resolves one-part names via the user’s default schema first, then dbo). (learn.microsoft.com)

Two easy ways to make this class of issue disappear:

  • Fully qualify the call from C#: use the three-part name so the procedure runs in the correct database regardless of the connection’s default.
using (var conn = new SqlConnection(cs))
using (var cmd  = new SqlCommand("YourDbName.dbo.InsertUser", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@userName", SqlDbType.NVarChar, 50).Value = userName;
    cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50).Value = password;
    conn.Open();
    var rows = cmd.ExecuteNonQuery(); // rows affected
}

ExecuteNonQuery is the right choice for a proc that does not return a result set; it returns the number of rows affected. (learn.microsoft.com)

  • Or, set the database explicitly in your connection string via the Database/Initial Catalog keyword (they are synonyms). (learn.microsoft.com)

A couple of extras inspired by @Mike Askew and ’s comments:

  • Verify the table exists under the expected schema: SELECT s.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id=t.schema_id WHERE t.name='Users';.
  • Your procedure takes @ID but does not use it. Either remove it, or make the column an IDENTITY and return the new value with an OUTPUT clause if you need it later.
  • Do not store plaintext passwords. Hash and salt on the .NET side (e.g., PBKDF2/bcrypt/Argon2) before inserting. See the OWASP Password Storage Cheat Sheet for current guidance. (cheatsheetseries.owasp.org)

Recommended Answers

All 12 Replies

Member Avatar for Mike Askew

Have you confirmed this table exists?

Member Avatar for IT_Techno

yes it exists

Member Avatar for 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.

Edited by Ketsuekiame
Member Avatar for 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));
        }
    }
}
Member Avatar for 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.

Member Avatar for 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?

Member Avatar for 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.

Member Avatar for 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

Member Avatar for Ketsuekiame

Can you post your ConnectionString please?

Member Avatar for 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

Member Avatar for 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"

Member Avatar for Ketsuekiame

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.