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 sp_xml_preparedocument

Hi All,

Follwing are the 2 code snippets of XOPEN

1.
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='
<b>
<a>
<s>aaa</s>
</a>
<a>
<s>bbb</s>
</a>
</b>'
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc,'/b/a',2)
WITH (s varchar(100) '@.s')

2.

create table newtemp
(
s varchar(100)
)
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='
<b>
<a>
<s>aaa</s>
</a>
<a>
<s>bbb</s>
</a>
</b>'
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc,'/b/a',2)
WITH newtemp

Code snippet 2 works but 1 doesnot.

Can some one me the reason why ?

Thanks & Regards

Nitesh

Try changing

WITH (s varchar(100) '@.s')

to

WITH (s varchar(100) 's')

|||

Thanks a lot !!!!

It worked out.

Problem with source from Books Online - Application.LoadPackage

I'm trying to execute a package in the file system from an c# assembly. In BOL there is the following sample code available:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/M_Microsoft_SqlServer_Dts_Runtime_Application_LoadPackage_1_cfc592c3.htm

Application.LoadPackage Method (String, IDTSEvents)

class ApplicationTests
{
static void Main(string[] args)
{
// The variable pkg points to the location of the
// ExecuteProcess package sample installed with
// the SSIS samples.
string pkg = @."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ExecuteProcess Sample\ExecuteProcess\UsingExecuteProcess.dtsx";

Application app = new Application();
Package p = app.LoadPackage(pkg, null);
// Now that the package is loaded, we can query on
// its properties.
int n = p.Configurations.Count;
DtsProperty p2 = p.Properties["VersionGUID"];
DTSProtectionLevel pl = p.ProtectionLevel;

Console.WriteLine("Number of configurations = " + n);
Console.WriteLine("VersionGUID = " + p2);
Console.WriteLine("ProtectionLevel = " + pl);
}
}

I added the reference to "Microsoft.SQLServer.DTSRuntimeWrap" and declared it by

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

No I'm not able to compile because app.LoadPackage(pkg, null); needs an additional IDTSEvents90. After changing the row to

Package p = app.LoadPackage(pkg, true, null);

I'm getting the following errors:

Error 1 Cannot implicitly convert type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackage90' to 'Microsoft.SqlServer.Dts.Runtime.Wrapper.Package'. An explicit conversion exists (are you missing a cast?)

Error 2 The type or namespace name 'DtsProperty' could not be found (are you missing a using directive or an assembly reference?)

Error 3 'Microsoft.SqlServer.Dts.Runtime.Wrapper.Package' does not contain a definition for 'Properties'

Can someone please give me an example how to start the Package now in the latest code syntax and set the values of a variable from outside?

I am using the following versions

Microsoft Visual Studio 2005
Version 8.0.50727.26 (RTM.050727-2600)
Microsoft .NET Framework
Version 2.0.50727

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1314.00

Best regards,
Dirk

Add a reference to Microsoft.SqlServer.ManagedDts.dll and use namespace Microsoft.SqlServer.Dts.Runtime (without .Wrapper).

.Wrapper assembly and namespace contain COM interop declarations - i.e. if you want to work with unmanaged object model directly. It is usually more convinient to work with managed object model built on top of it.sql

Problem with Setting a variable in SQL String

Hi,

I am having problems setting the value of a variable in a SQL String
that I have to create dynamically in my procedure. The code that I
currently have is as follows:

set @.sqlStatement='Set @.compare_string=' + '(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

exec(@.sqlStatement)

The error message that I get is as follows:

Must declare the variable '@.compare_string'.

Here @.compare_string has already been declared in the procedure and I
don't have a problem using the variable anywhere else but this SQL
Statement (when called using the EXEC function).

I am not sure why SQL Server can't see the variable declared when used
in a string in conjunction with EXEC. Is this a syntax issue? Any help
on this issue would be greatly appreciated!

Thanks in advance.You need a parms string and an exec string, like this:

SET @.Parms = `@.compare_string`

set @.sqlStatement='Set @.compare_string=' + '(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string

SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )

> exec(@.sqlStatement)
> The error message that I get is as follows:
> Must declare the variable '@.compare_string'.
> Here @.compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).
> I am not sure why SQL Server can't see the variable declared when used
> in a string in conjunction with EXEC. Is this a syntax issue? Any help
> on this issue would be greatly appreciated!
> Thanks in advance.|||Thanks for your reply. The sp_executesql procedure still doesn't give
the desired results. I am posting the updated piece of code and sample
output from the Query Analyzer.

------
set @.parameter_String=N'@.compare_string nvarchar(4000)'

set @.sqlStatement='Set @.compare_string=(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

Print @.sqlStatement
EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string

Print @.compare_String
------

When I print the value of @.compare_String in the end its a NULL.
However, if I run the same query without the set @.compare_string
clause, it does work perfectly and returns the values of two columns
concatenated together. Any clues as to where I might be going wrong?

Thanks,

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> You need a parms string and an exec string, like this:
> SET @.Parms = `@.compare_string`
> set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > exec(@.sqlStatement)
> > The error message that I get is as follows:
> > Must declare the variable '@.compare_string'.
> > Here @.compare_string has already been declared in the procedure and I
> > don't have a problem using the variable anywhere else but this SQL
> > Statement (when called using the EXEC function).
> > I am not sure why SQL Server can't see the variable declared when used
> > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > on this issue would be greatly appreciated!
> > Thanks in advance.|||[posted and mailed, please reply in news]

Aamer Nazir (aamernazir_01@.hotmail.com) writes:
> I am having problems setting the value of a variable in a SQL String
> that I have to create dynamically in my procedure. The code that I
> currently have is as follows:
>
> set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> exec(@.sqlStatement)
> The error message that I get is as follows:
> Must declare the variable '@.compare_string'.
> Here @.compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).

The EXEC() statement is another scope which is not part of your procedure.
Thus, @.compare_string is not defined in that example.

For better examples than the one posted, see
http://support.microsoft.com/?id=262499 and
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, that won't work. Sorry, I just focused on the parameter part.

You can just do this:

select @.compare_string = mytable.myfield FROM mytable where Identity_Column
= myvalue

or, in your specific case:

'Select @.compare_string=' + @.group_column_list_mod + ' from ' + @.Tbl_Name +
'_Sorted' + ' where Identity_Column=' + ltrim(rtrim(str @.loop_counter))'

At least this is the syntax you should use in this case. Otherwise, you are
effectively trying to bind @.compare_string to a recordset result, which
doesn't work.

Make sure you add in the error checking afterwards!! :)

"Aamer Nazir" <aamernazir_01@.hotmail.com> wrote in message
news:60b6d0a1.0312231058.14540a2c@.posting.google.c om...
> Thanks for your reply. The sp_executesql procedure still doesn't give
> the desired results. I am posting the updated piece of code and sample
> output from the Query Analyzer.
>
> ------
> set @.parameter_String=N'@.compare_string nvarchar(4000)'
> set @.sqlStatement='Set @.compare_string=(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> Print @.sqlStatement
> EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
> Print @.compare_String
> ------
> When I print the value of @.compare_String in the end its a NULL.
> However, if I run the same query without the set @.compare_string
> clause, it does work perfectly and returns the values of two columns
> concatenated together. Any clues as to where I might be going wrong?
> Thanks,
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> > You need a parms string and an exec string, like this:
> > SET @.Parms = `@.compare_string`
> > set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> > SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > > > exec(@.sqlStatement)
> > > > The error message that I get is as follows:
> > > > Must declare the variable '@.compare_string'.
> > > > Here @.compare_string has already been declared in the procedure and I
> > > don't have a problem using the variable anywhere else but this SQL
> > > Statement (when called using the EXEC function).
> > > > I am not sure why SQL Server can't see the variable declared when used
> > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > on this issue would be greatly appreciated!
> > > > Thanks in advance.|||Thanks for pointing me to the right direction. The code works
perfectly fine now. The problem was with the syntax that Erland
Sommarskog mentioned in his posting. You have to specify the parameter
type (input or output) in the parameter specification string (the
second argument to sp_executesql).

