• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 742
  • Last Modified:

Why Won't My C# Application/Project Retrieve Data From My MS SQL Server 2008 Database And Display it In A Text Box, Help Please.

I Am Writing A Booking System And I want to Retrieve Information Such As Costumer ID and Customer  Name On The Subjects Postcode Store On The Database.
I Have the Following Code
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;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.Odbc;

namespace Plaza_Theater_Booking_System
{

public partial class FrmQueryControl : Form
    {
        //Search Varibles

        //Custumer Names
        string CustumerName1;
        string CustumerName2;
        string CustumerNameF;

        //Custumer Postcode
        string CustumerPostcode;
//Create InputBox
public static DialogResult InputBox(string title, string promptText, ref string value)
        {
            Form form = new Form();
            Label label = new Label();
            TextBox textBox = new TextBox();
            Button buttonOk = new Button();
            Button buttonCancel = new Button();

            form.Text = title;
            label.Text = promptText;
            textBox.Text = value;

            buttonOk.Text = "OK";
            buttonCancel.Text = "Cancel";
            buttonOk.DialogResult = DialogResult.OK;
            buttonCancel.DialogResult = DialogResult.Cancel;

            label.SetBounds(9, 20, 372, 13);
            textBox.SetBounds(12, 36, 372, 20);
            buttonOk.SetBounds(228, 72, 75, 23);
            buttonCancel.SetBounds(309, 72, 75, 23);

            label.AutoSize = true;
            textBox.Anchor = textBox.Anchor | AnchorStyles.Right;
            buttonOk.Anchor = AnchorStyles.Bottom | AnchorStyles.Right;
            buttonCancel.Anchor = AnchorStyles.Bottom | AnchorStyles.Right;

            form.ClientSize = new Size(396, 107);
            form.Controls.AddRange(new Control[] { label, textBox,buttonOk,buttonCancel });
            form.ClientSize = new Size(Math.Max(300, label.Right + 10),    form.ClientSize.Height);
            form.FormBorderStyle = FormBorderStyle.FixedDialog;
            form.StartPosition = FormStartPosition.CenterScreen;
            form.MinimizeBox = false;
            form.MaximizeBox = false;
            form.AcceptButton = buttonOk;
            form.CancelButton = buttonCancel;

            DialogResult dialogResult = form.ShowDialog();
            value = textBox.Text;
            return dialogResult;
        }
//Run Code For Querry Postcode On Click
private void BTNCustumerPostcode_Click(object sender, EventArgs e)
        {
            RTBResults.Text = "";
            string value = "";
            if (FrmQueryControl.InputBox("SQL Varible", "Custumers                   Postcode", ref value) == DialogResult.OK)
            {
                CustumerPostcode = value;
               
            }
            try
            {
                SqlConnection connection = new SqlConnection(@"server =                 TOMDESKPC\SQLEXPRESS; integrated security  = true;                       AsynchronousProcessing=true; database = RAODS Custumer                    SQLData;");
                connection.Open();
                SqlParameter param = new SqlParameter();
                param.ParameterName = "@PostCode";
                param.SqlDbType = SqlDbType.Text;
                param.Value = CustumerPostcode;
                string sql = (@"
                SELECT [Custumer ID], [Title], [First Name], [Second Name],                         [Post Code]
                FROM [RAODS Custumer SQLData].[dbo].[Custumer Data]
                WHERE [Post Code] = '@PostCode'
                ");
                SqlCommand Command = new SqlCommand(sql, connection);
                SqlDataReader rdr = Command.ExecuteReader();
                Command.Parameters.Add(CustumerPostcode);
                while (rdr.Read())
                {
                    RTBResults.Text = "" + rdr[0].ToString();
                    RTBResults.Text += "\t" + rdr[1].ToString();
                    RTBResults.Text += "\t" + rdr[2].ToString();
                    RTBResults.Text += "\t" + rdr[3].ToString();
                    RTBResults.Text += "\t\r\r" + rdr[4].ToString();
                }
                rdr.Close();
                connection.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Error: Unable To Connect To SQL Server,                 Please Contact Your System Adminstrator. Error : {0} Ocared                    " + ex, "SQL Console", MessageBoxButtons.OK,                             MessageBoxIcon.Error);
            }  
        }

When This Run's I Get The Error Message:

System.InvalidCastException
 
The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

The Data In The SQL Table Is Like This (#### ###) eg (SO51 7HS).

The Varible type for the Database Field [Post Code] is NVarChar.

Any Help Would Be Much Aprceated as i have been trying to solve this all week.

Thank You

Thomas Smythe-Wright

0
Smithers1041
Asked:
Smithers1041
  • 2
  • 2
1 Solution
 
Todd GerbertIT ConsultantCommented:
The error message would indicate that you're trying to assign a String to some object's property that should be a SqlParameter.

I think change:

Command.Parameters.Add(CustumerPostcode);

To:
Command.Parameters.Add(param);
0
 
Smithers1041Author Commented:
Thanks for that. Got rid of my error message

I still have a problem that the database is not adding data to the text box.

Any Ideas, code is same as above with

Command.Parameters.Add(param);

instead

Command.Parameters.Add(CustumerPostcode);
0
 
Todd GerbertIT ConsultantCommented:
You don't really need to use a SqlParamter here, you can just set the CommandText property of the SqlCommand to something like "SELECT * FROM TableName WHERE SomeField = 'SomeValue'", but if you do you need to add the paramter to the SqlCommand.Paramters collection BEFORE you try to execute the command.

You'll probably also want to store the connection string as an application setting, the snippet below assumes an application setting named "SqlConnectionString" contains the connection string.

I'm also not sure what's going on with all that white space...?


private void BTNCustumerPostcode_Click(object sender, EventArgs e)
{
    string postCodeQueryValue = String.Empty;
 
    if (FrmQueryControl.InputBox("SQL Varible",
        "Custumers                   Postcode",
        ref postCodeQueryValue) != DialogResult.OK)
            return;
 
    try
    {
        SqlConnection connection = new SqlConnection(
            Properties.Settings.Default.SqlConnectionString);
        connection.Open();
        SqlCommand sqlCmd = connection.CreateCommand();
        sqlCmd.CommandText = String.Format(
            "SELECT * FROM [Customer Data] WHERE [Post Code] = '{0}'", postCodeQueryValue);
        SqlDataReader rdr = sqlCmd.ExecuteReader();
        while (rdr.Read())
        {
            RTBResults.Text = "" + rdr[0].ToString();
            RTBResults.Text += "\t" + rdr[1].ToString();
            RTBResults.Text += "\t" + rdr[2].ToString();
            RTBResults.Text += "\t" + rdr[3].ToString();
            RTBResults.Text += "\t\r\r" + rdr[4].ToString();
        }
        rdr.Close();
        connection.Close();
    }
    catch (SqlException ex)
    {
        MessageBox.Show("Error: " + ex.Message, "SQL Console", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Open in new window

0
 
Smithers1041Author Commented:
Made me think about doing it another way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now