Smithers1041
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_Syst em
{
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,buttonCan cel });
form.ClientSize = new Size(Math.Max(300, label.Right + 10), form.ClientSize.Height);
form.FormBorderStyle = FormBorderStyle.FixedDialo g;
form.StartPosition = FormStartPosition.CenterSc reen;
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=tru e; 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(Cus tumerPostc ode);
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.InvalidCastExceptio n
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
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_Syst
{
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
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
form.ClientSize = new Size(Math.Max(300, label.Right + 10), form.ClientSize.Height);
form.FormBorderStyle = FormBorderStyle.FixedDialo
form.StartPosition = FormStartPosition.CenterSc
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(
{
RTBResults.Text = "";
string value = "";
if (FrmQueryControl.InputBox(
{
CustumerPostcode = value;
}
try
{
SqlConnection connection = new SqlConnection(@"server = TOMDESKPC\SQLEXPRESS; integrated security = true; AsynchronousProcessing=tru
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(Cus
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.InvalidCastExceptio
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
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(par am);
instead
Command.Parameters.Add(Cus tumerPostc ode);
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(par
instead
Command.Parameters.Add(Cus
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Made me think about doing it another way.
I think change:
Command.Parameters.Add(Cus
To:
Command.Parameters.Add(par