CString.Format

Hi all

I have a little problem wich you guys might be able to help med with

Code:

---

void CDeploymentDlg::OnOK()
{
        CString str;
        CDatabase db;

        try
        {
                db.OpenEx("Drive={Microsoft Access Driver(deployment.mdb)};DBQ=deployment.mdb");

                //db.OpenEx("Driver = {Microsoft Access Driver(deployment.mdb)};DBQ=deployment.mdb");
                //db.OpenEx("deployment.mdb");
                //db.OpenEx("deployment.mdb", CDatabase::noOdbcDialog );
                //db.OpenEx("Driver={Microsoft Access Driver (deployment.mdb)};DBQ=deployment.mdb;", CDatabase::noOdbcDialog);
        }
        catch(CDBException*)
        {
                AfxMessageBox("The database deployment.mdb was not found!!!", MB_ICONERROR);
                exit(0);
                throw;
        }

        str.Format("UPDATE webcapsdeployment SET Build=%s, Date=%s WHERE Platform = 'SAND'",m_sSandBuild, m_sSandDate);
        db.ExecuteSQL(str);

        CDialog::OnOK();
}

---

I get this error in a AfxMessageBox when running the program:
syntax error in UPDATE statement

That could be an sql query problem, try this:

Code:

---

str.Format("UPDATE webcapsdeployment SET Build='%s', Date='%s' WHERE Platform = 'SAND'",m_sSandBuild, m_sSandDate);

---

Perhaps the SQL statement needs a semicolon at the end?

str.Format("UPDATE webcapsdeployment SET Build=%s, Date=%s WHERE Platform = 'SAND';",m_sSandBuild, m_sSandDate);

Quote:

---

Originally Posted by okinrus

Perhaps the SQL statement needs a semicolon at the end?

str.Format("UPDATE webcapsdeployment SET Build=%s, Date=%s WHERE Platform = 'SAND';",m_sSandBuild, m_sSandDate);

---

I think the SQL statement just needs single quotes around the strings...since single SQL statements usually don't need semicolons.

In SQL strings need to be encased in a single quotation mark (as MitchellH points out)

also I found dynamically adding the DSN helpful

Code:

---

CDatabase        DB;
if(!DB.OpenEx("DSN=YourDataSourceName",CDatabase::noOdbcDialog))
  return -1;

//if not add one with
CString      Attrib.Format("DSN=YourDataSourceName; DBQ=\\\\server\\Path\\YourDatabase.mdb; DESCRIPTION=Test; READONLY=FALSE; EXCLUSIVE=FALSE;" );

//this adds a SYSTEM DSN for the database to use, there other flags for diff  types

::SQLConfigDataSource(NULL, ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)", Attrib);

---

hmmm i still get the syntax error in UPDATE, anyone with another solution ?

Hmmmmmmmmmmm...

try putting the table name in square brackets
ie UPDATE [TableName] ......


I have working code at home but don't have code that does that (with access) at work...
Will check when I get home in a few hours and post.

Code:

---

                db.OpenEx("Drive={Microsoft Access Driver(deployment.mdb)};DBQ=deployment.mdb");

---

Is this a valid connection string? According to ConnectionStrings.com, the correct connection string is:

Code:

---

"Driver={Microsoft Access Driver (*.mdb)};Dbq=deployment.mdb;"

---

You must also make sure your strings do not contain embedded apostrophes. Finally, if the Date field is actually a date-type field rather than a string you should use hash (#) symbols to surround it rather than apostrophes. Obviously it requires a valid date format as well.

Code:

---

        str.Format("UPDATE webcapsdeployment SET Build='%s', Date=#%s# WHERE Platform = 'SAND';",m_sSandBuild, m_sSandDate);

---

If all that fails to work, could you post the actual SQL string after it has been formatted?

none of it worked again :S but yes it is driveR but i just removed the r for a short test and forgot to put it back on :)

i have tested a bit and find out that:

Code:

---

str.Format("UPDATE webcapsdeployment SET Build=%s WHERE Platform = 'SAND';",m_sSandBuild);

---

this works.

So i can update when i only want to update one string but if i add more than one to my code it gets syntax error in update statement :S

okay it might be a problem with my date string cause i can save all other than that so i will just test a bit more and post a result if i find one :D until then good ideas are welcome :D

oh my god... i have been working with this problem for weeks now and now i find out that date is a reserved word... how far out is that! GRRRRR :mad: