Trying to Query a DB in C# and then store it to a variable

Question

Here is my main goal: I want a user to be able to enter a computername in a text box and I have a table in my db that has computernames and ipaddresses in it. So i want to be able to do something like this when I query the db... "select ipaddress from computername where computername = 'textbox1.text'". This way when a user types in a computer name it will then look in the db and use the ipaddress to map to the pc.

so far I am just trying to get results back from the db into the textbox1. Any help is appreciated.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace testwf
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            InitializeComponent();

                        SqlConnection cs = new SqlConnection(@"Server=10-nuerp-006acdst;Database=Rert;User Id=reports;Password=Password");
            SqlDataAdapter da = new SqlDataAdapter();
            cs.Open();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cs = new SqlConnection(@"Server=10-nuerp-006acdst;Database=Rert;User Id=reports;Password=Password
            SqlDataAdapter da = new SqlDataAdapter();
            cs.Open();
            da.SelectCommand = new SqlCommand();
            SqlCommand command = new SqlCommand("select top 1 * from station", cs);
            SqlDataReader dr = command.ExecuteReader();
            //cs.Open();
            dr.Read();

            while (dr.Read())
            {
                cs.Open();
                command.ExecuteReader();
                textbox1.Text = dr.GetSqlValue(1).ToString();
                MessageBox.Show(dr.GetSqlValue(0).ToString());
                            }

            MessageBox.Show(dr.GetSqlValue(0).ToString());
            cs.Close();

        }
    }
}

Answer 1

This code replaces what's in your button1_click event:

using (SqlConnection cs = new SqlConnection(@"Server=10-nuerp-006acdst;Database=Rert;User Id=reports;Password=Password"))
{
    cs.Open();

    using (SqlCommand cmd = new SqlCommand("select top 1 * from station", cs))
    using (SqlDataReader dr = command.ExecuteReader())
    {
        while (dr.Read())
        {
            textbox1.Text = dr.GetSqlValue(1).ToString();
            MessageBox.Show(dr.GetSqlValue(0).ToString());
        }
    }
}

As for "uelreader"s answer: It's much easier to add SQL parameters like that:

cmd.Parameters.AddWithValue("@parameterName", someVariableOrConstant);

The type is determined automatically and conversion stuff is done by the framework. Use urlreader's version only if you need to set the type and length explicitly.

Answer 2

here is an example, refer to http://www.akadia.com/services/dotnet_data_reader.html

 private void btnFind_Click(object sender, System.EventArgs e)
    {
        SqlDataReader rdr = null;
        SqlConnection con = null;
        SqlCommand cmd = null;

        try
        {
            // Open connection to the database
            string ConnectionString = "server=xeon;uid=sa;"+
                "pwd=manager; database=northwind";
            con = new SqlConnection(ConnectionString);
            con.Open();

            // Set up a command with the given query and associate
            // this with the current connection.
            string CommandText = "SELECT FirstName, LastName" +
                                 "  FROM Employees" +
                                 " WHERE (LastName LIKE @Find)";
            cmd = new SqlCommand(CommandText);
            cmd.Connection = con;

            // Add LastName to the above defined paramter @Find
            cmd.Parameters.Add(
                new SqlParameter(
                "@Find", // The name of the parameter to map
                System.Data.SqlDbType.NVarChar, // SqlDbType values
                20, // The width of the parameter
                "LastName"));  // The name of the source column

            // Fill the parameter with the value retrieved
            // from the text field
            cmd.Parameters["@Find"].Value = txtFind.Text;

            // Execute the query
            rdr = cmd.ExecuteReader();

            // Fill the list box with the values retrieved
            lbFound.Items.Clear();
            while(rdr.Read())
            {
                lbFound.Items.Add(rdr["FirstName"].ToString() +
                " " + rdr["LastName"].ToString());
            }
        }
        catch(Exception ex)
        {
            // Print error message
            MessageBox.Show(ex.Message);
        }
        finally
        {
            // Close data reader object and database connection
            if (rdr != null)
                rdr.Close();

            if (con.State == ConnectionState.Open)
                con.Close();
        }
    }
}