error: 25 - Connection string is not valid -- HELP Please!

I have been having a problem trying to connect to a SQL Server. I have installed the Developer edition on an MS Small Business edition 2003 server

I also installed MS Sql Manager and I was able to create a database and connect to it. The database server is in the same PC. I used the surface configuration to enable remote connections using TCP /IP and pipe lines.

My application runs without a problem on my development machine but when I deployed on this server I get theprovider: SQL Network Interfaces, error: 25 - Connection string is not valid Error.

this is my config file setup

<connectionStrings>
<add name="Diamond_dbConnectionString" connectionString="Data Source=192.168.1.104\MSSQLSERVER;Initial Catalog=Diamond_db;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

I have google and yahoo this error I found a lot of information I've tried many of them but still having the problem. I will appreciate your help solving this problem. I have a customer waiting since last week to see this site and I'm still stuck.

Tia

Charles

PS. Below I pasted the complete error info I get.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) +162 System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) +107 DiamondTableAdapters.COUNTRIES_TEMPLATETableAdapter.GetContriesTemplate() +108[TargetInvocationException: Exception has been thrown by the target of an invocation.] System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0 System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +296 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29 System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +482 System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2040 System.Web.UI.WebControls.BaseDataList.GetData() +53 System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +284 System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +56 System.Web.UI.WebControls.BaseDataList.DataBind() +72 System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +55 System.Web.UI.WebControls.BaseDataList.CreateChildControls() +63 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

Do you have ISA running on the server? Can you connect through Server Manager to the server using the same credentials?

Jeff

|||

Jeff,

I'm using EMS SQL Manager for SQL Server

http://sqlmanager.net/products/mssql/manager/

to create and manage my SQL server. I can connect to the server using windows credentials but when I try with the ASP application I can't

Thanks

Charles

|||

Is the ASP.NET app using the same Windows account to access the database, or is it using the ASP.NET process account?

Jeff

|||

Jeff,

Honestly I'm very green regarding ASP.NET and MS-SQL Server

When I installed the server I asigned the Windows account to gain access to the SQL Server. When I created the database in question I also used the Windows Account.

When I registered the database with MS Server manager tool I also used the same Windows Account.

How can I check If I'm using the ASP.NET process account instead of the Windows Account?

I will appreciate your inputs.

Tia

Charles

|||

The ASP.NET process account, unless you have set it or changed it, is ASPNET or NETWORK SERVICE depending on operatiing system. It is unlikely you chose this account for access to SQL. But normally you would get a mdifferent error if that was the problem.

Take a look at connectionstrings.com for some possibilities in changing your connection string. You may want to use a standard authentication with SQL user and password rather than rely on a trusted connection.

Is your SQL on a different server than the web server? I wouldn't think so but you use the IP address of the SQL server in your connection. You should just need to enable TCP/IP, which you said you've done.

Jeff

|||Are you sure the MSSQLSERVER is the name of the named (not default) instance? It seems to be the SQL service name of default instance. If you're trying to connect to a default SQL instance on a remote server, just set the Data Source to the IP address of the remote server is ok.sql

error: 17194

I cannot use sql connection. This is my connection string

'Conn.Open "Provider=SQLNCLI;Database=dbname;Server=.\\MSSQLSERVER;UID=username;PWD=test123;"

The error message appears on browser is

Microsoft SQL Native Client error '80004005'

Client unable to establish connection due to prelogin failure

/Connect.asp, line 8

in SQL server ERRORLOG, it shows
Error: 17194, Severity: 16, State: 1.
The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications....

How can I fix this error?

thanks

Are you using SQL Express DB? Normally, if u did install it with default settings the instance name is COMPUTERNAME\SQLEXPRESS

Second, assuming that your Server=.\\MSSQLSERVER is correct. Go to Management Studio and check if the UID has permission to login.

Third, check your Provider=SQLNCLI; if this is correct.

Cheers,

Glenn

|||In SSCM, it shows
SQL Server 2005 services:
- SQL Server Browser
- SQL Server (MSSQLSERVER)

So, I think the instance name is MSSQLSERVER

About the login, I am using Windows Authentication.
So,
- created an account in Computer Management
- add that account in SQL Express DB by Management Studio

what else do I need to do?

about the connection, I am using OLEDB, so I chose Provider=SQLNCLI. How can I check if it's error or not?

thanks

|||One thing I have noticed: when going to command promt, use this command
sqlcmd -S <servername> <<< this command is working

but
sqlcmd -S <servername>\MSSQLSERVER <<< this is not working. the error message is

"HResult 0x57, Level 16, State 1
SQL Network Interfaces: Connection string is not valid [87].
Sqlcmd: Error: Microsoft SQL Native Client: An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Sqlcmd: Error: Microsoft SQL native Client : Login timeout expired"

