Search sql server database server using Textbox and button in C#

Question

So I am using MS visual studio to create an application in c# that will pull information from a sql server database. I have created a textbox and a button to search my gridview. I am using a stored procedure that searched multiple rows to pull information from my Sql Database. I am having trouble with my aspx.cs code. I have tried so many different ways to create a searchbox but haven't had any luck yet

Here is my code for my search button.

I am getting the error-

"Input string was not in a correct format."

this error is on the line cmd.ExecuteNonQuery();

Help is much appreciated, thank you.

protected void Button_srch_invest1_Click(object sender, EventArgs e)
{
    string connectionStr = ConfigurationManager.ConnectionStrings["ORAProjectConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(connectionStr))
    {
        string find = "sp_SrcProtocols";
        SqlCommand cmd = new SqlCommand(find, con);

        cmd.Parameters.Add("@ORAID", SqlDbType.Int).Value = TextBox_Srch.Text;
        cmd.Parameters.Add("@InvestLastName", SqlDbType.NVarChar).Value = TextBox_Srch.Text;
        cmd.Parameters.Add("@ManagerLastName", SqlDbType.NVarChar).Value = TextBox_Srch.Text;

        con.Open();
        cmd.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        DataSet ds = new DataSet();
        da.Fill(ds, "ORAID");
        da.Fill(ds, "InvestLastName");
        da.Fill(ds, "ManagerLastName");
        GridView1.DataSource = ds;
        GridView1.DataBind();

        con.Close();
    }   
}

Gabriel Negut · Accepted Answer · 2018-07-09 16:36:09Z

3

By default, a SqlCommand expects a query, not a stored procedure's name. You have to set the command type before executing it.

cmd.CommandType = CommandType.StoredProcedure;

answered Jul 9, 2018 at 16:36

Gabriel Negut

14k4 gold badges40 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Browne - Microsoft Over a year ago

And you shouldn't be calling ExecuteNonQuery. SqlDataAdapter.Fill will run the SelectCommand for you. And you shouldn't have multiple calls to da.Fill().

Vinit · Accepted Answer · 2018-07-09 18:29:20Z

1

It seems, you are passing same text box value (TextBox_Srch.Text) to all 3 parameters. And first parameter @ORAID is expecting integer value and you might be passing text. So it's causing SQL server to raise below error.

Input string was not in a correct format.

answered Jul 9, 2018 at 18:29

Vinit

2,6271 gold badge17 silver badges22 bronze badges

Comments

Anderson · Accepted Answer · 2018-07-11 19:11:17Z

This is what worked (and i changed my sql to just accept one parameter @search)

  protected void Button_srch_invest1_Click(object sender, EventArgs e)
{

    string connectionStr = ConfigurationManager.ConnectionStrings["ORAProjectConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(connectionStr))
    {


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "sp_SrcProtocols";
        cmd.CommandType = CommandType.StoredProcedure;



        cmd.Parameters.Add("@search", SqlDbType.NVarChar).Value = TextBox_Srch.Text;


        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();


        con.Close();




    }



}

Collectives™ on Stack Overflow

Search sql server database server using Textbox and button in C#

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related