Database is not being Updated - Only the Datatable is

[lahiruagar]

Hi guys!
I'm new to C Sharp and the forums. I have this problem where I have to build a project for an agriculture company. I am using datatables to populate the data. The data is read from the database just fine, but the problem arises when I try to save the data(update the table). However the datatable gets updated, but the database doesn't. I am using an object oriented approach, which is working just fine - trust me For coming up with this code, I used the Sams "Teach yourself" series. I need help asap, so any suggestions are appreciated. Thanks in advance...

The code of the addUser form is;

public partial class Page2 : Page
{
SqlConnection editPDConn = new SqlConnection();
SqlDataAdapter editPDAdapter;
SqlCommandBuilder editPDCB;
DataTable editPDDT = new DataTable();
int counter, anyErrors;
string line, Id;
editPD editThisUser;

public Page2()
{
InitializeComponent();
editPDConn.ConnectionString =
@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AgTrainDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
editPDConn.Open();
readUserFromFile();
getInfo();
displayInfo();
}

private void Page_Loaded(object sender, RoutedEventArgs e)
{
}

private void reset_Click(object sender, RoutedEventArgs e)
{
ed_firstName.Text = "";
ed_lastName.Text = "";
ed_address.Text = "";
ed_teleNumber.Text = "";
}

private void displayInfo()
{
ed_userName.Text = editThisUser.UserNameED.ToString();
ed_firstName.Text = editThisUser.FirstNameED;
ed_lastName.Text = editThisUser.LastNameED;
ed_address.Text = editThisUser.AddressED;
ed_teleNumber.Text = editThisUser.TeleED.ToString();
ed_userRole.Text = editThisUser.RoleED;
}

//With the use of the retreived ID - the info is aquired here
private void getInfo()
{
editThisUser = new editPD();
editPDAdapter =
new SqlDataAdapter("SELECT * FROM users WHERE userId = '" + Id + "'", editPDConn);
editPDCB =
new SqlCommandBuilder(editPDAdapter);
editPDAdapter.Fill(editPDDT);

SqlCommandBuilder Builder = new SqlCommandBuilder(editPDAdapter);

if (editPDDT.Rows.Count > 0)
{
editThisUser.UserIdED = (int)editPDDT.Rows[0]["userId"];
editThisUser.UserNameED = editPDDT.Rows[0]["userName"].ToString();
editThisUser.PasswordED = editPDDT.Rows[0]["userPassword"].ToString();
editThisUser.FirstNameED = editPDDT.Rows[0]["userFirstName"].ToString();
editThisUser.LastNameED = editPDDT.Rows[0]["userLastName"].ToString();
editThisUser.AddressED = editPDDT.Rows[0]["userAddress"].ToString();
editThisUser.TeleED = (int)editPDDT.Rows[0]["userTele"];
editThisUser.RoleED = editPDDT.Rows[0]["userRole"].ToString();
}
}

//Get the user ID from the text file so that his/her info can be displayed
private void readUserFromFile()
{
counter = 0;
//Id = 0;
StreamReader fromFile = new StreamReader("E:\\loginInfo.txt");
while ((line = fromFile.ReadLine()) != null)
{
if (counter == 2) //ID is in this line
{
Id = line;

}
counter++;
}

fromFile.Close();

}

private void save_Click(object sender, RoutedEventArgs e)
{
int a;
anyErrors = 0;

if (ed_firstName.Text == "")
{
MessageBox.Show("Error! Please enter Your First Name!");
anyErrors = 1;
}
else if (ed_lastName.Text == "")
{
MessageBox.Show("Error! Please enter Your Last Name!");
anyErrors = 1;
}
else if (ed_address.Text == "")
{
MessageBox.Show("Error! Please enter Your Address!");
anyErrors = 1;
}
else if (ed_teleNumber.Text == "")
{
MessageBox.Show("Error! Please enter Your Tele!");
anyErrors = 1;
}
else if (ed_teleNumber.Text != "")
{
try
{
a = int.Parse(ed_teleNumber.Text);
}
catch (Exception ex)
{
MessageBox.Show("Error! Please enter a numeric Tele!");
anyErrors = 1;
}
}

//Set new values to the object
if (anyErrors != 1)
{
editThisUser.FirstNameED = ed_firstName.Text.ToString();
editThisUser.LastNameED = ed_lastName.Text.ToString();
editThisUser.AddressED = ed_address.Text.ToString();
editThisUser.TeleED = int.Parse(ed_teleNumber.Text);
saveDetails();
}
}

//Saves the details by using the object
private void saveDetails()
{
//editPDDT.Rows[0]["userId"] = editThisUser.UserIdED;
//editPDDT.Rows[0]["userName"] = editThisUser.UserNameED;
//editPDDT.Rows[0]["userPassword"] = editThisUser.PasswordED;
editPDDT.Rows[0]["userFirstName"] = editThisUser.FirstNameED;
editPDDT.Rows[0]["userLastName"] = editThisUser.LastNameED;
editPDDT.Rows[0]["userAddress"] = editThisUser.AddressED;
editPDDT.Rows[0]["userTele"] = editThisUser.TeleED;
//editPDDT.Rows[0]["userRole"] = editThisUser.RoleED;
//editPDAdapter.Update(editPDDT);
this.editPDAdapter.Update(this.editPDDT); //This is where the data should get updated

editPDConn.Close();
editPDConn.Dispose();
}

}
}