Best Regards,

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bsboku$o7p$1$8300dec7@.news.demon.co.uk>...
> Yes, that won't work. Sorry, I just focused on the parameter part.
> You can just do this:
> select @.compare_string = mytable.myfield FROM mytable where Identity_Column
> = myvalue
> or, in your specific case:
> 'Select @.compare_string=' + @.group_column_list_mod + ' from ' + @.Tbl_Name +
> '_Sorted' + ' where Identity_Column=' + ltrim(rtrim(str @.loop_counter))'
> At least this is the syntax you should use in this case. Otherwise, you are
> effectively trying to bind @.compare_string to a recordset result, which
> doesn't work.
> Make sure you add in the error checking afterwards!! :)
> "Aamer Nazir" <aamernazir_01@.hotmail.com> wrote in message
> news:60b6d0a1.0312231058.14540a2c@.posting.google.c om...
> > Thanks for your reply. The sp_executesql procedure still doesn't give
> > the desired results. I am posting the updated piece of code and sample
> > output from the Query Analyzer.
> > ------
> > set @.parameter_String=N'@.compare_string nvarchar(4000)'
> > set @.sqlStatement='Set @.compare_string=(Select ' +
> > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > Print @.sqlStatement
> > EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
> > Print @.compare_String
> > ------
> > When I print the value of @.compare_String in the end its a NULL.
> > However, if I run the same query without the set @.compare_string
> > clause, it does work perfectly and returns the values of two columns
> > concatenated together. Any clues as to where I might be going wrong?
> > Thanks,
> > "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> > > You need a parms string and an exec string, like this:
> > > > SET @.Parms = `@.compare_string`
> > > > set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> > > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > > > EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> > > > SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > > > > > > exec(@.sqlStatement)
> > > > > > The error message that I get is as follows:
> > > > > > Must declare the variable '@.compare_string'.
> > > > > > Here @.compare_string has already been declared in the procedure and I
> > > > don't have a problem using the variable anywhere else but this SQL
> > > > Statement (when called using the EXEC function).
> > > > > > I am not sure why SQL Server can't see the variable declared when used
> > > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > > on this issue would be greatly appreciated!
> > > > > > Thanks in advance.

problem with SELECT statement and WHERE

i am using visual web developer 2005 and SQL server 2005 with VB as the code behind

i am using the following code to retrieve data from the database and display in textboxes and it works fine

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;IntegratedSecurity=True;User Instance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM Table1 ", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()
TextBox4.Text = tblData.Rows(0).Item("name").ToString()
TextBox5.Text = tblData.Rows(0).Item("age").ToString()
TextBox6.Text = tblData.Rows(0).Item("email").ToString()

now i want to display data in the text boxes that match certain criteria.i modified the code like this but it did not work

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;IntegratedSecurity=True;UserInstance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM Table1 WHERE name=peter", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()
TextBox4.Text = tblData.Rows(0).Item("name").ToString()
TextBox5.Text = tblData.Rows(0).Item("age").ToString()
TextBox6.Text = tblData.Rows(0).Item("email").ToString()

it gave the result as Invalid column name 'peter'.

but if i give as SELECT * FROM Table1 WHERE id=1result is displayed correctly

but id is the primary key whereas name is not a primary key.

please help me how to display the values corresponding to the name field

i would think your "Select * from table1 where name = peter"
should be
"Select * From Table1 where name = 'peter'"

notice the single quotes on around peter as your querying a char field

HTH,
AjaxButter

|||

Try putting single quotes around peter like this 'peter'. The SQLAdapter is interrepting the string peter as a column name and not a value. The reason it works with the id is because id is most likely a numeric type value (IE: int or decimal, etc.) so single quotes are not needed.

Dapanther

|||

AjaxButter,

Beat again by my slow typing LOL

Dapanther

|||

if peter is just a substring and i want all the rows containing peter as a substring in name field what will i do

please help me

|||

If you need peter as a substring then there are the follwoing possiblities.

1. the name is "xyz peter"

For this the querry would be Select * From table1 where name like '%peter'

2. the name is "Peter xyz"

