SQL Server Compact Forum

SQLCE Error while using UPDATE with WHERE PrimaryKey=

Sun, 29 Nov 2009 00:12:11 Z

I have literally wasted 2 days of my life trying to find an answer to a problem that makes no sense to me. I'm receiving an error when I try to update a record in an SQLCE Database where the Primary Key (Identity) column is equal to a particular value.

Here is the code I'm using:

    Public Sub CloseRecordByKey(ByVal Key As Int32)
        Dim conn As SqlCeConnection = Nothing
        Dim cmd As SqlCeCommand = Nothing

        Try
            conn = New SqlCeConnection(My.Settings.Database1ConnectionString)
            conn.Open()
            cmd = New SqlCeCommand("UPDATE Entries Set TimeEnd=@p1 WHERE Key=@p2", conn)

            Dim p1 As New SqlCeParameter("@p1", SqlDbType.DateTime, 8, "TimeEnd")
            p1.Value = Now
            cmd.Parameters.Add(p1)

            Dim p2 As New SqlCeParameter("@p2", SqlDbType.Int, 4, "Key")
            p2.Value = Key
            cmd.Parameters.Add(p2)

            cmd.Prepare()
            cmd.ExecuteNonQuery() 'THIS IS WHERE I GET THE ERROR
            cmd.Dispose()
        Finally
            conn.Close()
        End Try
    End Sub

This is the error i received:
"There was an error parsing the query. [ Token line number = 1,Token line offset = 38,Token in error = Key ]"

Any help will be greatly appreciated.
Thank you,
Jim

content based image retrieval in sql server compact

Sun, 29 Nov 2009 15:55:28 Z

i want to give an image [jpeg] as an input to a query and retrieve yes if the image is in the db and not otherwise. is it possible to accomplish this using sql server compact?

thanks

Unable to load DLL 'sqlceme35.dll' - Vista 32 bit

Tue, 24 Jun 2008 13:18:56 Z

The full message is "Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)"

Various forums deal with this error and all solutions that I have seen say you should change the "Any CPU" to "x86" configuration because of a problem on 64 bit Windows systems.

But I am using a 32 bit Vista Home Premium system. I have MS Visual Studio Professional Edition (Education). Shortly after seeing the above message, VS typically crashes and restarts.

I have a "Microsoft SQL Server Compact Edition" folder in "C:\Program Files\" and of course sqlceme35.dll is present in the \v3.5\ folder.

Any clues?

Can i ask you something about how to use mdb file on wince 5.0 device??

Thu, 26 Nov 2009 11:16:24 Z

Hello.

I'm newbie on embedded development.

I'm working on wince 5.0 with Magic Eyes Board, MMSP2+,

I've been working seeing mdf file on wince 5.0 whole weeks. but still woking on it.

Even though i tried to search about mdb with wince, There's no clear reference of mdb on internet.

taking up the main subject, as it were,

How can i open mdb file on wince 5.0?

If could not open mdb file, is it neeeded to convert mdb to like a cdb format?

In that case, are there any free conversion tool provided by microsoft?

If possible, can i read mdb file adding some module on platform build module in catalog?

I read i cannot access a .mdb file using any APIs on wincows CE.

here is the URL : http://www.pocketpcjunkies.com/Uwe/Forum.aspx/wince-vc/727/MDB-Operation

If that is true, no solution seeing mdf file on wince?

Any helps will be appreciate. thanks in advance

SQL Server Compact 3.5 SP1 Adding data to table

Thu, 26 Nov 2009 18:11:21 Z

Hello,

i'm adding data to the sql server compact edition database. Everything when i add a row i call the commit function. But when suddely stop the application in visual studio with stop debugging the data is not stored in the database. The database is completed empty like it was before i started the application. How come is that? When i let the program exit normal without press stop debugging the data is stored to the database.

gr,

Sander

Generate DB from Script

Thu, 26 Nov 2009 13:40:12 Z

Hello All,

After sucessfully generating a script for my sqlce db, using the great "SQL Server Management Studio 2008 (Express) add-in " from CodePlex, how can I create the database from this script.

I will do that at hte application initialisation, I'm looking for some sample code that creates the database and execute the commands on the script to generate all tabels, keys, etc.

Do I have no get every single instruction on the script and execute it one by one? In a single transaction?

Regards,

Igor

Project Manager at INOVATIVA Tecnologia www.inovativatec.com

SQL Server Compact Edition Device Deployment

Tue, 24 Nov 2009 16:27:22 Z

We’re building a custom PC installation package to deploy our .NET Compact Framework 3.5 / SQL Server Compact Edition based application. The installation package uses the System.Configuration.Install and calls Process.Start(ceAppPath, iniPath). We are targeting ARM and x86 devices. Specifically, which SQL Server Compact Edition cab files do we need to install/deploy for our application to run on the these device platforms?

SQL CE

Tue, 24 Nov 2009 16:07:37 Z

I am new to SQL CE and C#.

I have two tables
- Address
- Customer

Customer table has a foreign key i.e. the AddressID which refers to the ID field of the address table.

What is the right way to:
1. Generate ID for the address table.
2. Insert the record in the address table.
3. Get the ID of the record just inserted.
4. Set this ID to the address field of the customer record.
5. Insert the customer record into the database.

Assume that this is a console application.

SQL CE 3.5 as data source in Integration Services-project (BIDS)

Wed, 25 Nov 2009 11:26:00 Z

Hello,

is there a way to use a SQL CE 3.5 SQL-database as a data flow source within an Integration Services-Project using Server Business Intelligence Studio 2008 and thus reducing a staging database?

It is unbelievable that SQL Server Compact is currently only provided as a data flow destination-object.

Kind regards,
Tom

SQL Server Compact data source not available in VS 2005 but works in VS 2008

Wed, 25 Nov 2009 12:40:43 Z

I just downloaded and installed Sql Server Compact 3.5 (SSCERuntime-ENU-x86.msi).

