Thanks to anyone who helps!
I'm building a data entry windows form that requires the data to
be sent to a SQL Server table (the table's name is "Local") when the
form's "SAVE" button is clicked. I've built the form using the windows
form designer, and now I'm attempting to use SQL statements to
insert/update data into the table. There are six fields that will use
SQL statements to send data to the Local table, and none of the fields
are permitted to be null values. Two of the fields are comboboxes that
are populated through the use of their own respective datasets
(DisasterType and Dwelling); the datasets were built using the forms
designer OLEdbDataAdapter. Three fields are textboxes(FamilyName,
WorkerName, and DamageDescGeneral) that require the user to manually
enter data. Finally, there is a checkbox (Utilities) that should be
checked if the parameter is "yes/true."
My main problem is that I don't know the correct SQL code that
will allow
the data to be inserted/updated into the Local table. Or, can the
issue be solved using the forms designer? There are also two other
issues.
1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
be changed before being sent to the Local table. I am using the more
user-readable names and descriptions of the respective fields to
populate the form, rather than their Primary Key ID's. However, their
Primary Keys are also Foreign Keys in the Local table, so their
datatypes have to be changed. You'll see this attempted conversion in
the "//convert Disaster_Type to Disaster_ID" and "//convert
Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
recognize the While statement I'm using. Here is the error: Cannot
find method 'While(boolean)' in 'ArcMaster.frmLocal'
2. The datatype for the Utilities column in the Local table is a bit
value, but the datatype on the windows form is a boolean checkbox. I
need to be able to convert from boolean to bit through the use of an If
statement, but I keep getting errors. Here are my compile errors under
the current configuration:
Type 'boolean' is not assignable to 'Object'
Type 'int' is not assignable to 'Object'
I've included my code. I KNOW it's wrong, and I'm hoping
someone can
assist me. Thanks again!!!
import System.Drawing.*;
import System.Collections.*;
import System.ComponentModel.*;
import System.Windows.Forms.*;
import System.Data.*;
import System.Data.SqlClient.*;
import System.*;
/**
* Summary description for Local.
*/
public class frmLocal extends System.Windows.Forms.Form
{
//windows forms designer variables
private System.Windows.Forms.Label lblTitle;
private System.Windows.Forms.TextBox txtFamilyName;
private System.Windows.Forms.Label lblFamilyName;
private System.Windows.Forms.TextBox txtWorkerName;
private System.Windows.Forms.Label lblWorkerName;
private System.Windows.Forms.Label lblDisasterType;
private System.Windows.Forms.ComboBox cboDisasterType;
private System.Windows.Forms.Label lblDwellingType;
private System.Windows.Forms.ComboBox cboDwellingType;
private System.Windows.Forms.Label lblUtilities;
private System.Windows.Forms.Label lblDegreeOfDamage;
private System.Windows.Forms.Label lblDamageDescGeneral;
private System.Windows.Forms.TextBox txtDamageDescGeneral;
private System.Windows.Forms.Button cmdCloseWindow;
private System.Windows.Forms.Button cmdClearEntry;
private System.Windows.Forms.Button cmdSave;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter3;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand3;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand3;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand3;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand3;
private ArcMaster.dsDisasterType dsDisasterType1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter4;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand4;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand4;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand4;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand4;
private ArcMaster.dsDwelling dsDwelling1;
private System.Windows.Forms.CheckBox chkUtilities;
/**
* Required designer variable.
*/
private System.ComponentModel.Container components = null;
public frmLocal()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent
call
//
}
/**
* Clean up any resources being used.
*/
protected void Dispose(boolean disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
super.Dispose(disposing);
}
#region Windows Form Designer generated code
private void frmLocal_Load (Object sender, System.EventArgs e)
{
//populate disaster type combobox with data from Disaster_Type
table
dsDisasterType1.Clear();
oleDbDataAdapter3.Fill(dsDisasterType1);
oleDbConnection1.Close();
//populate dwelling type combobox with data from Dwelling table
dsDwelling1.Clear();
oleDbDataAdapter4.Fill(dsDwelling1);
oleDbConnection1.Close();
}
private void cmdSave_Click (Object sender, System.EventArgs e)
{
//SQL variables
Object sql;
SqlConnection cn;
DataSet dsLocal = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand myCommand;
SqlDataReader reader;
//initialize the variables that handle data to be bound to
Local table;
Object FamilyName = txtFamilyName.get_Text();
Object Dwelling = cboDwellingType.get_SelectedItem();
Object DisasterType = cboDisasterType.get_SelectedItem();
Object WorkerName = txtWorkerName.get_Text();
Object DamageDescGeneral = txtDamageDescGeneral.get_Text();
Object Utilities = chkUtilities.get_CheckState();
//Set the connection string of the SqlConnection object to
connect to the ARC database
cn = new SqlConnection("Server=METZLER"+
"Integrated security=SSPI;" +
"database=ARC");
myCommand= new SqlCommand(sql, cn);
cn.Open();
//convert Disaster_Type to Disaster_ID
sql = "SELECT Disaster_ID FROM Disaster_Type WHERE
((Disaster_Name = " + DisasterType + " ))";
reader = myCommand.ExecuteReader();
While(reader.Read());
{
DisasterType = reader.GetValue(0);
}
reader.Close();
//convert Dwelling_Desc to Dwelling_Type
sql = "SELECT Dwelling_Type FROM Dwelling WHERE ((Dwelling_Desc
= " + Dwelling + "))";
reader = myCommand.ExecuteReader();
While(reader.Read());
{
Dwelling = reader.GetValue(0);
}
reader.Close();
//Initialize the SqlCommandBuilder object to automatically
generate and
//initialize the UpdateCommand, the InsertCommand, and the
DeleteCommand
//properties of the SqlDataAdapter.
cmdBuilder = new SqlCommandBuilder(da);
da.Fill(dsLocal, "Local");
//convert Utilites data from text datatype to bit datatype
If (Utilities = true);
{
Utilities = 1;
}
If (Utilities = false);
{
Utilities = 0;
}
//SQL statement to insert data into Local table
sql = "INSERT INTO Local (Local_rowguid, ARC_Worker,
Description, Disaster_ID, Dwelling_Type, Family_Name, Utilities)" +
"VALUES (NewID(),'" + WorkerName + "', '" + DamageDescGeneral
+ "',
'" + DisasterType + "', '" + Dwelling + "', '" + FamilyName + "', '" +
Utilities;
da.Update(dsLocal, "Local");
//oleDbDataAdapter4.Update(dsDescriptionOfDamage1);
//oleDbConnection1.Close();
//dsLocal1.Clear();
//oleDbDataAdapter4.Update(dsLocal1);
//oleDbDataAdapter4.Fill(dsLocal1);
//oleDbConnection1.Close();
//oleDbConnection1.Close();
//Close the database connection.
cn.Close();
MessageBox.Show("Local Detailed Damage Assessment has been
updated.");
}
private void cmdCloseWindow_Click (Object sender, System.EventArgs e)
{
Close();> My main problem is that I don't know the correct SQL code that
> will allow
> the data to be inserted/updated into the Local table.
Rather than build a SQL statement string by concatenating the values, I
suggest you always use parameters. This is more secure and simplifies your
code since you don't need to format data, escape quotes or enclose values.
For Example:
sql = "INSERT INTO Local" +
" (" +
" Local_rowguid, " +
" ARC_Worker, " +
" Description, " +
" Disaster_ID, " +
" Dwelling_Type, " +
" Family_Name, " +
" Utilities)" +
" VALUES" +
" (" +
" @.Local_rowguid, " +
" @.ARC_Worker, " +
" @.Description, " +
" @.Disaster_ID, " +
" @.Dwelling_Type, " +
" @.Family_Name, " +
" @.Utilities" +
" )";
SqlCommand insertCommand = new SqlCommand(sql, cn);
insertCommand.Parameters.Add(@.Local_rowguid, Guid.NewGuid());
insertCommand.Parameters.Add(@.FamilyName, FamilyName);
insertCommand.Parameters.Add(@.Dwelling, Dwelling);
insertCommand.Parameters.Add(@.DisasterType, DisasterType);
insertCommand.Parameters.Add(@.WorkerName, WorkerName);
insertCommand.Parameters.Add(@.DamageDescGeneral, DamageDescGeneral);
insertCommand.Parameters.Add(@.DamageDescGeneral, Utilities);
Note that SqlCommandBuilder will generate parameterized INSERT/UPDATE/DELETE
SQL commands for the DataAdapter so you don't need to code those yourself.
You could use NEWID() to assign the Local_rowguid value (instead of
Guid.NewGuid()) and omit that parameter but my personal preference is to
supply the Guid value on the client side with single-row inserts. This way,
the value is known by your app so you don't retrieve the assigned value.
> Or, can the issue be solved using the forms designer?
It's unclear how you intend your app to work because the code is incomplete
are using a variety of data access techniques. Since you are a newbie, I
suspect you're just learning how to use these classes.
The DataAdapter Update method will execute all the necessary
INSERT/UPDATE/DELETE commands depending on the changes made
to the DataSet. In order to use Update, the dataset needs to contain both
before and after data images.
Normally, one uses the Fill method to load existing data into the dataset.
Your
application can then manipulate data in the dataset (adding rows,
changing values, deleting rows), This can be done manually or by binding
controls /datasets. Finally, the DataSet Update method is called to save
changes
to the database.
The 'Local' table dataset should probably be a member variable rather than a
local
variable if you want to use DataSet Update within your save method.
Otherwise,
your save method can be used only to insert new data.
> There are also two other
> issues.
> 1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
> be changed before being sent to the Local table. I am using the more
> user-readable names and descriptions of the respective fields to
> populate the form, rather than their Primary Key ID's. However, their
> Primary Keys are also Foreign Keys in the Local table, so their
> datatypes have to be changed. You'll see this attempted conversion in
> the "//convert Disaster_Type to Disaster_ID" and "//convert
> Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
> recognize the While statement I'm using. Here is the error: Cannot
> find method 'While(boolean)' in 'ArcMaster.frmLocal'
> 2. The datatype for the Utilities column in the Local table is a bit
> value, but the datatype on the windows form is a boolean checkbox. I
> need to be able to convert from boolean to bit through the use of an If
I expect that these are just a few of many compile errors because your code
is a mix of VB, C# and Java.
Assuming you are using C#, that language is case-sensitive. Specify 'while'
instead of 'While' and ditch the semi-colon after the predicate. For
example:
while(reader.Read())
{
Dwelling = reader.GetValue(0);
}
C# is a strongly-typed language so you should specify the proper types when
possible and convert explicitly. To access property values, don't call the
accessor method directly; specify only the property name.
string FamilyName = txtFamilyName.Text;
string Dwelling = (string)cboDwellingType.SelectedItem;
string DisasterType = (string)cboDisasterType.SelectedItem;
string WorkerName = txtWorkerName.Text;
string DamageDescGeneral = txtDamageDescGeneral.Text;
bool Utilities = chkUtilities.Checked;
The above example also shows how to avoid selecting the Dwelling and
DisasterType values from the database again. It looks like you've already
loaded those datasets in your init code. With typed datasets, all you need
to do is set the combobox DisplayMember and ValueMember ti the desired
properties and so that SelectedItem returns the selected value.
> import System.Drawing.*;
Specify 'using' instead of 'import' for namespace references:
using System.Drawing;
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pmetz" <p1metzler@.yahoo.com> wrote in message
news:1144446095.869601.105850@.z34g2000cwc.googlegroups.com...
> Thanks to anyone who helps!
> I'm building a data entry windows form that requires the data to
> be sent to a SQL Server table (the table's name is "Local") when the
> form's "SAVE" button is clicked. I've built the form using the windows
> form designer, and now I'm attempting to use SQL statements to
> insert/update data into the table. There are six fields that will use
> SQL statements to send data to the Local table, and none of the fields
> are permitted to be null values. Two of the fields are comboboxes that
> are populated through the use of their own respective datasets
> (DisasterType and Dwelling); the datasets were built using the forms
> designer OLEdbDataAdapter. Three fields are textboxes(FamilyName,
> WorkerName, and DamageDescGeneral) that require the user to manually
> enter data. Finally, there is a checkbox (Utilities) that should be
> checked if the parameter is "yes/true."
> My main problem is that I don't know the correct SQL code that
> will allow
> the data to be inserted/updated into the Local table. Or, can the
> issue be solved using the forms designer? There are also two other
> issues.
>
> 1. The datatypes of the "Disaster Type" and "Dwelling" fields have to
> be changed before being sent to the Local table. I am using the more
> user-readable names and descriptions of the respective fields to
> populate the form, rather than their Primary Key ID's. However, their
> Primary Keys are also Foreign Keys in the Local table, so their
> datatypes have to be changed. You'll see this attempted conversion in
> the "//convert Disaster_Type to Disaster_ID" and "//convert
> Dwelling_Desc to Dwelling_Type" statements. The compiler doesn't
> recognize the While statement I'm using. Here is the error: Cannot
> find method 'While(boolean)' in 'ArcMaster.frmLocal'
>
> 2. The datatype for the Utilities column in the Local table is a bit
> value, but the datatype on the windows form is a boolean checkbox. I
> need to be able to convert from boolean to bit through the use of an If
> statement, but I keep getting errors. Here are my compile errors under
> the current configuration:
> Type 'boolean' is not assignable to 'Object'
> Type 'int' is not assignable to 'Object'
>
> I've included my code. I KNOW it's wrong, and I'm hoping
> someone can
> assist me. Thanks again!!!
>
> import System.Drawing.*;
> import System.Collections.*;
> import System.ComponentModel.*;
> import System.Windows.Forms.*;
> import System.Data.*;
> import System.Data.SqlClient.*;
> import System.*;
>
> /**
> * Summary description for Local.
> */
> public class frmLocal extends System.Windows.Forms.Form
> {
>
> //windows forms designer variables
> private System.Windows.Forms.Label lblTitle;
> private System.Windows.Forms.TextBox txtFamilyName;
> private System.Windows.Forms.Label lblFamilyName;
> private System.Windows.Forms.TextBox txtWorkerName;
> private System.Windows.Forms.Label lblWorkerName;
> private System.Windows.Forms.Label lblDisasterType;
> private System.Windows.Forms.ComboBox cboDisasterType;
> private System.Windows.Forms.Label lblDwellingType;
> private System.Windows.Forms.ComboBox cboDwellingType;
> private System.Windows.Forms.Label lblUtilities;
> private System.Windows.Forms.Label lblDegreeOfDamage;
> private System.Windows.Forms.Label lblDamageDescGeneral;
> private System.Windows.Forms.TextBox txtDamageDescGeneral;
> private System.Windows.Forms.Button cmdCloseWindow;
> private System.Windows.Forms.Button cmdClearEntry;
> private System.Windows.Forms.Button cmdSave;
> private System.Data.OleDb.OleDbConnection oleDbConnection1;
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter3;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand3;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand3;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand3;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand3;
> private ArcMaster.dsDisasterType dsDisasterType1;
> private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter4;
> private System.Data.OleDb.OleDbCommand oleDbSelectCommand4;
> private System.Data.OleDb.OleDbCommand oleDbInsertCommand4;
> private System.Data.OleDb.OleDbCommand oleDbUpdateCommand4;
> private System.Data.OleDb.OleDbCommand oleDbDeleteCommand4;
> private ArcMaster.dsDwelling dsDwelling1;
> private System.Windows.Forms.CheckBox chkUtilities;
>
> /**
> * Required designer variable.
> */
> private System.ComponentModel.Container components = null;
>
> public frmLocal()
> {
> //
> // Required for Windows Form Designer support
> //
> InitializeComponent();
>
> //
> // TODO: Add any constructor code after InitializeComponent
> call
> //
>
> }
>
> /**
> * Clean up any resources being used.
> */
> protected void Dispose(boolean disposing)
> {
> if (disposing)
> {
> if (components != null)
> {
> components.Dispose();
> }
> }
> super.Dispose(disposing);
>
> }
>
> #region Windows Form Designer generated code
> private void frmLocal_Load (Object sender, System.EventArgs e)
> {
> //populate disaster type combobox with data from Disaster_Type
> table
> dsDisasterType1.Clear();
> oleDbDataAdapter3.Fill(dsDisasterType1);
> oleDbConnection1.Close();
>
> //populate dwelling type combobox with data from Dwelling table
>
> dsDwelling1.Clear();
> oleDbDataAdapter4.Fill(dsDwelling1);
> oleDbConnection1.Close();
>
> }
>
> private void cmdSave_Click (Object sender, System.EventArgs e)
> {
> //SQL variables
> Object sql;
> SqlConnection cn;
> DataSet dsLocal = new DataSet();
> SqlDataAdapter da = new SqlDataAdapter();
> SqlCommand myCommand;
> SqlDataReader reader;
> //initialize the variables that handle data to be bound to
> Local table;
> Object FamilyName = txtFamilyName.get_Text();
> Object Dwelling = cboDwellingType.get_SelectedItem();
> Object DisasterType = cboDisasterType.get_SelectedItem();
> Object WorkerName = txtWorkerName.get_Text();
> Object DamageDescGeneral = txtDamageDescGeneral.get_Text();
> Object Utilities = chkUtilities.get_CheckState();
>
> //Set the connection string of the SqlConnection object to
> connect to the ARC database
> cn = new SqlConnection("Server=METZLER"+
> "Integrated security=SSPI;" +
> "database=ARC");
>
> myCommand= new SqlCommand(sql, cn);
>
> cn.Open();
>
> //convert Disaster_Type to Disaster_ID
> sql = "SELECT Disaster_ID FROM Disaster_Type WHERE
> ((Disaster_Name = " + DisasterType + " ))";
> reader = myCommand.ExecuteReader();
> While(reader.Read());
> {
> DisasterType = reader.GetValue(0);
> }
> reader.Close();
>
> //convert Dwelling_Desc to Dwelling_Type
> sql = "SELECT Dwelling_Type FROM Dwelling WHERE ((Dwelling_Desc
> = " + Dwelling + "))";
> reader = myCommand.ExecuteReader();
> While(reader.Read());
> {
> Dwelling = reader.GetValue(0);
> }
> reader.Close();
>
> //Initialize the SqlCommandBuilder object to automatically
> generate and
> //initialize the UpdateCommand, the InsertCommand, and the
> DeleteCommand
> //properties of the SqlDataAdapter.
> cmdBuilder = new SqlCommandBuilder(da);
>
> da.Fill(dsLocal, "Local");
>
> //convert Utilites data from text datatype to bit datatype
> If (Utilities = true);
> {
> Utilities = 1;
> }
> If (Utilities = false);
> {
> Utilities = 0;
> }
>
> //SQL statement to insert data into Local table
> sql = "INSERT INTO Local (Local_rowguid, ARC_Worker,
> Description, Disaster_ID, Dwelling_Type, Family_Name, Utilities)" +
> "VALUES (NewID(),'" + WorkerName + "', '" + DamageDescGeneral
> + "',
> '" + DisasterType + "', '" + Dwelling + "', '" + FamilyName + "', '" +
> Utilities;
>
> da.Update(dsLocal, "Local");
>
> //oleDbDataAdapter4.Update(dsDescriptionOfDamage1);
> //oleDbConnection1.Close();
> //dsLocal1.Clear();
> //oleDbDataAdapter4.Update(dsLocal1);
>
> //oleDbDataAdapter4.Fill(dsLocal1);
> //oleDbConnection1.Close();
> //oleDbConnection1.Close();
>
> //Close the database connection.
> cn.Close();
>
> MessageBox.Show("Local Detailed Damage Assessment has been
> updated.");
> }
>
> private void cmdCloseWindow_Click (Object sender, System.EventArgs e)
> {
> Close();
>