Followed the below help without any luck
http://support.microsoft.com/default.aspx?scid=kb;en-us;914277

|||

Since you have done the KB atricle http://support.microsoft.com/default.aspx?scid=kb;en-us;914277 try to restart your workstation.

In your workstation, are you only using SQL Express 2005 or their is an existing MSDE 2000 or SQL 2000 ?

Go To SQL Server Configuration Manager make sure TCP-IP is enabled

Again go management studio--> Security-> Logins --> go to your windows user added previously and invoke its properties go to user mappnig to have acces on the database

Go to management studio --> security--> logins --> go to your windows user added previously and invoke its properties go to status and choose login--> enabled

|||Glenn,

I have switched authentication mode to mixed mode, then add a new sql user, and the db is working fine :) Not sure why windows authentication mode is not working, even I have done all above steps :(

anyways, thanks a lot for your time :)

cheers,

|||

Hi again...

Could you try this connection string

Data Source=SQLEXPRESSINSTANCENAME;database=YOURDATABASENAME;Integrated Security=True;

I guess this work this time. In my understanding using UID and PWD is for SQL user.

To check if the problem is really in your connection string or SQL Express, try to go to management studio and choose windows authentication mode if you can get through.

P.S. Hung Pham mind if i asked your msn id so that i can add you.

Cheers,

Glenn

Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the cha
racter string that I'm creating is giving me an error saying can't convert d
atetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtri
m(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where
b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (th
is is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output
'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date
time nothing seems to work. I can't even put quotes around it because it r
efuses to run without it.You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.|||You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the c
haracter string that I'm creating is giving me an error saying can't convert
datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd
) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1
= m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time valu
e)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'en">
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time
nothing seems to work. I can't even put quotes around it because it refuses to run
without it.

Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.|||You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error xp_cmdshell

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.
You have to explictly convert your 2 parameters into a character format
using the CONVERT function if you want to get them into the string.
Herbert
"D" <D@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0B41DF21-B79F-495A-8FE7-6BE5A484FAB4@.microsoft.com...
> I'm having the hardest problem. My input values are of datetime and the
character string that I'm creating is giving me an error saying can't
convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' +
rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m
where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and
date (this is a date time value) between ' + (date time value) + ' and ' +
end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is
date time nothing seems to work. I can't even put quotes around it because
it refuses to run without it.
|||You only need to post this question once.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the cha
racter string that I'm creating is giving me an error saying can't convert d
atetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtri
m(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where
b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (th
is is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output
'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date
time nothing seems to work. I can't even put quotes around it because it r
efuses to run without it.You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the c
haracter string that I'm creating is giving me an error saying can't convert
datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd
) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1
= m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time valu
e)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'en">
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time
nothing seems to work. I can't even put quotes around it because it refuses to run
without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.You only need to post this question once.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error with xp_cmdshell command

I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
Thanks in advance for your help.
set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value) be
tween ' + (date time value) + ' and ' + end date time value + ''', no_output'
Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.
You only need to post this question once.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
D wrote:
> I'm having the hardest problem. My input values are of datetime and the character string that I'm creating is giving me an error saying can't convert datetime to char. Is what I'm trying to do possible?
> Thanks in advance for your help.
> set @.dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sservername -E -o ' + rtrim(@.cmd) + ' -Q" set nocount off;select * from table_one b, table_two m where b.table_one_1 = m.table_two_2 and b.table_one_1= m.table_two_2 and date (this is a date time value)
between ' + (date time value) + ' and ' + end date time value + ''', no_output'
> Because @.dcmd is of varchar and the parms that I'm trying to send it is date time nothing seems to work. I can't even put quotes around it because it refuses to run without it.

Error with conn string

Just installed AJAX for VS2005 and started to build a AJAX enabled web site. I got the following message after creating a database (SQL) and Dataset "FOLLOWING ERROR OCCURED WHLE GETTING CONNECTION STRING INFORMATION FROM CONFIGURATION:'CANNOT GET WEB APPLICATION SERVICE'.

I didn't have this problem before,can it be related to AJAX?

Thank you in advance,

close your VS 2005 and open it again The SQLConnection error might go away.

Error with a simple JOIN query....

Hi!

I've a big problem by using the following query :

Code Snippet

public SqlCeResultSet selectRSQuery(String query)

{

SqlCeResultSet resultSet = initializeCommand(query).ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

return resultSet;

}

SqlCeResultSet resultSet = sgb.selectRSQuery(

"SELECT p.pId, p.pLogin FROM Profiles p, ProfilesGroups pg, Groups g " +

"WHERE g.gId = pg.tpGroupId " +

"AND p.pId = pg.tpProfileId " +

"AND g.gProfileID = '" + app.Settings.Default.id + "'");