When I create a new application in VS2005 and try to add a new Data Source, I don't see "Microsoft SQL Server Compact 3.5" listed amongst the data sources. However, when I try this in VS2008 it does show. Is this not supposed to work with 2005?

Thanks

Amit

Where to find sqlce35 and required dlls

Tue, 24 Nov 2009 19:53:58 Z

I'm trying to find the sqlce35 cabs for installing the dlls.

SQL server 2008 compact edition: can we rename a sql table after saving it?

Tue, 24 Nov 2009 22:35:36 Z

We use SQL server 2008 compact edition.
Is there a way we can rename the sql table after we save it?
So far, in VS2008, we can not find a way to do it in the Server Explorer.
thx!

SQL Server Compact Edition - Pocket PC - Unspecified Error

Tue, 24 Nov 2009 21:19:54 Z

I’m getting an "Unspecified error" when SqlCeConnection.Open is called on a Microsoft Pocket PC Version 4.20.0 (Build 14053) device.

I’ve tested this code on the following platforms and it works fine.

Microsoft Windows Mobile 5.0 OS 5.1.195 (Build 14928.2.2.0)

Microsoft Windows Mobile 6.1 Professional CE OS 5.2.19972 (Build 19972.1.2.7)

Microsoft Windows Mobile 6.1 Classic CE OS 5.2.19581 (Build 19581.1.1.1)

The problem child Microsoft Pocket PC Version 4.20.0 (Build 14053) device has the following components installed.

Microsoft .NET CF 3.5 EN-String Resource

Microsoft .NET CF 3.5

SQLServerComact 3.5 Core

The Unspecifid Error message is not all that helpful. Any ideas?

NOTE: I'm using VS 2008 and I used InstallShield to deploy the .NET Framework 3.5 and SQL Server Compact Edition.

Thanks,

Sean

SQL CE stopped to flush to disk?

Fri, 20 Nov 2009 17:01:55 Z

I'm bundling SQL CE 3.5 SP1 with an Windows application. The application has been installed in roughly 30000 locations. In one of these locations, it seems like SQL CE stopped flushing changes to disk and that the changes made to the database after that was made only in memory - and not on disk - resulting in loss of data.

The user running the software reported the following:
- The software had been up and running on a computer for 30 days.
- Windows rebooted, due to an automatic install of a Windows update.
- After the reboot, all changes he had made in the software the last 30 days were gone. The changes made prior to that were still there.
- Several users at his location noticed this. The users access the database via a Windows service (my application). Among other things, the database contains username/password hashes. Several users noticed that the password were reverted to old values, and that the information they had stored the last 30 days were was lost. The problem seems to affect all users in the installation (>10).

