ODBC Database Record Add

[fdsouth]

I've created an ODBC database with a single table named "Housings". This table has 8 different fields that I would like to add data from a VC++ application. I've completed everything up to the point of interfacing with the database. I connect as follows:

if(!database.Open(NULL, FALSE, TRUE, "ODBC;DSN=On-X Component Data"))
{
   AfxMessageBox("Failed to open the database connection.");
}
CWallAngle_Set wallangle_set(&database);
wallangle_set.m_strFilter = "";
if(!wallangle_set.Open())
{
   AfxMessageBox("Cannot find records.");
}

*** I assume this is where I need to add the INSERT code. ***

wallangle_set.Close();
database.Close();

I've already calculated values and entered them into 8 different CString objects. I now need to insert the values into the database. Is there a relatively simple way to accomplish this?

PLEASE HELP!!

 

[SpenBabe]

Between the CRecordset Open & Close statements, try :-

wallangle_set.AddNew();
wallangle_set.m_FIELD1 = csMyString1;
wallangle_set.m_FIELD2 = csMyString2;
...
wallangle_set.Update();

It's the 'Update' function that builds the SQL INSERT statement for you based on the fact that you have called 'AddNew' beforehand (if the record exists call 'Edit' instead & it will form an UPDATE statement for you) Spencer Window (not a joke name)
spencer.window@eastmidlandcomputers.ltd.uk

 

[fdsouth]

I added in the code that you suggested:

if(!database.Open(NULL, FALSE, TRUE, "ODBC;DSN=On-X Component Data"))
{
   AfxMessageBox("Failed to open the database connection.");
}
CWallAngle_Set wallangle_set(&database);
wallangle_set.m_strFilter = "";
if(!wallangle_set.Open())
{
   AfxMessageBox("Cannot find records.");
}
else
{
   wallangle_set.AddNew();
   wallangle_set.m_Lot_Number = Lot;
   wallangle_set.m_Serial_Number = Serial;
   wallangle_set.m_Size = Size;
   wallangle_set.m_Surface_Finish = Finish;
   wallangle_set.m_W1 = W1;
   wallangle_set.m_W2 = W2;
   wallangle_set.m_W3 = W3;
   wallangle_set.m_W4 = W4;
   wallangle_set.m_W5 = W5;
   wallangle_set.Update();
   
   wallangle_set.Close();
   database.Close();
}
MessageBox("Done");

That all makes sense to me. However, when I run the application, I get an "Access Violation" error when Update() is executed. Do you have an idea as to why this would happen? I ran the Debug tool "run to cursor" and everything appears fine (as far as I can tell) up to that point.

Any suggestions. I'm stumped.

 

[Zyrenthian]

Your using the defaults to open the database. Open it as a CRecordSet::dynaset (i belive) this is the first arg to open.


Matt

 

[fdsouth]

CWallAngle_Set (CRecordSet class) is defined elsewhere in my source code. In the definition, there are a number of functions that appear redundant to the connection code I listed above. I have:

CWallAngle_Set::CWallAngle_Set(CDatabase* pdb)
	: CRecordset(pdb)
{
	//{{AFX_FIELD_INIT(CWallAngle_Set)
	m_Lot_Number = _T("");
	m_Serial_Number = _T("");
	m_Size = _T("");
	m_Surface_Finish = _T("");
	m_W1 = _T("");
	m_W2 = _T("");
	m_W3 = _T("");
	m_W4 = _T("");
	m_W5 = _T("");
	m_nFields = 9;
	//}}AFX_FIELD_INIT
	m_nDefaultType = CRecordset::snapshot;
}

CString CWallAngle_Set::GetDefaultConnect()
{
	return _T("ODBC;DSN=On-X Component Data");
}

CString CWallAngle_Set::GetDefaultSQL()
{
	return _T("[Housings]");
}

Am I trying to do something in these "default" functions which is conflicting with the connection code I listed in my original posting? I'm new at database programming so I'm a little confused as to what is going on.

Also, you said to connect as a dynaset. I changed the code in my CRecordSet definition to:

m_nDefaultType = CRecordset::dynaset;

This just gave me a runtime error.

 

[Zyrenthian]

I am sorry for my post. I just tested what I said about dynaset and it does not work with odbc. I see you tried that :-( sorry for the mislead. I too tried to do what you were doing and though update returned 1, it did not update the database. Once I figure out why it does not update I will let you know. It may need a "Commit" but I dont know.

matt

 

[Zyrenthian]

ok... here ya go... this is a bit convoluted but you have to go back to Regular SQL because when CRecordset is used in edit mode it will not work (at least with access because that is what I am using)... here is how I did it

CDatabase db("ODBCNAME"
MyCRecordset rs(&db);
// rs has the followign member vars
// m_UID (primary key auto increment)
// m_data (type int)
// in a table called MyTable


CString SQL = "SELECT * FROM [MyTable] WHERE [UID] = 7"
rs.Open(CRecordset::snapshot,SQL);

if(rs.GetRecordCount())
{
// we need to edit

db.BeginTrans();
SQL= "UPDATE [MyTable] SET [MyTable].data = 17 WHERE [MyTable].UID = 7"
db.ExecuteSQL(SQL);
db.CommitTrans();
rs.Close();
}

Sorry for the run around. I didnt have time to check my reply at work today.

Good luck

matt