newbie having trouble with SQL connection, insert/update

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();Hi
You insert statement does not have a closing bracket, you also seem to be
making values strings by enquoting them when they are not character data
types. You could form the sql string differently depending on whether
utility is true or not.
John
"pmetz" <p1metzler@.yahoo.com> wrote in message
news:1144497354.135933.54280@.i40g2000cwc.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();
>