Entries made to the Windows Event log the last 30 days is still there. At first, I was guessing that maybe the entire partition on the hard drive was restored from backup / restore point / whatever but apparently this does not seem to be the case. The logs from my application for the last 30 days is still available as well. My application has an application log which records all application events and errors, and no errors were reported from SQL CE at the time the problem started to occur (when the flush'ing stopped).

The next thing I thought was that he had restored a backup of the database. But apparently, the company had not set up backup routines for the database so this wasn't the issue either.

The connection string I'm using is as follows:

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=%s;SSCE:Database Password=%s;SSCE:Max Database Size=4000;SSCE:Flush Interval=10;

I have a few questions:

Of course, is this a known issue? I'm guessing not, since I haven't found any info on it.
I assume SQL CE has a background thread running which flushes the changes to disk. What happens if this thread dies for any reason? Will the thread terminate the process?
If the background thread has problems writing to the disk, for instance due to hardware or driver problems, what will happen? Since it's a background thread, the only reasonable thing I can think of is that SQL CE terminates the process.

Getting an access not allowed message when trying to create a DB connection to the Northwind DB in VB Studio 2008

Mon, 04 Feb 2008 04:50:31 Z

I recently installed VB Studio Express 2008, which also installed SQL Server Compact Edition. I was going thro the online tutorial from Microsoft, which tells you to create a connection to the Northwind DB. But, when I tried to connect, I get an error message saying access is not allowed. Note that the is running under Vista Home Premium. I have tried searching for an answer to this question, but have not found an answer that solves my problem. The closest was a similar problem for a C# application running in Internet Explorer, and the answer for that one was to change security settings in IE. But, for this demo, IE was not involved.

Query problem

Sat, 21 Nov 2009 11:04:59 Z

Hello,

I have query that i can't write in Sql CE. The SQL CE contains around 30.000 rows. It gives the error that it can't do a group by on ntext or image columns (and i need the columns in the result):

SELECT TSA.sa_id,TSA."priceout__price", TSA."priceout__price2", TSA."desc__title", TSA."desc__title2", TSA."worddoc__typeid", TSA."worddoc__typeid2", TSA."salenote__staffel", TSA."salenote__staffel2", TSA."webinfo__productcontent", TSA."webinfo__productcontent2", TSA."lpicture__imagesmall", TSA."lpicture__imagesmall2", TSA."picture__image", TSA."picture__image2", TSA."inactive{showgbaweb__hide", TSA."inactive{showgbaweb__hide2", TSA."pk_prodid__productid", TSA."pk_prodid__productid2", TSA."prodid__number", TSA."prodid__number2", COUNT(T1."pk_prodid__productid") AS pk_prodid__productid_CNT, COUNT(T2."pk_prodid__productid2") AS pk_prodid__productid2_CNT
FROM SA TSA
INNER JOIN SA T1 ON T1."pk_prodid__productid" = TSA."pk_prodid__productid"
INNER JOIN SA T2 ON T2."pk_prodid__productid2" = TSA."pk_prodid__productid2"
GROUP BY TSA.sa_id,TSA."priceout__price", TSA."priceout__price2", TSA."desc__title", TSA."desc__title2", TSA."worddoc__typeid", TSA."worddoc__typeid2", TSA."salenote__staffel", TSA."salenote__staffel2", TSA."webinfo__productcontent", TSA."webinfo__productcontent2", TSA."lpicture__imagesmall", TSA."lpicture__imagesmall2", TSA."picture__image", TSA."picture__image2", TSA."inactive{showgbaweb__hide", TSA."inactive{showgbaweb__hide2", TSA."pk_prodid__productid", TSA."pk_prodid__productid2", TSA."prodid__number", TSA."prodid__number2", T1."pk_prodid__productid", T2."pk_prodid__productid2"
ORDER BY TSA.SA_ID

Well i rewriten the query to but it takes more then 10 minues to execute the query:

SELECT TSA.sa_id,TSA."priceout__price", TSA."priceout__price2", TSA."desc__title", TSA."desc__title2", TSA."worddoc__typeid", TSA."worddoc__typeid2", TSA."salenote__staffel", TSA."salenote__staffel2", TSA."webinfo__productcontent", TSA."webinfo__productcontent2", TSA."lpicture__imagesmall", TSA."lpicture__imagesmall2", TSA."picture__image", TSA."picture__image2", TSA."inactive{showgbaweb__hide", TSA."inactive{showgbaweb__hide2", TSA."pk_prodid__productid", TSA."pk_prodid__productid2", TSA."prodid__number", TSA."prodid__number2", "pk_prodid__productid_CNT", "pk_prodid__productid2_CNT"
FROM SA TSA
INNER JOIN (SELECT "pk_prodid__productid", COUNT("pk_prodid__productid") AS "pk_prodid__productid_CNT" FROM SA T1 GROUP BY "pk_prodid__productid")
T1 ON T1."pk_prodid__productid" = TSA."pk_prodid__productid"
INNER JOIN (SELECT "pk_prodid__productid2", COUNT("pk_prodid__productid") AS "pk_prodid__productid2_CNT" FROM SA T2 GROUP BY "pk_prodid__productid2")
T2 ON T2."pk_prodid__productid2" = TSA."pk_prodid__productid2"
ORDER BY TSA.SA_ID

Can somebody help me out?

Prepare: CommandText property has not been initialized

Mon, 27 Oct 2008 16:50:52 Z

I have a form that I drug a "details" list into from the data sources pane. When I click the save button I get that non-descript error and I cannot figure out why.

    Private Sub ButtonSaveUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSaveUser.Click
        Me.Validate()
        Me.UsersBindingSource.EndEdit()
        Me.UsersTableAdapter.Update(Me.UsersDS.Users)
    End Sub

OLEDB Perormance problems with SQL CE

Tue, 24 Nov 2009 08:45:56 Z

Hi Everybody,

I am acing big problem and i will appreciate if any one could help me. I am developing MFC smart device application which i need to retreive a huge number of records from sql ce database.
I have made sample application to test data access performance of OLEDB then something wired happens.
I am looping on table in my database to read all records from this table(5000 records)

CString wGetStmt = L"Select * From HH_Item";
CDatabaseConnection * db = getDB();
db->Close();
if(db != NULL)
{
     for(int i=0; i<147; i++)
    {
          CString LogString;
          LogString.Format(_T("\\Storage card\\LogFiles\\%i.txt"),i);
          CMessageLogger* Logger = new CMessageLogger(LogString);
          db->ReOpen();
          CERecordset* rs = db->ExecSql(wGetStmt);
           int noFields = rs->GetFieldCount()-5;
           int counter = 0;
           while(!rs->IsEOF())
           {
                 counter++;			
                 for(int j=0;j< noFields; j++)
                 {
	         s = rs->GetFieldValueString(j);
                 }
                 rs->MoveNext();
           }
           rs->Close();
           db->Close();
           if(rs != NULL)
           {
                delete rs ;
                rs = NULL ;
           }
          if(Logger)
          {
                delete Logger;
                Logger = NULL;
          }
    }
}
db->Close();

The strange thing that happens is that some loops finish in 3 min and other loops takes 10 min (which is extremly long time).

The Code I used for CRecordSet Class is

CERecordset::CERecordset(IDBCreateSession *pIDBCreateSession)
{
      COLUMN_ALIGNVAL           = 8;
      VariantInit(&rowsetprop[0].vValue);
      m_pIDBCreateSession = pIDBCreateSession;
      HRESULT hr;
      if (NULL == m_pIDBCreateSession)
      {
	hr = E_POINTER;
      }
    // Create a session object 
    hr = m_pIDBCreateSession->CreateSession(NULL, IID_IOpenRowset, (IUnknown**) &pIOpenRowset);
}

CERecordset::~CERecordset()
{
    VariantClear(&rowsetprop[0].vValue);
    if (prgBinding)
    {
        CoTaskMemFree(prgBinding);
		delete [] prgBinding;
        prgBinding = NULL;
    }

    if (pDBColumnInfo)
    {
        CoTaskMemFree(pDBColumnInfo);
		delete [] pDBColumnInfo;
        pDBColumnInfo = NULL;
    }
	
    if (pStringsBuffer)
    {
        CoTaskMemFree(pStringsBuffer);
		delete [] pStringsBuffer;
        pStringsBuffer = NULL;
    }

     if (pData)
     {
        CoTaskMemFree(pData);
        delete pData;
        pData = NULL;
     }

     if(pIAccessor)
     {
          pIAccessor->ReleaseAccessor(hAccessor, NULL); 
          pIAccessor->Release();
          delete pIAccessor;
          pIAccessor = NULL;
     }
     if (pIColumnsInfo)
     {
	pIColumnsInfo->Release();
	delete pIColumnsInfo;
	pIColumnsInfo = NULL;
     }

     if(pIRowset)
     {
	pIRowset->Release();
	delete pIRowset;
	pIRowset = NULL;
     }

     if(pIOpenRowset)
     {
	pIOpenRowset->Release();
	delete pIOpenRowset;
	pIOpenRowset = NULL;
     }
}
BOOL CERecordset::Close()
{
       ulNumCols = 0 ;
       VariantClear(&rowsetprop[0].vValue);

    if (prgBinding)
    {
        CoTaskMemFree(prgBinding);
		delete [] prgBinding;
        prgBinding = NULL;
    }

    if (pDBColumnInfo)
    {
        CoTaskMemFree(pDBColumnInfo);
		delete []pDBColumnInfo;
        pDBColumnInfo = NULL;
    }
	
    if (pStringsBuffer)
    {
        CoTaskMemFree(pStringsBuffer);
		delete [] pStringsBuffer;
        pStringsBuffer = NULL;
    }

	if (pData)
	{
        CoTaskMemFree(pData);
		//delete pData;
		pData = NULL;
	}

	 if (pIAccessor) 
	 {
		  if (hAccessor)
			 pIAccessor->ReleaseAccessor(hAccessor, NULL) ;
		  pIAccessor->Release() ;
		  delete pIAccessor;
		  pIAccessor = NULL ;
	 }

	if (pIColumnsInfo)
	{
		pIColumnsInfo->Release();
		delete pIColumnsInfo;
		pIColumnsInfo = NULL;
	}
	 if (prghRows) 
	 {
		if (*prghRows)
			pIRowset->ReleaseRows(cRowsObtained, prghRows, NULL, NULL, NULL) ;
	 }
	 if (pIRowset) 
	 {
      pIRowset->Release() ;
      delete pIRowset;
	  pIRowset = NULL ;
   }

	if(pIOpenRowset)
	{
		pIOpenRowset->Release();
		delete pIOpenRowset;
		pIOpenRowset = NULL;
	}
	m_fIsOpen = false;
	return TRUE;
}
BOOL CERecordset::IsBOF()
{
	return m_BOF;
}

BOOL CERecordset::IsEOF()
{
	if(m_fIsOpen)
	{
		HRESULT hr = pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &prghRows);
		if(DB_S_ENDOFROWSET == hr)
			return TRUE;
		else
			return FALSE;
	}
	else
		return FALSE;
}

