none
C# updating database question RRS feed

  • Question

  • I am trying to make my first application using a database, and have really made some good progress (Mostly thanks to some good articles online, etc,).  I am able to connect to the Access 2007 Database, I am able to load the data into the dataGridView, or the bound textBoxes, whichever the case may be.  I am having a heck of a time though getting the code together to save a record to the database or update a record.  I'm really not sure how I might do this.  I read some interesting articles, and a book that went over using SQL statements to Update and Add records, and this sounded good, but in practice I was unable to get any of this to work. 

    I have a unique ID for each item in a table, and I guess to add / update I would first check to see if that number already exists in the database?  If it does it would update, if it does not it would add?  I'm really not sure how I might go about doing this.  I really feel like I was making good progress with this, then I hit this wall and no matter what I do I can't seem to get it working.  Adding / updating records in a database is pretty important too...  lol

    Any help is greatly appreciated.

    Thanks,
    Tuesday, August 28, 2007 6:41 PM

Answers

  • well you can either insert OR update a record - you cant do both. The record has to exist in order to update the record otherwise it would fail with an error.

    To update a record you need to create a SQL command like so:

     

    update [tablename]

    set field = @newvalue

    where [id] = idhere

     

    then add the appropriate parameters to the sqlcommand parameters collection and execute the query :-)

     

    Example:

     

    SqlCommand command = new SqlCommand("Update TableName SET description = @desc WHERE ID = @id", new SqlConnection(connectionString));

     

    SqlParameter descriptionParam = new SqlParameter("@desc", this.txtDescription.Text);

    SqlParameter idParam = new SqlParameter("@id", this.recordID);

     

    command.Parameters.Add(descriptionParam);

    command.Parameters.Add(idParam);

     

    try

    {

       command.Connection.Open();

       command.ExecuteNonQuery();

       command.Connection.Close();

    }

    catch (SqlException ex)

    {

       //handle error

    }

     

     

     

     

    Now if you were using SqlDataAdapters then you need to create the UpdateCommand, InsertCommand and DeleteCommand properties, then do an Update(DataSet) method, this will then automatically correctly execute the appropriate commands based on how your DataSet/DataTable was modified.

     

    http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter(VS.71).aspx

    http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand(VS.71).aspx

     

    does this help you?

     

    Tuesday, August 28, 2007 10:33 PM
  • It is possible to insert/update a record with one call.  The below example might be of help (since you are using Access):

     

     

    Code Snippet

    bool Execute(OleDbConnection connection,int id,string description) {

     

    OleDbCommand command;

    string query;

    bool success;

     

    query = @"

    IF EXISTS(SELECT ID FROM TABLENAME WHERE ID = @ID) BEGIN

    UPDATE TABLENAME SET DESC = @DESC WHERE ID = @ID

    END ELSE BEGIN

    INSERT INTO TABLENAME (ID,DESC) VALUES(@ID,@DESC)

    END

    ";

     

    using(command = new OleDbCommand(query,connection) {

    command.Parameters.Add("@ID",id);

    command.Parameters.Add("@DESC,description);

    success = command.ExecuteNonQuery() > 0;

    }

     

    return success;

     

    }

     

     

     

    Tuesday, August 28, 2007 11:00 PM
  • It is possible but not recommended as it has to perform 2 queries in the SQL itself, and can have perf issues depending on how big a database/table exists. - its best practice to know exactly how your data is being behaved in that you need to know if its to be inserted or to be updated.

     

    If you were using the SqlDataAdapter's then you needn't worry as it takes care of this finding out etc.. for you by looking at the DataTable's/DataSet's rows status/change status and will execute the appropriate command, if you have implemeted the InsertCommand, DeleteCommand and UpdateCommand correctly.

    Tuesday, August 28, 2007 11:19 PM
  • I agree that it is best to know whether data exists or not prior to execution, but it is not always the case.  Sometimes external data is not always as clean as desired.

     

    Also, correct me if I am wrong, but the row status will not indicate if the data already exists in the database, but only if the data is has been added, deleted, or modified inside of the DataTable.  If I add data to a DataTable that exists in the database already, but has not been pulled down by the DbDataAdapter, the RowState will be DataRowState.Added and the insert command will be called and subsequently fail.

    Tuesday, August 28, 2007 11:35 PM
  • That is correct in regards to the row status and comparison with the data already in the database :-)

    However it is strongly advised to pretty much "know" what may happen in terms of what will happen to the data and have scenarios in place. Whilst you can check to see if a specific record exists, you can do the same thing (ish) with the SqlDataAdapter.

     

    The Update() method returns an int which returns the rows affected by the Update(), if we delete a record in the database but we modify it in the dataset/datatable and we do an Update(), it should return 0, which then you need to perform the Insert statement after calling the SetAdded() method on that Row in the Dataset/Datatable - this then will execute the InsertCommand

     

    This is one way of doing it within C# without having to really know much about T-SQL :-)

     

    Good job Eric!

     

    Tuesday, August 28, 2007 11:51 PM
  • I'm in two minds on this.

     

    On the one hand I would never manually write this code; there are several options for auto-generating code that automatically handles all that stuff for you. These options include Typed DataSets, Linq-to-Sql and my own Foundation product.

     

    On the other hand I'm happy to use these methods (well obviously I use Foundation) because I already know how to do these things manually. I do worry that these skills are being lost, as it's so easy to have it all done for you. While using these products will get you results very quickly you're not learning the low-level stuff.

     

    So I guess my advice is to learn how to do it yourself, and then get something to do it for you. It's like learning to drive in a car with manual gears even if you're going to be buying an automatic 

     

    As mentioned you can query the database to determine if it should do an insert or an update, but it's not ideal. Are you populating your controls by binding them to objects containing the data? If not then you should. You can then give your objects a property called, perhaps, ObjectState. In each object's constructor you set this to Insert. In the Load method you set it to AsDB. In the property setters you set it to Update (but only if the new value doesn't equal the old value). This is enough for you to know what you need to do in order to save the object.

     

    This is basically what I do in Foundation, although it's a bit more sophisticated because it also handles deleting, undeleting, and it also recognises if a value has been changed back to its original value (and works out if the object still needs to be saved). But these are all extras that you don't necessarily need.

     

    As mentioned Typed DataSets, Linq-to-Sql and Foundation handle all of this for you, but it's good to know how to do it for yourself.

     

    Sean

     

     

     

     

     

    Wednesday, August 29, 2007 12:08 AM
  • your command is incorrect...lets fix it:

     

    string sSQLCommand = "INSERT INTO Table1 (FirstName, LastName, Company, Phone, Address1, Address2, City, State, Zip) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)";

     

    OleDbParameter p1 = new OleDbParameter("@p1", this.textBox1.Text);

    OleDbParameter p2 = new OleDbParameter("@p2", this.textBox2.Text);

    OleDbParameter p3 = new OleDbParameter("@p3", this.textBox3.Text);

    OleDbParameter p4 = new OleDbParameter("@p4", this.textBox4.Text);

    OleDbParameter p5 = new OleDbParameter("@p5", this.textBox5.Text);

    OleDbParameter p6 = new OleDbParameter("@p6", this.textBox6.Text);

    OleDbParameter p7 = new OleDbParameter("@p7", this.textBox7.Text);

    OleDbParameter p8 = new OleDbParameter("@p8", this.textBox8.Text);

    OleDbParameter p9 = new OleDbParameter("@p9", this.textBox9.Text);

     

    OleDbCommand cmdAdder = new OleDbCommand(sSQLCommand, new OleDbConnection(conString));

     

    cmdAdder.Parameters.Add(p1);

    cmdAdder.Parameters.Add(p2);

    cmdAdder.Parameters.Add(p3);

    cmdAdder.Parameters.Add(p4);

    cmdAdder.Parameters.Add(p5);

    cmdAdder.Parameters.Add(p6);

    cmdAdder.Parameters.Add(p7);

    cmdAdder.Parameters.Add(p8);

    cmdAdder.Parameters.Add(p9);

     

     

    //now execute your command

     

     

    you are better to use parameterized queries as it is securer than having a string concatinated command.

    Wednesday, August 29, 2007 2:29 PM
  • I would suggest that you try dummy values in the statement.  When you do not specify a data type for a DbParameter, the value is inferred.  Access is pretty loose with the data type constraints (all I can think of in this case might be a Text value greater than 255 characters), but some value appears to not be getting inferred correctly.  The snippet below shows the use of a transaction around an insert statement.  The transaction is not being committed so that you do not have duplicate id constraints limiting your testing.  Also, if some set of dummy values should insert correctly, but does not, run the query directly against the database.  You might get a clearer display of what is wrong.

     

    Code Snippet

    TransactionScope scope;

    OleDbConnection connection;

    OleDbCommand command;

    string insertQuery;

    int rowsAffected;

     

    using(scope = new TransactionScope()) {

     

    using(connection = new OleDbConnection(connectionString)) {

     

    insertQuery = "INSERT INTO Table1 (FirstName, LastName, Company, Phone, Address1, Address2, City, State, Zip) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)";

     

    connection.Open();

     

    using(command = new OleDbCommand(insertQuery,connection)) {

     

    command .Parameters.Add("@p1", this.textBox1.Text);

    command .Parameters.Add("@p2", this.textBox2.Text);

    command .Parameters.Add("@p3", this.textBox3.Text);

    command .Parameters.Add("@p4", this.textBox4.Text);

    command .Parameters.Add("@p5", this.textBox5.Text);

    command .Parameters.Add("@p6", this.textBox6.Text);

    command .Parameters.Add("@p7", this.textBox7.Text);

    command .Parameters.Add("@p8", this.textBox8.Text);

    command .Parameters.Add("@p9", this.textBox9.Text);

     

    rowsAffected = command.ExecuteNonQuery();

     

    }

     

    connection.Close();

     

    }

     

    // Do not commit the transaction. 

     

    }

     

     

     

    Wednesday, August 29, 2007 4:23 PM
  • ok what that means is that you are dealing with different data types (nchars, ints, floats etc...) and you need to specify the correct data type you are inserting into the field.

     

    So if a field on your database was of type int, then you need to specify in the parameters (OleDbParameter) that it will be an Integer/Number.

     

    Example:

    string sSQLCommand = "INSERT INTO Table1 (FirstName, LastName, Company, Phone, Address1, Address2, City, State, Zip) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)";

     

    OleDbParameter p1 = new OleDbParameter("@p1", OleDbType.VarChar);

    p1.Value = this.textBox1.Text;

     

    OleDbParameter p2 = new OleDbParameter("@p2", OleDbType.Integer);

    p2.Value = this.textBox2.Text;

     

    ..

    ..

     

    so for each column of data, you need to specify the data type for that column, so it can insert the data correctly.

     

    Does this help?

     

    Wednesday, August 29, 2007 4:26 PM

All replies

  • well you can either insert OR update a record - you cant do both. The record has to exist in order to update the record otherwise it would fail with an error.

    To update a record you need to create a SQL command like so:

     

    update [tablename]

    set field = @newvalue

    where [id] = idhere

     

    then add the appropriate parameters to the sqlcommand parameters collection and execute the query :-)

     

    Example:

     

    SqlCommand command = new SqlCommand("Update TableName SET description = @desc WHERE ID = @id", new SqlConnection(connectionString));

     

    SqlParameter descriptionParam = new SqlParameter("@desc", this.txtDescription.Text);

    SqlParameter idParam = new SqlParameter("@id", this.recordID);

     

    command.Parameters.Add(descriptionParam);

    command.Parameters.Add(idParam);

     

    try

    {

       command.Connection.Open();

       command.ExecuteNonQuery();

       command.Connection.Close();

    }

    catch (SqlException ex)

    {

       //handle error

    }

     

     

     

     

    Now if you were using SqlDataAdapters then you need to create the UpdateCommand, InsertCommand and DeleteCommand properties, then do an Update(DataSet) method, this will then automatically correctly execute the appropriate commands based on how your DataSet/DataTable was modified.

     

    http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter(VS.71).aspx

    http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand(VS.71).aspx

     

    does this help you?

     

    Tuesday, August 28, 2007 10:33 PM
  • It is possible to insert/update a record with one call.  The below example might be of help (since you are using Access):

     

     

    Code Snippet

    bool Execute(OleDbConnection connection,int id,string description) {

     

    OleDbCommand command;

    string query;

    bool success;

     

    query = @"

    IF EXISTS(SELECT ID FROM TABLENAME WHERE ID = @ID) BEGIN

    UPDATE TABLENAME SET DESC = @DESC WHERE ID = @ID

    END ELSE BEGIN

    INSERT INTO TABLENAME (ID,DESC) VALUES(@ID,@DESC)

    END

    ";

     

    using(command = new OleDbCommand(query,connection) {

    command.Parameters.Add("@ID",id);

    command.Parameters.Add("@DESC,description);

    success = command.ExecuteNonQuery() > 0;

    }

     

    return success;

     

    }

     

     

     

    Tuesday, August 28, 2007 11:00 PM
  • It is possible but not recommended as it has to perform 2 queries in the SQL itself, and can have perf issues depending on how big a database/table exists. - its best practice to know exactly how your data is being behaved in that you need to know if its to be inserted or to be updated.

     

    If you were using the SqlDataAdapter's then you needn't worry as it takes care of this finding out etc.. for you by looking at the DataTable's/DataSet's rows status/change status and will execute the appropriate command, if you have implemeted the InsertCommand, DeleteCommand and UpdateCommand correctly.

    Tuesday, August 28, 2007 11:19 PM
  • I agree that it is best to know whether data exists or not prior to execution, but it is not always the case.  Sometimes external data is not always as clean as desired.

     

    Also, correct me if I am wrong, but the row status will not indicate if the data already exists in the database, but only if the data is has been added, deleted, or modified inside of the DataTable.  If I add data to a DataTable that exists in the database already, but has not been pulled down by the DbDataAdapter, the RowState will be DataRowState.Added and the insert command will be called and subsequently fail.

    Tuesday, August 28, 2007 11:35 PM
  • That is correct in regards to the row status and comparison with the data already in the database :-)

    However it is strongly advised to pretty much "know" what may happen in terms of what will happen to the data and have scenarios in place. Whilst you can check to see if a specific record exists, you can do the same thing (ish) with the SqlDataAdapter.

     

    The Update() method returns an int which returns the rows affected by the Update(), if we delete a record in the database but we modify it in the dataset/datatable and we do an Update(), it should return 0, which then you need to perform the Insert statement after calling the SetAdded() method on that Row in the Dataset/Datatable - this then will execute the InsertCommand

     

    This is one way of doing it within C# without having to really know much about T-SQL :-)

     

    Good job Eric!

     

    Tuesday, August 28, 2007 11:51 PM
  • I'm in two minds on this.

     

    On the one hand I would never manually write this code; there are several options for auto-generating code that automatically handles all that stuff for you. These options include Typed DataSets, Linq-to-Sql and my own Foundation product.

     

    On the other hand I'm happy to use these methods (well obviously I use Foundation) because I already know how to do these things manually. I do worry that these skills are being lost, as it's so easy to have it all done for you. While using these products will get you results very quickly you're not learning the low-level stuff.

     

    So I guess my advice is to learn how to do it yourself, and then get something to do it for you. It's like learning to drive in a car with manual gears even if you're going to be buying an automatic 

     

    As mentioned you can query the database to determine if it should do an insert or an update, but it's not ideal. Are you populating your controls by binding them to objects containing the data? If not then you should. You can then give your objects a property called, perhaps, ObjectState. In each object's constructor you set this to Insert. In the Load method you set it to AsDB. In the property setters you set it to Update (but only if the new value doesn't equal the old value). This is enough for you to know what you need to do in order to save the object.

     

    This is basically what I do in Foundation, although it's a bit more sophisticated because it also handles deleting, undeleting, and it also recognises if a value has been changed back to its original value (and works out if the object still needs to be saved). But these are all extras that you don't necessarily need.

     

    As mentioned Typed DataSets, Linq-to-Sql and Foundation handle all of this for you, but it's good to know how to do it for yourself.

     

    Sean

     

     

     

     

     

    Wednesday, August 29, 2007 12:08 AM
  • Thank you all very much for your very informative posts.  It was a lot to take in.  This is my first attempt at working with data from a database, and I have found it both very interesting, and challenging.  I meant to post this yesterday, but forgot, and when I tried to get back on to the forums couldn't for some reason.  Anyway, here is the code that I was trying to use to insert data.  I hadn't worked out updating yet, and after everyones responses think I will handle that seperatesly (And already have an idea as to how to distinguish between the two).

    I don't get any build errors from this code, but when I click on the "Save" button, I get the following error:

    OleDBException was unhandled
    No value given for one or more required parameters.

    I'm not entirely sure what this error means.  None of the fields are required, with the exception of the ID field, and I thought that Access automatically generated that when an item was added.  Is that not the case?

    Here is the code (C#):

    private void buttonX1_Click(object sender, EventArgs e)
                {
                    //Create Record
                    //Connect to Database
                    string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"\MYDatabase.accdb;Persist Security Info=False;";

                    String sSQLCommand = "INSERT INTO Table1 (FirstName, LastName, Company, Phone, Address1, Address2, City, State, Zip) " +
                        "VALUES (textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, textBox5.Text, textBox6.Text, textBox7.Text, textBox8.Text, textBox9.Text);";

                    // Create the command object
                    OleDbCommand cmdAdder = new OleDbCommand(sSQLCommand);
                    cmdAdder.Connection = new OleDbConnection(conString);
                    cmdAdder.Connection.Open();

                    // Execute the SQL command
                    int nNoAdded = cmdAdder.ExecuteNonQuery();
                    System.Console.WriteLine("\nRow(s) Added = " + nNoAdded + "\n");
                }

    I get the error in the // Execute the SQL command section.


    I imagine updating would be handled similarly, but I haven't yet started on that.  I figured I would get adding items working first.  There seems to be so many different ways of doing this that I have become a bit confused.

    Thanks Again,
    Wednesday, August 29, 2007 2:24 PM
  • your command is incorrect...lets fix it:

     

    string sSQLCommand = "INSERT INTO Table1 (FirstName, LastName, Company, Phone, Address1, Address2, City, State, Zip) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)";

     

    OleDbParameter p1 = new OleDbParameter("@p1", this.textBox1.Text);

    OleDbParameter p2 = new OleDbParameter("@p2", this.textBox2.Text);

    OleDbParameter p3 = new OleDbParameter("@p3", this.textBox3.Text);

    OleDbParameter p4 = new OleDbParameter("@p4", this.textBox4.Text);

    OleDbParameter p5 = new OleDbParameter("@p5", this.textBox5.Text);

    OleDbParameter p6 = new OleDbParameter("@p6", this.textBox6.Text);

    OleDbParameter p7 = new OleDbParameter("@p7", this.textBox7.Text);

    OleDbParameter p8 = new OleDbParameter("@p8", this.textBox8.Text);

    OleDbParameter p9 = new OleDbParameter("@p9", this.textBox9.Text);

     

    OleDbCommand cmdAdder = new OleDbCommand(sSQLCommand, new OleDbConnection(conString));

     

    cmdAdder.Parameters.Add(p1);

    cmdAdder.Parameters.Add(p2);

    cmdAdder.Parameters.Add(p3);

    cmdAdder.Parameters.Add(p4);

    cmdAdder.Parameters.Add(p5);

    cmdAdder.Parameters.Add(p6);

    cmdAdder.Parameters.Add(p7);

    cmdAdder.Parameters.Add(p8);

    cmdAdder.Parameters.Add(p9);

     

     

    //now execute your command

     

     

    you are better to use parameterized queries as it is securer than having a string concatinated command.

    Wednesday, August 29, 2007 2:29 PM
  • Wow, I really wasn't close, was I?...  I worked that out from examples, articles, and how to's that I had found online (All the books I found were about using SQL Server).  I really did try to get that right...  I made the changes, and now I am getting an error, which I have gotten before, but in this case I don't know what it would even apply to... 

    This is the line it's throwing the error on:

    int nNoAdded = cmdAdder.ExecuteNonQuery();

    It says:

    OleDbException was Unhandled
    Data type mismatch in criteria expression.

    Now, in the past, I have gotten this particular error if I have numbers surrounded by single quotes with reference to the database.  In this case though, everything is being pulled from the textBoxes, and so none of them have or (As far as I know) need quotes. 

    I tried to change the code as such (As I thought this might be the issue, but apparently not):

    char nNoAdded = cmdAdder.ExecuteNonQuery();

    I don't really understand why it would be throwing this error.

    I tried VS Help, and that wasn't very helpful either (As appears to be quite common).  I hope that in VS2008 the Help System is much more specific.  People like me need it... 



    Wednesday, August 29, 2007 3:50 PM
  • I would suggest that you try dummy values in the statement.  When you do not specify a data type for a DbParameter, the value is inferred.  Access is pretty loose with the data type constraints (all I can think of in this case might be a Text value greater than 255 characters), but some value appears to not be getting inferred correctly.  The snippet below shows the use of a transaction around an insert statement.  The transaction is not being committed so that you do not have duplicate id constraints limiting your testing.  Also, if some set of dummy values should insert correctly, but does not, run the query directly against the database.  You might get a clearer display of what is wrong.

     

    Code Snippet

    TransactionScope scope;

    OleDbConnection connection;

    OleDbCommand command;

    string insertQuery;

    int rowsAffected;

     

    using(scope = new TransactionScope()) {

     

    using(connection = new OleDbConnection(connectionString)) {

     

    insertQuery = "INSERT INTO Table1 (FirstName, LastName, Company, Phone, Address1, Address2, City, State, Zip) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)";

     

    connection.Open();

     

    using(command = new OleDbCommand(insertQuery,connection)) {

     

    command .Parameters.Add("@p1", this.textBox1.Text);

    command .Parameters.Add("@p2", this.textBox2.Text);

    command .Parameters.Add("@p3", this.textBox3.Text);

    command .Parameters.Add("@p4", this.textBox4.Text);

    command .Parameters.Add("@p5", this.textBox5.Text);

    command .Parameters.Add("@p6", this.textBox6.Text);

    command .Parameters.Add("@p7", this.textBox7.Text);

    command .Parameters.Add("@p8", this.textBox8.Text);

    command .Parameters.Add("@p9", this.textBox9.Text);

     

    rowsAffected = command.ExecuteNonQuery();

     

    }

     

    connection.Close();

     

    }

     

    // Do not commit the transaction. 

     

    }

     

     

     

    Wednesday, August 29, 2007 4:23 PM
  • ok what that means is that you are dealing with different data types (nchars, ints, floats etc...) and you need to specify the correct data type you are inserting into the field.

     

    So if a field on your database was of type int, then you need to specify in the parameters (OleDbParameter) that it will be an Integer/Number.

     

    Example:

    string sSQLCommand = "INSERT INTO Table1 (FirstName, LastName, Company, Phone, Address1, Address2, City, State, Zip) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)";

     

    OleDbParameter p1 = new OleDbParameter("@p1", OleDbType.VarChar);

    p1.Value = this.textBox1.Text;

     

    OleDbParameter p2 = new OleDbParameter("@p2", OleDbType.Integer);

    p2.Value = this.textBox2.Text;

     

    ..

    ..

     

    so for each column of data, you need to specify the data type for that column, so it can insert the data correctly.

     

    Does this help?

     

    Wednesday, August 29, 2007 4:26 PM
  • He is working with Access 2007, so his data types are the following:

    • AutoNumber
    • Text (255 character limit)
    • Memo (2 GB limit)
    • Number (16 byte limit)
    • Date/Time
    • Currency
    • Yes/No
    • OLE Object
    • Hyperlink (1 GB limit)
    • Attachment

    The MLyons, can you post the schema for the table that you are inserting into?

    Wednesday, August 29, 2007 4:53 PM
  • Thank you all for all of your help, it's working now to insert records. 

    ahmedilyas
    Thank you in particular, I am still quite confused about using a database, but I feel like I'm "kinda" beginning to understand some aspects of it. 

    ericrtodd
    The VarChar did work, so I guess that must be an Access 2007 data type too?  I don't know.  It worked beautifully though. 

    I would post the "schema", but I don't know what that is...  Is that like a SQL Select Query?


    Thanks Again for all of the help.
    Wednesday, August 29, 2007 5:51 PM
  • Glad we could all help :-)

    Indeed, its a tricky thing to grasp when you don't know but when you do know - your flying high :-)

     

    Keep asking questions, its the only way to learn

    Wednesday, August 29, 2007 6:04 PM
  • Thank you very much.

    I do have one more question, as I can't find an example updating the database in this way, I thought I could just change the sSQLCommand to and UPDATE call, but it doesn't do anything.  I don't get any build errors, but it freezes up the UI when I try it.  This is what I tried.  I wasn't sure if the WHERE Clause should go before or after the text in the "( )", so I tried both and both times experienced the same issue.  Can I not update this way, or is there just a quirk that I am unaware of?

    Here is the code I tried:

    string sSQLCommand = "UPDATE Table1 (FirstName, LastName, Company, Phone, Address1, Address2, City, State, Zip) WHERE ID = " textBox10.Text + "VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)";

     

    OleDbParameter p1 = new OleDbParameter("@p1", this.textBox1.Text);

    OleDbParameter p2 = new OleDbParameter("@p2", this.textBox2.Text);

    OleDbParameter p3 = new OleDbParameter("@p3", this.textBox3.Text);

    OleDbParameter p4 = new OleDbParameter("@p4", this.textBox4.Text);

    OleDbParameter p5 = new OleDbParameter("@p5", this.textBox5.Text);

    OleDbParameter p6 = new OleDbParameter("@p6", this.textBox6.Text);

    OleDbParameter p7 = new OleDbParameter("@p7", this.textBox7.Text);

    OleDbParameter p8 = new OleDbParameter("@p8", this.textBox8.Text);

    OleDbParameter p9 = new OleDbParameter("@p9", this.textBox9.Text);

     

    OleDbCommand cmdAdder = new OleDbCommand(sSQLCommand, new OleDbConnection(conString));

     

    cmdAdder.Parameters.Add(p1);

    cmdAdder.Parameters.Add(p2);

    cmdAdder.Parameters.Add(p3);

    cmdAdder.Parameters.Add(p4);

    cmdAdder.Parameters.Add(p5);

    cmdAdder.Parameters.Add(p6);

    cmdAdder.Parameters.Add(p7);

    cmdAdder.Parameters.Add(p8);

    cmdAdder.Parameters.Add(p9);


    // Execute the SQL command
    int nNoAdded = cmdAdder.ExecuteNonQuery();
    //char nNoAdded = cmdAdder.ExecuteNonQuery();
    System.Console.WriteLine("\nRow(s) Added = " + nNoAdded + "\n");

    MessageBoxEx.Show("Record Updated Successfully!", "Confirmation",
       MessageBoxButtons.OK);


    I'm sorry if this is a stupid question, but I couldn't find any examples using Parameters, and most of the examples just had "Update()", which I don't understand how that little bit of code could update the database...

    I also checked if there was an Update for this line:  cmdAdder.Parameters.Update(p1), but there was not...

    Thanks Again,
    Thursday, August 30, 2007 7:00 PM
  •  

    The WHERE clause should be like this:

     

    UPDATE [TableName]

    SET [FieldName] = @p1,

    SET [FieldName2] = @p2

    WHERE [ID] = @p3

     

     

    Thursday, August 30, 2007 7:03 PM
  • I'm sorry, but I don't quite follow.  It should look like this?:

    String sSQLCommand =
    "UPDATE[table1]
    SET[FirstName] = @p1,
    SET[LastName] = @p2,
    SET[Company] = @p3,

    etc, etc.

    WHERE[ID] = @p0"

    OleDbParameter p1 = new OleDbParameter("@p1", this.textBox1.Text);

    OleDbParameter p2 = new OleDbParameter("@p2", this.textBox2.Text);

    OleDbParameter p3 = new OleDbParameter("@p3", this.textBox3.Text);


    etc, etc.


    OleDbParameter p0 = new OleDbParameter("@p0", this.textBox10.Text);


    OleDbCommand cmdAdder = new OleDbCommand(sSQLCommand, new OleDbConnection(conString));


    cmdAdder.Parameters.Add(p1);

    cmdAdder.Parameters.Add(p2);

    cmdAdder.Parameters.Add(p3);


    etc, etc.


    // Execute the SQL command
    int nNoAdded = cmdAdder.ExecuteNonQuery();
    //char nNoAdded = cmdAdder.ExecuteNonQuery();
    System.Console.WriteLine("\nRow(s) Added = " + nNoAdded + "\n");


    I tried this and the UI froze again.

    I'm sorry if I'm just not getting this...

    Thanks Again,
    Thursday, August 30, 2007 7:43 PM
  • Wouldn't understand why the UI would freeze. have you left it to unfreeze, perhaps its trying to throw the exception or something - which line does it freeze at? And yes, the code you have is correct

     

    Thursday, August 30, 2007 8:20 PM
  • O.K., I waited like 10 minutes and it doesn't unfreeze.  VS does not like the following code unless it is all surrounded in ""
    String sSQLCommand =
    UPDATE[table1]
    SET[FirstName] = @p1,
    SET[LastName] = @p2,
    SET[Company] = @p3,

    etc, etc.

    WHERE[ID] = @p0

    So, I added quotes like so:
    String sSQLCommand =
    "UPDATE[table1]" +
    "SET[FirstName] = @p1," +
    "SET[LastName] = @p2," +
    "SET[Company] = @p3," +

    etc, etc.

    "WHERE[ID] = @p0"

    I don't know what line it's freezing on.  How do I find that out?  It isn't actually throwing an error, it just fails to update the database and freezes.

    I hope that helps a bit.  I don't understand why it wouldn't work if the code is right...

    Thanks Again,

    Thursday, August 30, 2007 8:55 PM
  • bizzare. You should write the code like this, obviously it does seem to compile so its a problem when you are running the application.

     

    string sSQLCommand = "Update [Table1] SET [FirstName] = @p1, SET [LastName] = @p2, SET [Company] = @p3 WHERE [ID] = @p0";

     

    Could it be a firewall issue when connecting to the database?

    Does it connect initially when you select your records?

    Did you close the connection after connecting initially? Be sure you do close connections after you are finished using them.

     

    To find out roughly where it is freezing, place a breakpoint on the sql command above and step through the debugger line by line until it freezes (press F10/F11 on each line to execute that line and move to the next)

    Thursday, August 30, 2007 9:14 PM
  • Hm, This is very strange.  I rewrote the code all on one line, and the same thing happens.  I made sure that I wasn't missing something stupid, like a comma or something (This is a common mistake of mine), and everything is there.  Originally I was ending the string with ;";, so I changed it per your example to ";, but the application still freezes. 

    It actually freezes the second I click the button...  lol  The form loses focus, then the UI freezes.  I end up having to end it through VS.

    I also checked your suggestions:

    I can connect to the database to read the data and insert a new record, would update cause a problem?

    It connects for the main page, it connects for the second page (The one I am working on now), it connects to insert records...  I'm assuming it is connecting here.

    I do close the connection, using the following code for each connection (I just double checked that I wasn't missing one):

                // close the connection
                conn.Close();

    I am not closing the connection when inserting, I'm assuming I probably should.  I am not inserting a record before performing this update though, so that shouldn't be an issue.

    I greatly appreciate all of your help.  I just don't understand the issue here...

    Thanks Again,
    Thursday, August 30, 2007 9:34 PM
  • I don't either to be honest but ALWAYS close the connection when you have finished executing your query/non query functions, be sure you do close it because that could be one factor on why it freezes.

     

    Thursday, August 30, 2007 9:40 PM
  • Hm,  How frustrating is this?  lol...

    I don't understand why I'm not getting any errors...  Shouldn't I at least get an OleDbError?

    I did make one oops in the code I posted above...

    I'm sorry about the error.

    Instead of this code:

    OleDbParameter p1 = new OleDbParameter("@p1", this.textBox1.Text);

    OleDbParameter p2 = new OleDbParameter("@p2", this.textBox2.Text);

    OleDbParameter p3 = new OleDbParameter("@p3", this.textBox3.Text);


    I have this code (Which is the result of a change you had suggested for the Insert Statement):


    OleDbParameter P1 = new OleDbParameter("@P1", OleDbType.VarChar);
    P1.Value = this.textBox1.Text;
    OleDbParameter P2 = new OleDbParameter("@P2", OleDbType.VarChar);
    P2.Value = this.textBox2.Text;
    OleDbParameter P3 = new OleDbParameter("@P3", OleDbType.VarChar);
    P3.Value = this.textBox3.Text;

    I tried closing the connection for the Insert Command with Conn.Close(); but it doesn't seem to work for the way the Insert Statement connects to the database.  What exactly should I close, the NonQuery?

    Thanks Again, I hope I can get this working...  It doesn't make any sense to me.
    Thursday, August 30, 2007 10:50 PM
  • Yes, ExecuteNonQuery is what you should be doing, no idea why it freezes.

    Send over the entire project and the database in an email and i will be sure to post back the solution (email in profile, click my name)

     

    Thursday, August 30, 2007 11:05 PM
  • O.K., I can do that.  There's one thing I wanted to mention first, as it might be helpful. 

    I did set up some breakpoints this evening, and it appears to be freezes / stopping at this line:

    System.Console.WriteLine("\nRow(s) Added = " + nNoAdded + "\n");

    Should this line be revised to Updated or something?  Perhaps it's trying to update the database, but because of the Added call it's choking when it finds data already in the fields being updated...?

    Thanks Again,

    Friday, August 31, 2007 1:19 AM
  • Wouldn't understand why it would hang on that line to be honest, but comment it out and see what happens. (still waiting for the email)

     

    Friday, August 31, 2007 1:24 AM
  • I have been playing around with this some more, and wanted to post an update.  It still isn't working, but I have set some breakpoints and such and came to the following:

    1) The reason the form is freezing is actually when the catch fires to generate the MessageBox.  This will require some more investigation, as I do not know why a MessageBox would cause it to freeze.

    2) I set a lot of breakpoints, and this is the line that the program breaks on.:

    Code Snippet

    System.Console.WriteLine("\nRow(s) Added = " + nNoAdded + "\n");


    * Is the use of the word "Added" causing the issue?  Is there a seperate instruction for updating?  I looked around and didn't see anything.  I don't fully understand this line of code, other than that it is the line of code that actually writes the data to the database.  I do not get any errors, which I do not understand.

    3) When I comment out the line of code above, it doesn't update the database (No surprise), but it also still never fires the confirmation MessageBox.  I don't understand this either.

    Any help is greatly appreciated.

    Thanks Again,
    Tuesday, September 4, 2007 4:03 PM
  • im hoping your not updating or interacting through different threads.

     

    Wouldn't understand why the Message Box or Console would cause an issue - try commenting it out and see what happens.

     

    The correct way really to use the Console.WriteLine is as follows:

     

    Console.WriteLine("\nRow(s) added = {0}", NoAdded");

     

    Just makes it easier to read

     

    Tuesday, September 4, 2007 4:09 PM
  • Thank you very much for your response.  To answer your questions:

    I am not using multiple threads. 

    I commented both out and nothing happens.

    I changed the Console.WriteLine code to:

    Console.WriteLine("\nRow(s) added = {0}", nNoAdded);

    * VS didn't like the last ", and didn't recognize NoAdded, as I had called it nNoAdded.

    After making this change though, I experience the same issue.  It just doesn't appear to do anything...  lol

    I just don't get this.  It looks like the examples I have seen, and I have nothing else funny going on.  When I click the Save Btn, all it does is check to see if it is adding or updating, then it adds or updates.  Adding a record works fine, and it does fire the updating code when necessary, it just doesn't actually update the database.

    Any help is greatly appreciated.

    Thanks Again,

    Tuesday, September 4, 2007 4:45 PM