"Duplicated parameter names are not allowed" exception in SQL Server Everywhere

I'm using SQL Server Everywhere CTP with ADO.NET and C# 2.0 and getting following exception:
"Duplicated parameter names are not allowed. [ Parameter name = @.NODE_ID ]"
for the query:

SELECT CHANGE_TYPE,CHANGED,VTRANS,LINK_PARTITION_ID,LINK_ID, SOURCE_PARTITION_ID,SOURCE_NODE_ID,TARGET_PARTITION_ID,TARGET_NODE_ID,TYPE,LOCAL_CHANGE_TIME,ATTR FROM VP8657b26964d4c595a7430761c222f3b3_REL WHERE (((TARGET_NODE_ID=@.NODE_ID AND REL_BASE_TYPE=2)) AND LOCAL_CHANGE_TIME > @.LOCAL_CHANGE_TIME ) OR ((SOURCE_NODE_ID=@.NODE_ID AND REL_BASE_TYPE=2)) ORDER BY LOCAL_CHANGE_TIME DESC

Without underlined frament, query runs fine

IDbCommand.Parameters shows that there are exactly 2 parameters with different names: @.LOCAL_CHANGE_TIME and @.NODE_ID

Exception stack trace is:
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() + 0xd5 bytes
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(System.Data.CommandBehavior behavior = Default, string method = "ExecuteReader", System.Data.SqlServerCe.ResultSetOptions options = None) + 0x145 bytes
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(System.Data.CommandBehavior behavior = Default) + 0x2d bytes
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(System.Data.CommandBehavior behavior = Default) + 0x1e bytes
System.Data.dll!System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() + 0xa bytes

So, what this exception might mean? does it mean that a parameter might not be used multiple times in a query?

alex777 wrote:

So, what this exception might mean? does it mean that a parameter might not be used multiple times in a query?

Unfortunately, that's precisely what it means. If you watch (with SQL Profiler, for example) how a query is run when you submit it with ADO, you'll see that it's fluffed and rumpled just a little bit -- in order to make parameters work. That fluff-and-rumple mechanism can't deal with the same parameter name being bound again.

You can rewrite your query to be a batch that assigns @.NODE_ID to a variable, then reference the variable in the query. Or, you can create @.NODE_ID1 and @.NODE_ID2, and spread those guys around in the query -- then bind the same variable in the client code to the two different values (that is, add two parameters, each with the same value as the other).

|||Thanks for your reply,

Is it an issue only with SQL Mobile (aka Everywhere) or this will also

be present in SQL Express and Full-Featured SQL Server or even in any

ADO.NET provider?

> You can rewrite your query to be a batch that assigns @.NODE_ID to a variable, then reference the variable in the query.

SQL Mobile seems not to support batch queries (maybe executing multiply commands within the same connection will work, but it's a pretty mess. )

Anyway, I'm going just to substitue this parameter manually:
com.CommandText = com.CommandText.Replace("@.NODE_ID", "'"+guid.ToString()+"'");

"Could not find installable ISAM"

Hi, when l use the ADO.NET with the following query,
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

i get the following error, i.e.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')" failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task

Any idea, what's wrong?

Regards,
YB Lim

I read before... a fix for that could be to reinstall MDAC.

http://support.microsoft.com/default.aspx/kb/283881

http://support.microsoft.com/default.aspx/kb/209805

http://support.microsoft.com/default.aspx/kb/90111

"[DBNETLIB]ConnectionWrite (send())" && "Communication link failure"

Hi ALL

I am facing a strange problem using ADO to connect Sql Server that I can not resolve it.

My program connected to SqlServer when it started, the state of the connectionPtr was adoStateOpen.Then I disable the network card and enable it immeditately, the errors happened when I executed the sql query probably because of connectionPtr was invalid,but the state of connectionPtr was always adoStateOpen.

1)Msg: Unspecified error Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

2)Msg: Unspecified error Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver]Communication link failure.

Should I reconnect to SqlServer?How can I catch this error when the connection is valid except other errors?

Sorry,my english is poor and thanks for your response.