BOOL CERecordset::MoveNext()
{
	HRESULT hr = pIRowset->ReleaseRows(1, prghRows, NULL, NULL, NULL);
	if(FAILED(hr))
	{
		return FALSE;
	}
	// Fetches next row.
	hr = pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &cRowsObtained, &prghRows);
	return TRUE;
}

LPCTSTR CERecordset::GetFieldName(int iField)
{
	if(m_fIsOpen)
	{
		return (LPCTSTR)(pStringsBuffer[iField]);
	}
	return NULL;
}
LPCTSTR CERecordset::GetFieldValueString(int iField)
{
	memset(pData, 0, dwOffset);

	// Fetch actual data
	HRESULT hr = pIRowset->GetData(prghRows[0], hAccessor, pData);

	// If return a null value, ignore the contents of the value and length parts of the buffer.
	//
	if (DBSTATUS_S_ISNULL != *(DBSTATUS *)(pData+prgBinding[iField].obStatus))
	{
		return (WCHAR*)(pData+prgBinding[iField].obValue);
	}
	return NULL;
}
BOOL CERecordset::Delete()
{
	if(!m_fIsOpen)
	{
		return FALSE;
	}

	HRESULT		hr;

	hr = pIRowset->ReleaseRows(1, prghRows, NULL, NULL, NULL);

	return !FAILED(hr);
}

