private void button1_Click(object sender, EventArgs e)
{
try
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=*******;Initial Catalog=ChatApp;User ID=Chatapplication;Password=****";
conn.Open();
SqlCommand cmd = new SqlCommand();
string chatroomidno = textBox1.Text;
string chatroomname = textBox2.Text;
//cmd.CommandText = "Select ChatRoomID=@ChatRoomID,ChatRoomName=@ChatRoomName from tblChatRoom";
//cmd.Connection = conn;
SqlDataAdapter adapt = new SqlDataAdapter("Chatroomapp",conn);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds=new DataSet();
DataTable dt = new DataTable();
adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomID", SqlDbType.VarChar, 100));
adapt.SelectCommand.Parameters["@ChatRoomID"].Value = chatroomidno;
adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomName", SqlDbType.VarChar, 50));
adapt.SelectCommand.Parameters["@ChatRoomName"].Value = chatroomname;
adapt.Fill(ds, "tblChatRoom");
if (dt.Rows.Count > 0)
{
MessageBox.Show("Connection Succedded");
}
else
{
MessageBox.Show("Connection Fails");
}
}
catch (Exception ex)
{
MessageBox.Show("Error", ex.Message);
}
}
While compiling the program I got only connection fails message box, in the database. I found correct, how to overcome the program to get the connection succeeded message box.
In addition to commands built with strings, the SqlCommand type can be used to execute stored procedures. There are two tasks required to make this happen: let the SqlCommand object know which stored procedure to execute and tell the SqlCommand object that it is executing a stored procedure.
You can execute a stored procedure programmatically using the command object. Instead of passing a SQL statement, you pass the stored procedure name as the SQL statement to execute a stored procedure. Each data provider provides a command object to execute SQL statements.
Well, you're filling the ds
data set - but then you're checking the dt
data table for presence of rows... that's never going to work, of course!
If you only need a single DataTable
- just use and fill that data table alone - no need for the overhead of a DataSet
. Also, put your SqlConnection
and SqlCommand
into using blocks like this:
using (SqlConnection conn = new SqlConnection("Data Source=*******;Initial Catalog=ChatApp;User ID=Chatapplication;Password=****"))
using (SqlCommand cmd = new SqlCommand("Chatroomapp", conn))
{
string chatroomidno = textBox1.Text;
string chatroomname = textBox2.Text;
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomID", SqlDbType.VarChar, 100));
adapt.SelectCommand.Parameters["@ChatRoomID"].Value = chatroomidno;
adapt.SelectCommand.Parameters.Add(new SqlParameter("@ChatRoomName", SqlDbType.VarChar, 50));
adapt.SelectCommand.Parameters["@ChatRoomName"].Value = chatroomname;
// fill the data table - no need to explicitly call `conn.Open()` -
// the SqlDataAdapter automatically does this (and closes the connection, too)
DataTable dt = new DataTable();
adapt.Fill(dt);
if (dt.Rows.Count > 0)
{
MessageBox.Show("Connection Succedded");
}
else
{
MessageBox.Show("Connection Fails");
}
}
And just because you get back no rows in dt.Rows
doesn't necessarily mean that your connection failed..... it could just be that there are no rows that match your search critieria! The connection worked just fine - but the SQL command just didn't return any rows.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With