I am new in writing code, I have some following issues when I want to edit the database on the server-side. Date, StartTime & EndTime cannot be read when edit.
// 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
}
}
CodePudding user response：
It seems to be that you writing a lot of code here.
Step back.
We want:
Get data (to a data table REALLY good idea) Put data from table to controls.
Let user edit - have fun.
Put data from controls to table.
Save table back to database.
note the STEPS above.
So, lets assume some controls, and a Date start, and a date end.
However, we ALSO have start end time. Now of course we DO NOT want to make 4 database columns here. We ONLY want dtStart, and dtEnd in the database.
We assume that the start/end time is the SAME as the current date. But, in theory in this example, a booking could extend past midnight, and it would be EASY to extend this to allow say a booking of a few days. But, we stick to one simple date for this.
So, our markup:
<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>
And now our code to fill above:
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.