BOOL CERecordset::Update()
{
	 HRESULT hr = pIRowset->QueryInterface(IID_IColumnsInfo, (void **)&pIColumnsInfo);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// Get the column metadata 
	//
	if (pDBColumnInfo)
    {
        CoTaskMemFree(pDBColumnInfo);
        pDBColumnInfo = NULL;
    }
    hr = pIColumnsInfo->GetColumnInfo(&ulNumCols, &pDBColumnInfo, &pStringsBuffer);
	if(FAILED(hr) || 0 == ulNumCols)
	{
		goto Exit;
	}
	WCHAR** pwszEmployees = new WCHAR*[ulNumCols];
	for(int i=0; i < ulNumCols; i++)
	{
		pwszEmployees[i] = pDBColumnInfo[i].pwszName;
	}
	 // Create a DBBINDING array.
	//
	// dwBindingSize = sizeof(pwszEmployees)/sizeof(pwszEmployees[0]);
	dwBindingSize = ulNumCols;
	prgBinding = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING)*dwBindingSize);
	if (NULL == prgBinding)
	{
		hr = E_OUTOFMEMORY;
		goto Exit;
	}

	// Set initial offset for binding position
	//
	dwOffset = 0;
		// Prepare structures to create the accessor
	//
    for (dwIndex = 0; dwIndex < dwBindingSize; ++dwIndex)
    {
		if (!GetColumnOrdinal(pwszEmployees[dwIndex], &dwOrdinal))
		{
			hr = E_FAIL;
			goto Exit;
		}

		prgBinding[dwIndex].iOrdinal	= dwOrdinal;
		prgBinding[dwIndex].dwPart		= DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
		prgBinding[dwIndex].obLength	= dwOffset;                                     
		prgBinding[dwIndex].obStatus	= prgBinding[dwIndex].obLength + sizeof(ULONG);  
		prgBinding[dwIndex].obValue		= prgBinding[dwIndex].obStatus + sizeof(DBSTATUS);
		prgBinding[dwIndex].wType		= pDBColumnInfo[dwIndex].wType;
		prgBinding[dwIndex].pTypeInfo	= NULL;
		prgBinding[dwIndex].pObject		= NULL;
		prgBinding[dwIndex].pBindExt	= NULL;
		prgBinding[dwIndex].dwMemOwner	= DBMEMOWNER_CLIENTOWNED;
		prgBinding[dwIndex].dwFlags		= 0;
		prgBinding[dwIndex].bPrecision	= pDBColumnInfo[dwOrdinal].bPrecision;
		prgBinding[dwIndex].bScale		= pDBColumnInfo[dwOrdinal].bScale;

		switch(prgBinding[dwIndex].wType)
		{
		case DBTYPE_WSTR:		
			prgBinding[dwIndex].cbMaxLen = sizeof(WCHAR)*(pDBColumnInfo[dwOrdinal].ulColumnSize + 1);	// Extra buffer for null terminator 
			break;
		default:
			prgBinding[dwIndex].cbMaxLen = pDBColumnInfo[dwOrdinal].ulColumnSize; 
			break;
		}

		// Calculate the offset, and properly align it
		// 
		dwOffset = prgBinding[dwIndex].obValue + prgBinding[dwIndex].cbMaxLen;
		dwOffset = ROUND_UP(dwOffset, COLUMN_ALIGNVAL);
	}

	// Get IAccessor 
	//
	hr = pIRowset->QueryInterface(IID_IAccessor, (void**)&pIAccessor);
	if(FAILED(hr))
	{
		goto Exit;
	}

	// Create the accessor
	//
	hr = pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, 
									dwBindingSize, 
									prgBinding, 
									0, 
									&hAccessor, 
									NULL);
	if(FAILED(hr))
	{
		goto Exit;
	}
	pData = (BYTE*)CoTaskMemAlloc(dwOffset);
	if (NULL == pData)
	{
		hr = E_OUTOFMEMORY;
		goto Exit;
	}

	m_fIsOpen = true;	
	return true ;
	Exit:
    // Clear Variants
    //
	VariantClear(&rowsetprop[0].vValue);

  

    // Free allocated DBBinding memory
    //
    if (prgBinding)
    {
        CoTaskMemFree(prgBinding);
        prgBinding = NULL;
    }

    // Free allocated column info memory
    //
    if (pDBColumnInfo)
    {
        CoTaskMemFree(pDBColumnInfo);
        pDBColumnInfo = NULL;
    }
	
	// Free allocated column string values buffer
    //
    if (pStringsBuffer)
    {
        CoTaskMemFree(pStringsBuffer);
        pStringsBuffer = NULL;
    }

    // Free data record buffer
    //
	if (pData)
	{
        CoTaskMemFree(pData);
		pData = NULL;
	}

	// Release interfaces
	//
	if(pIAccessor)
	{
		pIAccessor->ReleaseAccessor(hAccessor, NULL); 
		pIAccessor->Release();
	}

	if (pIColumnsInfo)
	{
		pIColumnsInfo->Release();
	}

	if(pIRowset)
	{
		pIRowset->Release();
	}

	if(pIOpenRowset)
	{
		pIOpenRowset->Release();
	}
	m_fIsOpen = false;
	return m_fIsOpen;
}
BOOL CERecordset::GetColumnOrdinal( WCHAR* pwszColName, DWORD* pOrdinal)
{
	for(DWORD dwCol = 0; dwCol< ulNumCols; ++dwCol)
	{
		if(NULL != pDBColumnInfo[dwCol].pwszName)
		{
			if(0 == _wcsicmp(pDBColumnInfo[dwCol].pwszName, pwszColName))
			{
				*pOrdinal = pDBColumnInfo[dwCol].iOrdinal;
				return TRUE;
			}
		}
	}

	return FALSE;
}

so can anybody helps me to know what i am doing wrong. I really need to increase the perormance and thats why i am using OLEDB.

Thanks in advace

SQL Server Compact recreates the indexes on PPC device

Mon, 23 Nov 2009 22:31:17 Z

SQL Server Compact recreates the indexes on PPC device since the NLS version of Vista and PPC are different on first connection. Has this been resolved in 3.5 SP2? If not, is there any work around?

Best,

Steven Ellis

There was an error parsing the query

Mon, 23 Nov 2009 23:35:28 Z

I'm attempting to insert string data into SQL Server Compact Edition 3.5 from a .NET 3.5 C# application using SqlCeConnection, SqlCeCommand, and SqlCeParameter objects. I've followed the steps outlined by numerous examples and posts I've seen online, but nothing seems to work. Below is a snippet of code:
string strSQL = "INSERT INTO Clients(LastName, FirstName) " +
"VALUES(@LastName, @FirstName)";
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
SqlCeParameter paramLN = new SqlCeParameter("@LastName", SqlDbType.NVarChar, 60, "LastName");
paramLN.Value = this.mLastName; //NOTE: this.mLastName.Replace("'", "''") does NOT work!
cmd.Parameters.Add(paramLN);
//FirstName
SqlCeParameter paramFN = new SqlCeParameter("@FirstName", SqlDbType.NVarChar, 40, "FirstName");
paramFN.Value = this.mFirstName;
cmd.Parameters.Add(paramFN);
//Prepare the command
cmd.Prepare();
// INSERT Client Data
cmd.ExecuteNonQuery();

What am I missing?

Is it possible to use a class derived from CAccessor to bind columns in .sdf file?

Tue, 24 Nov 2009 00:15:41 Z

Hi. (I don't know english well. Please consider it. Thanks.)

I just want to use my old codes of MDB accessed in native C++.
But when I tried to use that code just with change of the connection string, I got an error message something like below.

____________________________________________________________________________
(with dbpropset)
OLE DB Error Record dump for hr = 0x80040e21
_CrtDbgReport: String too long or IO ErrorOLE DB Error Record dump end

(w/o dbpropset)
Binding entry 12 failed. Status: 2
OLE DB Error Record dump for hr = 0x80040e21
Row #: 0 Source: "Microsoft SQL Server Compact OLE DB Provider" Description: "Errors occurred. [,,,,,]" Help File: "(null)" Help Context: 0 GUID: {0C733A8C-2A1C-11CE-ADE5-00AA0044773D}
OLE DB Error Record dump end
----------------------------------------------------------------------------------------------------------

So I use a CDynamicAccessor class instead of CAccessor derived class, and I got the data. But it is too difficult and code is too long to get the data.
Is there an easy way to get the data? (I want to use CAccessor derived class if possible.)

Thanks.

Here's my code.
-------------------
CString strConnectionString = _T("Provider = Microsoft.SQLSERVER.CE.OLEDB.3.5; Data Source = D:\\Temp\\SQLCE_Test\\test.sdf");
CDataSource ds;
HRESULT hr = ds.OpenFromInitializationString( strConnectionString );
if ( SUCCEEDED( hr ) )
{
CSession session;
if ( SUCCEEDED( session.Open( ds ) ) )
{
    CDBPropSet dbPropSet( DBPROPSET_ROWSET );
    dbPropSet.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
    dbPropSet.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
    //dbPropSet.AddProperty( DBPROP_IRowsetChange, true, DBPROPOPTIONS_OPTIONAL );
    dbPropSet.AddProperty( DBPROP_UPDATABILITY,   DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE );

    CLucionDBv1_StudyTable table;
    //hr = table.Open( session, NULL, &dbPropSet );
    hr = table.Open( session, NULL, NULL );
    if ( SUCCEEDED( hr ) )
    {
     table.Close();
    }
    else
    {
     AtlTraceErrorRecords( hr );
    }
   }
}
}

