Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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.

Posted on 2009-02-20
4
Medium Priority
?
734 Views
Last Modified: 2013-12-14
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
Comment
Question by:Smithers1041
  • 2
  • 2
4 Comments
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 23696539
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
 

Author Comment

by:Smithers1041
ID: 23700636
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
 
LVL 33

Accepted Solution

by:
Todd Gerbert earned 1500 total points
ID: 23705230
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
 

Author Closing Comment

by:Smithers1041
ID: 31549442
Made me think about doing it another way.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question