It return me this error :

Cannot generate an updatable cursor for the query because there is a non-standard join expression.

What can I do?

Thxx

Try using ANSI joins?

public SqlCeResultSet selectRSQuery(String query)

{

SqlCeResultSet resultSet = initializeCommand(query).ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

return resultSet;

}

SqlCeResultSet resultSet = sgb.selectRSQuery(

"SELECT p.pId, p.pLogin

FROM Profiles p

inner join ProfilesGroups pg on pg.tpProfileID = p.pld

inner join Groups g on g.gld = pg.tpProfileId" +

"WHERE g.gProfileID = '" + app.Settings.Default.id + "'");

|||

hello!

I tried it... and it returns me :

Cannot generate an updatable cursor for the query because there is no updatable column.

thx for help

|||

re

if I remove the Options of the resultset.... It works..... but then It says me :

This operation is not valid because the cursor is not scrollable.

when I bind the resultset to my ListView :

lstViewTest.DataContext = resultSet;

edit : Here is the explanation of the problem :

Forward-only/Read-only Cursors

Forward-only/read-only cursors, referred to as forward-only cursors in earlier versions of SQL Server Compact Edition, are the fastest cursors, but cannot be updated.

The following is an example of how to obtain a forward-only/read-only cursor by using ADO.NET:

cmd.CommandText = "Select * from tablename";

SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.None);

Note You cannot create read-only cursors on query that returns only read only columns because internally all SQL Server Compact Edition cursors are updatable. SQL Server Compact Edition cannot update read-only columns returned in the SqlCeResultSet. Therefore, it would fail with the error "Cannot generate an updatable cursor for the query because there is no updatable column."

|||

how can I fill my ListView without using a ResultSet ?

thx...

|||ExecuteReader returns a DataReader that can be fed to the DataTable.Load. Either that or create a DataAdapter and use the Fill method to create a DataTable. Bind the DataTable to the list.|||

Hi!

I decided to use ObservableCollection! I load my objects with a sqlcedatareader in a ObservableCollection, and then bind it in xaml mode!

The disadvantage of this method is that I lost "the design preview" in Blend

Here is my code :

Code Snippet

public class ProfileI : INotifyPropertyChanged

{

private String _id;

private String _profileInfoID;

private String _login;

private String _password;

public ProfileI() { }

public ProfileI(string id, string profileInfoID, string login)

{

Id = id;

ProfileInfoID = profileInfoID;

Login = login;

}

public ProfileI(string id, string profileInfoID, string login, string password)

{

Id = id;

ProfileInfoID = profileInfoID;

Login = login;

Password = password;

}

#region Properties Getters/Setters

public String Login

{

get

{

return _login;

}

set

{

if (value != "")

{

if (value.Length < 5)

{

throw new ArgumentException(app.Resources.errorLoginTooSmall);

}

_login = value;

OnPropertyChanged("Login");

}

}

}

public String Password

{

get

{

return _password;

}

set

{

if (value != "")

{

if (value.Length < 5)

{

throw new ArgumentException(app.Resources.errorLoginTooSmall);

}

_password = value;

OnPropertyChanged("Password");

}

}

}

public string ProfileInfoID

{

get

{

return _profileInfoID;

}

set

{

_profileInfoID = value;

OnPropertyChanged("ProfileInfoID");

}

}

public string Id

{

get

{

return _id;

}

set

{

_id = value;

OnPropertyChanged("Id");

}

}

#endregion

public event PropertyChangedEventHandler PropertyChanged;

protected void OnPropertyChanged(String propertyName)

{

if (this.PropertyChanged != null)

PropertyChanged(this, new PropertyChangedEventArgs(propertyName));

}

}

public class myProfilesI :

ObservableCollection<ProfileI>

{

public myProfilesI()

{

// On r?cup?re les "profiles-amis" de l'utilisateur

SingleDatabase sd = SingleDatabase.getInstance();

SqlCeDataReader sdr = sd.selectDRQuery(

"SELECT p.pId, p.pProfileInfoID, p.pLogin FROM Profiles p, ProfilesGroups pg, Groups g " +

"WHERE g.gId = pg.tpGroupId " +

"AND p.pId = pg.tpProfileId " +

"AND g.gProfileID = '" + app.Settings.Default.id + "'");

while (sdr.Read())

{

Add(new ProfileI(sdr["pId"].ToString(), sdr["pProfileInfoID"].ToString(), sdr["pLogin"].ToString()));

}

}

}

And my XAML file :

Code Snippet

<Window

xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

xmlns:d="http://schemas.microsoft.com/expression/blend/2006"

