Results 1 to 26 of 26

Thread: Could not find Stored Procedure?

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Could not find Stored Procedure?

    I've created a .mdf file in my web app. I then opened SSMS and attached to the .mdf file. I added a table and some records and then a Stored Procedure that takes to parameters. I've tested the SP within SSMS and it works as it should. However, when I try to access it from the web app, I get the "Could not find Stored Procedure: spValidateLoginInfo".

    Now, I switched gears and used inline sql in the app and when I run it, now I get the infamous "Ojbect reference not set to an instance of an object". So, I'm thinking this error could be the original problem but I have no idea why it's happening. Below is the method it occurs in.

    Code:
            public static string ValidateLogin(string userName, string passWord)
            {
                try
                {
                    if (cnn.State == ConnectionState.Open)
                    {
                        string sqlquery = "SELECT Id FROM MyUsers WHERE UPPER(Username) = '" + userName.ToString().ToUpper() + "' AND UPPER(Password) = '" + passWord.ToString().ToUpper() + "'";
                        Int32 retValue = 0;
    
                        sqlcmd = new SqlCommand(sqlquery, cnn);
                        //sqlcmd = new SqlCommand("spValidateLoginInfo", cnn);
                        sqlcmd.CommandType = CommandType.Text;
    
                        sqlcmd.Parameters.AddWithValue("@Username", userName);
                        sqlcmd.Parameters.AddWithValue("@Password", passWord);
    
                        retValue = (Int32)sqlcmd.ExecuteScalar();           'Exception is thrown on this line
    
                        if (retValue > 0)
                        {
                            return "";
                        }
                    }
                }
    
                catch (Exception ex)
                {
                    errorMsg = ex.Message;
                    MsgBox(ex.Message);
                    return errorMsg;
                }
            }
    Blake

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    Firstly, why are you using string concatenation to insert values into your SQL code and then adding parameters as well? Secondly, when you were trying to use a sproc, did you set the CommandType to StoredProcedure? Thirdly, given that it looks like 'sqlcomd' can't be null, you should be looking at the stack trace of the exception to see where the exception was actually thrown to gauge what reference might be null.

    Also, the way you describe creating your database is weird. If you are going to create the MDF data file in VS then why not create the sproc, etc, in VS too? If you're going to use SSMS to create sprocs, etc, then why not create the database in SSMS? I'm not sure whether that mixing and matching may have a hand in the issues but there seems no good reason for it regardless. I guess that, with regards to the original issue, you should actually look at the data file in VS and make sure that the sproc actually is there, given that the error message suggests that it's not. Maybe you managed to make changes to a copy or something, e.g. you attached the copy in the output file instead of the original file in the project folder.

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Could not find Stored Procedure?

    My guess (and it is a guess) is that your application is creating a new development database every time it runs (or more likely, every time you introduce a schema change). So the database you added the sproc to is not the same as the database you're looking at now in visual studio. Either that or you're accessing the database under a different login and the application login does not have permission to that SPROC. I'm with JM, though, do all your management in either SSMS or Visual Studio. The mix and match approach should work but really just serves to add confusion for no real benefit.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Could not find Stored Procedure?

    Do not name your SPROCS to start with "sp" - that makes the SQL engine look in the MASTER database for what is called a "special" stored procedure.

    "sp" was reserved in early versions of MS SQL - I believe in latest versions this is no longer a rule. But no reason to not adhere to it...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    jmc - Well, to start, I've never used a .mdf file before so I'm not sure how do things with it in VS. The query string should have been commented out. I just forgot
    to show that in the post. Also, when I look at the table in SSMS, it is clearly different than in VS, plus the SP doesn't exist when I look at it in VS so that
    makes sense there.

    funkydexter - Since I haven't used .mdf files before, I couldn't tell you if there is a new instance every time I run the app.

    szlmany - I was unaware of the naming issue. I've alway used "sp" as the prefix for my SP's even when I'm working from SSMS. For naming conventions sakes,
    what is the correct way to name a SP...as far as a prefix is concerned?


    With all this said, I'm trying to figure out how to create the SP within VS. I can create the shell but how do you save it to where it actually shows up under "Stored Procedures"? Also, how can I manually add data to the tables from VS?

    Thanks guys!
    Blake

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    Well, I made past not being able to find the SP to "Object reference not set to an instance of an object" error. I'm not sure how to tell which object it is. Below is my code.

    Code:
            public static string ValidateLogin(string userName, string passWord)
            {
                string strMsg = "";
    
                try
                {
                    if (cnn.State == ConnectionState.Open)
                    {
                        Int32 retValue = 0;
    
                        sqlcmd = new SqlCommand("ValidateLoginInfo", cnn);
                        sqlcmd.CommandType = CommandType.StoredProcedure;
    
                        sqlcmd.Parameters.AddWithValue("@Username", userName);
                        sqlcmd.Parameters.AddWithValue("@Password", passWord);
    
                        retValue = (Int32)sqlcmd.ExecuteScalar();     'Exception is thrown here
                    }
    
                    string val = "Made it here!";
                }
    
                catch (Exception ex)
                {
                    strMsg = ex.Message;
                    MsgBox(ex.Message);
                }
    
                return strMsg;
            }
    Blake

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    With regards to the NullReferenceException, as I said in an earlier post, it doesn't appear to be 'sqlcmd' and I don't see that a cast to Int32 from DBNull could throw that particular exception. That means that it must be inside the call to ExecuteScalar somewhere, if indeed it is that call that throws. The first step to diagnosing the issue is to look at the stack trace for the exception, to determine exactly WHERE it happens, which will hopefully indicate WHAT reference is null.

    With regards to working with MDF data files in VS, it goes like this. After adding a Service-based Database to your project, you should see the MDF file listed in the Solution Explorer. Double-click it to open it in the Server Explorer. From there, it's much like using SSMS, although not quite the same. You right-click the Tables node and select Add New Table to create a new table. You define the columns much as you do in SSMS but you can also directly modify the SQL script that generates the table. Click Update to execute the SQL and create the table. Once the table exists, you can right-click on it in the Server Explorer and select Show Table Data to modify the data directly.

    Creating a sproc is much the same. You right-click the Stored Procedures node in the Server Explorer and select Add New Stored Procedure and go from there.

    Note that any modifications made via the Server Explorer are made to the source data file. Just like Access database and any other local data files, MDF files are copied from the project folder to the output folder when you build and any changes made at run time are made to the working copy. Any time that working copy is overwritten, which is every time you build by default, changes made at run time will be lost but changes made via the Server Explorer remain.

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    jmc,

    Thanks for that clarification. However, you lost me on the last sentence.

    Any time that working copy is overwritten, which is every time you build by default, changes made at run time will be lost but changes made via the Server Explorer remain.
    Are you saying that during runtime, let's say my app saves a record...it won't be saved to the MDF?
    Blake

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    Quote Originally Posted by blakemckenna View Post
    Are you saying that during runtime, let's say my app saves a record...it won't be saved to the MDF?
    No I'm not. It works exactly the same way as it does for an Access database, if you're familiar with that. You add the data file to your project and, when you build, a copy is made in the output folder. At run time, any changes you save are saved to that working copy. If you open the source data file then you won't see those changes because they weren't made to that file, but you'll see them through the application because it connects to the working copy, not the source file.

    By default, the Copy To Output Directory property of the source file will be set to Copy Always. That means that every time you make code changes and run, your project will be built and the working copy will be overwritten by a new copy of the source file, meaning any changes you made at run time will be lost. If you don't want that to happen then set that property to Copy If Newer. That means that your working copy will only be overwritten if you actually change the source file, e.g. change the schema, modify the default data or add a sproc.

    For more information on how local data files are managed, follow the first link in my signature below.

  10. #10

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    Making progress here. Now through looking at the auto watch that I set. I'm getting the following exception (first time I've ever seen it).

    Unable to cast object of type 'System.Data.SqlClient.SqlInternalConnectionTds' to type 'System.Data.SqlClient.SqlInternalConnectionSmi'.

    I suspect it might have something to do with my connection string but not positive on this. Here is my connection string below.

    static string connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=MyFriends;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailove r=False";

    Does anyone have a clue as to what this means?

    Thanks,
    Blake

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    I'd say that it has something to do with the fact that, as I said, you mixed and matched the way you built the database. You should choose one way and stick with it. If you want to use an attached database, create the database and build the schema in SSMS and use the Data Source and Initial Catalog attributes. If you want to use a local data file, create the database and build the schema in VS and use the Data Source and AttachDbFilename attributes. The only other attribute you should generally need in your connection string is Integrated Security.

  12. #12

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    jmc,

    Since I've never used .mdf files, do you know what the pros and cons of using .mdf files are? When should .mdf files be used? I will probably end up going with SSMS. It's what I'm used to anyway.

    Thanks,
    Blake

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    I did a bit of searching and it appears that TDS is Tabular Data Stream and is the format of messages sent between SQL Server servers and clients. The latest version I could find mention of was TDS 8.0, which supported SQL Server 2000 and earlier. I can't find any information on SMI but my guess is that it is a newer protocol intended to either fully or partially replace TDS. There is no publicly available documentation for TDS so they may not even be mentioning SMI publicly at all.

    In your specific case, it looks like a connection that speaks TDS is being created but a connection that speaks SMI is expected at some point. It may be, as I suggested, due to the peculiar way you created your database or it might just be your connection string. I'd get rid of everything but the three attributes I mentioned earlier. Also, you really need to decide whether you're using a local data file or not because that half-and-half approach may cause other issues.

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    Quote Originally Posted by blakemckenna View Post
    jmc,

    Since I've never used .mdf files, do you know what the pros and cons of using .mdf files are? When should .mdf files be used? I will probably end up going with SSMS. It's what I'm used to anyway.

    Thanks,
    By adding an MDF file to your project, the database becomes part of the application. It means that you can manage and deploy the database as part of the application with no need to venture out of VS. After deployment, it means that copying or moving the application folder will also copy or move the database, as you'd expect if it is part of the application. Each machine the application runs on has its own database that gets attached to its own SQL Server instance at run time automatically. To the user, it's hardly different, if at all, than using an Access database or the like.

    Using an always-attached database means that you can have a single database that multiple clients can connect to, which is often desirable. Even if each client still has their own database, it means that an admin can access the database independently of the application. It does mean that there's an extra step in the deployment though, and you have to decide exactly how to create that database.

    It's also worth noting that you can easily use an MDF file during development and then switch to database attached to an instance on a server after deployment if you want. In that case, you'd simply not deploy the MDF file with the app and change the connection string in the config file as part of the deployment.

  15. #15

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    Wow, that's some good info. I like that flexibility. I tried what you said in previous post. I scrapped the existing .mdf file and started over creating the .mdf file in VS along with the tables and SP. Plus I added data to the table. However, when I ran the app, I still got the same message as in post #10.
    Blake

  16. #16
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    Quote Originally Posted by blakemckenna View Post
    However, when I ran the app, I still got the same message as in post #10.
    What does your connection string look like now?

  17. #17
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    It should be something like this:
    Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename="|DataDirectory|\Database1.mdf";Integrated Security=True
    I just got that by double-clicking the data file in the Solution Explorer, then selecting the data file in the Server Explorer and copying the Connection String property value from the Properties window, then replacing the folder path with "|DataDirectory|". That place-holder gets resolved at run time and, for a non-ClickOnce Windows app, it will produce the same path as Application.StartupPath. That means you'll connect to the working copy in the output folder rather than the source file in the project folder, which is what the Server Explorer is doing.

  18. #18

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    My connection string doesn't look like that. When I double-clicked the .mdf file in Solution Explorer, it literally opened the file in binary mode. Here is my connection string:

    static string connectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=Friends;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailove r=False";

    Also, if I create a .mdf file in the SQL Server Object Explorer, it doesn't automatically add the .mdf file to the project does it? At least I didn't see it listed in the Solution Explorer. I had to right click on the project and add an existing item, which was the .mdf file.

    By the way...this is a web app and not a windows app. Probably doesn't make a difference though.
    Blake

  19. #19
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    OK, I suggest that you start again. As I said, if you want to use a local data file then do EVERTYTHING in VS. To create the database, right-click on your project in the Solution Explorer and select Add -> New Item. In the dialogue, select the Data category and then select Service-based Database. That will add a new MDF file to your project. Then you double-click on that file in the Solution Explorer to open it in the Server Explorer. You then do as I mentioned earlier to add tables, populate them and also to add stored procedures. At no point do you need to use SSMS.

  20. #20

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    That might be my whole problem. I never did that. Let me give a shot!

    Thanks!
    Blake

  21. #21
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    I've never actually used a local data file with a web app but it shouldn't matter much. That said, the "|DataDirectory|" place-holder resolves to the App_Data folder for a web app. So that will make some difference. You might have to put the data file in that folder to begin with, rather than in the root folder of the project. I might test it out myself now anyway, just in case.

  22. #22
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    OK, I just did some testing and here's what I found. I created a web application and then I right-clicked the App_Data folder to create the database there instead of in the root. It was set to not copy to the output folder by default and I left it that way initially. I used basically the same connection string as before (including "|DataDirectory|") and I successfully connected to, queried and updated the database in the App_Data folder. This means that, by default, there is just one data file rather than a source and a working copy as there is for Windows apps.

    I then changed the Copy To Output Directory property to Copy If Newer and ran the app again. This time, an App_Data folder containing a copy of the data file was created in the output folder but the app still connected to the original source file in the App_Data folder of the project. This might seem preferable but having a clean source file and a working copy has its advantages.

    It means that you can rest the database for testing simply by deleting the copy. It also means that you will automatically get a clean database when you compile a Release build. The way the web app works, you'd have to explicitly clean out the database of all test data to test afresh or release.

    If using a local data file with a web app, I'd actually recommend using a working copy so that you do keep your source file clean. You can do that as I did above and then using an explicit path in your connection string. You'd just have to make sure not to include that extra App-Data folder when you publish.

  23. #23

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    For some reason I can't connect to the (localdb)MSSQLLocalDB. I've attached images below. On another note, I had already tried what you did in paragraph 1 but because of not being able to add the (localdb)MSSQLLocalDB, it didn't create it.
    Attached Images Attached Images   
    Blake

  24. #24
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    You're not doing what I told you to do. To get to that dialogue, I had to click the SQL Server Object Explorer button on the toolbar of the Server Explorer window, then click the Add SQL Server button on the toolbar of the SQL Server Object Explorer window. That is nothing like what I said to do above.

  25. #25

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    5,034

    Re: Could not find Stored Procedure?

    My bad, I did what you said in last post. The images are in the sequence they happened.
    Attached Images Attached Images    
    Blake

  26. #26
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Could not find Stored Procedure?

    Yes, you did do what I said in post #24. Don't. That's not what I told you do. Do what I told you to do, i.e. in post #19 with the modification from post #22. That's why I told you to do it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width