DTS Transformation Task: building

Showing posts with label building. Show all posts

Monday, March 26, 2012

Newbie question

hi,
I am interested in building a DW for my company. As usual, we have 4
different systems which holds different applications. I would like to know
what is the first step of buidling the DW? Retrieve the columns / data that
we need? Any good link to view?
Thanks so much
Ed
On Nov 5, 10:26 pm, Ed <E...@.discussions.microsoft.com> wrote:
> hi,
> I am interested in building a DW for my company. As usual, we have 4
> different systems which holds different applications. I would like to know
> what is the first step of buidling the DW? Retrieve the columns / data that
> we need? Any good link to view?
> Thanks so much
> Ed
The first step is to close your eyes, take a deep breath, and prepare
for a long and exciting journey.
I am _very_ new to the DW/BI scene so bear that in mind when reading
this post.
I have found these websites helpful:
http://www.dwinfocenter.org/
http://www.kimballgroup.com/
However, what works best for me is the old-fashioned approach: book
learning combined with trail-and-error. You might want to stop by a
bookstore (or use Amazon's "Search Inside" feature) and flip through
some of these titles: http://www.kimballgroup.com/html/books.html
I all but stopped buying technical books several years ago because the
information is often outdated by the time the book goes to press.
Having said that, for a subject that is deep, wide, and unfamiliar,
trying to get up to speed using only internet searches can be
frustrating and counterproductive.
Best wishes on your project!
|||You can hire my team!
"happy camper" <keenan@.p2sol.com> wrote in message
news:1194471643.414378.82940@.57g2000hsv.googlegrou ps.com...
> On Nov 5, 10:26 pm, Ed <E...@.discussions.microsoft.com> wrote:
> The first step is to close your eyes, take a deep breath, and prepare
> for a long and exciting journey.
> I am _very_ new to the DW/BI scene so bear that in mind when reading
> this post.
> I have found these websites helpful:
> http://www.dwinfocenter.org/
> http://www.kimballgroup.com/
> However, what works best for me is the old-fashioned approach: book
> learning combined with trail-and-error. You might want to stop by a
> bookstore (or use Amazon's "Search Inside" feature) and flip through
> some of these titles: http://www.kimballgroup.com/html/books.html
> I all but stopped buying technical books several years ago because the
> information is often outdated by the time the book goes to press.
> Having said that, for a subject that is deep, wide, and unfamiliar,
> trying to get up to speed using only internet searches can be
> frustrating and counterproductive.
> Best wishes on your project!
>
|||Gartner claimed a short time ago that DW efforts were the near 50% failure
rate.
Maybe start with just a data mart on a specific subject area on just one of
your 4 applications. Get the ETL working with SSIS, develop a cube in SSAS
and use Excel 2007 to browse cubes or just use SSMS.
Once you are succesful on a specific subject area then you can start pulling
in your other hetrogenous sources.
Without help you will flail about some - so if at all possible; hire some
assistance.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eS3A$SdIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:CD21EB5F-6FA2-422B-A115-CDCC5D5910AD@.microsoft.com...
> If you don't have BI experience then get some experienced assistance on
> site. A DW is only one part of a BI solution. To be successful and deliver
> value to your business customers you should first consider the whole
> end-to-end picture.
> --
> David Portas
>
|||To name just a few items: Create small wins for yourself by Implementing
incrementally, always working on those sections that will have the biggest
positive businss impact and can released in the shortest amount of time. If
you don't already know how to data model, learn. Read Ralph Kimball's books
on OLAP design and implementation
Sincerely,
John K
President
Knowledgy Consulting, LLC
1024 Spalding Forest Ct.
Atlanta, GA 30328
Knowledgy.org
Contact: Knowledgy$knowledgy.org
Replace $ with @. for emails

Friday, March 23, 2012

Newbie question

hi,
I am interested in building a DW for my company. As usual, we have 4
different systems which holds different applications. I would like to know
what is the first step of buidling the DW? Retrieve the columns / data that
we need? Any good link to view?
Thanks so much
EdOn Nov 5, 10:26 pm, Ed <E...@.discussions.microsoft.com> wrote:
> hi,
> I am interested in building a DW for my company. As usual, we have 4
> different systems which holds different applications. I would like to kno
w
> what is the first step of buidling the DW? Retrieve the columns / data th
at
> we need? Any good link to view?
> Thanks so much
> Ed
The first step is to close your eyes, take a deep breath, and prepare
for a long and exciting journey.
I am _very_ new to the DW/BI scene so bear that in mind when reading
this post.
I have found these websites helpful:
http://www.dwinfocenter.org/
http://www.kimballgroup.com/
However, what works best for me is the old-fashioned approach: book
learning combined with trail-and-error. You might want to stop by a
bookstore (or use Amazon's "Search Inside" feature) and flip through
some of these titles: http://www.kimballgroup.com/html/books.html
I all but stopped buying technical books several years ago because the
information is often outdated by the time the book goes to press.
Having said that, for a subject that is deep, wide, and unfamiliar,
trying to get up to speed using only internet searches can be
frustrating and counterproductive.
Best wishes on your project!|||You can hire my team!
"happy camper" <keenan@.p2sol.com> wrote in message
news:1194471643.414378.82940@.57g2000hsv.googlegroups.com...
> On Nov 5, 10:26 pm, Ed <E...@.discussions.microsoft.com> wrote:
> The first step is to close your eyes, take a deep breath, and prepare
> for a long and exciting journey.
> I am _very_ new to the DW/BI scene so bear that in mind when reading
> this post.
> I have found these websites helpful:
> http://www.dwinfocenter.org/
> http://www.kimballgroup.com/
> However, what works best for me is the old-fashioned approach: book
> learning combined with trail-and-error. You might want to stop by a
> bookstore (or use Amazon's "Search Inside" feature) and flip through
> some of these titles: http://www.kimballgroup.com/html/books.html
> I all but stopped buying technical books several years ago because the
> information is often outdated by the time the book goes to press.
> Having said that, for a subject that is deep, wide, and unfamiliar,
> trying to get up to speed using only internet searches can be
> frustrating and counterproductive.
> Best wishes on your project!
>|||"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:CD21EB5F-6FA2-422B-A115-CDCC5D5910AD@.microsoft.com...
> hi,
> I am interested in building a DW for my company. As usual, we have 4
> different systems which holds different applications. I would like to
> know
> what is the first step of buidling the DW? Retrieve the columns / data
> that
> we need? Any good link to view?
> Thanks so much
> Ed
If you don't have BI experience then get some experienced assistance on
site. A DW is only one part of a BI solution. To be successful and deliver
value to your business customers you should first consider the whole
end-to-end picture.
David Portas|||Gartner claimed a short time ago that DW efforts were the near 50% failure
rate.
Maybe start with just a data mart on a specific subject area on just one of
your 4 applications. Get the ETL working with SSIS, develop a cube in SSAS
and use Excel 2007 to browse cubes or just use SSMS.
Once you are succesful on a specific subject area then you can start pulling
in your other hetrogenous sources.
Without help you will flail about some - so if at all possible; hire some
assistance.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eS3A$SdIIHA.4196@.TK2MSFTNGP04.phx.gbl...
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:CD21EB5F-6FA2-422B-A115-CDCC5D5910AD@.microsoft.com...
> If you don't have BI experience then get some experienced assistance on
> site. A DW is only one part of a BI solution. To be successful and deliver
> value to your business customers you should first consider the whole
> end-to-end picture.
> --
> David Portas
>|||To name just a few items: Create small wins for yourself by Implementing
incrementally, always working on those sections that will have the biggest
positive businss impact and can released in the shortest amount of time. If
you don't already know how to data model, learn. Read Ralph Kimball's books
on OLAP design and implementation
Sincerely,
John K
President
Knowledgy Consulting, LLC
1024 Spalding Forest Ct.
Atlanta, GA 30328
---
Knowledgy.org
---
Contact: Knowledgy$knowledgy.org
Replace $ with @. for emails
---

Monday, March 19, 2012

Newbie needing help ASAP with width issue in sql report.

I am building a Sql Report in VS.net 2006 I have run into an issue I have 258 colums it am using the wizard table generator in design mode. the problem is the screen will on allow 160 inches wide I need to mak this larger to 300 inches the purpose of the report is to sort data and them save to csv format is there a better way or can some on tell me how to increase the size of the width the properties tab will not allow me to explain pass 160 inches. Some one please help me thanksWhy not tackle the job another way ? Have a look at http://www.codeproject.com/useritems/filehelpers.asp "An easy to use .NET library to read/write strong typed data from fileswith fixed length or delimited records (CSV). Also has support toimport/export data from different data storages (Excel, Acces,SqlServer, MySql)"|||The issue I am having is a task was given to me to pull data from an invoice module out of SQL and creat a 288 field output of data in a csv format. The problem is we on have about 100 fields data the rest have to be blank and all this has to be in an order so . I was going to build a report in th sql reports in my business intell create the headers for all 288 files and create the expression in the correct placement create several sort paramerters then I was going to just save as csv format this would create the csv file with the correct order and blank fields separated by the commas. the problem is I dont know how to creat empty fields on an output to csv.|||

Suppose your table FRED has three fields A, C and D and you want to output 5, use:

SELECT A, ' ' AS B, C, ' ' AS D, E FROM FRED

That will create 2 empty fields - you can readily extend the technique to create 188 blank fields.

|||

Thank youy this makes sense. The only question is I may need it to look like this

"john", "Smith","","","","","","","404","555-5555","123 Main Street","","completed",

If I under stand the your code I can select A,B,' ' AS C,' ' AS D,' ' AS E, ' ' AS F, ' ' AS G, ' ' AS H,I,J,K,' ' AS L,M FROM TABLE

|||

>>The only question is I may need it to look like this: "john", "Smith","","","","","","","404","555-5555","123 Main Street","","completed",
The quoting takes places aroung each value, whether it is occupied or empty.

>>If I under stand the your code I can select A,B,' ' AS C,' ' AS D,' ' AS E, ' ' AS F, ' ' AS G, ' ' AS H,I,J,K,' ' AS L,M FROM TABLE

Yes! I put a space between the quote marks for clarity; you will not need to do this.

Friday, March 9, 2012

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();
>

newbie having trouble with SQL connection, insert/update

newbie having trouble w/ insert/update statments

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();
>

newbie having trouble w/ insert/update statments

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();
>

Monday, March 26, 2012

Newbie question

Friday, March 23, 2012

Newbie question

Monday, March 19, 2012

Newbie needing help ASAP with width issue in sql report.

Friday, March 9, 2012

newbie having trouble with SQL connection, insert/update

newbie having trouble with SQL connection, insert/update

newbie having trouble w/ insert/update statments

newbie having trouble w/ insert/update statments

DTS Transformation Task

Blog Archive

About Me