[Arjay]

Everytime you run your program, the database is copied over into the output directory and overwritten.

[lahiruagar]

Everytime you run your program, the database is copied over into the output directory and overwritten.

Finally a reply! Well Arjay, is there a way around it?

[Arjay]

Finally a reply! Well Arjay, is there a way around it?

Sure, while developing change the AttachDbFilename path to point to the db in the parent directory.

Another way is leave the AttachDbFilename path as it is and to change the mdb file copy setting to 'Never copy'. If you make a change to the db schema, you'll need to manually copy it.

Pretty obvious stuff once someone mentions it, but it's puzzling at first.

[lahiruagar]

Sure, while developing change the AttachDbFilename path to point to the db in the parent directory.

How should the connection string look like then?

Another way is leave the AttachDbFilename path as it is and to change the mdb file copy setting to 'Never copy'. If you make a change to the db schema, you'll need to manually copy it.

And again, how should I go about doing this?

Appreciate your input Arjay

[Talikag]

How should the connection string look like then?

Assuming the current connection string looks like that:

...;Data Source=db.mdb;...

Simply change it to:

...;Data Source=..\db.mdb;...

[lahiruagar]

Assuming the current connection string looks like that:

...;Data Source=db.mdb;...

Simply change it to:

...;Data Source=..\db.mdb;...

The connection string is as follows;
editPDConn.ConnectionString =
@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AgTrainDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

But the thing is I use the "AttachDbFilename" as you can see, so I cannot change the connection string as you suggested...

Anywayz, how can I change the mdf file copy setting to 'Never copy' as Arjay said?

[Talikag]

add your db to the project (as an item), and pick it in the solution explorer. in the properties window you'll see "Copy Settings" or something like that. default is "Always Copy", change it to "Never Copy".

[lahiruagar]

add your db to the project (as an item), and pick it in the solution explorer. in the properties window you'll see "Copy Settings" or something like that. default is "Always Copy", change it to "Never Copy".

I get an sqlexception when I do that. The error is;

"An attempt to attach an auto-named database for file C:\Users\AgAr\Documents\Visual Studio 2008\Projects\Ag_Train_App\Ag_Train_App\bin\Debug\AgTrainDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

This error occurs on the conn.open() statement...

[lahiruagar]

I renamed the connection string to:
conn.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\AgAr\Documents\Visual Studio 2008\Projects\Ag_Train_App\Ag_Train_App\AgTrainDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

and also changed the "Copy to output directory" to "Do not copy". But still the data is not updated in the database. But the thing is this; I have a login page. When the user logs in a message is shown on successful login, saying "Welcome" + FirstName. When I login then go and edit the info, and change the firstname, then close the application, and login again, the name on the message box(FirstName) has changed successfully, but still, no changes to the database.

[Talikag]

If you change it to "Do not copy" you have to update the database manually once you want to save the changes...

[lahiruagar]

If you change it to "Do not copy" you have to update the database manually once you want to save the changes...

If that's the case, it's useless using ADO.NET for database operations right? Please check my code and see if that's really necessary. Appreciate the feedback

[Talikag]

Either change the "Copy to output directory" propertie to "Do not copy" and copy the changes to the database manually, or change the AttachDbFilename to the db in the parent directory.
but don't do both...

[lahiruagar]

Either change the "Copy to output directory" propertie to "Do not copy" and copy the changes to the database manually, or change the AttachDbFilename to the db in the parent directory.
but don't do both...

I tried both ways, but still nothing.

[sotoasty]

Where are you looking for your changes? Are you looking through your app to see the changes? Are looking through Sever Management?

If through server management, try changing the location to the NEW file in the .bin folder.