2

I think the error may be coming from a reserved word that I may be using, but I don't know which word. Last time I had this issue was in an INSERT INTO statement and I fixed it by putting brackets around the reserved word. But if it is an issue with a reserved word. How can I put brackets around a reserved word when the code is like this? Any help?

        OleDbCommandBuilder cb;
        cb = new OleDbCommandBuilder(clientAD);

        DataRow dRow = clientDS.Tables["Client"].Rows[cInc];

        bool thingy = false;
        int defaultInt = 0;
        byte defualtByte = 0;
        double defualtDouble = 0.0;

        #region dRow items
        dRow["ID"] = textBox3.Text;
        dRow["ID_Type"] = "";
        dRow["LastName"] = textBox1.Text;
        dRow["FrstName"] = "";
        dRow["MI"] = "";
        dRow["MassMail"] = thingy; //Bool
        dRow["BusName"] = "";
        dRow["Bus"] = thingy; //Bool
        dRow["Title"] = "";
        dRow["Title2"] = "";
        dRow["Sex"] = "";
        dRow["Cli_DOB"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["Spo_DOB"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["Salute"] = "";
        dRow["Salute2"] = "";
        dRow["Spouse"] = "";
        dRow["SSAN"] = "";
        dRow["Review"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["Occup"] = "";
        dRow["AgentNo"] = defaultInt; //Int32
        dRow["Status"] = "";
        dRow["Wedding_DT"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["Flag_1"] = "";
        dRow["Flag_2"] = "";
        dRow["Flag_3"] = "";
        dRow["Flag_4"] = "";
        dRow["Flag_5"] = "";
        dRow["Flag_6"] = "";
        dRow["Flag_7"] = "";
        dRow["Flag_8"] = "";
        dRow["Flag_9"] = "";
        dRow["Flag_10"] = "";
        dRow["Group"] = thingy; //Bool
        dRow["GroupMember"] = thingy; //Bool
        dRow["KeyWords"] = "";
        dRow["Smoker"] = "";
        dRow["Married"] = "";
        dRow["NumChild"] = defaultInt; //Int32
        dRow["NetAsset"] = defaultInt; //Int32
        dRow["Income"] = defualtByte; //Byte
        dRow["LstUpdate"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["AutoSal"] = "";
        dRow["CIP_ID"] = defaultInt; //Int32
        dRow["EmailAddr_1"] = "";
        dRow["EmailAddr_2"] = "";
        dRow["UD1"] = "";
        dRow["UD2"] = "";
        dRow["UD3"] = "";
        dRow["UD4"] = "";
        dRow["UD5"] = "";
        dRow["UD6"] = "";
        dRow["UD7"] = "";
        dRow["UD8"] = "";
        dRow["UD9"] = "";
        dRow["UD10"] = "";
        dRow["UD11"] = "";
        dRow["UD12"] = "";
        dRow["UD13"] = "";
        dRow["UD14"] = "";
        dRow["UD15"] = defualtDouble; //Double
        dRow["UD16"] = defualtDouble; //Double
        dRow["UD17"] = defualtDouble; //Double
        dRow["UD18"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["UD19"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["UD20"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["New"] = thingy; //Bool
        dRow["TDate"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["TTime"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["ImportID"] = defaultInt; //Int32
        dRow["SalespTxt"] = "";
        dRow["Notepad"] = "";
        dRow["ANote"] = "";
        dRow["expCount"] = defaultInt; //Int32
        dRow["PNote"] = "";
        dRow["DrLic_Issue"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["DrLic_Exp"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["DrLic_state"] = "";
        dRow["CoDrLic_State"] = "";
        dRow["CoDrLic_Issue"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["CoDrLic_Exp"] = DateTime.Now.ToShortDateString(); //Date/Time
        dRow["Void_check"] = "";
        dRow["ASER"] = "";
        dRow["Anniversery"] = "";
        dRow["Client_Note"] = "";
        dRow["C o_Citizenship"] = "";
        dRow["Attachment"] = "";
        dRow["Pass_Notes"] = "";
        dRow["Other_Notes"] = "";
        dRow["CE_Notes"] = "";
        dRow["Comp_Notes"] = "";
        #endregion

        clientAD.Update(clientDS, "Client");

I am putting default values into the row, and I've checked to make sure the data types match. And I originally didn't include the field names, I instead used the field indexes the first time.

Improve this question
4
  • Whats the Exact error you got when executing this code? Commented Feb 3, 2012 at 20:15
  • Try looking at cb.GetUpdateCommand().CommandText? Commented Feb 3, 2012 at 20:21
  • which database are you OleDb connecting to... Access, Excel, FoxPro? Commented Feb 4, 2012 at 22:11
  • I'm using an access database. Commented Feb 13, 2012 at 16:02

2 Answers 2

Reset to default
1

Should dRow["C o_Citizenship"] have a space between C and o?

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yes, it was a typo when the database was made.
0

I would wrap it in a try/catch and see what its specifically choking on

try
{
   clientAD.Update(clientDS, "Client");
}
catch( Exception oError )
{
   MessageBox.Show( oError.Message );
}

but if you put a breakpoint on the actual MessageBox and look at the entire oError object in a watch debug window (or local variable debug window), it might expose some more specifics for you. For all we know, you could be trying to issue an "Update" on a table that has an auto-increment column that is specifically read-only and won't let you touch it. If you try to set the ID, it may be considering that a changed value and try to force it through.

Also, you reference your "clientAD" which is an instance of an OleDbDataAdapter. You may have to specify the "column" that is the primary key to be the basis.

Improve this answer

6 Comments

I'm sorry its taken me so long to get back to you on this. I've been away on vacation. But I added the try/catch like you said and it is giving me a message that reads "Syntax error in UPDATE statement." And yes, my table does have an auto-increment column. The first column, ID, is the auto.
@user1161086, I would then look back at your "Update" command in your OleDbDataAdapter (clientAD). The update command should NOT include in it the auto-increment column as an UPDATE COLUMN, only in the WHERE clause of it... So that might be where its happening. Is the command being generated for you, or did you construct it... if so, you can not update an auto-inc column (nor insert) as they are intentionally read-only and assigned by the database.
The command is being generated for me. But I added MessageBox.Show(cb.GetUpdateCommand().CommandText.ToString()) in order to see the command being generated. ID seems to only be in the WHERE clause already. But the values for everything are "= ?". And its the same in the where clause. If I were to create the statement myself, is there a quick easy way to do so. As you can see, I am updating a lot of fields.
@user1161086, no quick-and-easy except for brute-force hand-jam, but then you still need to deal with adding all the parameters coming from the record source. The only other thing I can think of is the data type of the "ID". You are setting its value based on txtBox3.Text, try changing that to Int.TryParse( txtBox3.Text ) to ensure its the correct integer value type and not choking on that.
Parse didn't work. I think I'll just have to type out the whole command. Which is what I was hoping to avoid.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.