Here the querry would be like this Select * From table1 where name like 'Peter %'

3. The name could be "abc Peter xyz"

The querry would be as follows: Select * From table1 where name like '%peter%'

It all depends upon what you require. The % is a wild string search.

Please mark this as answer if the post help you.

problem with select and int statement

hey the following code doesnt worki it comes up with an error Specified cast is not valid. pointing at the exceutescalar

any ideas?

cheers

string maxquery = "Select Max(activity_order) from roomactivitylk where room_code = 'v1106'";

//int max = 0;
int max;

SqlCommand cmd15 = new SqlCommand();
cmd15.Connection = con;
cmd15.CommandText = maxquery;
max = (int)cmd15.ExecuteScalar();

++max;
max++;
max = max + 1;
tbtest1.Text = max.ToString();

I'm not shure, but AFAIK aggregate functions (like MAX()) usually requires GROUP BY clause.

Try modify line with error such way.

max = cmd15.ExecuteScalar();

And look what woul be max equal to in debugger.

Looks like your query returns result which simply can't be parsed to integer.

|||

Your query is OK. Run the query in SQL directly, make sure it return integer. If not integer, you need to set it as 0 value.

|||

Be sure to check to see what happens if it returns NULL as well. I don't believe your code would work if that were the case.

problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

problem with saving data to SQL Server Mobile ?

Hi everybody there

i have a small problem with this code, it cann't save changes on Datatable to the database SQL Server Mobile ..

the execution work succesfully but without changes in the database !

here is the code, please try to help :

Code Snippet

#Region " << Declarations >> "

Dim objCon As New SqlCeConnection("Data source=\Storage Card\Full_Database.sdf")
Dim objDA As SqlCeDataAdapter
Dim objCmdBldr As SqlCeCommandBuilder
Dim objTB As New DataTable("MyTB")
Dim objBS As New BindingSource

#End Region

Private Sub frmDatabase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

objDA = New SqlCeDataAdapter("Select * From MyTB", objCon)
objDA.MissingSchemaAction = MissingSchemaAction.AddWithKey

objTB.Clear()

objDA.Fill(objTB)
objBS.DataSource = objTB

End Sub

' Add New Record for example
Private Sub mnuAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAddNew.Click

If Not CType(objBS.Current, DataRowView).IsNew Then
objBS.AddNew()

Dim dRowView As DataRowView = objBS.Current
dRowView.BeginEdit()

dRowView("id") = 10
dRowView("name") = "Someone"

dRowView.EndEdit()
End If

' Calling Save Methode
mnuSave_Click(sender, e)

End Sub

' Save Values
Private Sub mnuSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuSave.Click

objBS.EndEdit()
objCmdBldr = New SqlCeCommandBuilder(objDA)
objDA.Update(objTB)

End Sub

UP .|||

See this.

By the way, there's search up and right. Works great - try it with "save changes", for example…

Problem with Saving a Stored Procedure

I have had the same problem. In the past I had the sql commands in the code. Last project was with SQL2000 and I decided to add store procedures part as a learning tool and part to better control the code. With the current project I am using SQL2005 and have a ummm fun time with the learning curve.

Below is my stored procedure.

USE [AdventureWorks]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[WDR_CountryAdd]

(

@.CountryName nvarchar(50),

@.CountryID int OUTPUT

)

AS

INSERT INTO Country

(

CountryName

)

VALUES

(

@.CountryName

)

SELECT

@.CountryID = @.@.Identity

When I hit the execute button, I get the following error.

Msg 208, Level 16, State 6, Procedure WDR_CountryAdd, Line 24

Invalid object name 'WDR_CountryAdd'.

I I know that it does not exist as that is what I am trying to do, create it. I have checked to make sure that I am on the currect database. So how do I associate this procedure with the data base? Do I need to write a SQL query to create the procedure? Maybe that is the answer and then just modify it.

If it doesn't exists, you need to use a CREATE PROCEDURE statement instead of an ALTER PROCEDURE statement.|||

Arnie

I appreciate the fast response.

