Problem with SQL Ce Connection

I have a strange problem while using sqlceconnection object. I'm using Sql Server 2005 Compact edition. While I'm trying to open the connection, an exception with blank message is raised, But if I open Query analyser on my windows ce 5.0 device and try to run the application again, it works(connection is getting opened)...Anyone faced this issue before?

Any help is greatly apreciated

JK789 wrote:

I have a strange problem while using sqlceconnection object. I'm using Sql Server 2005 Compact edition. While I'm trying to open the connection, an exception with blank message is raised, But if I open Query analyser on my windows ce 5.0 device and try to run the application again, it works(connection is getting opened)...Anyone faced this issue before?

Any help is greatly apreciated

I'm using VS2005 with SP1

|||Out of memory? When do you open your connection in your app (early or later)?|||

Hi,

Below is the code I'm using to open the connection.

Code Snippet

publicDataSet GetDataToPopulate(string sSql)

{

string sDataSource = ConfigurationSettings.AppSettings["db"].ToString();

SqlCeDataAdapter da = newSqlCeDataAdapter();

SqlCeCommand cmdSelect = newSqlCeCommand();

DataSet ds = newDataSet();

if(!System.IO.File.Exists(sDataSource))

throw(newApplicationException("Database not exists"));

SqlCeConnection cn=newSqlCeConnection("Data Source = " + sDataSource);

try

{

cn.Open();

cmdSelect.Connection = cn;

cmdSelect.CommandText = sSql;

cmdSelect.CommandType = CommandType.Text ;

da.SelectCommand = cmdSelect;

da.Fill(ds);

return ds;

}

catch(SqlCeException ex)

{

sError = ex.Message;

returnnull;

}

catch(Exception ex1)

{

sError = ex1.Message ;

returnnull;

}

finally

{

ds.Dispose();

ds.Dispose();

cmdSelect.Dispose();

if(cn.State == ConnectionState.Open )

cn.Close();

cn.Dispose();

}

}

|||You are creating a dataset. Is your table large, then the dataset will take a large amount of memory. In order to get the exact SQL CE error message, see this sample (getting ex.Message is not enough!) : http://msdn2.microsoft.com/en-us/library/ms174079.aspx|||

I am having the identical problem. Does anyone know of a fix or work around?

In the code below, open throws a blank error. I have tested the code with and without tables in the referenced database. If the path to the database file is wrong I get a specific error so it’s not a path issue. Any ideas?

private SqlCeConnection loadDatabase(string Path)
{
SqlCeConnection temp_cn = null;
try
{
temp_cn = new SqlCeConnection("Data Source=" + Path + "");
temp_cn.Open();
}
catch (SqlCeException sqlex)
{
foreach (SqlCeError sqlERROR in sqlex.Errors)
{
MessageBox.Show(sqlERROR.Message);
}
}
return temp_cn;
}

Thanks,

Matthew

|||

The error now produced from this code from the posting above is as follows:

Error Code: 8007000E

Message :

Minor Err: 0

Source: SQL Server Compact Edition ADO.Net Data Provider

private SqlCeConnection loadDatabase(string Path)

{

SqlCeConnection temp_cn = null;

try

{

temp_cn = new SqlCeConnection("Data Source=" + Path + "");

temp_cn.Open();

temp_cn.Close();

}

catch (SqlCeException e)

{

ShowErrors(e);

}

return temp_cn;

}

// Error handling routine that generates an error message

public static void ShowErrors(SqlCeException e)

{

SqlCeErrorCollection errorCollection = e.Errors;

StringBuilder bld = new StringBuilder();

Exception inner = e.InnerException;

if (null != inner)

{

MessageBox.Show("Inner Exception: " + inner.ToString());

}

// Enumerate the errors to a message box.

foreach (SqlCeError err in errorCollection)

{

bld.Append("\n Error Code: " + err.HResult.ToString("X"));

bld.Append("\n Message : " + err.Message);

bld.Append("\n Minor Err.: " + err.NativeError);

bld.Append("\n Source : " + err.Source);

// Enumerate each numeric parameter for the error.

foreach (int numPar in err.NumericErrorParameters)

{

if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);

}

// Enumerate each string parameter for the error.

foreach (string errPar in err.ErrorParameters)

{

if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);

}

MessageBox.Show(bld.ToString());

bld.Remove(0, bld.Length);

}

}

|||The error indicates an out of memory condition. Which device are you runnng on: Desktop, Pocket PC, Smartphone?

Problem with SQL Ce Connection

I have a strange problem while using sqlceconnection object. I'm using Sql Server 2005 Compact edition. While I'm trying to open the connection, an exception with blank message is raised, But if I open Query analyser on my windows ce 5.0 device and try to run the application again, it works(connection is getting opened)...Anyone faced this issue before?

Any help is greatly apreciated

JK789 wrote:

I have a strange problem while using sqlceconnection object. I'm using Sql Server 2005 Compact edition. While I'm trying to open the connection, an exception with blank message is raised, But if I open Query analyser on my windows ce 5.0 device and try to run the application again, it works(connection is getting opened)...Anyone faced this issue before?

Any help is greatly apreciated

I'm using VS2005 with SP1

|||Out of memory? When do you open your connection in your app (early or later)?|||

Hi,

Below is the code I'm using to open the connection.

Code Snippet

public DataSet GetDataToPopulate(string sSql)

