'System.Data.OleDb.OleDbException'

Question

All I'm getting a: 'An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll' after adding my 'phonenumber' field to: string str = string.Format(INSERT INTO Customers (firstname lastname address city statezipcodephonenumber) VALUES ('{0}''{1}''{2}''{3}''{4}''{5}''{6}') this.fname.Textthis.lname.Textthis.address.Textthis.city.Textthis.state.Textthis.zipcode.Textthis.phonenum.Text); OleDbCommand command = new OleDbCommand(strconn); command.ExecuteNonQuery(); conn.Close(); The error points to line: command.ExecuteNonQuery(); If I remove phonenumber everything works fine. The database is set to accept 'Number' as the data type for this field. I should also add I'm using an Access dB. Any ideas?

eejay · Answer

NEVER MIND!!! The notesbox field was named incorrectly......

eejay · Answer

The first suggest didn't work so I changed the data type of my phone number field to Text. That seems to have resolved it. But now.... :) when I add notesbox I get the same error. I included the MessageBox.Show(str) and everything appears to be okay. The notesbox data shows as 'Test!' but it still errors out.

spgilmore · Answer

In fact I wouldn't recommend that phone number field be a number at all. a 10-digit number will overflow 60% to 80% of the time depending on if the number is signed. I would recommend that you change it to a 10-character text field.

spgilmore · Answer

The problem is in your sql text. It is invalid. My first inclination is the '{6}' is going to turn into '9875551234' which is a string not a number. Remove the enclosing quotes from the {6} and try it. If that doesn't work evaluate the formatted text like this: string str = string.Format(INSERT INTO Customers (firstname lastname address city statezipcodephonenumber) VALUES ('{0}''{1}''{2}''{3}''{4}''{5}''{6}') this.fname.Textthis.lname.Textthis.address.Textthis.city.Textthis.state.Textthis.zipcode.Textthis.phonenum.Text); MessageBox.Show(str); When you see the formatted sql text it may become more obvious where the error is. If all else fails copy str to the clipboard and paste it into access and run it there. You can then adjust it until you get it working and you'll know what you have to change.