SqlDataReader parameter not working

Question

I have a windows form that I am asking a user to enter a pcname in textbox1 and then trying to use SqlDataReader to the read from the database to get the pc ipaddress and then map the pc drive to my local pc.

But for some reason when I use the textbox within the SQL parameter it's not working. But when I replace the textbox1.text with the actual pcname it works fine. Hopefully someone can help me find out why the parameter isn't working correctly.

Here is my code:

public void button1_Click(object sender, EventArgs e)
{
    string results = "";

    using (SqlConnection cs = new SqlConnection(@"***removed connection string***"))
    {
        cs.Open();

        string query = "select stationipaddress from station where stationname = @StationName";

        using (SqlCommand cmd = new SqlCommand(query, cs))
        {
            // Add the parameter and set its value -- 
            cmd.Parameters.AddWithValue("@StationName", textBox1.Text);

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    label3.Text = dr.GetSqlValue(0).ToString();
                    results = dr.GetValue(0).ToString();
                    MessageBox.Show(dr.GetValue(0).ToString());
                    MessageBox.Show(results);
                }

                string myvar = string.Format(@"use S: \\" + label3.Text + "\\c$\logs 0A36303 /user:admin", label3.Text);

                Process p = new Process();
                p.StartInfo.FileName = "net.exe";
                p.StartInfo.Arguments = (myvar);
                p.StartInfo.UseShellExecute = false;
                p.StartInfo.RedirectStandardOutput = true;
                p.StartInfo.RedirectStandardInput = true;
                p.StartInfo.CreateNoWindow = true;
                p.Start();

Please lern to format your questions properly. It shows some effort to take that few minutes. — juergen d, Nov 30 '12 at 15:17
Get rid of the Single quotes around '@StationName'. With them there it makes it a the string literal @StationName rather than a parameter. — Conrad Frix, Nov 30 '12 at 15:18
also besides the single quotes as Conrad Frix said you could replace the `using(SqlDataReader)` with a simple 'SqlDataReader reader = cmd.ExecuteReader()' to get rid of the inner using. — CuccoChaser, Nov 30 '12 at 15:21
I've updated the code but it still isn't working, does it look correct? — user1836162, Nov 30 '12 at 15:31
If you're only returning a single column from a single row, you can replace the `ExecuteReader` with `ExecuteScalar`: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx — Richard Deeming, Nov 30 '12 at 16:01
Also, are you getting an error, or is it just not returning any rows? — Richard Deeming, Nov 30 '12 at 16:01
try to use breakpoints, and see whats happening on the parameter row — serhat_pehlivanoglu, Nov 30 '12 at 16:02
shouldn't the messagbox just be blank>? If I take the @StationName out of the query and just insert the station name it populates the messageboxes fine — user1836162, Nov 30 '12 at 16:18
No, because if there are no rows returned, `dr.Read()` will never return `true`, and your code will never enter the `while` loop. — Richard Deeming, Nov 30 '12 at 16:21
@FlorisPrijt - Why get rid of the inner using? The SqlDataReader needs to be disposed too. — Chris Dunaway, Nov 30 '12 at 16:32

score 0 · Answer 1 · answered Nov 30 '12 at 16:22

0

Maybe if you separate you code in different lines, one to set up your parameter, and one to add it, you can better see where the problem is. Something like this:

SqlParameter param1 = new SqlParameter("@StationName", SqlDbType.NVarChar, textBox1.Text.length);
param1.Value = textBox1.Text;
cmd.Parameters.Add(param1);

at least is easier to see what is going on on the debugger.

answered Nov 30 '12 at 16:22

Julio Garcia

1,904
16
26

When I put the stationname in the query like this: 'stationname' it works but doesn't with a parameter, does this have something to do with it? – user1836162 Nov 30 '12 at 16:50
When using mssql you use single quotes in a sql statement to denote a string. In c# single quotes denote char type not a string. – Julio Garcia Dec 01 '12 at 09:05

SqlDataReader parameter not working

1 Answers1

Linked

Related