question

JumboDumbo-8428 avatar image
0 Votes"
JumboDumbo-8428 asked karenpayneoregon commented

ADO NET Connection Fails

ADO NET connection fails everytime i try to select Data with a Button.
error:
ADO NET : Error occurred while establishing a connection to SQL Server
I have tried changing Settings in the SQL Server but it doesnt work, can someone help?

My Code:

public partial class NewCustomer : Form
{
// Storage for IDENTITY values returned from database.
private int parsedCustomerID;
private int orderID;

public NewCustomer()
{
InitializeComponent();
}

     private bool IsCustomerNameValid()
     {
         if (txtCustomerName.Text == "")
         {
             MessageBox.Show("Please enter a name.");
             return false;
         }
         else
         {
             return true;
         }
     }

     private bool IsOrderDataValid()
     {
         // Verify that CustomerID is present.
         if (txtCustomerID.Text == "")
         {
             MessageBox.Show("Please create customer account before placing order.");
             return false;
         }
         // Verify that Amount isn't 0.
         else if ((numOrderAmount.Value < 1))
         {
             MessageBox.Show("Please specify an order amount.");
             return false;
         }
         else
         {
             // Order can be submitted.
             return true;
         }
     }

     private void ClearForm()
     {
         txtCustomerName.Clear();
         txtCustomerID.Clear();
         dtpOrderDate.Value = DateTime.Now;
         numOrderAmount.Value = 0;
         this.parsedCustomerID = 0;
     }

     private void btnCreateAccount_Click(object sender, EventArgs e)
     {
         if (IsCustomerNameValid())
         {
             // Create the connection.
             using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
             {
                 // Create a SqlCommand, and identify it as a stored procedure.
                 using (SqlCommand sqlCommand = new SqlCommand("Sales.uspNewCustomer", connection))
                 {
                     sqlCommand.CommandType = CommandType.StoredProcedure;

                     // Add input parameter for the stored procedure and specify what to use as its value.
                     sqlCommand.Parameters.Add(new SqlParameter("@CustomerName", SqlDbType.NVarChar, 40));
                     sqlCommand.Parameters["@CustomerName"].Value = txtCustomerName.Text;

                     // Add the output parameter.
                     sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
                     sqlCommand.Parameters["@CustomerID"].Direction = ParameterDirection.Output;

                     try
                     {
                         connection.Open();

                         // Run the stored procedure.
                         sqlCommand.ExecuteNonQuery();

                         // Customer ID is an IDENTITY value from the database.
                         this.parsedCustomerID = (int)sqlCommand.Parameters["@CustomerID"].Value;

                         // Put the Customer ID value into the read-only text box.
                         this.txtCustomerID.Text = Convert.ToString(parsedCustomerID);
                     }
                     catch
                     {
                         MessageBox.Show("Customer ID was not returned. Account could not be created.");
                     }
                     finally
                     {
                         connection.Close();
                     }
                 }
             }
         }
     }

     private void btnPlaceOrder_Click(object sender, EventArgs e)
     {
         // Ensure the required input is present.
         if (IsOrderDataValid())
         {
             // Create the connection.
             using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
             {
                 // Create SqlCommand and identify it as a stored procedure.
                 using (SqlCommand sqlCommand = new SqlCommand("Sales.uspPlaceNewOrder", connection))
                 {
                     sqlCommand.CommandType = CommandType.StoredProcedure;

                     // Add the @CustomerID input parameter, which was obtained from uspNewCustomer.
                     sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int));
                     sqlCommand.Parameters["@CustomerID"].Value = this.parsedCustomerID;

                     // Add the @OrderDate input parameter.
                     sqlCommand.Parameters.Add(new SqlParameter("@OrderDate", SqlDbType.DateTime, 8));
                     sqlCommand.Parameters["@OrderDate"].Value = dtpOrderDate.Value;

                     // Add the @Amount order amount input parameter.
                     sqlCommand.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Int));
                     sqlCommand.Parameters["@Amount"].Value = numOrderAmount.Value;

                     // Add the @Status order status input parameter.
                     // For a new order, the status is always O (open).
                     sqlCommand.Parameters.Add(new SqlParameter("@Status", SqlDbType.Char, 1));
                     sqlCommand.Parameters["@Status"].Value = "O";

                     // Add the return value for the stored procedure, which is  the order ID.
                     sqlCommand.Parameters.Add(new SqlParameter("@RC", SqlDbType.Int));
                     sqlCommand.Parameters["@RC"].Direction = ParameterDirection.ReturnValue;

                     try
                     {
                         //Open connection.
                         connection.Open();

                         // Run the stored procedure.
                         sqlCommand.ExecuteNonQuery();

                         // Display the order number.
                         this.orderID = (int)sqlCommand.Parameters["@RC"].Value;
                         MessageBox.Show("Order number " + this.orderID + " has been submitted.");
                     }
                     catch
                     {
                         MessageBox.Show("Order could not be placed.");
                     }
                     finally
                     {
                         connection.Close();
                     }
                 }
             }
         }
     }

     private void btnAddAnotherAccount_Click(object sender, EventArgs e)
     {
         this.ClearForm();
     }

     private void btnAddFinish_Click(object sender, EventArgs e)
     {
         this.Close();
     }
 }

