I have a solutin with forms that use table adapters to input data into sql tables and that all works fine. My trouble comes when I have another form that uses a sql stored procedure. When I try to connect to the server I get an error that the login failed. Im using the same type of connection as I did for my dataset to login and that was integrated security. Is this problem because I'm already in the data base with the solution and then I try to open another connection with the following code for the execution of a stored procedure? Any help would be greatly apprecieated. I have also used the line Integrated Security = True but this did not work either.

private void approveestBN_Click(object sender, EventArgs e)
    {
        //string ConnectionString = "Data Source={0};Initial Catalog={1};Integrated Security=True";
        string ConnectionString = "Data Source={0};Initial Catalog={1};Trusted_Connection=True";

        int estno;
        if (int.TryParse(startestnoCB.Text, out estno))
        {
            SqlConnection conn = new SqlConnection(string.Format(ConnectionString, "server1", "Estimate"));
            SqlCommand cmd = new SqlCommand("EstimateApproval", conn);                
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Estno", estno);
            try
            {
                conn.Open();
#if WantSomethingBack
                DataTable result = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(result);
                foreach(DataRow row in result.Rows)
                {

                }
                result.Dispose()
#else
                cmd.ExecuteNonQuery();  // If you do not need a return;
#endif
            }
            catch (SqlException err)
            {
                MessageBox.Show(err.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                    conn.Dispose();
            }
        }

    }

Edited 3 Years Ago by Reverend Jim: Fixed formatting

Well if you are using multiple connections, then you can try enabling pooling. MARS is also an option that uses a single connection to execute multiple queries, however is restricted to only DataReaders.

Not a well seasoned programmer. Are you saying I have to enable pooling or MARS to make this work . If so can you explain.
Thanks

To enable pooling:

String connectionString = "Data Source=./SQLExpress; Initial Catalog=StudentDetails; Integrated Scurity=SSPI; Connection Timeout=20; Pooling = true";

You can also specfy the Min pool size and Max pool size To enable MARS:

String connectionString = "Data Source=./SQLExpress; Initial Catalog=StudentDetails; Integrated Scurity=SSPI; MultipleActiveResultSets = True";

I don't know why you are getting an error when your accessing the database using two connections. It shouldn't create a problem for the server if the same user connects to it twice using different connections.

MJV,
Run it in debug mode, and see what the InnerException.Messge is telling you. (post it here if you do not understand it)

The syntax of the code looks right. You can have many connections to SqlExpress if that is what you are using. I have applications with many threads hitting an SqlExpress with no issues. MS recommends limiting to 5, but that is only a recommendation. I have never seen it reject a login due to the number of connections( at least on SqlExpress).

Viewing the InnerException should tell you exactly what is going on.

This article has been dead for over six months. Start a new discussion instead.