puzzle question 2

maiyude · Feb 5, 2009

public partial class Insert : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strCon = "Data Source=localhost;Initial
Catalog=pubs;Integrated Security=True;User ID =sa;Password =sa";
SqlConnection myConnection = new SqlConnection(strCon);
// myConnection.Open();

SqlCommand myCommand = new SqlCommand("select * from authors",
myConnection);
//

///puzzle 1 begin
//begin *
SqlCommand sqlInsertCommand1 = new SqlCommand();
sqlInsertCommand1.CommandText = @"INSERT INTO authors(au_id,
au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone, @address, @city, @state, @zip,
@contract);
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract
FROM authors WHERE (au_id = @au_id)";
sqlInsertCommand1.Connection = myConnection;

sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_id",
System.Data.SqlDbType.VarChar, 11, "au_id"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@phone",
System.Data.SqlDbType.VarChar, 12, "phone"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@address",
System.Data.SqlDbType.VarChar, 40, "address"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@city",
System.Data.SqlDbType.VarChar, 20, "city"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@state",
System.Data.SqlDbType.VarChar, 2, "state"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@zip",
System.Data.SqlDbType.VarChar, 5, "zip"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@contract",
System.Data.SqlDbType.Bit, 1, "contract"));

//end *
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = myCommand;
Adapter.InsertCommand = sqlInsertCommand1;//if begin* and end*'s
codes is omitted, this line should be omitted too?

DataSet myDs = new DataSet();
Adapter.Fill(myDs);

//
DataTable myTable = myDs.Tables[0];
//
DataRow myRow = myTable.NewRow();
//
myRow["au_id"] = "322-22-2222";
myRow["au_lname"] = "Li";
myRow["au_fname"] = "Paul";
myRow["phone"] = "12345678";
myRow["city"] = "Chengdu";
myRow["contract"] = 1;

//
myTable.Rows.Add(myRow);

//
Adapter.Update(myDs);

//
myConnection.Close();
myConnection.Open();
Adapter.Fill(myDs);
///it seems that there is no use for the codes between begin* and
end*? Because i found there is no Insertcommand
///below except the Adapter.InsertCommand = sqlInsertCommand1?
///what codes would call the function of the Adapter.InsertCommand?
///puzzle1 end

//
Response.Write("<h3>Insert Data</h3><hr>");
Response.Write("<table border=1 cellspacing=0 cellpadding=2>");

//
Response.Write("<tr bgcolor=#DAB4B4>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + myColumn.ColumnName + "</td>");
}

Response.Write("</tr>");

//
foreach (DataRow row in myTable.Rows)
{
if (row["au_id"].ToString() == "322-22-2222")
{
Response.Write("<tr>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + row[myColumn] + "</td>");
}
Response.Write("</tr>");
break;
}
}
Response.Write("</table>");

myConnection.Close();
}

}

William Vaughn \(MVP\) · Feb 5, 2009

All of these questions (and I expect a lot more) are answered in my book.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

maiyude said:
public partial class Insert : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strCon = "Data Source=localhost;Initial
Catalog=pubs;Integrated Security=True;User ID =sa;Password =sa";
SqlConnection myConnection = new SqlConnection(strCon);
// myConnection.Open();

SqlCommand myCommand = new SqlCommand("select * from authors",
myConnection);
//

///puzzle 1 begin
//begin *
SqlCommand sqlInsertCommand1 = new SqlCommand();
sqlInsertCommand1.CommandText = @"INSERT INTO authors(au_id,
au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone, @address, @city, @state,
@zip,
@contract);
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract
FROM authors WHERE (au_id = @au_id)";
sqlInsertCommand1.Connection = myConnection;

sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_id",
System.Data.SqlDbType.VarChar, 11, "au_id"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@phone",
System.Data.SqlDbType.VarChar, 12, "phone"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@address",
System.Data.SqlDbType.VarChar, 40, "address"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@city",
System.Data.SqlDbType.VarChar, 20, "city"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@state",
System.Data.SqlDbType.VarChar, 2, "state"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@zip",
System.Data.SqlDbType.VarChar, 5, "zip"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@contract",
System.Data.SqlDbType.Bit, 1, "contract"));

