|
I am wondering what is the best way to edit a database that has multiple tables.
(Section 1) I have a Administration, Teacher and Student table that is linked up with the Person table which has a Primary Key.
The Person table consists of the general information of the person.
The Student table consists of information about the student; student ID and qualification code.
The Teacher table consists of information about the teacher; teacher ID, Reg No and password.
The Administration table consists of information about the admin; admin ID, Role and password.
As the Primary Key in the Person table is the ID, I have linked up with each of the other's table with their appropriate ID.
(Section 2) I have a Course and Qualification table that is linked up with the Student and Teacher table as well as each other.
The course table constists of the Course ID, Course Name ... and Teacher ID.
The Qualifications table consists of Qualification Code, Qualification Name and Duration.
There is a section where I have to create a view which shows just the Student ID, Course ID and has the Student's Marks in it.
I have got a combobox which then links up with the dgv(datagridview).
I have got insert and delete methods for both sections. Here is an example of the insert method into the Admin/Person table.
try
{
personTableAdapter.Insert(aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text);
administrationTableAdapter.Insert(Convert.ToInt32(aAID1.Text), aRole.Text, aPassword.Text);
MessageBox.Show(aFirstName.Text + " " + aSurname.Text + " has been added. Your ID is " + aAID1.Text);
this.personTableAdapter.Fill(this._30002195DataSet.Person);
this.administrationTableAdapter.Fill(this._30002195DataSet.Administration);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Here is an example of the delete method in the Admin/Person table.
try
{
personTableAdapter.Delete(Convert.ToInt32(aID.Text), aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text);
administrationTableAdapter.Delete(Convert.ToInt32(aAID.Text), aRole.Text, aPassword.Text);
MessageBox.Show("Person Deleted");
this.personTableAdapter.Fill(this._30002195DataSet.Person);
this.administrationTableAdapter.Fill(this._30002195DataSet.Administration);
}
catch (Exception)
{
MessageBox.Show("Cannot delete Person");
}
Those methods above are working fine, what I'm having problems with, is with the editing/updating part.
I have tried a few things and haven't worked.
Here's an example of an edit, while trying to use textboxes.
personTableAdapter.Update(aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text, Convert.ToInt32(aID.Text), aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text);
administrationTableAdapter.Update(aRole.Text, aPassword.Text, Convert.ToInt32(aAID.Text), aRole.Text, aPassword.Text);
personBindingSource.EndEdit();
administrationBindingSource.EndEdit();
administrationTableAdapter.Update(_30002195DataSet.Administration);
personTableAdapter.Update(_30002195DataSet.Person);
MessageBox.Show("Person Updated");
this.personTableAdapter.Fill(this._30002195DataSet.Person);
this.administrationTableAdapter.Fill(this._30002195DataSet.Administration);
Here I tried to do the new values/original values, while trying to use textboxes
personTableAdapter.Update(aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text, Convert.ToInt32(aID.Text), aFirstName.Text, aSurname.Text, Convert.ToDateTime(aDoB.Text), aPhone.Text, aAdd1.Text, aAdd2.Text, aSuburb.Text, aState.Text, aPostcode.Text, AdminType.Text);
administrationTableAdapter.Update(aRole.Text, aPassword.Text, Convert.ToInt32(aAID.Text), aRole.Text, aPassword.Text);
Trying to use the example through the mdsn, this is trying to use the datagridview.
this.Validate();
personBindingSource.EndEdit();
teacherBindingSource.EndEdit();
_30002195DataSet.PersonDataTable deletedPerson = (_30002195DataSet.PersonDataTable)
_30002195DataSet.Person.GetChanges(DataRowState.Deleted);
_30002195DataSet.PersonDataTable newPerson = (_30002195DataSet.PersonDataTable)
_30002195DataSet.Person.GetChanges(DataRowState.Added);
_30002195DataSet.PersonDataTable modifiedPerson = (_30002195DataSet.PersonDataTable)
_30002195DataSet.Person.GetChanges(DataRowState.Modified);
try
{
if (deletedPerson != null)
{
personTableAdapter.Update(deletedPerson);
}
teacherTableAdapter.Update(_30002195DataSet.Teacher);
if (newPerson != null)
{
personTableAdapter.Update(newPerson);
}
if (modifiedPerson != null)
{
personTableAdapter.Update(modifiedPerson);
}
_30002195DataSet.AcceptChanges();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (deletedPerson != null)
{
deletedPerson.Dispose();
}
if (newPerson != null)
{
newPerson.Dispose();
}
if (modifiedPerson != null)
{
modifiedPerson.Dispose();
}
MessageBox.Show("Updated");
this.personTableAdapter.Fill(this._30002195DataSet.Person);
this.teacherTableAdapter.Fill(this._30002195DataSet.Teacher);
}
Now because I am trying to edit a certain user which requires the Person table along with the other table, it just doesn't seem to work at all.
With the datagridview, I made a new view in the sql and it has both tables combined and shows when I bring it out from the datasource, but where I go into the dataset builder and try to create an update method, all I get is the "Update" not what I would get when I created the update method from just the person's table by itself.
Can someone provide me with an example or help me out someway because I am struggling with this, I can't seem to find much information at all.
Thanks.
|
|
|
|
|
I'm relatively new to .NET. Using VS 2013.
I am not sure what to call this or how to search it so I apologize if this is a duplicate entry.
I have a listbox (lstConditions) populated with quote conditions. Each being a string with a Int ConditionID associated with it.
There can be any number of conditions selected for each quote (from 1 to 15 items for example) thereby setting the checkbox as checked.
Currently when a quote is saved the selected items from the lstConditions listbox are saved to an SQL Database table with each item being a new row. So each quote saved can have up to 15 rows in the table per quote.
Is there a way to store the list of selected items as one string in one row per quote?
i.e.
QuoteID = 1
SelectedConditions = 1,2,3,5,6,9,12,15
If QuoteID 1 was modified, only 1 row would need changed.
Would it be difficult to repopulate the list of conditions the next time someone was to view QuoteID 1 again?
Hope this make sense.
Thanks in advance.
|
|
|
|
|
Don't.
Seriously, it's simple enough to do, but it makes processing the SQL data a total pain in the backside for anything more complex than minor information retrieval. For example, if you remove "condition 7" from the system completely, how are you going to remove all references to it from all the strings? Keeping it as separate rows makes that trivially easy.
Instead, if you want to display multiple conditions as a string then use SQL to return them as a comma separated list:
SELECT Id, Conditions=
STUFF((SELECT ',' + CONVERT(VarChar(10), Condition)
FROM myTable b
WHERE b.Id = a.Id
FOR XML PATH('')), 1, 1, '')
FROM myTable a
GROUP BY Id
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
It probably won't be a problem for this query, but in general this method would interpret the concatenated column as XML. This could lead to errors, or to characters being "entitized".
The fix is fairly simple:
SELECT Id, Conditions=
STUFF((SELECT ',' + CONVERT(varchar(10), Condition)
FROM myTable b
WHERE b.Id = a.Id
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
FROM myTable a
GROUP BY Id
Concatenating Row Values in Transact-SQL[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
from what I understand, when using session ended event, the action will be done if the computer shuts down or if the user logs off.
I have this event, but the action only occurs when the computer shuts down, but when the user logs off, nothing happens.
Here is the code I'm using:
SystemEvents.UserPreferenceChanging += new UserPreferenceChangingEventHandler(SessionEnded);
static void SessionEnded(object sender, UserPreferenceChangingEventArgs e)
{
}
Can some one please tell me how to make my app do something, when the user logs off?
Thanks.
|
|
|
|
|
Member 10850253 wrote: I have this event
You have the "UserPreferenceChanging" event.
Try SessionEnding[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I tried:
InitializeComponent();
SystemEvents.SessionEnding += (s, e) =>
{
switch(e.Reason){
case SessionEndReasons.Logoff:
ComPort.Close();
ComPort.Write(new byte[] { 48 }, 0, 1);
break;
case SessionEndReasons.SystemShutdown:
ComPort.Close();
ComPort.Write(new byte[] { 48 }, 0, 1);
break;
}
};
But still nothing works.
Please help me.
Thanks.
|
|
|
|
|
Member 10850253 wrote: ComPort.Close();
ComPort.Write(new byte[] { 48 }, 0, 1);
You're trying to write to the ComPort after you've closed it. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I also tried this:
SystemEvents.SessionEnding += new SessionEndingEventHandler(SystemEvents_SessionEnding);
void SystemEvents_SessionEnding(object sender, SessionEndingEventArgs e)
{
if (e.Reason == SessionEndReasons.Logoff)
{
\\Code
}
}
And I still can't get it working.
Can someone please help me?
Thanks.
|
|
|
|
|
Have you tried these suggestions[^]?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi All !
I using DataReader for Sum Value of Column :
double Sum = 0.00;
OleDbDataReader dr=null;
string strSum =@"Select SUM(Val1 + Val2 + Val3) as Total from myTable Where year="+Convert.int32(txtyear.Text);
try
{
cnn = new OleDbConnection(strcn);
cnn.Open();
OleDbCommand cmd = new OleDbCommand(strSum,cnn);
dr = cmd.ExecuteReader();
if (dr.Read())
{
int ordinal = dr.GetOrdinal("Total");
txtFasl_Two_Msvb.Text = dr.GetDouble(ordinal).ToString();
}
else
{
txtSum.Text = "0";
}
}
catch(Exception exp)
{
MessageBox.Show(exp.ToString());
}
Sum = Convert.ToDouble(txtSum.Text);
return Sum;
I want to put Zero for Sum if DataReader has no row or Value , but it throw to an exception .
How can handle it ?
Thanks
|
|
|
|
|
There is so much to say about these few lines of code.
First, this does not compile, due to the third line.
Basically you are:
- reading a double value from your database.
- make a string out of this value if found, and set the Text property of a TextBox from the result ; if not found, you put a "0" string in another TextBox.
- then you convert it back to a double from the TextBox that can only be "0".
Moreover, as your WHERE clause restricts the result to one line, you should be using ExecuteScalar instead of ExecuteReader.
When you want to work with numbers, then keep up with the numbers. The only moment when you have to make a string from your number is when you want to present it to the user. Every design moving back and forth from number to string is a terrible practice and should be avoided at any time.
Also, when dealing with Disposable objects (OleDbCOnnection and OleDbCommand), you should use a using block to make sure you release all resources when you do not need them anymore.
At the end of your method, you return a double. This function should only deal with computing the result. Displaying it in a TextBox should be done elsewhere.
A more appropriate implementation could be:
double result = 0;
string strSum = "SELECT SUM(Val1 + Val2 + Val3) AS Total FROM myTable WHERE year=@year";
int year;
if (!int.TryParse(txtyear.Text, out year)) {
throw new ArgumentException("Entered text is not convertible to a valid integer.");
}
using (OleDbConnection cnn = new OleDbConnection(strcn)) {
cnn.Open();
using (OleDbCommand cmd = new OleDbCommand(strSum, cnn)) {
cmd.Parameters.AddWithValue("@year", year);
try {
result = (double)cmd.ExecuteScalar();
}
catch (SqlException ex) {
MessageBox.Show(string.Format("{0}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace));
}
}
}
return result;
There are two kinds of people in the world: those who can extrapolate from incomplete data.
|
|
|
|
|
Hi All
I want to show a big Number in TextBox , so I define it to "double":
double Sum = 0.00;
for (int j = 0; j < 10; j++)
{
for (int i = 0; i < dgVal.RowCount; i++)
{
Sum += Convert.ToDouble(dgVal[j + 1, i].Value);
}
}
txtSum.Text = Sum.ToString();
but my output is : 1.00000000010833E+15 that's a scientific Number , plz Help me , How to show the "Sum" in form Like : 123456789012345678 about 18 digit.
thanks
|
|
|
|
|
Something like
Sum.ToString("F30").TrimEnd("0".ToCharArray());
|
|
|
|
|
|
You can't use a double to show 18 digits: it only shows 15 significant digits (though it uses 17 internally).
If you want 18 digit precision, then you need to use an Int64 (or long) value, which can hold up to 9,223,372,036,854,775,807 - 19 digits.
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
A bit less than 17 even, approximately 15.9545897702 (it's probably a bad idea to measure it in decimal digits, 0.954etc of a digit is confusing).
|
|
|
|
|
Hi,
I have inherited what I would call a badly designed database using SQL Server. At the moment the application that uses the database has an Access 97 front-end and with XP now being end of life, I have been tasked with creating a new application to replace Access 97.
I am looking quite a bit into the project architecture to future proof the application and also for best practices. I have been reading into using a 3-tier architecture - DAL, BLL and UI.
I originally started looking into using EF but it doesn't seem overly compatible with my database as there are quite a few tables that don't have PK's and we already have a lot of stored procedures that will be used rather than directly to the table. I have currently decided that I will use ADO.Net but can't help but feel that is a bad decision.
I really have a couple of questions here -
1 - What project architecture do people generally use these days and what is best practice, can you provide me with some examples?
2 - If I continue down the ADO.Net route do I create different classes for the different Stored Procedures that are used?
3 - Am I making the right decision using ADO.Net?
Any help/advice/links are greatly appreciated.
|
|
|
|
|
EF also supports sprocs[^].
JammoD87 wrote: 1 - What project architecture do people generally use these days and what is
best practice, can you provide me with some examples?
EF is the most mentioned out-of-the-box ORM's around here. LINQ is mainly something used on collections, and sometimes there's a mention of NHibernate. Most go for some custom implementation tough.
JammoD87 wrote: 2 - If I continue down the ADO.Net route do I create different classes for the
different Stored Procedures that are used?
That's possible, if you'd want to.
JammoD87 wrote: 3 - Am I making the right decision using ADO.Net?
Yes.
Well, EF makes a close second; I don't have hands-on experience with it, but heard good things about it. ADO has been there since .NET, and there's a lot of documentation on the subject. I'd say it is the path that has proven to work, with EF looking as a nice new alternative.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for that, there's some helpful advice fhere!
|
|
|
|
|
If you are already using ADO.net I wouldn't suggest you to go with EF. If you wish to use EF you can use it You can even use your existing stored procedures if you want but may have to do some changes. The decision with EF or ADO.net depends on other factors also like
1) if you want better performance and working on huge data then go with ADO.net.
2) Whatever you are comfortable with. If you have a very good expertise in ADO.net then go with ADO.net or if you have very good expertise in EF then go with EF.
Instead of a 3-tier architecture I would suggest you to try GUI Architectural patterns either MVVM or MVC depending on the type of the application.
If you want to use 3 tier architecture then there may be more layers required to have separation of concerns e.g:- for cross cutting concerns etc.
|
|
|
|
|
Hello. I have this small application in which I am using some open source dlls. Whenever I build my project, git notifies that I have new files ("git status" command in Git Bash). Now I tried to ignore these *.exe, *.dll, *.pdb and *.xml files in .gitignore but in vain (I don't know where to put it...so I placed mutiple copies of it, in different locations).
How do use I git properly so that it recognize only the *.cs files and not all the EXEs and other stuff. Thanks for any pointer.
This world is going to explode due to international politics, SOON.
|
|
|
|
|
|
I have one Form with panel added to it. I have separate user control which display the rectangle.Now I have to add this user control to the form panel.I added it, but it not showing the rectangle.
|
|
|
|
|
This does not make any sense.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
