Retrieve a record on itemid

[cathy25]

Hi all,

My requirement is to allow users to enter partno. in a text box.
There is a button called 'populate'. whenever the user enters a partno. and hits on populate button, the matching record from the database should show up on the screen. Each field in the databse got corresponding textboxes on the screen.
I have used the following code:
under Button_Click event,

SqlConnection MyConnection = new SqlConnection(Mysqlconnectionstring);
            MyConnection.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = MyConnection;
            Cmd.CommandType = CommandType.Text ;
            Cmd.CommandText = "select * from database where Number = '+Partno.ToString ()'";
            SqlDataAdapter DA = new SqlDataAdapter(Cmd);
            DataSet DS = new DataSet();
            DA.Fill(DS);
          Partname.ToString() = DS.Tables[0].Rows[1][1].ToString();
         //all other fileds.
 

when I am executing this script it is giving the following error.

System.IndexOutOfRangeException
There is no row at position 1.

But, a record with that partno does exists in the database.
Am I using any thing wrong here? Please advise.

[Plater]

You get index out of range because your datatable is empty.
You are not builing your commandtext correctly:

Cmd.CommandText = "select * from database where Number = '+Partno.ToStri ng ()'";
Will produce the literal string:

select * from database where Number = '+Partno.ToStri ng ()'

Assuming Partno is a number, try doing this:
Cmd.CommandText = "select * from database where Number = "+Partno.ToStri ng()+"";
Which should produce something like:

select * from database where Number = 1234

[cathy25]

Thanks! It worked.
A small + can make a world of difference.

[Curtis Rutland]

To avoid these kinds of mistakes and since string concatenation is always messy, I like to use String.Format.

string select = String.Format("select * from database where number = {0}",Partno.ToString());
 

String.Format will replace {#} with the parameters that follow it. Much cleaner than lots of concatenation.

Of course, when using SQL, you can just use Parameterized Queries, which are easier, cleaner, and more secure:

Cmd.CommandText = "select * from database where Number = @Number";
Cmd.Parameters.AddWithValue("@Number", Partno);

Plus you don't have to cast the parameter values to a string.