Thanks a lot.

Env:

1)OS : Windows 2000 Server

2)SqlServer:Sql Server 2000

When you disable network card, the OS may inform network layer to invalidate the socket we are using. The problem you described could happen, and we don't support this scenario.

Btw, why do you want to disable network card?

"[DBNETLIB]ConnectionWrite (send())" && "Communication link failure"

Hi ALL

I am facing a strange problem using ADO to connect Sql Server that I can not resolve it.

My program connected to SqlServer when it started, the state of the connectionPtr was adoStateOpen.Then I disable the network card and enable it immeditately, the errors happened when I executed the sql query probably because of connectionPtr was invalid,but the state of connectionPtr was always adoStateOpen.

1)Msg: Unspecified error Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

2)Msg: Unspecified error Source: Microsoft OLE DB Provider for ODBC Drivers Description: [Microsoft][ODBC SQL Server Driver]Communication link failure.

Should I reconnect to SqlServer?How can I catch this error when the connection is valid except other errors?

Sorry,my english is poor and thanks for your response.

Thanks a lot.

Env:

1)OS : Windows 2000 Server

2)SqlServer:Sql Server 2000

When you disable network card, the OS may inform network layer to invalidate the socket we are using. The problem you described could happen, and we don't support this scenario.

Btw, why do you want to disable network card?

<undefined value>

Greetings,

I asked this question over on the ADO.NET newsgroup and couldn't scrape
up an answer. I realize that it is more of an ADO question than an SQL
Server question, but I'm hoping there might be an ADO programmer here
that can explain this to me.

I'm developing database applications using C# on VS.NET 2003 and SQL
Server Standard edition (SP3a).

I've run into a situation I'm trying to understand, to wit, if I submit
a query using SqlCommand.ExecuteScalar which returns no results, why is
the returned item a System.Object of <undefined value>?

(Actually, I think I know why -- ExecuteScalar returns a null reference
if there are no results.)

How do I do test for that condition?

I guess I can sort of see why they didn't want to throw an exception --
lot's of queries don't return any results, but on the other hand, I
can't figure out how to test for <undefined value>, either.

Any ideas?

-- Rick> How do I do test for that condition?

Object myScalarResult = myCommand.ExecuteScalar();
if ( myScalarResult == null )
{
// no result returned
}

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Guinness Mann" <GMann@.dublin.com> wrote in message
news:MPG.19edf588afa984d79896f5@.news.newsguy.com.. .
> Greetings,
> I asked this question over on the ADO.NET newsgroup and couldn't
scrape
> up an answer. I realize that it is more of an ADO question than an
SQL
> Server question, but I'm hoping there might be an ADO programmer here
> that can explain this to me.
> I'm developing database applications using C# on VS.NET 2003 and SQL
> Server Standard edition (SP3a).
> I've run into a situation I'm trying to understand, to wit, if I
submit
> a query using SqlCommand.ExecuteScalar which returns no results, why
is
> the returned item a System.Object of <undefined value>?
> (Actually, I think I know why -- ExecuteScalar returns a null
reference
> if there are no results.)
> How do I do test for that condition?
> I guess I can sort of see why they didn't want to throw an
exception --
> lot's of queries don't return any results, but on the other hand, I
> can't figure out how to test for <undefined value>, either.
> Any ideas?
> -- Rick|||In article <pV4hb.9598$mQ2.8310@.newsread1.news.atl.earthlink.n et>,
danguzman@.nospam-earthlink.net says...
> > How do I do test for that condition?
> Object myScalarResult = myCommand.ExecuteScalar();
> if ( myScalarResult == null )
> {
> // no result returned
> }

Sheesh. Simple enough once you know the answer, eh? Thanks!

Here's what I've been doing:

Object myScalarResult = myCommand.ExecuteScalar();
int returnValue = Convert.ToInt32(myScalarResult);

Which returns 0 if null, otherwise the Int32 I'm looking for.
Fortuitously, 0 is not in the possible solution set, so by coincidence,
it works.

Thanks again,

-- Rick