Problem with SQL SQLCECommand - Missing Parameter

Have a nice day. I'm developing for PocketPC using VB2005 and SQLMobile and need some help to solve the following issue:

At runtime the following code throws an exception #25950 SSCE_M_QP_MISSINGPARAMETER

Missing Parameter [Parameter ordinal = 1]

Everything looks fine, but it still fails. In the same app I have a datagrid connected via a dataset and it works OK. Please give me directions!!

Dim conn As SqlCeConnection = Nothing

Try

conn = New SqlCeConnection("Data Source =" & _

(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "\db.sdf;"))

conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand

cmd.CommandText = " INSERT t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.ToString)

Finally

conn.Close()

End Try

Shouldn't it be "INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "|||I first tried with it, but still raises the error. I've tried with all the possible syntax of the INSERT clause with the same result. Not yet resolved.|||

Hi ELugoL,

The Sql statement is supported to be : INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT').

Could you please debug step by step to see which line raise this exception? It seems like it may be other issue other than sql statement.

Thanks,

Zero Dai - MSFT

|||

Hi Zero Dai, have a nice day.

Running the code on debug mode throws the exception in the instruction

cmd.ExecuteNonQuery()

and the exception details are:

HResult: -2147217904

Message: "Parameter missing. [ Parameter ordinal = 1 ]"

NativeError:25950

Source: "SQL Server 2005 Mobile Edition ADO.NET Data Provider"

StackTrace:

en System.Data.SqlServerCe.SqlCeCommand.ProcessResults()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
en SvitaMovil2.frmDetalleCliente.cmdGuardar_Click()
en System.Windows.Forms.Control.OnClick()
en System.Windows.Forms.Button.OnClick()
en System.Windows.Forms.ButtonBase.WnProc()
en System.Windows.Forms.Control._InternalWnProc()
en Microsoft.AGL.Forms.EVL.EnterMainLoop()
en System.Windows.Forms.Application.Run()
en SvitaMovil2.frmSplash.Main()

I will appreciate any help you can provide to me. Thanks in advance.

Edgar Lugo L.

|||

Hi Edgar,

Sorry for delaying reply.

After trying several times, I still cannot reproduce your issue. The code and SQL statement SHOULD be right with no exception thrown when executing. So, could you please check if the field in your sql is correct? Or is it the right table name?

And I move to Sql Server 2005 Compact forum, where increases the chances for getting your question answered.

Thanks,

Zero Dai - MSFT

|||delete the "[" "]" symbols from the query and thats all

Problem with SQL SQLCECommand - Missing Parameter

Have a nice day. I'm developing for PocketPC using VB2005 and SQLMobile and need some help to solve the following issue:

At runtime the following code throws an exception #25950 SSCE_M_QP_MISSINGPARAMETER

Missing Parameter [Parameter ordinal = 1]

Everything looks fine, but it still fails. In the same app I have a datagrid connected via a dataset and it works OK. Please give me directions!!

Dim conn As SqlCeConnection = Nothing

Try

conn = New SqlCeConnection("Data Source =" & _

(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.GetName.CodeBase) + "\db.sdf;"))

conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand

cmd.CommandText = " INSERT t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.ToString)

Finally

conn.Close()

End Try

Shouldn't it be "INSERTINTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT') "|||I first tried with it, but still raises the error. I've tried with all the possible syntax of the INSERT clause with the same result. Not yet resolved.|||

Hi ELugoL,

The Sql statement is supported to be : INSERT INTO t_People ([txt_Name], [txt_LastName]) VALUES ('CLARK', 'KENT').

Could you please debug step by step to see which line raise this exception? It seems like it may be other issue other than sql statement.

Thanks,

Zero Dai - MSFT

|||

Hi Zero Dai, have a nice day.

Running the code on debug mode throws the exception in the instruction

cmd.ExecuteNonQuery()

and the exception details are:

HResult: -2147217904

Message: "Parameter missing. [ Parameter ordinal = 1 ]"

NativeError:25950

Source: "SQL Server 2005 Mobile Edition ADO.NET Data Provider"

StackTrace:

en System.Data.SqlServerCe.SqlCeCommand.ProcessResults()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
en System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
en SvitaMovil2.frmDetalleCliente.cmdGuardar_Click()
en System.Windows.Forms.Control.OnClick()
en System.Windows.Forms.Button.OnClick()
en System.Windows.Forms.ButtonBase.WnProc()
en System.Windows.Forms.Control._InternalWnProc()
en Microsoft.AGL.Forms.EVL.EnterMainLoop()
en System.Windows.Forms.Application.Run()
en SvitaMovil2.frmSplash.Main()

I will appreciate any help you can provide to me. Thanks in advance.

Edgar Lugo L.

|||

Hi Edgar,

Sorry for delaying reply.

After trying several times, I still cannot reproduce your issue. The code and SQL statement SHOULD be right with no exception thrown when executing. So, could you please check if the field in your sql is correct? Or is it the right table name?

And I move to Sql Server 2005 Compact forum, where increases the chances for getting your question answered.

Thanks,

Zero Dai - MSFT

Problem with SQL Server 2005 Express Edition

Ok, I've read almost a million ng postings on this, still I can't solve it.
This place is my final hope.
I'll start from the beginning: I have a Windows application developed in C#.
It uses a SQL Server 2005 Express Edition database to store data. Now I have
created a deployment of this app and database, and I try to install it on my
own machine. No problems, works without errors.
Now, I put this deployment project on the network and a user downloads it to
his Vista machine and runs the setup. When he starts the app he gets the
following error message:
"Login failed for user "MyUser". The user is not associated with a thrusted
server connection".
The connection string I use in my application is this:
Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
Since I have no control over the Vista machine, I can't force him/her to
create a new account that is thrusted. All I want is to deploy my app and
database, without having to worry about authorization and stuff. If
possible, it should allow for ALL users to connect and use it.
I have already tried alot of the suggestions I've found, but lets start all
over so it will be right. What should I do to remove this error? How can I
deploy my database without having to think about authorization/security
stuff?
TIA
PZPreben,
You deployed your database and your code, correct? A SQL Server login is
not in the database, but is stored in the master database. Does your
deployment include the step to create the SQL Server login "MyUser"?
If not, then you will need to supply a script to do that. Such as (from the
BOL):
CREATE LOGIN MyUser WITH PASSWORD = '3KHJ6dhx(0xVYsdf'
Another possibility is that you have created the login, but the client's SQL
Server 2005 Express installation was set with Authentication Mode = Windows
Authentication Mode. If so, that installation is not willing to accept a
SQL Login and will need to be changed to Mixed Mode.
RLF
"Preben Zacho" <post@.zacho-web.dk> wrote in message
news:OKXT6f4lHHA.1776@.TK2MSFTNGP05.phx.gbl...
> Ok, I've read almost a million ng postings on this, still I can't solve
> it. This place is my final hope.
> I'll start from the beginning: I have a Windows application developed in
> C#. It uses a SQL Server 2005 Express Edition database to store data. Now
> I have created a deployment of this app and database, and I try to install
> it on my own machine. No problems, works without errors.
> Now, I put this deployment project on the network and a user downloads it
> to his Vista machine and runs the setup. When he starts the app he gets
> the following error message:
> "Login failed for user "MyUser". The user is not associated with a
> thrusted
> server connection".
> The connection string I use in my application is this:
> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
> Since I have no control over the Vista machine, I can't force him/her to
> create a new account that is thrusted. All I want is to deploy my app and
> database, without having to worry about authorization and stuff. If
> possible, it should allow for ALL users to connect and use it.
> I have already tried alot of the suggestions I've found, but lets start
> all over so it will be right. What should I do to remove this error? How
> can I deploy my database without having to think about
> authorization/security stuff?
> TIA
> PZ
>
>|||Since you are specifying the user name and password in the connection string
I assume you are using SQL Server authentication for your logins rather than
Windows authentication. One cause for that error is trying to use a SQL
Server login on a database server that is configured to only accept Windows
logins.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Preben Zacho" <post@.zacho-web.dk> wrote in message
news:OKXT6f4lHHA.1776@.TK2MSFTNGP05.phx.gbl...
> Ok, I've read almost a million ng postings on this, still I can't solve
> it. This place is my final hope.
> I'll start from the beginning: I have a Windows application developed in
> C#. It uses a SQL Server 2005 Express Edition database to store data. Now
> I have created a deployment of this app and database, and I try to install
> it on my own machine. No problems, works without errors.
> Now, I put this deployment project on the network and a user downloads it
> to his Vista machine and runs the setup. When he starts the app he gets
> the following error message:
> "Login failed for user "MyUser". The user is not associated with a
> thrusted
> server connection".
> The connection string I use in my application is this:
> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
> Since I have no control over the Vista machine, I can't force him/her to
> create a new account that is thrusted. All I want is to deploy my app and
> database, without having to worry about authorization and stuff. If
> possible, it should allow for ALL users to connect and use it.
> I have already tried alot of the suggestions I've found, but lets start
> all over so it will be right. What should I do to remove this error? How
> can I deploy my database without having to think about
> authorization/security stuff?
> TIA
> PZ
>
>|||Hi Russell

