1,105,399 Community Members

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

Member Avatar
IT_Techno
Junior Poster in Training
84 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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,,

Member Avatar
Mike Askew
Posting Shark
927 posts since Nov 2010
Reputation Points: 132 [?]
Q&As Helped to Solve: 85 [?]
Skill Endorsements: 25 [?]
Featured
 
0
 

Have you confirmed this table exists?

Member Avatar
IT_Techno
Junior Poster in Training
84 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

yes it exists

Member Avatar
Ketsuekiame
Posting Virtuoso
1,676 posts since May 2010
Reputation Points: 793 [?]
Q&As Helped to Solve: 231 [?]
Skill Endorsements: 27 [?]
 
0
 

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.

Member Avatar
Cameronsmith63
Junior Poster
108 posts since May 2010
Reputation Points: 9 [?]
Q&As Helped to Solve: 4 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Ketsuekiame
Posting Virtuoso
1,676 posts since May 2010
Reputation Points: 793 [?]
Q&As Helped to Solve: 231 [?]
Skill Endorsements: 27 [?]
 
0
 

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
Cameronsmith63
Junior Poster
108 posts since May 2010
Reputation Points: 9 [?]
Q&As Helped to Solve: 4 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Ketsuekiame
Posting Virtuoso
1,676 posts since May 2010
Reputation Points: 793 [?]
Q&As Helped to Solve: 231 [?]
Skill Endorsements: 27 [?]
 
0
 

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
IT_Techno
Junior Poster in Training
84 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Ketsuekiame
Posting Virtuoso
1,676 posts since May 2010
Reputation Points: 793 [?]
Q&As Helped to Solve: 231 [?]
Skill Endorsements: 27 [?]
 
0
 

Can you post your ConnectionString please?

Member Avatar
IT_Techno
Junior Poster in Training
84 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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
IT_Techno
Junior Poster in Training
84 posts since Dec 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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"

Question Answered as of 1 Year Ago by Ketsuekiame, Cameronsmith63 and Mike Askew
Member Avatar
Ketsuekiame
Posting Virtuoso
1,676 posts since May 2010
Reputation Points: 793 [?]
Q&As Helped to Solve: 231 [?]
Skill Endorsements: 27 [?]
 
0
 

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

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article