• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1341
  • Last Modified:

UPDATE in ODBC

When using Open() to run a database query with ODBC, it appears that only SELECT can be used in the query string.  I tried using UPDATE, and it returned debug assertion error dbcore.cpp line 3282.  However, when I call CanUpdate(), it returns true.

I once read that in order to make record changes on your database, you first need to do a SELECT query, set the new values in your program and exchange the data.  Is this correct?  If so, it seems long-winded.  Is there a way that UPDATE can be used instead?
0
reidy_boy
Asked:
reidy_boy
2 Solutions
 
mrjoltcolaCommented:
Not true. All valid SQL statements are fair game in ODBC.

Please post your code so we can see?
0
 
brad2575Commented:
Does the ID you are using in the ODBC have permissions to do an update on the database?

If so you should just be able to do an update no problem.
0
 
reidy_boyAuthor Commented:
I have attached the cpp and h class files, and the code snippet that contains the update query.
CRecordset PositiveResultSet(theApp.GetDatabase() );
CDBVariant Variant;
TCHAR Message[2000];
 
	if (sAcmeConfig[ACME_STAFF_COLUMN] == "")
		return;
 
	sprintf (Message, "UPDATE LECTDETS SET AcmeMiles='10' WHERE SetId='2008/2009' AND LecturerId='ESN'");
	PositiveResultSet.Open(CRecordset::snapshot, Message);

Open in new window

AcmeCorporation.cpp.txt
AcmeCorporation.h.txt
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
alb66Commented:
Try to use CRecordset::dynaset instead of CRecordset::snapshot
0
 
alb66Commented:
I think you don't need a recordset.

Try also theApp.GetDatabase()->ExecuteSQL( Message );
0
 
itsmeandnobodyelseCommented:
>>>> it appears that only SELECT can be used in the query string.
That is nearly true. You also could pass a table name or space what would cause the GetDefaultSQL() member function of your CRecordset derived class being called. It is also possible to pass a call to a stored procedure in the Open()-

>>>> Try also theApp.GetDatabase()->ExecuteSQL( Message );

alb66 is right. For update/insert/delete sql statements you need to use CDataBase::ExecuteSQL rather than a CRecordset member function. The point is that these statements don't return a recordset. Hence they were independent of a recordset and could be called with any open connection (what is the CDatabase instance).

But of course you could make delete, update and insert on a CRecordset as well. For that you need to derive from CRecordset - best by using the class wizard. Then you get a own recordset class generated which fully was connected to an existing table. When calling Open on an instance of that class it would read in either all the records of that table or filtered by the m_strFilter where clause. In any case you  now could move within that record set using any of teh Move member functions and either call AddNew or Edit at the current position - both were finished by an Update - or call Delete member function  on a position which would remove the current record.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now