I used a create and it ran successfully. However, it still did not show up under the stored procedures. So if it does not show up in the list, how do I access it from my web application? With SQL 2000 I just did a right click create new, did the code and it saved it to the list and then I accessed it. I was planning on a code along the lines of WDR_CountryAdd("USA") (of course in the proper codes)

Now here is a beginners question, When I first execute this procedure, do I need to supply the parameters for it to successfully attach to the database?

Jerry

|||

Never Mind. I ran the CREATE again and it worked very well. Must of had a brain dead moment there.

Thanks for the help. I have learned a lot from this forum.

Jerry

Problem with reporting and queries

I am having a problem with several reports at work. We use an SQL
generator package where we fill in a template, and the system
generates SQL code.

The reports I have been running at a low level return a sales value of
$96,000 for a specific office for 2006.

Here is my filter,

Office = 23

Region = Northeast

Product Cat = (several different categories)

Year = 2006

When I added some additional columns, the sale for the same office
went to over $9 Million. When I analyzed this further, I found all
offices in the region were being returned for the second report, and
thus I ended up with the sales for all of the regions sales.

What I am really confused about is how using the exact same filter, a
simple report can show one number and then by adding some facts or
columns my sales went up. (and I did confirm character by character
we are using the same filter.)

Is this explainable based on some principle of SQL I am unfamiliar
with?

One explanation I received from IT, who is too busy to look at my
problem, is that by adding additional columns, I essentially asked our
SQL generator to set up a larger join than I expected.

If this were true, wouldn't the filter still eliminate records that
don't meet the filter requirements?

I suspect the SQL generator applied the filter at the wrong spot. I
tried looking at the SQL: code, but it is very complicated.

So I am turning to this forum to see if anyone can think of a logical
explanation that would allow SQL to in effect return a larger dataset
than my original report.

Thanks for any help.AF (bscinc@.Yahoo_NoSpam.com) writes:

Quote:

Originally Posted by

When I added some additional columns, the sale for the same office
went to over $9 Million. When I analyzed this further, I found all
offices in the region were being returned for the second report, and
thus I ended up with the sales for all of the regions sales.
>
What I am really confused about is how using the exact same filter, a
simple report can show one number and then by adding some facts or
columns my sales went up. (and I did confirm character by character
we are using the same filter.)
>
Is this explainable based on some principle of SQL I am unfamiliar
with?
>
One explanation I received from IT, who is too busy to look at my
problem, is that by adding additional columns, I essentially asked our
SQL generator to set up a larger join than I expected.
>
If this were true, wouldn't the filter still eliminate records that
don't meet the filter requirements?
>
I suspect the SQL generator applied the filter at the wrong spot. I
tried looking at the SQL: code, but it is very complicated.
>
So I am turning to this forum to see if anyone can think of a logical
explanation that would allow SQL to in effect return a larger dataset
than my original report.

It's of course impossible to debug a tool that I have never seen.
I can think of lots of reasons, including user errors on your
part, errors in the tool you use, or in the data model you access.

If I understood your story correctly, the second report rendered the
filter on office void and useless. That's some kind of clue, but enough
to say "Aha!".

You could at least post the queries, to give us something to work with.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problem with referencing System.Data.SqlServerCE.dll

Hello

I have referrenced System.Data.SqlServerCE.dll to my web project in order to create a .sdf file. I haven't wrote any code,just referenced it. When I build the project I get this error:

Error 2 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' cannot be loaded.

It sounds like you dont have the client dlls installed.

If you already installed the SDK you need to install it from C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\bin\Desktop

if you dont have the SDK, I suggest you to install it, another option would be to download directly another redist.

|||

I have had problems too, related to incorrect referencing. There is a version in the public assemblies folder which causes this error.
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies\System.Data.SqlServerCe.dll

This specific file/version is needed by the data designer in visual studio so dont delete it, just change any references in your projects, for System.Data.SqlServerCe.dll, to point to the correct file.

Usually

C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\System.Data.SqlServerCe.dll

OR

C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\System.Data.SqlServerCe.dll

Cheers

Anton

Problem With RecordSet