Using SQL server compact edition 3.0 with Sql server 2008

Mon, 23 Nov 2009 18:59:40 Z

Is is possible to use sqlce 3.0 with sql server 2008? For example, would it be possible to install the sqlce 3.0 server tools on top of sql 2008 3.5 install in order to synchronize the handheld application? Upgrading all of the handheld apps in the field to 3.5 would be a very costly expense for my customers and I was wondering if there is a way to phase it in. Most of the handhelds in the field come with sqlece 3.0 pre installed and it does make sense to install 3.5 on top of that. Any experience or suggestions would be appreciated.

Compact Edition .NET application instal error on Vista (works on XP pro)

Mon, 23 Nov 2009 15:25:22 Z

Question what is the best way to use and install Compact Edition accross Windows Vista, XP and 7?

These are the files I install with the program so that the user will not be required to install SQL server Compact Edition 3.5

Application was developed on Microsoft .NET Framework 3.5 SP1 (Visual Studio 2008 Pro Edition Version 9.0.21022.9 RTM)
My connection and location code is this:
My.Settings.MyConnectionString = Data Source=|DataDirectory|\My.sdf;Password=MyPassword;Persist Security Info=True;mode='Read Write'

sqlceca35.dll

sqlcecompact35.dll

sqlceer35EN.dll

sqlceme35.dll

sqlceoledb35.dll

sqlceqp35.dll

sqlcese35.dll

My.Settings.MyConnectionStringAlt = Data Source=AltDirectory\My.sdf;Password=MyPassword;Persist Security Info=True;mode='Read Write'

Imports System.Data

Imports System.Data.Common

Imports System.Data.SqlServerCe

Imports System.IO

Imports System.Deployment.Application

Private _connection As SqlCeConnection

' I do this because when my application changes without changes to the database then the Compact Edition doesn't copy over 
Private Sub setupConnection()
        _connection = New SqlCeConnection
        _connection.ConnectionString = My.Settings.MyConnectionString.ToString()
        If Not System.IO.File.Exists(_connection.Database) Then
            Dim DataDirectory As String = AppDomain.CurrentDomain.GetData("DataDirectory").ToString
            Dim NewDataDirectory = DataDirectory + "History"
            Dim AltSetting As String = My.Settings.MyConnectionStringAlt
            Dim NewConnectionString As String = Replace(AltSetting, "AltDirectory", NewDataDirectory)
            _connection.ConnectionString = NewConnectionString
            Try
                System.IO.File.Copy(_connection.Database, DataDirectory + "\My.sdf")
            Catch ex As Exception

            End Try
        End If
    End Sub

Error Details:
Error in System.Data.SqlServerCe.dll
An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
Exception.Type: System.BadImageFormatException
Exception Source: System.Data.SqlServerCe
Declaring Type: System.Data.SqlServerCe.NativeMethods
Method: Int32 DllAddRef()
Stack Trace: at System.Data.SqlServerCe.NativeMethods.DllAddRef()

Operating System: Microsoft® Windows Vista™ Home Premium
Operating System Platform: Win32NT
OS Version: 6.0.6001.65536

Error Message

Mon, 23 Nov 2009 16:11:22 Z

How do I prevent the error message from continuously popping up on my computer?
"Windows Search.exe - unable to locate component. This application has failed to start because uncdms.dll was not found. Re-installing the application may fix the problem."

Determining if a SqlCeDataReader has returned data from a SELECT statement

Tue, 26 Sep 2006 17:52:48 Z

Hi all,

is there no a way to determine if a SqlCeDataReader has managed to return data (rows) from an executed SQL SELECT statement? I ask this because I have the following problem where I first need to determine if data (rows) are returned, if so, cycle through them and get the data out. But if I do the test to determine if data is returned (like I have in my code below) then the Reader.Read() is classed as reading a row, so when I do my 'while (Reader.Read())' this will then only work with data in the 2nd row.

if (Reader.Read())
{
    while (Reader.Read())
    {
        //Get data from rows
    }
}

I recall having this issue before as there doesn't seem to be a standard way of testing for data returned from a SELECT statement. I hope you can shed some light on this for me.

Thanks

Using Sql CE with a tool that uses dbo.TableName in the generated code

Fri, 21 Aug 2009 00:50:22 Z

I'm using SQL CE to test an application that will be deployed on SQL Server. (CE is very useful for that.)

I was originally using LinqToEntityFramework in the project but dropped that in favour of LinqToSQL (the older technology).

During test I'm getting Table does not exist exceptions because the generated code using the dbo.TableName syntax.

I can fix that in test by altering the generated code and everything just works.

I guess others have faced this issue.

What strategies work for you to use both CE and a production SQL Server database with the same code base?

SqlCeCommand.Dispose() Error

Fri, 20 Nov 2009 12:41:52 Z

My application use SQL CE 3.5(in the Visual Studio 2008).And The Application (of Mine) had Crashed Randomly.
This is the Stack Message :

NativeMethods.SafeRelease(IntPtr& ppUnkown)
SqlCeCommand.ReleaseNativeInterfaces()
SqlCeCommand.Dispose(Boolean disposing)
SqlCeCommand.Finalize()