//end *
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = myCommand;
Adapter.InsertCommand = sqlInsertCommand1;//if begin* and end*'s
codes is omitted, this line should be omitted too?

DataSet myDs = new DataSet();
Adapter.Fill(myDs);

//
DataTable myTable = myDs.Tables[0];
//
DataRow myRow = myTable.NewRow();
//
myRow["au_id"] = "322-22-2222";
myRow["au_lname"] = "Li";
myRow["au_fname"] = "Paul";
myRow["phone"] = "12345678";
myRow["city"] = "Chengdu";
myRow["contract"] = 1;

//
myTable.Rows.Add(myRow);

//
Adapter.Update(myDs);

//
myConnection.Close();
myConnection.Open();
Adapter.Fill(myDs);
///it seems that there is no use for the codes between begin* and
end*? Because i found there is no Insertcommand
///below except the Adapter.InsertCommand = sqlInsertCommand1?
///what codes would call the function of the Adapter.InsertCommand?
///puzzle1 end

//
Response.Write("<h3>Insert Data</h3><hr>");
Response.Write("<table border=1 cellspacing=0 cellpadding=2>");

//
Response.Write("<tr bgcolor=#DAB4B4>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + myColumn.ColumnName + "</td>");
}

Response.Write("</tr>");

//
foreach (DataRow row in myTable.Rows)
{
if (row["au_id"].ToString() == "322-22-2222")
{
Response.Write("<tr>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + row[myColumn] + "</td>");
}
Response.Write("</tr>");
break;
}
}
Response.Write("</table>");

myConnection.Close();
}

}

Cowboy \(Gregory A. Beamer\) · Feb 5, 2009

I am missing the question.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************

maiyude said:
public partial class Insert : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strCon = "Data Source=localhost;Initial
Catalog=pubs;Integrated Security=True;User ID =sa;Password =sa";
SqlConnection myConnection = new SqlConnection(strCon);
// myConnection.Open();

SqlCommand myCommand = new SqlCommand("select * from authors",
myConnection);
//

///puzzle 1 begin
//begin *
SqlCommand sqlInsertCommand1 = new SqlCommand();
sqlInsertCommand1.CommandText = @"INSERT INTO authors(au_id,
au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone, @address, @city, @state,
@zip,
@contract);
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract
FROM authors WHERE (au_id = @au_id)";
sqlInsertCommand1.Connection = myConnection;

sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_id",
System.Data.SqlDbType.VarChar, 11, "au_id"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@phone",
System.Data.SqlDbType.VarChar, 12, "phone"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@address",
System.Data.SqlDbType.VarChar, 40, "address"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@city",
System.Data.SqlDbType.VarChar, 20, "city"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@state",
System.Data.SqlDbType.VarChar, 2, "state"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@zip",
System.Data.SqlDbType.VarChar, 5, "zip"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@contract",
System.Data.SqlDbType.Bit, 1, "contract"));

//end *
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = myCommand;
Adapter.InsertCommand = sqlInsertCommand1;//if begin* and end*'s
codes is omitted, this line should be omitted too?

DataSet myDs = new DataSet();
Adapter.Fill(myDs);

//
DataTable myTable = myDs.Tables[0];
//
DataRow myRow = myTable.NewRow();
//
myRow["au_id"] = "322-22-2222";
myRow["au_lname"] = "Li";
myRow["au_fname"] = "Paul";
myRow["phone"] = "12345678";
myRow["city"] = "Chengdu";
myRow["contract"] = 1;

//
myTable.Rows.Add(myRow);

//
Adapter.Update(myDs);

//
myConnection.Close();
myConnection.Open();
Adapter.Fill(myDs);
///it seems that there is no use for the codes between begin* and
end*? Because i found there is no Insertcommand
///below except the Adapter.InsertCommand = sqlInsertCommand1?
///what codes would call the function of the Adapter.InsertCommand?
///puzzle1 end

//
Response.Write("<h3>Insert Data</h3><hr>");
Response.Write("<table border=1 cellspacing=0 cellpadding=2>");