{

string sDataSource = ConfigurationSettings.AppSettings["db"].ToString();

SqlCeDataAdapter da = new SqlCeDataAdapter();

SqlCeCommand cmdSelect = new SqlCeCommand();

DataSet ds = new DataSet();

if(!System.IO.File.Exists(sDataSource))

throw(new ApplicationException("Database not exists"));

SqlCeConnection cn=new SqlCeConnection("Data Source = " + sDataSource);

try

{

cn.Open();

cmdSelect.Connection = cn;

cmdSelect.CommandText = sSql;

cmdSelect.CommandType = CommandType.Text ;

da.SelectCommand = cmdSelect;

da.Fill(ds);

return ds;

}

catch(SqlCeException ex)

{

sError = ex.Message;

return null;

}

catch(Exception ex1)

{

sError = ex1.Message ;

return null;

}

finally

{

ds.Dispose();

ds.Dispose();

cmdSelect.Dispose();

if(cn.State == ConnectionState.Open )

cn.Close();

cn.Dispose();

}

}

|||You are creating a dataset. Is your table large, then the dataset will take a large amount of memory. In order to get the exact SQL CE error message, see this sample (getting ex.Message is not enough!) : http://msdn2.microsoft.com/en-us/library/ms174079.aspx|||

I am having the identical problem. Does anyone know of a fix or work around?

In the code below, open throws a blank error. I have tested the code with and without tables in the referenced database. If the path to the database file is wrong I get a specific error so it’s not a path issue. Any ideas?

private SqlCeConnection loadDatabase(string Path)
{
SqlCeConnection temp_cn = null;
try
{
temp_cn = new SqlCeConnection("Data Source=" + Path + "");
temp_cn.Open();
}
catch (SqlCeException sqlex)
{
foreach (SqlCeError sqlERROR in sqlex.Errors)
{
MessageBox.Show(sqlERROR.Message);
}
}
return temp_cn;
}

Thanks,

Matthew

|||

The error now produced from this code from the posting above is as follows:

Error Code: 8007000E

Message :

Minor Err: 0

Source: SQL Server Compact Edition ADO.Net Data Provider

private SqlCeConnection loadDatabase(string Path)

{

SqlCeConnection temp_cn = null;

try

{

temp_cn = new SqlCeConnection("Data Source=" + Path + "");

temp_cn.Open();

temp_cn.Close();

}

catch (SqlCeException e)

{

ShowErrors(e);

}

return temp_cn;

}

// Error handling routine that generates an error message

public static void ShowErrors(SqlCeException e)

{

SqlCeErrorCollection errorCollection = e.Errors;

StringBuilder bld = new StringBuilder();

Exception inner = e.InnerException;

if (null != inner)

{

MessageBox.Show("Inner Exception: " + inner.ToString());

}

// Enumerate the errors to a message box.

foreach (SqlCeError err in errorCollection)

{

bld.Append("\n Error Code: " + err.HResult.ToString("X"));

bld.Append("\n Message : " + err.Message);

bld.Append("\n Minor Err.: " + err.NativeError);

bld.Append("\n Source : " + err.Source);

// Enumerate each numeric parameter for the error.

foreach (int numPar in err.NumericErrorParameters)

{

if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);

}

// Enumerate each string parameter for the error.

foreach (string errPar in err.ErrorParameters)

{

if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);

}

MessageBox.Show(bld.ToString());

bld.Remove(0, bld.Length);

}

}

|||The error indicates an out of memory condition. Which device are you runnng on: Desktop, Pocket PC, Smartphone?

Problem with Returning a recordset from a stored procedure in ADO/VBScript

I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.

Here is my code:
<%
.....
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters.Refresh
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()

if not logRS.EOF then
.....
%>

My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.A good place to start looking is the ADO Connection Error collection. Check to see if Connect.Errors.Count > 0. If so, you will probably find your problem there.

Also, you can try adding SET NOCOUNT ON at the beginning of your SP, and SET NOCOUNT OFF at the end, before you return your recordset. Sometimes the command object stops asking for data when it gets the "X records affected" messages.

Finally, if that doesn't work, try being more explicit with your parameter naming. A good (and more readable) approach would be to use the CreateParameter function.

CreateParameter([Name As String], [Type As DataTypeEnum = adEmpty], [Direction As ParameterDirectionEnum = adParamInput], [Size As ADO_LONGPTR], [Value]) As Parameter

Assume your parameter is an INT named @.my_param

cmm.Parameters.Append cmm.CreateParameter("@.my_param",3,1, 4,client_id)

[Note: the values of DataTypeEnum and ParameterDriectionEnum can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaenumnz_2.asp ]

Hope this helps...|||Ahh. Thank you so much. It was the NOCOUNT property.

Problem with Replication

I keep getting an error when I try to set up a merge replication...
Cannot insert duplicate key row in object 'TRDFIL' with unique index
'ITRDFIL0'.
(Source: CMTSQL2 (Data source); Error number: 2601)
I can't seem to figure out how to prevent this from happening, any ideas on
what is not correct?
ThanksHow did you apply your snapshot?
Normally you get this error for one of two reasons.
1) You applied the snapshot manually, either by a restoring a backup, or a
data transfer process (DTS, bcp, etc). Your snapshot is out of sync, and you
are performaning an insert, update operation for which the data already
exists on the Subscriber.
2) Someone or some process has already entered data in the Subscriber, and
the replication process is trying to add the same row.
Use EM, expand your server, expand Replication Monitor, expand the
Publishers Folder, expand your publisher, right click on the publication,
select Validate Subscriptions.
After this process has run, double click on your distribution agent, select
session details to view the results of the data validation process.
"Atley" <atley_1@.homtmail.com> wrote in message
news:eCBH6bVCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> I keep getting an error when I try to set up a merge replication...
> Cannot insert duplicate key row in object 'TRDFIL' with unique index
> 'ITRDFIL0'.
> (Source: CMTSQL2 (Data source); Error number: 2601)
> I can't seem to figure out how to prevent this from happening, any ideas
on
> what is not correct?
> Thanks
>