Link to home
Start Free TrialLog in
Avatar of Smithers1041
Smithers1041Flag for United States of America

asked on

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

Avatar of Todd Gerbert
Todd Gerbert
Flag of United States of America image

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);
Avatar of Smithers1041

ASKER

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);
ASKER CERTIFIED SOLUTION
Avatar of Todd Gerbert
Todd Gerbert
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Made me think about doing it another way.