Thread: Warning message adding database

Warning message when adding data to database

This is my code of connection to database:

Public void connection()
{
connection.ConnectionString = "Data Source=localhost;Initial Catalog=base; Integrated Security=true";
connection.Open();
if (connection.State == ConnectionState.Open)
{
Interaction.MsgBox("Connection was successful", MsgBoxStyle.Exclamation, "Status");
}
else
{
Interaction.MsgBox("Connection failed", MsgBoxStyle.Critical, "Status");
}
connection.Close();
}

Whenever I try to save the data in my database but it will give the error message:

Here is my code:

{
try {
connection();
string strRequest = "SELECT * FROM Coaches";
DataTable dtt = default(DataTable);
SqlDataAdapter oSqlDataAdapter = new SqlDataAdapter(strRequest, connection);
DataSet oDataSet = new DataSet("Coaches");
oSqlDataAdapter.Fill(oDataSet, "Coaches");
dtt = oDataSet.Tables("Coaches");
oSqlDataAdapter.InsertCommand = new SqlCommand("INSERT INTO Coaches(Name,Surname,Address,Telephone) Values(@TextBoxName_E,@TextBoxSurname_E,@TextBoxAd _E,@TextBoxTel_E)", connection);
oSqlDataAdapter.InsertCommand.Parameters.Add("@Tex tBoxSurname_E", SqlDbType.NChar, 15, "TextBoxSurname_E");
oSqlDataAdapter.InsertCommand.Parameters.Add("@Tex tBoxTel_E", SqlDbType.Int, 100, "TextBoxTel_E");
DataRow oDataRow = default(DataRow);
byte[] byteArray = { };
oDataRow = oDataSet.Tables("Coaches").NewRow;
oDataRow("TextBoxName_E") = TextBoxName_E.Text;
oDataRow("TextBoxAd_E") = TextBoxAd_E.Text;
oDataRow("TextBoxTel_E") = TextBoxTel_E.Text;
oDataSet.Tables("Coaches").Rows.Add(oDataRow);
oSqlDataAdapter.Update(oDataSet, "Coaches");
oDataSet.Clear();
dtt = oDataSet.Tables("Coaches");
Interaction.MsgBox("Article successfully registered with", MsgBoxStyle.Information, "Status");
connection.Close();
}
catch {
Interaction.MsgBox("Failed registration");
}

Your Sub Connection closes the connection right away once opening, therefore it will not work.

On the other hand, if the Open method is successful, the connection status will without doubt open. And so the Open method fails, you have an exception, so in any case you will never pass in the else.

You should not use the same connection object every time it does that get you problems. In its place use a local variable that you initialized with the result of a function GetConnection:

public SqlConnection GetConnection()
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source=localhost;Initial Catalog=base;Integrated Security=true";
connection.Open();
return connection;
}
{
try {
Using (connection = new GetConnection())
string strRequest = "SELECT * FROM Coaches";
DataTable dtt = default(DataTable);
SqlDataAdapter oSqlDataAdapter = new SqlDataAdapter(strRequest, connection);
DataSet oDataSet = new DataSet("Coaches");
oSqlDataAdapter.Fill(oDataSet, "Coaches");
dtt = oDataSet.Tables("Coaches");

oSqlDataAdapter.InsertCommand = new SqlCommand("INSERT INTO Coaches(Name,Surname,Address,Telephone) Values(@TextBoxName_E,@TextBoxSurname_E,@TextBoxAd _E,@TextBoxTel_E)", connection);
oSqlDataAdapter.InsertCommand.Parameters.Add("@Tex tBoxSurname_E", SqlDbType.NChar, 15, "TextBoxSurname_E");
oSqlDataAdapter.InsertCommand.Parameters.Add("@Tex tBoxTel_E", SqlDbType.Int, 100, "TextBoxTel_E");
DataRow oDataRow = default(DataRow);
byte[] byteArray = { };

oDataRow = oDataSet.Tables("Coaches").NewRow;
oDataRow("TextBoxName_E") = TextBoxName_E.Text;
oDataRow("TextBoxAd_E") = TextBoxAd_E.Text;
oDataRow("TextBoxTel_E") = TextBoxTel_E.Text;
oDataSet.Tables("Coaches").Rows.Add(oDataRow);
oSqlDataAdapter.Update(oDataSet, "Coaches");
oDataSet.Clear();
dtt = oDataSet.Tables("Coaches");
Interaction.MsgBox("Article successfully registered with", MsgBoxStyle.Information, "Status");
connection.Close();
}
catch {
Interaction.MsgBox("Failed registration");
}