Topic: MSSQL, C#, ASP.NET and Stored Procedures... Help?

[Tuoni]

So I've got right royally stuck on my current project.

I'm using MS SQL Server 2008, C# & ASP.NET 3.5 to write a quote engine for a large insurance broker... only problem is, I'm not an MS SQL guy.

I've taken a local copy of their live database to test on - all the tables, as well as the stored procedures.  I have set up the same username/password to connect to my localhost and... well, that's where I'm stuck.  The C# connects perfectly happily to my SQL server, and if I log into the server using the Managment Studio using the same credentials I can see all of the tables, as well as the stored procedures and (I guess, more importantly...) I can execute them all.

If I try and do the same thing programmatically, I get the error "Stored procedure could not be found".

I can't work it out.

My connection string is:

Code: [Select]

"Data Source=localhost;Initial Catalog=<database>;User ID=<username>;password=<password>"
and the code I'm using to try and execute this stored proc. is:

Code: [Select]

   SqlConnection conn = new SqlConnection(connectionString);
           
            SqlCommand insert = new SqlCommand("page1Details", conn);

            insert.CommandType = CommandType.StoredProcedure;

            insert.Parameters.Add("@Title", SqlDbType.Char, 10).Value = title.SelectedItem.Text.Trim();
            insert.Parameters.Add("@FirstName", SqlDbType.Char, 15).Value = firstName.Text.Trim();
            insert.Parameters.Add("@Surname", SqlDbType.Char, 15).Value = surname.Text.Trim();
            insert.Parameters.Add("@Address1", SqlDbType.Char, 40).Value = address1.Text.Trim();

            <snip>

            try {
                conn.Open();
                insert.ExecuteNonQuery(); <- The line which craps out
                userSettings.id = insert.Parameters["@ID"].Value.ToString();
            }
            catch (Exception ex) {
                throw new Exception("ERROR INSERTING DATA " + ex.Message);
            }
            finally {
                if (conn != null)
                    conn.Close();
            }
Is it a permissions thing?  Anyone any ideas?  I'm kinda making wild stabs in the dark here, the user permissions system is very different to that of MySQL.

I just can't work out why it says it can't find it if I am able to log in through the Managment program and execute it using the same credentials.

[Tuoni]

Curiouser and curiouser...

I hacked together a quick method which dumps the name of all of the stored proc's it can find into an arraylist using the same connection string, and lo and behold, there is the stored procedure.  Now I'm *REALLY* flummoxed

[Tuoni]

Right, well somehow I managed to get c# talking to the stored procedure (don't ask me how, I'm blissfully unaware myself!) and now I've hit another problem.

The stored procedure it's calling isn't working:

Code: [Select]

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [database].[storedProcedure]

(
   @Title char(10),
   @FirstName char(15),
   @Surname char (15),
   @Address1 char (40),

   <snip>
   
   @ID Int output
   
)
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    Insert into [database].[user].table1(columns)

    Values (@Address1, @Address2, [etc])

Set @ID = @@IDENTITY

    Insert into [database].[table2] (column1, column2)
    Values (@ID, '1') <- craps out saying @ID is null

   <snip>

END

(Note: I've taken out table names, etc - you'll just have to trust me that they're correct)
It's populating the first table correctly, and the primary key (ID) is incrementing properly...  it just seems the variable @ID in the s.p. isn't being given a value.  Any ideas?

[n.yuan]

I am not very sure what is wrong. Since you said after the first inserting, the ID column of table1 was populated correctly, I assume it is "Identity".

However, you should use SCOPE_IDENTITY() to set @ID instead of @@IDENTITY. That is, after first inserting,

SET @ID=SCOPE_IDENTITY()

There are a lot of links on the net to the topic of using SCOPE_IDENTITY() versus @@IDENTITY, and in most cases, you should use SCOPE_IDENTITY().

Right, well somehow I managed to get c# talking to the stored procedure (don't ask me how, I'm blissfully unaware myself!) and now I've hit another problem.

The stored procedure it's calling isn't working:

Code: [Select]

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [database].[storedProcedure]

(
   @Title char(10),
   @FirstName char(15),
   @Surname char (15),
   @Address1 char (40),

   <snip>
   
   @ID Int output
   
)
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    Insert into [database].[user].table1(columns)

    Values (@Address1, @Address2, [etc])

Set @ID = @@IDENTITY

    Insert into [database].[table2] (column1, column2)
    Values (@ID, '1') <- craps out saying @ID is null

   <snip>

END

(Note: I've taken out table names, etc - you'll just have to trust me that they're correct)
It's populating the first table correctly, and the primary key (ID) is incrementing properly...  it just seems the variable @ID in the s.p. isn't being given a value.  Any ideas?