And this is other message :

OffSet:		        0x008cdb0
Error Model:	        sqlceqp35.dll
Read:		        0x00000000
Exception Address : 	0x427bcdb0
Exception Code: 	0x00000007

For I'm a Chinese ,and the Itranslated the Message in English ,this is the original message if you can read Chinese:

偏移:		0x008cdb0
出错模块:	        sqlceqp35.dll
读取:		0x00000000
异常地址: 	0x427bcdb0
异常代码: 	0x00000007

I Have found a Link ,which said the Bug has been fixed in SQL CE 3.5.
Please Help Me,Thank You Very Much.

SQL Server Compact 3.5 vs clickonce

Sun, 22 Nov 2009 17:04:22 Z

Hello,

I develop a business application deployed through clickonce. Till now, we were using MSAccess as local DB but having read many times MSAccess is not the right tool for prod, we decided to migrate to SQL Server Compact 3.5. We are not yet using VS2010. We are still using VS2008.
I am struggling with 2 issues:
- having added SQL Server Compact 3.5 as new pre-requisite is not detected for upgrade. An uninstall is first necessary
- on Vista, even after uninstall/re-install, we get the error:System.DllNotFoundException: Unable to load 'sqlceme35.dll'
Could anyone help on this ? Could SQLCE 3.5 SP2 Beta help for this ? If yes, is it sensible to go in prod with this Beta version ?
Thanks

Michel

How to send a null value as a parameter?

Fri, 20 Nov 2009 14:22:40 Z

I am writing an application to retrieve some data via a third-party API, and consume the XML it provides and populate a SQL CE Database.

I have the following code:

SqlCeCommand InsertVol = new

 SqlCeCommand("insert into volumes(id, name, publisher, start_year)"

 + " values(@id, @name, @publisher, @start_year)"
, _Conn);


InsertVol.Parameters.Add(new 
SqlCeParameter("@id"
 , thisVol.ID));
InsertVol.Parameters.Add(new
 SqlCeParameter("@name"
 thisVol.Name));
InsertVol.Parameters.Add(new
 SqlCeParameter("@publisher"
, thisVol.Publisher));

InsertVol.Parameters.Add(new
 SqlCeParameter("@start_year"
, thisVol.Start_Year));
InsertVol.ExecuteNonQuery();

The data has some null values in the Publisher property, and when I attempt to execute the SQL, I get an error: "Parameterized query 'insert into volumes(id, name, publisher, start_year) values(@id, @name, @publisher, @start_year)' expects a parameter value which was not supplied.
Parameter name: @publisher"

The publisher field in my database is a nullable int, so a null value should be accepted.

I've also tried:

InsertVol.Parameters.Add(new
 SqlCeParameter("@publisher"
, (thisVol.Publisher != null
) ? thisVol.Publisher: DBNull.Value));

but that gives "Type of conditional expression cannot be determined because there is no implicit conversion between 'int?' and 'System.DBNull'"

How do I send this parameter in as null?

Thanks!

Gabe

Insufficient memory to continue the execution of the program

Thu, 07 Feb 2008 14:25:10 Z

I'm using VS'08, developing in VB.NET, using an SQL'05 DB and a local sync DB (3.5)

When attempting to append a record to the SQL CE DB's local sync'd table I get the error <<Insufficient memory to continue the execution of the program>>, the same record gets appended to the SQL'05 DB without error.

Ideas?

David L.

SQL Server CE database losing data

Thu, 12 Nov 2009 19:24:05 Z

I'm creating a desktop app in VB 2008 Express with SSCE 3.5 SP1. The .sdf database was originally created in Management Studio and frontloaded with data using insert queries. My app opens the database, populates some controls with data, accepts any changes and saves just fine. The app can be run any number of times with no problems. The data is all there. The trouble comes if I open the database in management studio. It never shows any of the updated data. Then when I run my app again, any data I added is gone but the original data is still there. I have a feeling that I'm just missing something and this is not a bug.

Any help would be appreciated.

SQL CE doesnt update

Thu, 19 Nov 2009 20:54:42 Z