xmlns:ToDo="clr-namespace:ToDo"

x:Class="ToDo.ToDoConfig"

x:Name="Window"

Title="ToDoConfig"

Width="640" Height="480">

<Window.Resources>

<ObjectDataProvider x:Key="MyProfilesInfoDataSource"

ObjectType="{x:Type ToDo:myProfilesI}"/>

</Window.Resources>

<Grid x:Name="LayoutRoot">

<Grid.RowDefinitions>

<RowDefinition Height="0.169*"/>

<RowDefinition Height="0.831*"/>

<RowDefinition Height="30"/>

</Grid.RowDefinitions>

<ToDo:Footer HorizontalAlignment="Stretch" Margin="0,0,0,0" VerticalAlignment="Stretch" Width="Auto" Height="Auto" Grid.Row="2"/>

<ToDo:Header HorizontalAlignment="Stretch" Margin="0,0,0,0" VerticalAlignment="Stretch" Grid.RowSpan="1"/>

<ListView ItemsSource="{Binding Source={StaticResource MyProfilesInfoDataSource}}" IsSynchronizedWithCurrentItem="True" Grid.Row="1">

<ListView.View>

<GridView>

<GridViewColumn DisplayMemberBinding=

"{Binding Path=Login}"

Header="Login" Width="100"/>

</GridView>

</ListView.View>

</ListView>

</Grid>

</Window>

Also note that it's not possible to use construtors like this :

Code Snippet

public ProfileI(string id, string profileInfoID, string login)

: this(id, profileInfoID, login, null)

{}

public ProfileI(string id, string profileInfoID, string login, string password)

{

Id = id;

ProfileInfoID = profileInfoID;

Login = login;

Password = password;

}

If you have a solution to conserve the design in Blend, I will be happy .

+++

Error whilst concatenating field to a string

Hi,

I want to say on a report

"We are XX% Through the month" on a report. I added the data to the data set that I want to show and linked this to a table. I tried saying:

"We are" + Field!PercentageHoursWorked.Value + "% Through The Month" but I am getting the error:

"Input string was not in the correct format" The output that gives me this number is numeric.

Any ideas?

You have to use vb.net - code!

vb concatenates Strings using &

|||Yes, I know. I got there in the end by using convert in my SP

error while saving in text datatype

HI,

I've a table with 5 fields datatype as text. I get error like this when i save huge data (like letter format)

String or binary data would be truncated.
The statement has been terminated.

Is text a better datatype to store huge data. What are the problems i might get with this. is there any other better datatype other than varchar(8000).

thanks

venp--

HI,

I'm planning to try with varchar(max) datatype.

venp--

Error while making connection

hi

I am not able to create connection string through vwd express for sql express database file n getting a error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

plz tell me where is the problem n how can i resolve it

Thanks n with regards
CN

You could try searching for "Error: 26 - Error Locating Server/Instance Specified" in this forum , or you could look at some troubleshooting help in this blog post:Troubleshoot Connectivity Issue with SQL Server Express 2005.

Error while executing a a query string using EXEC statement

Hi,

I have written a stored proc to bulk insert the data from a data file.

I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query

Code Snippet

DECLARE @.LastUpdate varchar(20)

SET @.LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category

SELECT MSISDN, @.LastUpdate FROM OPENROWSET( BULK '\\remotemachine\datafile.txt',

FORMATFILE = '\\remotemachine\FormatFile.fmt',

FIRSTROW = 2) AS a

To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below

Code Snippet

@.Category - Will be passed as a parameter to the stored proc

DECLARE @.vsBulkSQL VARCHAR(MAX)

DECLARE @.LastUpdate varchar(20)

SET @.LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @.vsBulkSQL ='INSERT INTO '+ @.Category + ' SELECT MSISDN, ''' + @.LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\\remotemachine\datafile.txt'+ ''''+ ' ,' +

+ ' FORMATFILE ' + '=' + ''''+ '\\remotemachine\FormatFile.fmt'+ ''''+ ',' +

' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @.vsBulkSQL - This prints the folliwing statement

INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\\remotemachine\DataFile.txt' , FORMATFILE ='\\remotemachine\FormatFile.fmt', FIRSTROW =2) AS a

Exec @.vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\\remotemachine\Second.txt' , FORMATFILE ='\\remotemachine\FormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.

Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

Does it work if you alias the @.LastUpdate value?

|||

I suspect you need to execute the query with

EXEC (@.vsBulkSQL)

You have left out the parentheses, and when you do that, EXEC expects the variable to hold a procedure name, not a query string.

Steve Kass

Drew University

www.stevekass.com

|||

Steve,

Good catch. Its working now if i have the string etween ( and ).

Thanks a lot

~Mohan