> If not, then you will need to supply a script to do that. Such as (from
> the BOL):
> CREATE LOGIN MyUser WITH PASSWORD = '3KHJ6dhx(0xVYsdf'
I think so. I have 2 scripts that comes with the deployment: The first one
creates the user MyUser in the server scope. The next one creates the
database itself (thats what I mean with "deploying" the database). Both
scripts run in sqlcmd without errors. I then check to see if I can run a SQL
statements by doing this:
sqlcmd -S .\SQLEXPRESS
1> select * from customer
2> go
The SQL statement is then executed without any problems, so the database
seems to have been created alright.

>Another possibility is that you have created the login, but the client's
>SQL Server 2005 Express installation was set with Authentication Mode =
>Windows Authentication Mode. If so, that installation is not willing to
>accept a SQL Login and will need to be changed to Mixed Mode.
How do I check this on the remote machine? Can I somehow change this using a
script?
/PZ
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%230xS27lHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Preben,
> You deployed your database and your code, correct? A SQL Server login is
> not in the database, but is stored in the master database. Does your
> deployment include the step to create the SQL Server login "MyUser"?
> If not, then you will need to supply a script to do that. Such as (from
> the BOL):
> CREATE LOGIN MyUser WITH PASSWORD = '3KHJ6dhx(0xVYsdf'
> Another possibility is that you have created the login, but the client's
> SQL Server 2005 Express installation was set with Authentication Mode =
> Windows Authentication Mode. If so, that installation is not willing to
> accept a SQL Login and will need to be changed to Mixed Mode.
> RLF
> "Preben Zacho" <post@.zacho-web.dk> wrote in message
> news:OKXT6f4lHHA.1776@.TK2MSFTNGP05.phx.gbl...
>|||Hi Roger
How can I determine this? And how do I change it if that is the case?
/PZ
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:1105D112-C515-42E4-924D-85184144DC2A@.microsoft.com...
> Since you are specifying the user name and password in the connection
> string I assume you are using SQL Server authentication for your logins
> rather than Windows authentication. One cause for that error is trying to
> use a SQL Server login on a database server that is configured to only
> accept Windows logins.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Preben Zacho" <post@.zacho-web.dk> wrote in message
> news:OKXT6f4lHHA.1776@.TK2MSFTNGP05.phx.gbl...
>|||Here are the instructions for changing it.
http://msdn2.microsoft.com/en-us/library/ms188670.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Preben Zacho" <post@.zacho-web.dk> wrote in message
news:%23F%23hPh$lHHA.4568@.TK2MSFTNGP02.phx.gbl...
> Hi Roger
> How can I determine this? And how do I change it if that is the case?
> /PZ
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:1105D112-C515-42E4-924D-85184144DC2A@.microsoft.com...
>|||Hi Roger
This is a prerequisit deployment, which means the SQL Server 2005 Express
Edition is downloaded and installed automatically during setup and before
tha application is installed. As far as I know, the SQL Server Management
Studio is not by default part of this installation (at least I can't find
it). And I don't want my users to download additional software to get this
working.
The strange thing is this: I can execute a select statement using the SQLCMD
tool without any problems. And from the Visual Studio 2005 I can go to the
Server Tools and connect to the database as well and query tables there,
also without errors. But my application come up with the "Login failed for
user "MyUser". The user is not associated with a thrusted server connection"
message all the time. I was wondering if my connection string should be
turned to Windows authentication instead, which it seems both SQLCMD and
Visual Studio is using (cause I don't have to login when doing that).
/PZ
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:06C7E2FB-B373-4066-972B-EC2FB00D292E@.microsoft.com...
> Here are the instructions for changing it.
> http://msdn2.microsoft.com/en-us/library/ms188670.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Preben Zacho" <post@.zacho-web.dk> wrote in message
> news:%23F%23hPh$lHHA.4568@.TK2MSFTNGP02.phx.gbl...
>|||Ok, so I got it to work now. I simply changed from SQL Authentication to
Windows Authentication and changed my connection string. Seems that the
deployment goes smoothly after all, since when installating the SQL Server
2005 as prerequisit, the installing user is created as a login user. I was
not awear of that.
The database itself is then created using a script call to SQLCMD and the
application can run without errors.
I can live with that, it makes the installation quite simple after all.
/PZ
"Preben Zacho" <post@.zacho-web.dk> wrote in message
news:OKXT6f4lHHA.1776@.TK2MSFTNGP05.phx.gbl...
> Ok, I've read almost a million ng postings on this, still I can't solve
> it. This place is my final hope.
> I'll start from the beginning: I have a Windows application developed in
> C#. It uses a SQL Server 2005 Express Edition database to store data. Now
> I have created a deployment of this app and database, and I try to install
> it on my own machine. No problems, works without errors.
> Now, I put this deployment project on the network and a user downloads it
> to his Vista machine and runs the setup. When he starts the app he gets
> the following error message:
> "Login failed for user "MyUser". The user is not associated with a
> thrusted
> server connection".
> The connection string I use in my application is this:
> Server=.\SQLEXPRESS;Database=MyDB;User ID=MyUser;Password=thissucks
> Since I have no control over the Vista machine, I can't force him/her to
> create a new account that is thrusted. All I want is to deploy my app and
> database, without having to worry about authorization and stuff. If
> possible, it should allow for ALL users to connect and use it.
> I have already tried alot of the suggestions I've found, but lets start
> all over so it will be right. What should I do to remove this error? How
> can I deploy my database without having to think about
> authorization/security stuff?
> TIA
> PZ
>
>

Problem with SELECT, GROUP BY and aggregate function

Hi all,

I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.

/*INSERT INTO WERKS (
WERKS.Z8601,
WERKS.Z8602,
WERKS.Z8603,
WERKS.Z8604,
WERKS.Z8605,
WERKS.Z8606,
WERKS.Z8607,
WERKS.Z8608,
WERKS.Z8609,
WERKS.Z8610,
WERKS.Z8611,
WERKS.Z8621,
WERKS.Z8622,
WERKS.Z8623,
WERKS.Z8624,
WERKS.Z8625,
WERKS.Z8626,
WERKS.Z8627,
WERKS.Z8628,
WERKS.Z8629,
WERKS.Z8630,
WERKS.Z8631,
WERKS.Z8632) */
SELECT
0,
Stati.z4414,
Stati.z4402,
'',
'',
'',
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''),
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''),
'',
'',
'',
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)
FROM STATI
WHERE
(Stati.z4402 Between '40000' AND 'ZONE6') AND
(Stati.z4414 Between '2005028' AND '2005028') AND
(Stati.z4417 = 'A')
GROUP BY Stati.z4414, Stati.z4402

