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 simple SQL

I want to view all companyname that starts with A, the sql runs but does not show the output,
What's wrong with the query?, am I missing any quotes or comma. Pls 'elp
Dim myCharacter = "A"
SQL = "SELECTlogo, EmployerID, CompanyName FROM EmployerDetails wherecompanyName like '"&myCharacter & "%'"



Select seems correct! How you are using this in ASP.NET page? Are you getting data into Reader or not?|||Thanks Sreed
I think the problem is the SQL because when I hard code to this
SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like 'A%' "
It works, PLS help Thanks
|||

If you declared myCharacter,SQL as a local string variable, then it should work!

Just to debug after SQL = "..blabla" statement do,

Response.Write(SQL)
Response.End()
and run the page and see what is the output you are getting from SQL, and see if that is giving correct Select or not!

That should help us to find whether it building currect SQL or not!

|||Hi Sreed
This is the full code below , I did the PRINT STUFF and the output is also below
Sub BindDataToGrid(myCharacter)
Response.write("Testing "&myCharacter)
Dim Conn, Conn1 as SqlConnection
Dim mySqlCommand, mySqlCommand2 as SqlCommand
Dim SQL,SQL1,SQL2 ,GetQuestions As String
Dim myDate as date
Dim pic as ArrayList = new ArrayList()
Dim Job_no as Integer

IF myCharacter <> "" THEN
SQL ="SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL & " ' " & myCharacter & " % ' "
Else
SQL ="SELECT logo, EmployerID FROM EmployerDetails order by newID() "
End If
Conn = New SqlConnection(ConfigurationSettings.AppSettings("connString"))
Dim resultsDataSet as New DataSet()
Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(SQL, Conn)

Conn.Open()
myDataAdapter.Fill(resultsDataSet,"EmployerDetails")
mySqlCommand = New SqlCommand(SQL, Conn)
mySqlCommand.ExecuteNonQuery()

Dim myReader As SqlDataReader = mysqlCommand.ExecuteReader()
Dim content
While myReader.Read()
If not (myReader("Logo") is DBNull.Value) Then
If Len(myReader("Logo")) > 0 Then
Dim LOGO = myReader("Logo")
Dim EmployerID =myReader("EmployerID")
Content = "<br><ahref='/JBoard/EmployerJobs.aspx?EMPID="&EmployerID
Content = Content & " '><img src='/JBoard/CompLogo/"&Logo
Content = Content & " ' width='124' height='50'BORDER='1'></a>"
pic.Add(Content)
End If
End If
End While

LogoGrid.DataSource = pic
LogoGrid.DataBind()
Conn.Close()
End Sub

OUTPUT IS
SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like 'M % '
What is wrong with this tiny code, forum help me pls.


|||

SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL &" ' " & myCharacter &" % ' "

This looks like you have extra spaces in there. If you copied and pasted this directly from your code, you're actually querying for anything that starts with a space and then an 'A' and then has a space and then any character and then a space. Drop the spaces in theRED code above and it should work.

|||Hi PD
How do you drop the space, that's why I thought ?

|||Use your keyboard's Delete key to remove the extra spaces around thesingle quote and percentage characters. (A total of 4 spacesshould be deleted.)
That should leave you with the following statement:
SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL &"'" & myCharacter &"%' "
|||

tmorton wrote:

Use your keyboard's Delete key to remove the extra spaces

In a recent technical journal, it suggested that the Backspace key canalso be used to remove characters. I haven't tried it myself, as I fearthat I'm not advanced enough. But obinna123 seems pretty advanced, sohe may want to try that technique to removed the extra spaces.

Problem with sample databases

I have installed SQL Express 2005 together with VS 2005. After a while I found out that the sample databases (Northwind and Pubs) were missing, so I installed them. According to the 'Readme', which comes with the installation I could choose between attaching the db to the Server or running a SQL script.

Neither works. I've read this link about remote connections (http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx). My development machine is just a local single computer and I still get this error message:

'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 under the default settings SQL Server does not allow remote connections.'

I' ve opened TCP/IP and Named Pipes and I get the same result when trying to connect from the Server Explorer in VS.

Thanks for your help.

Hey,

SQL Server 2005 doesn't install with the sample databases anymore; I am having training on it, and that is what I just found out. Also, to setup remote connections, you can do so in the SQL Server Surface Area Configuration.

Brian

|||

Hey,

TCP/IP and Named Pipes are only selectable if you allow remote connections in the SQL Server Surface Area Configuration.

RTFT.

Anybody else coming up with an idea?