Hello SQL ASP guys,
I have written this small code:
<% @. Language=VBScript %>
<html>
<head>
<title>SQL Test</title>
</head>
<%
Dim objConn
Dim objRS
Dim strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = BOTBS; User Id = ; Password="
%>
Connection Open
<%
strSQL = "SELECT barname from bars"
objRS.Open strSQL, objConn
objRS("barname")
response.write objRS("barname")
%>
--
This works upto objRS("barname")
Does anyone have any suggestions?J Bowman [293288] wrote:
> Hello SQL ASP guys,
> I have written this small code:
> <% @. Language=VBScript %>
> <html>
> <head>
> <title>SQL Test</title>
> </head>
> <%
> Dim objConn
> Dim objRS
> Dim strSQL
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog => BOTBS; User Id = ; Password="
> %>
> Connection Open
> <%
> strSQL = "SELECT barname from bars"
> objRS.Open strSQL, objConn
> objRS("barname")
> response.write objRS("barname")
> %>
> --
> This works upto objRS("barname")
>
> Does anyone have any suggestions?
somevariable = objRS("barname")
also, you should have a userid and password in the connection string
unless you took that out when posting to this newsgroup?|||Got it... Thanks for your help Ken
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1155595738.014662.259630@.m79g2000cwm.googlegroups.com...
> J Bowman [293288] wrote:
>> Hello SQL ASP guys,
>> I have written this small code:
>> <% @. Language=VBScript %>
>> <html>
>> <head>
>> <title>SQL Test</title>
>> </head>
>> <%
>> Dim objConn
>> Dim objRS
>> Dim strSQL
>> Set objConn = Server.CreateObject("ADODB.Connection")
>> Set objRS = Server.CreateObject("ADODB.Recordset")
>> objConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog =>> BOTBS; User Id = ; Password="
>> %>
>> Connection Open
>> <%
>> strSQL = "SELECT barname from bars"
>> objRS.Open strSQL, objConn
>> objRS("barname")
>> response.write objRS("barname")
>> %>
>> --
>> This works upto objRS("barname")
>>
>> Does anyone have any suggestions?
> somevariable = objRS("barname")
> also, you should have a userid and password in the connection string
> unless you took that out when posting to this newsgroup?
>

problem with query

Hi

I have the following bit of code

string test0001 = "Select Max(activity_order) from roomactivitylk";

int max;

SqlCommand cmd15 = new SqlCommand();
cmd15.Connection = con;
cmd15.CommandText = test0001;
max = (int)cmd15.ExecuteScalar();


max = max + 1;

what it does is add 1 to the value max which is taken from the database

however it seems to be set to 0 as everytime it brings back 0 even though the next incrment value should be 2

any suggestions?

cheerts

Hi

I am not sure which parts of the code is in a loop. But i guess, the "int max" declaration should be outside of the loop (if it is already not).

Hope this helps.

VJ

|||

My guess is u want to perform autoincrement . If i m right i think u may not get it because when there r no records exist check the return value.u may get null if u get null make it as 1 or increment with the max value. Plz reply to me am i rt or wrong

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Hi

thanks for the replys manmaged to sort it

this is the soltuion

string maxquery = "Select Max(activity_order) from roomactivitylk ";
//cmd14.ExecuteScalar();

int max;

SqlCommand cmd15 = new SqlCommand();
//SqlCommand cmd15 = new SqlCommand(test0001, con);
cmd15.Connection = con;
cmd15.CommandText = maxquery;
max = (int)cmd15.ExecuteNonQuery();


max = max + 2;
++max;
max++;

ps yes i was tring to increment:-)

cheers!!

Problem with procedure call style