I get the following error:

Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.

The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS.
I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?

This query is dynamicly generated from within my program from what the user selected.

Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.

Thanks in advance!

If you really want to use that dynamic query, I would use a Subquery to use the column names to group, otherwise it often the case that you use the overview over the statement.

SELECT col1,col2
FROM
(
Your dynamic query here
) Subquery
Group by col1, col2

HTH, Jens K. Suessmeyer.

http://www.sqlserver205.de|||Assuming the value of STAI.Z4400 is always the same for the group do this:
.
.
.
MAX(Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), '')),
MAX(Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), '')),
.
.
.

That should fix it.

In this case, it would be easier to JOIN the STAI file intead of doing the subquery. Then just do

CASE WHEN Srtas.z02 IS NULL THEN '' ELSE Srtas.z02 END,
CASE WHEN Klant.z0102 IS NULL THEN '' ELSE Klant.z0102 END,

|||Thank you both for your opinion and help on this problem.

Reading your posts gave me some new ideas so I started changing the query like this:

SELECT

0,

Stati.z4414,

Stati.z4402,

'',

'',

'',

Max(Srtas.z02),

Max(Klant.z0102),

'',

'',

'',

sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)

FROM STATI

LEFT JOIN KLANT ON STATI.Z4400 = KLANT.Z0100 AND STATI.Z4402 = KLANT.Z0101

LEFT JOIN SRTAS ON STATI.Z4400 = SRTAS.Z00 AND STATI.Z4414 = SRTAS.Z01

WHERE

(Stati.z4402 Between '40000' AND 'ZONE6') AND

(Stati.z4414 Between '2005028' AND '2005028') AND

(Stati.z4417 = 'A')

GROUP BY Stati.z4414, Stati.z4402

This query seems to work.

I have to admit that there's a huge pile of dust ontop of my MSSQL-knowledge. Been a while since I last used it.

So if any of you have any comments on me doing something wrong, I would be pleased to hear it :-)