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

SQL Server Database Problem.

==> I am using SQL Server database where I have a col dt_entry of type date/time with format dd/mm/yyyy. In my application I have one CRecordset derived class for above table say m_pSet. Now I want to filter data on a specific date. I am using Masked CDateEdit Edit control for taking user input. Pl help me to give code like m_pSet->m_strFilter=.......

==> When I am trying to save data by transfering value like m_pSet->m_Name = "sdf" and calling m_pSet->Update() It is giving "RECORDSET IS READ ONLY". The database property has been properly set to allow update,modify,delete etc.

=> Will ExecuteSQL("select * from ab") will be perfectly working in SQL Server as ACCESS ? I am facing some problem of not executing it. What may be the error?

Thanks in advance.
0
majorjohn
Asked:
majorjohn
  • 2
  • 2
  • 2
  • +2
1 Solution
 
balugaaCommented:
1. the m_strFilter basically provides the where clause of your SELECT statement.
if the date being received is in text format ie. a string then something like

m_strFilter.Format(" dt_entry = #%s#", strDate)

will work.

2.  Some code about how you are editing/adding may be helpful here, to find out why the problem is arising.

3.  Again some code, or more detailed description maybe it is not clear what you are asking    

 
0
 
vachoohoCommented:
1. Your sequence for updateing data should be the following
m_pSet->Edit();
m_pSet->m_Name = "...";
m_pSet->... = ...;
m_pSet->Update();

2. Check CRecordset's GetDefaultSQL() - it should contain ONLY ONE table name or the exact "select ... from ..." clause with ONE table involved in query.

3. SQL Server accept date values only in string form
Thus your m_strFilter should be something like "dt_entry = 'Jan 12, 2000'"

NOTE, that I do not use ## separator as it is needed for ACCESS.

By the way - you can input date values in many ways, as far as they are strings

Look at the following dates

12 Jan 2000
Jan 12 2000
12/01/2000

As I understand from SQL server documnetation it accept all these values and deal correctly with them

However I advice you use default locale formats for date values

thus I use this snippet

COleDateTime dt;
dt = ... // input value from user or get default date/time
m_pSet->m_strFilter.Format("dt_value = '%s'", dt.Format());

this will format date/time using current machine locale information in place it into sql query's filter.

Hope this helps
0
 
majorjohnAuthor Commented:
In my SQL Server Database I have set Option checked for APPEND,MODIFY AND DELETE. Pl. see the following :

1) In DOC header class I am declaring an object of CDatabase.(CDatabase mdb)
2) In OnNewDocument() of DOC CPP I am opening the database by
mdb.Open(NULL,FALSE,FALSE, strconnect,.....).
Before that I am creating a new CRecordset Class for my table. and in OnNewDocuement() linking this CRecordset pointer with CDatabase object.

3) Then in OnSave() using
m_Pset->Edit()
m_Pset->m_Name="sfdsdfdsf"
m_Pset->Update()

It is doing everything but only giving Recordset is READONLY.

What to do?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
balugaaCommented:
Hope this helps:

CDatabase m_Database;

// Name used to register database in Registry;

CString sDBName("MyDatabase");

m_Database.Open((LPCTSTR) sDBName);

CSomeRecordSet foo(&m_Database);

// To edit there has to be a record:
foo.m_strfilter.Format(" bar = barfoo");
 if(foo.Open())
{
   if(!IsEOF)
  {
      //Do Edit Stuff
  }
  else // Do Add Stuff;

}
0
 
vachoohoCommented:
what is in your CRecordset's derived class GetDefaultSQL() member body?
0
 
khyattCommented:
Hi,

==> I am using SQL Server database where I have a col dt_entry of type date/time with format dd/mm/yyyy. In my application I have one CRecordset derived class for above table say m_pSet. Now I want to filter data on a specific date. I am using Masked CDateEdit Edit control for taking user input. Pl help me to give code like m_pSet->m_strFilter=.......


As mention in a previous answer, you must make sure that the locale is not interfering with your date structure. I always send date strings to the server in DD MMM YYYY formar (i.e. 12 Jan 2000). This is a foolproof way to send date stuff to SQL.

For a CTime/COleDateTime structure, there's a Format method you can use which is local-proof:

CTime ct;

ct.Format("%d %b %Y ");

Therefore, since m_strFilter is basically a WHERE clause, you can enter:

m_strFilter.Format(" dt_entry = '%s' ", ct.Format("%d %b %Y"));  // Date conversion to dd mmm yyyy


==> When I am trying to save data by transfering value like m_pSet->m_Name = "sdf" and calling m_pSet->Update() It is giving "RECORDSET IS READ ONLY". The database property has been properly set to allow update,modify,delete etc.

If you join tables (i.e. run a query to extract data from more than one table), your recordset will be readonly as it doesn't know how to update more than one table as a time.
Instead of the Update method, you should build SQL UPDATE statements (i.e. UPDATE table1 SET field = value WHERE condition) and send them viw the CDatabase::ExecuteSQL() method. If you use try/catch you will pick up any problems with the execution.
=> Will ExecuteSQL("select * from ab") will be perfectly working in SQL Server as ACCESS ? I am facing some problem of not executing it. What may be the error?

ExecuteSQL() doesn't return any rows (see documentation). Therefore a Select statement sent with ExecuteSQL won't return anything. Use CRecordset or CDAORecordset... if it 's a JET database.

I hope this was useful.
0
 
kkarunakarCommented:
Hi there..
I am suggesting solution to ur problem and hope it will work.
Answers are in order of question.
1. Pls return COleDateTime object from mask date class. Or if u r getting CString u can get COleDateTime object using ParseDateTime() function.
Now  u can make u r query easily.
U can just make COleDateTime to CString...
U r m_strFilter.
CString cDate = odtDate.Format(...);
m_strFilter.Format("[date] = '%s'",cDate);
Sqlserver compare date with string..
2.Go to recordset constructor change type at the last line to dynaset.
m_nDefaultType = dynaset;
3.ExecuteSql() - It is used for only Insert,delete,update statement..
so it won't work for sql statement
Regards keshav
0
 
kkarunakarCommented:
Thanks for accepting my answer..
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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