Not retriving data into textboxes from database.

Question

1.00/5 (1 vote)

See more:

Hello sir,
I created in windows application,one form in that i had created three labels (first,middle,last) & infront of that i created textboxes with Search button in bottom .And now i created in database three fields called first,middle,last also i gave values for those fields . what i need is wen i enter in "first" textbox and hit the button search,for the remaining textboxs like middle & last the data has to be retrived into it.Now its giving error saying cannot convert varchar to int.I had given datatype as for all the fields in database as "varchar(50)". This is the error Conversion failed when converting the varchar value 'f' to data type int..

And this the code:

C#

namespace Config_admin
{
    public partial class Form2 : Form
    {
        SqlCommand cmd = new SqlCommand();
        SqlDataReader rdr;
        DataSet ds;
        SqlDataAdapter da;
        public Form2()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=CBP\\SQLEXPRESS;Initial Catalog=ECG;Integrated Security=True");
            con.Open();
            cmd.CommandText = "select * from  Table2 where first=" + textBox1.Text.Trim();
            cmd.Connection = con;
            rdr = cmd.ExecuteReader();
            bool temp = false;
            while (rdr.Read())
            {
                //textBox1.Text = rdr.GetString(0);
                textBox2.Text = rdr.GetString(1);
                textBox3.Text = rdr.GetString(2);
                //textBox4.Text = rdr.GetString(3);
                //textBox5.Text = rdr.GetString(4);
                //textBox6.Text = rdr.GetString(5);
                //textBox7.Text = rdr.GetString(6);
                //textBox8.Text = rdr.GetString(7);
                //textBox9.Text = rdr.GetString(8);
                //textBox10.Text = rdr.GetString(9);
                temp = true;
            }
            if (temp == false)
                MessageBox.Show("not found");
            con.Close();

            con.Open();
            ds = new DataSet();
            da = new SqlDataAdapter("select * from Table2", con);
            da.Fill(ds, "Table2");
            con.Close();



        }
    }
}

Please any one help me.

Thanks & Regards
Pradeep CBZ

Posted 12-Mar-12 17:05pm

Pradeep CBZ

Updated 12-Mar-12 17:31pm

walterhevedeich

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2012-03-12T21:20:00

1) Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
2) There is no point in looping through entries if all you are going to do is overwrite the last one in each textbox.
3) Please, dispose of your objects!
4) Why are you doing the last bit? All it will do is waste time...
5) Never, ever use "SELECT * ..." and then reference the returned data by numeric indexes. You do not know what order Sql Server will return columns in - if the table changes, your program fails. and not for any obvious reason. Nasty to find, and fiddly to fix. It is bad practice to always return every field anyway - it wastes bandwidth which can cause significant slowdown with large amounts of data.
6) Stop using the Visual Studio default names for controls. You may remember that textBox7 is the postcode today, but in a weeks time, you will have to hunt about to find out which one it is like the rest of us. Use meaningful names!
7) It is also a good idea to use UPPER CASE for Sql Command element, and lower case for fields - it makes it easier to follow the SQL command when you are reading it.
8) Try to think about your users: "not found" is not a helpfull error message - it doesn't tell them what is wrong, or what they should do to avoid it in future.
Try:

C#

private void button1_Click(object sender, EventArgs e)
    {
    using (SqlConnection con = new SqlConnection("Data Source=CBP\\SQLEXPRESS;Initial Catalog=ECG;Integrated Security=True"))
        {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT middle, last FROM Table2 WHERE first=@FIRST", con))
            {
            cmd.Parameters.AddWithValue("@FIRST", tbFirstName.Text.Trim());
            using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                if (rdr.Read())
                    {
                    tbMiddleName.Text = rdr.GetString("middle");
                    tbLastName.Text = rdr.GetString("last");
                    }
                else
                    {
                    MessageBox.Show(string.Format("No data was found for anyone with the first name of \"{0}\"",tbFirstName.Text.Trim()) );
                    }
                }
            }
        }
    }

It may not cure your immediate problem, but it goes a fair way toward getting rid of it.

When you have done this, which line is it erroring on?