Hi,
I am working on a small app with SQL CE DB and a form with datagridview binded to it,
when I insert a new record to the relevant table (through c# code) I see the new record in the datagridview
(after writing this.pilesTableAdapter.Fill(this.coalLocalDataSet.Piles);)

but when I look at the table data through VS server explorer I dont see the new record and it is also being deleted from the DB.

Am I doing something wrong here? (The same thing worked 100% with SQL Express)
Any clue is highly appreciated,
best regards,
Bastian

SqlCE 3.5SP1 Extremely slow database access

Fri, 13 Nov 2009 02:31:13 Z

Hi all,

I am currently stumped on the performance of my mobile device and was wondering if there was anyone that had any ideas as to why the database access is performing so poorly.

While developing a database access program for a Windows Mobile 6.1 device, I was encountering extremely horrendous database performance while just performing a simple table read.

I decided to write a very simple and basic test program to see if it was my code or the device itself.

I created a very basic database with the follow schema
- One table
- One Primary Key in table
- Table consists of 3 columns:
    - ID (primary key)
    - Name (Indexed)
    - Data (type int)
- One row in table

Using that database, I created a very basic mobile form to test the performance of simply accessing the database using basic SELECT statements

the entirely of the code is:

string query = @"SELECT * FROM TestingTable1 WHERE ( TestingTable1ID = 1 ) ";

Stopwatch sw = new Stopwatch();

using (SqlCeConnection connection = new SqlCeConnection(_connectionString))
{
connection.Open();

SqlCeCommand cmd = new SqlCeCommand();

cmd.Connection = connection;
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;

sw.Start();
SqlCeDataReader reader = cmd.ExecuteReader();
sw.Stop();

if (reader == null)
    WriteToTextbox("There was an error running query");
else
    WriteToTextbox(string.Format("The query finished in {0} milliseconds.", sw.ElapsedMilliseconds));
}

Using that code, I've been altering the Query string and clocking the time each query takes on the actual device where I actually measure (in milliseconds) the second run through in order to compensate for any code that must be Just-In-Time compiled.
Results:

Query for Name column (matching)
~ 13873 ms (second run through)

Query for Name column (mismatching)
~ 13816 ms (second run through)

Query for ID column (matching)
~ 13749 ms (second run through)

Query for ID column (mismatching)
~ 13931 ms (second run through)

With those results, my question is:
Is there something drastically wrong with my approach or is the device merely too underpowered to handle the platform?
Is there another more efficient way to access the database?

Platform:
.NetCF 3.5
SqlCE 3.5SP1
Windows Mobile 6.1 Professional
400-MHz Qualcomm MSM7500 processor
112MB of internal storage
84MB of total RAM
47MB free for user programs

Thanks in advance for any help you guys can give!

SQL Server Compact Edition Binaries

Thu, 29 Oct 2009 06:51:17 Z

I am maintaining an application which was deployed using ClickOnce. Its recent version required SQL CE; and we placed the SQL CE dlls along the application and every thing was working fine.

Now some people are reporting issues like at one place the user is getting the following exception. He has Visual Studio 2010 Beta 2 installed

File version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality.  This could be due to the presence of multiple instances of SQL Server Compact of different versions.  Please install SQL Server Compact binaries of matching version [ADO.NET Provider File Version = 8044, Native Binary File Version = 5692]
   at System.Data.SqlServerCe.NativeMethods.LoadValidLibrary(String modulePath, Int32 moduleVersion)
   at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()
   at System.Data.SqlServerCe.SqlCeConnection..ctor()
   at System.Data.SqlServerCe.SqlCeConnection..ctor(String connectionString)

At the other place the user is getting the following exception.

System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
   at System.Data.SqlServerCe.NativeMethods.DllAddRef()
   at System.Data.SqlServerCe.SqlCeConnection..ctor()
   at System.Data.SqlServerCe.SqlCeConnection..ctor(String connectionString)

How to bundle our application while keeping SQL CE dlls local to the application? It would be very hard to ask everyone to uninstall and reinstall with newer ClickOnce setup having package dependency on SQL CE

Khurram

Deployment Strategy for Schema Updates

Wed, 18 Nov 2009 17:11:02 Z

Hello,

I would like to hear from you what do you consider the best option for this specific scenario.

I must deploy a sqlce database on client computers, and eventually perform updates on the db schema when the user performs a version upgrade (keeping the user data).

I'm thinking about writting a custom installer class that will check for the current db version installed (using a simple table on db to keep the shema version) and perform the necessary updates acordingly to current version.

I would like to know if you think that I should keep the db creation scripts and update scripts "hard-coded" on my installer classes, or use a separated file (.sql or .sqlce)? If so, is there any way to get rid of this scripting files so the user will not know my db structure?

What do you think?

Regards,

Igor

Project Manager at INOVATIVA Tecnologia www.inovativatec.com

Getting Error Message "The 'Microsoft.SQLSERVER.CE.OLEDB.3.5' provider is not registered on the local machine."

Wed, 18 Nov 2009 02:33:23 Z

Hello, I have asked this question in another forum, but wasn't able to fully solve it. I have been developing a Windows Forms application using XP on a compute and I recently moved the project to a Windows 7 PC that is 64-bit. For some reason I cannot build or run the published version of the app on this new computer, however it works fine on another PC running the 32-bit version of Windows 7. Basically, I am having trouble isolating what the problem is. I have tried installing various version of SQL server compact/Express and had no luck getting it to run.

How secure is Encryption?

Wed, 18 Nov 2009 07:02:19 Z

Is there any known applications that can "recover" the password from an encrypted .sdf file?

The reason I ask is because I'm thinking of adding encryption on some of the more sensitive fields of some of the tables. Is it worth the effort, or is the built in encryption strong enough?

Regards

Magnus

ADOCE3.5?

Tue, 17 Nov 2009 15:16:49 Z

I have an old app that has ADOCE 3.1 Header files:

adoce31.h, many other files named adoce31: idl, tlb; etc, as well as oledb.h and oledb26x.h.

In my SQL Compact 3.5 SP1 directory, include directory, I only have the oledb stuff:

C:\>dir "c:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Include"
Volume in drive C has no label.
Volume Serial Number is 3CF6-5B57

Directory of c:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Include

08/27/2008 08:53 AM    <DIR>          .
08/27/2008 08:53 AM    <DIR>          ..
05/14/2008 02:28 PM           143,144 sqlce_err.h
05/14/2008 02:28 PM           567,433 sqlce_oledb.h
10/08/2007 12:08 PM           137,573 sqlce_sync.h
               3 File(s)        848,150 bytes

Is there ADOCE headers for SQL Compact 3.5?

Thanks.

help with the my Emulater

Tue, 17 Nov 2009 19:11:06 Z

hi all
i use pc 2003 Emulater with vstudio8, with Emulater option i give a shared folder, who can i reach the shared folder with my code program.
i see it in the Emulater as a Storage card but i can define it with my code...
help me pleace

SQL CE 3.5.1 unique index and null values conflict

Tue, 17 Nov 2009 17:47:06 Z

I have a clients table with an identity column as PK.

The table includes a column "passport number" that I have defined as unique and allow null true.

When I try to add a second record with the "passport number" null value I receive an error message:

No row was updated
The data in row 4 was not commited.
Error Source: SQL Server Compact ADO.NET Data provider.
Error Message: A Duplicate value cannot be inserted into a unique
index. [Table Name: Clients, Constraint name =
UQ__Clients_0000000000000038]

Correct the errors and retry or press ESC to cancel the change(s).

As far as I know this is a standard functionality of unique indexes (null values are not considered duplicate).

What can I do? Not all my clients will provide their passport number (hence, the need for the allow null true), but I need to enforce unique values for those who provide it (unique index).

Thank you for your help.