I try to execute code:
...
CallableStatement prep = conn.prepareCall("{call sp_xml_preparedocument
(?, ?)}");
prep.registerOutParameter(1, java.sql.Types.INTEGER);
prep.setString(2, "<root/>");
prep.executeUpdate();
System.out.println(prep.getInt(1));
CallableStatement rem = conn.prepareCall("{call sp_xml_removedocument
(?)}");
rem.setInt(1, prep.getInt(1));
rem.executeUpdate();
...
Java prints Exception Could not find prepared statement with handle 1.
Possible this problem is in using sp_execsql by MSSQL JDBC Driver.
How can I fix it?
Thank You!
| From: "Yuri Shustrov" <yuri_shustrov@.mail.ru>
| Subject: Problem with procedure call style
| Date: Fri, 10 Dec 2004 16:03:46 +0300
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OBfhHir3EHA.2404@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: ns.escort-center.ru 212.176.17.195
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP14
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6541
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I try to execute code:
| ...
| CallableStatement prep = conn.prepareCall("{call
sp_xml_preparedocument
| (?, ?)}");
| prep.registerOutParameter(1, java.sql.Types.INTEGER);
| prep.setString(2, "<root/>");
| prep.executeUpdate();
| System.out.println(prep.getInt(1));
| CallableStatement rem = conn.prepareCall("{call sp_xml_removedocument
| (?)}");
| rem.setInt(1, prep.getInt(1));
| rem.executeUpdate();
| ...
| Java prints Exception Could not find prepared statement with handle 1.
| Possible this problem is in using sp_execsql by MSSQL JDBC Driver.
| How can I fix it?
| Thank You!
|
|
|
Hello Yuri,
The SQL Server 2000 Books Online topic "sp_xml_preparedocument" states the
following:
"sp_xml_preparedocument returns a handle that can be used to access the
newly created internal representation of the XML document. This handle is
valid for the duration of the connection to Microsoft SQL Server 2000,
until the connection is reset, or until the handle is invalidated by
executing sp_xml_removedocument."
Your code shows two CallableStatements being prepared and executed on the
same connection variable "conn" without closing the first
CallableStatement. If you are using "SelectMethod=direct", then this will
result in a new cloned connection. Since the handle is only valid on the
first connection, the call to sp_xml_removedocument will fail. You can
resolve this by specifying "SelectMethod=cursor" in your connection string.
Otherwise, you can still use "SelectMethod=direct" and simply modify your
code so that the first CallableStatement is closed before the second
CallableStatement is prepared. This will maintain the same underlying
connection, and so the document handle will still be valid:
CallableStatement prep = conn.prepareCall("{call
sp_xml_preparedocument(?, ?)}");
prep.registerOutParameter(1, java.sql.Types.INTEGER);
prep.setString(2, "<root/>");
prep.executeUpdate();
System.out.println(prep.getInt(1));
int handle = prep.getInt(1);
prep.close();
prep = null;
CallableStatement rem = conn.prepareCall("{call
sp_xml_removedocument(?)}");
rem.setInt(1, handle);
rem.executeUpdate();
rem.close();
rem = null;
You can verify this behavior using SQL Profiler. In the failing scenario,
you will observe two different SPIDs (two different connections).
Hope that helps!
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Problem with parameterized SELECT statement

I'm trying to use a parameterized SELECT statement, but I must not have it right - the code below gives this compile error: System.Data.SqlClient.SqlException: Must declare the scalar variable "@.UserID".

string

strUserID = (string)Session["UserID"];string strSelectRatings ="SELECT [CommentID], [GameID], [UserID], [Rating], LEFT(Comment,40) as Comment FROM [Comments] WHERE [UserID] = @.UserID";SqlConnection myConnection =newSqlConnection("...");SqlCommand myCommand =newSqlCommand(strSelectRatings, myConnection);

myCommand.Parameters.Add(

"@.UserID", strUserID);

MySqlDataSource.SelectCommand = strSelectRatings;

GridView1.DataBind();

Add the parameters to your SqlDataSource object

MySqlDataSource.SelectParameters.Add("@.UserID", strUserID);

problem with OUTPUT params in Stored procedure

Hi all!
Running the code below in SQL-analyzeer (or through dbExpress) results in NULL.
As one might guess I would like the result to be 1. What is wrong? I.e, why
wont the result of the SP come back to the caller?

CREATE PROCEDURE test
@.val INTEGER OUT
AS
SELECT @.val = 1
GO

DECLARE @.val INTEGER
EXEC test @.val
SELECT @.valEXEC test @.val OUTPUT

Simon