public partial class FillOrCancel : Form
{
// Storage for the order ID value.
private int parsedOrderID;

public FillOrCancel()
{
InitializeComponent();
}

private bool IsOrderIDValid()
{
if (txtOrderID.Text == "")
{
MessageBox.Show("Please specify the Order ID.");
return false;
}
else if (Regex.IsMatch(txtOrderID.Text, @"^\D*$"))
{
MessageBox.Show("Customer ID must contain only numbers.");
txtOrderID.Clear();
return false;
}
else
{
parsedOrderID = Int32.Parse(txtOrderID.Text);
return true;
}
}

     private void btnFindByOrderID_Click(object sender, EventArgs e)
     {
         if (IsOrderIDValid())
         {
             using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
             {
                 const string sql = "SELECT * FROM Sales.Orders WHERE orderID = @orderID";
                 using (SqlCommand sqlCommand = new SqlCommand(sql, connection))
                 {
                     sqlCommand.Parameters.Add(new SqlParameter("@orderID", SqlDbType.Int));
                     sqlCommand.Parameters["@orderID"].Value = parsedOrderID;

                     try
                     {
                         connection.Open();
                         using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
                         {                                DataTable dataTable = new DataTable();
                             dataTable.Load(dataReader);
                             this.dgvCustomerOrders.DataSource = dataTable;
                             dataReader.Close();
                         }
                     }
                     catch
                     {
                         MessageBox.Show("The requested order could not be loaded into the form.");
                     }
                     finally
                     {
                         connection.Close();
                     }
                 }
             }
         }
     }

     private void btnCancelOrder_Click(object sender, EventArgs e)
     {
         if (IsOrderIDValid())
         {
             using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
             {
                 using (SqlCommand sqlCommand = new SqlCommand("Sales.uspCancelOrder", connection))
                 {
                     sqlCommand.CommandType = CommandType.StoredProcedure;
                     sqlCommand.Parameters.Add(new SqlParameter("@orderID", SqlDbType.Int));
                     sqlCommand.Parameters["@orderID"].Value = parsedOrderID;

                     try
                     {
                         connection.Open();
                         sqlCommand.ExecuteNonQuery();
                     }
                     catch
                     {
                         MessageBox.Show("The cancel operation was not completed.");
                     }
                     finally
                     {
                         connection.Close();
                     }
                 }
             }
         }
     }

     private void btnFillOrder_Click(object sender, EventArgs e)
     {
         if (IsOrderIDValid())
         {
             using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))
             {
                 using (SqlCommand sqlCommand = new SqlCommand("Sales.uspFillOrder", connection))
                 {
                     sqlCommand.CommandType = CommandType.StoredProcedure;
                     sqlCommand.Parameters.Add(new SqlParameter("@orderID", SqlDbType.Int));
                     sqlCommand.Parameters["@orderID"].Value = parsedOrderID;
                     sqlCommand.Parameters.Add(new SqlParameter("@FilledDate", SqlDbType.DateTime, 8));
                     sqlCommand.Parameters["@FilledDate"].Value = dtpFillDate.Value;

                     try
                     {
                         connection.Open();
                         sqlCommand.ExecuteNonQuery();
                     }
                     catch
                     {
                         MessageBox.Show("The fill operation was not completed.");
                     }
                     finally
                     {
                         connection.Close();
                     }
                 }
             }
         }
     }

     private void btnFinishUpdates_Click(object sender, EventArgs e)
     {
         this.Close();
     }
 }

public Navigation()
{
InitializeComponent();
}

private void btnGoToAdd_Click(object sender, EventArgs e)
{
Form frm = new NewCustomer();
frm.Show();
}

private void btnGoToFillOrCancel_Click(object sender, EventArgs e)
{
Form frm = new FillOrCancel();
frm.Show();
}

private void btnExit_Click(object sender, EventArgs e)
{
this.Close();
}

Maybe there is an Error in my Code or any of you have experienced the same Error.

sql-server-generaldotnet-csharpdotnet-adonet
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So you dumped a whole of code that is of little relevance, but you did not include the important part.

This is the critical line:

           using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.connString))

And more precisely, the key is what inside of this string. Not that we may be able to say why it fails, because what is right or not depends on your environment.

Also, the error message should be longer. There is usually a longer story which gives more details on why connection fails.

0 Votes 0 ·

Check your connection string against the following.


0 Votes 0 ·

1 Answer

Paul-5034 avatar image
0 Votes"
Paul-5034 answered

Could this be an issue with your connection string / are you able to connect to your SQL Server instance through VS if you add the connection through 'View > SQL Server Object Explorer' and then click the 'Add SQL Server' button in the explorer panel?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.