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:
"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:
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.