//
Response.Write("<tr bgcolor=#DAB4B4>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + myColumn.ColumnName + "</td>");
}

Response.Write("</tr>");

//
foreach (DataRow row in myTable.Rows)
{
if (row["au_id"].ToString() == "322-22-2222")
{
Response.Write("<tr>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + row[myColumn] + "</td>");
}
Response.Write("</tr>");
break;
}
}
Response.Write("</table>");

myConnection.Close();
}

}

Cor Ligthert[MVP] · Feb 6, 2009

Gregory,

Rofl

I am missing the question.

--

They are all answered in the book from Bill.

Cor

maiyude · Feb 6, 2009

thank you
then what is the book's name?where to buy or download?

William Vaughn (MVP) said:
All of these questions (and I expect a lot more) are answered in my book.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

maiyude said:

public partial class Insert : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strCon = "Data Source=localhost;Initial
Catalog=pubs;Integrated Security=True;User ID =sa;Password =sa";
SqlConnection myConnection = new SqlConnection(strCon);
// myConnection.Open();

SqlCommand myCommand = new SqlCommand("select * from authors",
myConnection);
//

///puzzle 1 begin
//begin *
SqlCommand sqlInsertCommand1 = new SqlCommand();
sqlInsertCommand1.CommandText = @"INSERT INTO authors(au_id,
au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone, @address, @city, @state,
@zip,
@contract);
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract
FROM authors WHERE (au_id = @au_id)";
sqlInsertCommand1.Connection = myConnection;

sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_id",
System.Data.SqlDbType.VarChar, 11, "au_id"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@phone",
System.Data.SqlDbType.VarChar, 12, "phone"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@address",
System.Data.SqlDbType.VarChar, 40, "address"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@city",
System.Data.SqlDbType.VarChar, 20, "city"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@state",
System.Data.SqlDbType.VarChar, 2, "state"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@zip",
System.Data.SqlDbType.VarChar, 5, "zip"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@contract",
System.Data.SqlDbType.Bit, 1, "contract"));

//end *
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = myCommand;
Adapter.InsertCommand = sqlInsertCommand1;//if begin* and end*'s
codes is omitted, this line should be omitted too?

DataSet myDs = new DataSet();
Adapter.Fill(myDs);

//
DataTable myTable = myDs.Tables[0];
//
DataRow myRow = myTable.NewRow();
//
myRow["au_id"] = "322-22-2222";
myRow["au_lname"] = "Li";
myRow["au_fname"] = "Paul";
myRow["phone"] = "12345678";
myRow["city"] = "Chengdu";
myRow["contract"] = 1;

//
myTable.Rows.Add(myRow);

//
Adapter.Update(myDs);

//
myConnection.Close();
myConnection.Open();
Adapter.Fill(myDs);
///it seems that there is no use for the codes between begin* and
end*? Because i found there is no Insertcommand
///below except the Adapter.InsertCommand = sqlInsertCommand1?
///what codes would call the function of the
Adapter.InsertCommand?
///puzzle1 end

//
Response.Write("<h3>Insert Data</h3><hr>");
Response.Write("<table border=1 cellspacing=0 cellpadding=2>");

//
Response.Write("<tr bgcolor=#DAB4B4>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + myColumn.ColumnName + "</td>");
}

Response.Write("</tr>");

//
foreach (DataRow row in myTable.Rows)
{
if (row["au_id"].ToString() == "322-22-2222")
{
Response.Write("<tr>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + row[myColumn] + "</td>");
}
Response.Write("</tr>");
break;
}
}
Response.Write("</table>");

myConnection.Close();
}

}

Click to expand...

Gregory A. Beamer · Feb 7, 2009

I just figured the person wanted me to pour through code and fix it, but I
saw no question. Good to know it is Bill's book.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Blog:
http://feeds.feedburner.com/GregoryBeamer

*********************************************
| Think outside the box |
*********************************************

William Vaughn \(MVP\) · Feb 8, 2009

See my sig or website (as shown below).

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

maiyude said:
thank you
then what is the book's name?where to buy or download?

William Vaughn (MVP) said:

All of these questions (and I expect a lot more) are answered in my book.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

maiyude said:

public partial class Insert : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strCon = "Data Source=localhost;Initial
Catalog=pubs;Integrated Security=True;User ID =sa;Password =sa";
SqlConnection myConnection = new SqlConnection(strCon);
// myConnection.Open();

SqlCommand myCommand = new SqlCommand("select * from authors",
myConnection);
//

///puzzle 1 begin
//begin *
SqlCommand sqlInsertCommand1 = new SqlCommand();
sqlInsertCommand1.CommandText = @"INSERT INTO authors(au_id,
au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone, @address, @city, @state,
@zip,
@contract);
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract
FROM authors WHERE (au_id = @au_id)";
sqlInsertCommand1.Connection = myConnection;

sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_id",
System.Data.SqlDbType.VarChar, 11, "au_id"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@phone",
System.Data.SqlDbType.VarChar, 12, "phone"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@address",
System.Data.SqlDbType.VarChar, 40, "address"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@city",
System.Data.SqlDbType.VarChar, 20, "city"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@state",
System.Data.SqlDbType.VarChar, 2, "state"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@zip",
System.Data.SqlDbType.VarChar, 5, "zip"));
sqlInsertCommand1.Parameters.Add(new SqlParameter("@contract",
System.Data.SqlDbType.Bit, 1, "contract"));

//end *
SqlDataAdapter Adapter = new SqlDataAdapter();
Adapter.SelectCommand = myCommand;
Adapter.InsertCommand = sqlInsertCommand1;//if begin* and end*'s
codes is omitted, this line should be omitted too?

DataSet myDs = new DataSet();
Adapter.Fill(myDs);

//
DataTable myTable = myDs.Tables[0];
//
DataRow myRow = myTable.NewRow();
//
myRow["au_id"] = "322-22-2222";
myRow["au_lname"] = "Li";
myRow["au_fname"] = "Paul";
myRow["phone"] = "12345678";
myRow["city"] = "Chengdu";
myRow["contract"] = 1;

//
myTable.Rows.Add(myRow);

//
Adapter.Update(myDs);

//
myConnection.Close();
myConnection.Open();
Adapter.Fill(myDs);
///it seems that there is no use for the codes between begin* and
end*? Because i found there is no Insertcommand
///below except the Adapter.InsertCommand = sqlInsertCommand1?
///what codes would call the function of the
Adapter.InsertCommand?
///puzzle1 end

//
Response.Write("<h3>Insert Data</h3><hr>");
Response.Write("<table border=1 cellspacing=0 cellpadding=2>");

//
Response.Write("<tr bgcolor=#DAB4B4>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + myColumn.ColumnName + "</td>");
}

Response.Write("</tr>");

//
foreach (DataRow row in myTable.Rows)
{
if (row["au_id"].ToString() == "322-22-2222")
{
Response.Write("<tr>");
foreach (DataColumn myColumn in myTable.Columns)
{
Response.Write("<td>" + row[myColumn] + "</td>");
}
Response.Write("</tr>");
break;
}
}
Response.Write("</table>");

myConnection.Close();
}

}

Click to expand...

Click to expand...

most puzzle question 1 about sqlparameters.My question is following the ///.thank you.	2	Feb 4, 2009
Error inserting record.String or binary data would be truncated. The statement has been terminated.	1	Nov 19, 2008
Help with SqlDataAdapter Update command (C#)	2	Jul 23, 2004
Cannot define the DataMember in code	8	Oct 25, 2004
Newbie Update, Insert, & Delete Using Visual Studio And TextBoxes	2	Aug 8, 2005
Newbie: Error in aspx file ?	1	Jan 26, 2006
cannot connect sql server with sqlDataAdapter	1	May 28, 2004
Newbie Question: Deleting from object data source in ASP 2	2	Dec 8, 2005

puzzle question 2

maiyude

William Vaughn \(MVP\)

Cowboy \(Gregory A. Beamer\)

Cor Ligthert[MVP]

maiyude

Gregory A. Beamer

William Vaughn \(MVP\)

Ask a Question

Similar Threads