我是撰寫代碼的新手，當我想在服務器端編輯資料庫時，我遇到了以下問題。編輯時無法讀取日期、開始時間和結束時間。
// This method is used to select the EVENT data from the database
SqlCommand UpdateEvent = new SqlCommand
{
CommandText = "SELECT * FROM EVENT WHERE Event_ID = " Event_ID,
CommandType = CommandType.Text,
Connection = con
};
con.Open(); // Open the database connection
// This function is uses to output the existing COMPETITOR infomation in the database for edit purpose
if (con.State == ConnectionState.Open) // If the database connection is open, execute the following code
{
SqlDataReader readEvent = UpdateEvent.ExecuteReader();
while (readEvent.Read()) //Check the EVENT infomation
{
this.EventNameHeader.InnerHtml = "<h2 class='standardheader'>Update " readEvent.GetInt32(1) "</h2>";
this.CtrlEvent_ID.Value = readEvent.GetInt32(0).ToString();
this.CtrlG_ID.Text = readEvent.GetInt32(1).ToString();
this.CtrlF_Event.Text = readEvent.GetString(2);
this.CtrlE_Venue.Text = readEvent.GetString(3);
this.CtrlE_Date.Value = readEvent.GetDateTime(4).Date.ToString("dd-MM-YYYY");
this.CtrlE_StartTime.SelectedValue = readEvent.GetTimeSpan(5).ToString("HH:mm:ss");
this.CtrlE_EndTime.SelectedValue = readEvent.GetTimeSpan(6).ToString("HH:mm:ss");
this.CtrlE_Desc.Text = readEvent.GetString(7);
this.CtrlW_Record.Text = readEvent.GetString(8);
}
}
else
{
Response.Write("SQL DB Connect Failed");
}
con.Close(); // Close the connection to END the EVENT creation
}
}
uj5u.com熱心網友回復：
看來是你在這里寫了很多代碼。
退后。
我們想要：
獲取資料（到資料表真的是個好主意）將資料從表中放入控制元件。
讓用戶編輯 - 玩得開心。
將資料從控制元件放到表格中。
將表保存回資料庫。
注意上面的步驟。
因此，讓我們假設一些控制元件，以及日期開始和日期結束。
但是，我們還有開始時間和結束時間。現在當然我們不想在這里創建 4 個資料庫列。我們只需要資料庫中的 dtStart 和 dtEnd。
我們假設開始/結束時間與當前日期相同。但是，理論上在這個例子中，預訂可以延長到午夜之后，并且可以很容易地延長它以允許預訂幾天。但是，我們為此堅持一個簡單的日期。
所以，我們的標記：
<div style="padding: 25px; width: 20%;border:solid;border-width:1px">
<h2>Booking for Oct 18</h2>
<div style="text-align:right">
<p>First Name: <asp:TextBox ID="FirstName" runat="server" /></p>
<p>Last Name: <asp:TextBox ID="LastName" runat="server" /></p>
<p>Venue: <asp:TextBox ID="Venu" runat="server" /></p>
<p>
Booking Date:<asp:TextBox ID="txtDate" runat="server" Width="130px"
TextMode="Date">
</asp:TextBox>
</p>
<p>
Start Time:<asp:TextBox ID="dtStart" runat="server" TextMode="Time">
</asp:TextBox>
</p>
<p>
End Time:<asp:TextBox ID="dtEnd" runat="server" TextMode="Time">
</asp:TextBox>
</p>
</div>
</div>
<br />
<asp:Button ID="cmdSave" runat="server" Text="Save Changes" CssClass="btn" OnClick="cmdSave_Click" />
</div>
現在我們的代碼要填寫在上面：
DataTable rstData = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadData();
ViewState["MyData"] = rstData;
TableToUI();
}
else
rstData = (DataTable)ViewState["MyData"];
}
void LoadData()
{
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
using (SqlCommand cmdSQL = new SqlCommand("SELECT top 1 * FROM People where Active = 1", conn))
{
conn.Open();
rstData.Load(cmdSQL.ExecuteReader());
}
}
}
void TableToUI()
{
// move Data to controls
DataRow OneDataRow = rstData.Rows[0];
FirstName.Text = OneDataRow["FirstName"].ToString();
LastName.Text = OneDataRow["LastName"].ToString();
Venu.Text = OneDataRow["Venu"].ToString();
txtDate.Text = ((DateTime)OneDataRow["dtStart"]).ToString("yyyy-MM-dd");
dtStart.Text = ((DateTime)OneDataRow["dtStart"]).ToString("HH:mm");
dtEnd.Text = ((DateTime)OneDataRow["dtEnd"]).ToString("HH:mm");
}
So note how we break out EACH part. Get data to table (don't mess with UI just yet). Send data to controls. --- note the ease in which we can format the data. Note how we SPLIT OUT the one date into 3 parts:
booking date
booking start time
booking end time
So, we now have this:
And note how I get free date and time pickers!!! - just set the "text mode" as I did above.
Now, to save, we have to take that date time back into one.
I also assume the end date is the SAME date, but if we allow bookings past midnight or say we want to book for a few days (maybe a weekend), then trival changes to above.
But, now our save button code looks like this:
protected void cmdSave_Click(object sender, EventArgs e)
{
UIToTable();
using(SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
{
using (SqlCommand cmdSQL = new SqlCommand("SELECT * FROM People WHERE ID = 0", conn))
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
SqlCommandBuilder daU = new SqlCommandBuilder(da);
da.Update(rstData);
}
}
}
And of course we do need the code for sending controls back to the data table.
void UIToTable()
{
// move controls back to table
DataRow OneDataRow = rstData.Rows[0];
OneDataRow["FirstName"] = FirstName.Text;
OneDataRow["LastName"] = LastName.Text;
OneDataRow["Venu"] = Venu.Text;
string sDate = txtDate.Text;
string sTime = dtStart.Text;
// combine Date time into one
OneDataRow["dtStart"] = DateTime.Parse(sDate " " sTime);
// combine end date time into one
sTime = dtEnd.Text;
OneDataRow["dtEnd"] = DateTime.Parse(sDate " " sTime);
}
So, how do you eat a elephant?
Answer: one bit at a time.
So, break out your steps into the above desing pattern.
And we could VERY easy extend the above to work say as a grid view, and just add outloops to each of our 2 routines (table to UI, and UI to table). So, this would result in a grid like (Excel like) result in which you could tab around, and again we could (and would) send all edits back to the database with the SAME code we used.
We just happen to only allow one row here, but the above code desing pattern would work if this was a grid. And in fact, if you need, and ask? I'll post the above code as a nice working grid with multiple rows - and you see/find the code is much the same as above (in other words, editing the one record, or a grid of records works quite much the same).
And note VERY careful in above: this:
using (SqlCommand cmdSQL = new SqlCommand("SELECT * FROM People WHERE ID = 0", conn))
That is NOT type-o. I simple open the database, don't select any rows (ID = 0), use command builder to make the update command, and then save the table.
I did the above to save world poverty and not have to write a gazillion parameters, but keep STRONG typed, and never allow sql injection as a 2nd bonus points.
So, using a datatable (and pulling out dataRow) can save a boatload of parameters and sql update code.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/325414.html