Using a Database Help...

MLyons10

I am trying to create my first application using a database, and have already gotten a tremendous amount of help from the members of this forum. I was given several suggestions recently to try to get this working, and just finished playing around with it and trying again to get it working with no luck, so I wanted to post to see if anyone else had any ideas... This is really frustrating...

I am trying to update my database. I AM able to insert items in my database, but for whatever reason updating will not work.

The suggestions I received recently were to try running this on another machine (Which did not work), and turning off all virus and spyware protection applications and trying to run the application (No luck). I would really like to get this working, and if I install it on another machine, I want to make sure it will be able to run on that as well... Any help is greatly appreciated.

When I create a new record (Insert), a Microsoft Access Record Locking Information file is created in the program directory, which is approximately 1kb. When I try to edit a record (Update), this file is also created, but is only 0kb... I don't understand the problem...

Here is my update code:

Any help is GREATLY appreciated. Thanks Again,

if (OptionsTextBox.Text == "Edit")
{
try
{
//Update Record
//Connect to Database
string uconString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"\DB.accdb;Persist Security Info=False;";

String uSQLCommand = "UPDATE[Table1] SET[Company] = @P1, [LastName] = @P2, [FirstName] = @P3, [Address1] = @P4, [Address2] = @P5, [City] = @P6, [State] = @P7, [Zip] = @P8, [Web] = @P9, [Phone1] = @P10, [Phone2] = @P11, [Phone3] = @P12, [Fax] = @P13, [EMail1] = @P14, [EMail2] = @P15, [EMail3] = @P16, [Type] = @P17 WHERE ID = @P0";

OleDbParameter P0 = new OleDbParameter("@P0", OleDbType.VarChar);
P0.Value = this.textBox1.Text;
OleDbParameter P1 = new OleDbParameter("@P1", OleDbType.VarChar);
P1.Value = this.textBox2.Text;
OleDbParameter P2 = new OleDbParameter("@P2", OleDbType.VarChar);
P2.Value = this.textBox3.Text;
OleDbParameter P3 = new OleDbParameter("@P3", OleDbType.VarChar);
P3.Value = this.textBox4.Text;
OleDbParameter P4 = new OleDbParameter("@P4", OleDbType.VarChar);
P4.Value = this.textBox5.Text;
OleDbParameter P5 = new OleDbParameter("@P5", OleDbType.VarChar);
P5.Value = this.textBox6.Text;
OleDbParameter P6 = new OleDbParameter("@P6", OleDbType.VarChar);
P6.Value = this.textBox7.Text;
OleDbParameter P7 = new OleDbParameter("@P7", OleDbType.VarChar);
P7.Value = this.textBox8.Text;
OleDbParameter P8 = new OleDbParameter("@P8", OleDbType.VarChar);
P8.Value = this.textBox9.Text;
OleDbParameter P9 = new OleDbParameter("@P9", OleDbType.VarChar);
P9.Value = this.textBox10.Text;
OleDbParameter P10 = new OleDbParameter("@P10", OleDbType.VarChar);
P10.Value = this.textBox11.Text;
OleDbParameter P11 = new OleDbParameter("@P11", OleDbType.VarChar);
P11.Value = this.textBox12.Text;
OleDbParameter P12 = new OleDbParameter("@P12", OleDbType.VarChar);
P12.Value = this.textBox13.Text;
OleDbParameter P13 = new OleDbParameter("@P13", OleDbType.VarChar);
P13.Value = this.textBox14.Text;
OleDbParameter P14 = new OleDbParameter("@P14", OleDbType.VarChar);
P14.Value = this.textBox15.Text;
OleDbParameter P15 = new OleDbParameter("@P15", OleDbType.VarChar);
P15.Value = this.textBox16.Text;
OleDbParameter P16 = new OleDbParameter("@P16", OleDbType.VarChar);
P16.Value = this.textBox17.Text;
OleDbParameter P17 = new OleDbParameter("@P17", OleDbType.VarChar);
P17.Value = this.comboBox1.Text;

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

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);
cmdAdder.Parameters.Add(P10);
cmdAdder.Parameters.Add(P11);
cmdAdder.Parameters.Add(P12);
cmdAdder.Parameters.Add(P13);
cmdAdder.Parameters.Add(P14);
cmdAdder.Parameters.Add(P15);
cmdAdder.Parameters.Add(P16);
cmdAdder.Parameters.Add(P17);
//cmdAdder.Parameters.Add(P0);

// Execute the SQL command
int nNoAdded = cmdAdder.ExecuteNonQuery();
Console.WriteLine("\nRow(s) added = {0}", nNoAdded);

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

IsshouFuuraibou

I'm not an expert on database access, but this line seems odd to me:

String uSQLCommand = "UPDATE[Table1] SET[Company] = @P1, [LastName] = @P2, [FirstName] = @P3, [Address1] = @P4, [Address2] = @P5, [City] = @P6, [State] = @P7, [Zip] = @P8, [Web] = @P9, [Phone1] = @P10, [Phone2] = @P11, [Phone3] = @P12, [Fax] = @P13, [EMail1] = @P14, [EMail2] = @P15, [EMail3] = @P16, [Type] = @P17 WHERE ID = @P0";

there isn't a space between UPDATE and [Table1], as well as SET and [Company]... Shouldn't there be spaces between the SQL keywords and the values It may just be the copy formatting, but it is worth checking you code for:

Can you confirm that the following doesn't work
String uSQLCommand = "UPDATE [Table1] SET [Company] = @P1, [LastName] = @P2, [FirstName] = @P3, [Address1] = @P4, [Address2] = @P5, [City] = @P6, [State] = @P7, [Zip] = @P8, [Web] = @P9, [Phone1] = @P10, [Phone2] = @P11, [Phone3] = @P12, [Fax] = @P13, [EMail1] = @P14, [EMail2] = @P15, [EMail3] = @P16, [Type] = @P17 WHERE ID = @P0";

MLyons10

Thank you very much for your help. I made that change, and it did appear to make some difference. The information is still not being updated in my database, but the Microsoft Access Record Locking Information file that is created when I click this button is now 1kb (Rather than 0kb as it was previously), this is the same size the file is when I insert a record. I don't really understand why it would create this file, but wouldn't actually make the update...

Any help is greatly appreciated.

Thanks Again,

jgalley

I think your problem is that you are providing parameters by name but Access is expecting the parameters by position.

I know this is a little confusing, but what Access thinks you are attempting to do is this..

============================

Update [table1] Set

Company = this.textbox1.Text, .....

Where

id = this.comboBox1.Text

============================

This is because the ID Parameter P0 (the first in your collection) is being assigned to the first slot in your parameterized SQL. the end result is your update searches for a record where the ID = the "type" value. Of course all the other parameters are wrong as well since they are "shifted" one in the collection.

Try this:

Code Snippet

// commented out OleDbParameter P0 = new OleDbParameter("@P0", OleDbType.VarChar, this.textBox1.Text);
OleDbParameter P1 = new OleDbParameter("@P0", OleDbType.VarChar, this.textBox1.Text);
OleDbParameter P2 = new OleDbParameter("@P0", OleDbType.VarChar, this.textBox2.Text);
...

OleDbParameter P16 = new OleDbParameter("@P0", OleDbType.VarChar, this.textBox61.Text);
OleDbParameter P17 = new OleDbParameter("@P17", OleDbType.VarChar, this.comboBox1.Text);
OleDbParameter P0 = new OleDbParameter("@P0", OleDbType.VarChar, this.textBox1.Text);

that should set the order right I think.

MLyons10

Thank you very much for your response and suggestion. I made that change, but it is still not updating the database... I just don't get it...

Any help and information is greatly appreciated.

Thanks Again,

MLyons10

I'm sorry, I misread your post. I did the following:

OleDbParameter P1 = new OleDbParameter("@P1", OleDbType.VarChar);
P1.Value = this.textBox2.Text;
OleDbParameter P2 = new OleDbParameter("@P2", OleDbType.VarChar);
P2.Value = this.textBox3.Text;
OleDbParameter P3 = new OleDbParameter("@P3", OleDbType.VarChar);
P3.Value = this.textBox4.Text;
OleDbParameter P4 = new OleDbParameter("@P4", OleDbType.VarChar);
P4.Value = this.textBox5.Text;
OleDbParameter P5 = new OleDbParameter("@P5", OleDbType.VarChar);
P5.Value = this.textBox6.Text;
OleDbParameter P6 = new OleDbParameter("@P6", OleDbType.VarChar);
P6.Value = this.textBox7.Text;
OleDbParameter P7 = new OleDbParameter("@P7", OleDbType.VarChar);
P7.Value = this.textBox8.Text;
OleDbParameter P8 = new OleDbParameter("@P8", OleDbType.VarChar);
P8.Value = this.textBox9.Text;
OleDbParameter P9 = new OleDbParameter("@P9", OleDbType.VarChar);
P9.Value = this.textBox10.Text;
OleDbParameter P10 = new OleDbParameter("@P10", OleDbType.VarChar);
P10.Value = this.textBox11.Text;
OleDbParameter P11 = new OleDbParameter("@P11", OleDbType.VarChar);
P11.Value = this.textBox12.Text;
OleDbParameter P12 = new OleDbParameter("@P12", OleDbType.VarChar);
P12.Value = this.textBox13.Text;
OleDbParameter P13 = new OleDbParameter("@P13", OleDbType.VarChar);
P13.Value = this.textBox14.Text;
OleDbParameter P14 = new OleDbParameter("@P14", OleDbType.VarChar);
P14.Value = this.textBox15.Text;
OleDbParameter P15 = new OleDbParameter("@P15", OleDbType.VarChar);
P15.Value = this.textBox16.Text;
OleDbParameter P16 = new OleDbParameter("@P16", OleDbType.VarChar);
P16.Value = this.textBox17.Text;
OleDbParameter P17 = new OleDbParameter("@P17", OleDbType.VarChar);
P17.Value = this.comboBox1.Text;
OleDbParameter P0 = new OleDbParameter("@P0", OleDbType.VarChar);
P0.Value = this.textBox1.Text;

In actuallity, I'm a bit unsure of what your suggesting. It looks like, from your example that your assigning all the Parameters @P0, except for P17. Would you be able to clarify I don't quite follow.

Thanks Again,

IsshouFuuraibou

Have you though about building the string to not use the @P# method Or is that a restriction you must follow

Would this method of building the command string be acceptable (pardon any errors, I only typed it in the post)

Code Snippet

System.Text.StringBuilder CommandString = new System.Text.StringBuilder();
CommandString.AppendFormat("UPDATE [Table1] SET [Company] = {0}", this.textBox2.Text);
CommandString.AppendFormat(", [LastName] = {0}", this.textBox3.Text);
CommandString.AppendFormat(", [FirstName] = {0}", this.textBox4.Text);
CommandString.AppendFormat(", [Address1] = {0}", this.textBox5.Text);
CommandString.AppendFormat(", [Address2] = {0}", this.textBox6.Text);
CommandString.AppendFormat(", [City] = {0}", this.textBox7.Text);
CommandString.AppendFormat(", [State] = {0}", this.textBox8.Text);
CommandString.AppendFormat(", [Zip] = {0}", this.textBox9.Text);
CommandString.AppendFormat(", [Web] = {0}", this.textBox10.Text);
CommandString.AppendFormat(", [Phone1] = {0}", this.textBox11.Text);
CommandString.AppendFormat(", [Phone2] = {0}", this.textBox12.Text);
CommandString.AppendFormat(", [Phone3] = {0}", this.textBox13.Text);
CommandString.AppendFormat(", [Fax] = {0}", this.textBox14.Text);
CommandString.AppendFormat(", [EMail1] = {0}", this.textBox15.Text);
CommandString.AppendFormat(", [EMail2] = {0}", this.textBox16.Text);
CommandString.AppendFormat(", [EMail3] = {0}", this.textBox17.Text);
CommandString.AppendFormat(", [Type] = {0}", this.comboBox1.Text);
CommandString.AppendFormat(" WHERE ID = {0}", this.textBox1.Text);
String uSQLCommand = CommandString.ToString();
// Create the command object
OleDbCommand cmdAdder = new OleDbCommand(uSQLCommand);
cmdAdder.Connection = new OleDbConnection(uconString);
cmdAdder.Connection.Open();

// Execute the SQL command
int nNoAdded = cmdAdder.ExecuteNonQuery();
Console.WriteLine("\nRow(s) added = {0}", nNoAdded);

MLyons10

Hello and thank you very much for your post. I will try that, but when I was trying to put that in to Visual Studio it is not recognizing "CommandString.AddFormat", as it says that CommandString does not contain a definition for AddFormat. I tried to see if there was a reference or something that I needed to add, but I couldn't find anything on this. Am I missing something

Also, I setup a bunch of breakpoints on the previous code that I was using, and everything seems to work well up until this bit of code (Which works fine when I am inserting data in the database). I'm not sure what the issue with this line of code could be...

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

I also tried using this, per another example I saw:

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

But it still fails at this line...

Thanks Again,

IsshouFuuraibou

Sorry, my mistake, the AddFormat is supposed to be AppendFormat, I'll fix the sample code

MLyons10

Oh, O.K. I will try that then.

Thanks,

MLyons10

Alright, I'm sorry to bother you again (I would love to mark this thread as answered... lol), but I just tried that with AppendFormat, and it is still failing at the below lines:

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

or

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

I don't understand this. It builds all of the data, then fails when it wants to put the data in the database And yet I can insert data without any problem... It makes no sense to me (And is beginning to get frustrating... lol).

Any help and information is greatly appreciated.

Thanks Again,

MLyons10

Hi, I was just thinking of something, and was wondering if this could have anything to do with the issue... I am not updating all of the columns in the specified row of the table in my code. Could this by what is causing the issue I think I will try to play around with this tonight. I can insert data without inserting nothing in certain columns, but I don't know if this is an issue when updating... I'm curious what your experience may have been in the past...

Thanks Again,

jgalley

Sorry,

I just reviewed your code again and you do have it almost right:

Remember that in MSSQL prameters are referenced by name but in MSAccess parameters are referenced from the Parameter collection by position. In fact, in MSAccess one often see parameters all assigned the name @ is the SQL to highlight the fact that the name has no meaning,

such as: Update employee set name = @ where id = @

Looking back at your origninal code, I see that you are adding the parameters to the collection in the order that they appear in the SQL statement (this is good) except for the final

//cmdAdder.Parameters.Add(P0);

this one you have commented out. It is in the right spot positionally as P0 is the last paramenter to appear in your SQL statement. It is just not being assigned a value and hence your update statment looks more like

update employee set name ="smaith' where id = nothing

Try uncommenting that final add of the paramenter to the collection.

MLyons10

Hm, thank you very much for your post, it was very informative. I will try to make that change and see if that works.

One question though, as you said that Access handles the update of the table by column position, I have several cells that I'm skipping, as the data is not on this form to update those fields. Do I still need to pass those columns through the update parameter Will they be overwritten with a !null value by doing this I'm just trying to understand this.

Thanks Again,

MLyons10

O.K., I tried uncommenting the cmdAdder.Parameters.Add(P0); line, and the code still fails on the following line:

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

Do I need to reference each column in the table when updating I don't really understand that. I haven't read anywhere that this was necessary, but your above post kind of sounds like it would be. How would I reference a column in the update statement without overwriting it Any help and information is greatly appreciated.

Thanks Again,

Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...

Re: Visual C# Express Edition Using a Database Help...