SqlCeResultSet Looses Updatable Option If Query Has 2 Params

Feb 26, 2008

i have result set from such query :

resultSetOptions = System.Data.SqlServerCe.ResultSetOptions.Scrollable | System.Data.SqlServerCe.ResultSetOptions.Updatable;

queryText =
SELECT agvb_id as ID
,agvb_cat_id as Category
,agv_brn_id as Brand
,agvb_brn_name as BrandName
,agv_brn_area as Area
,agv_brn_sku_qty as SkuQuantity
,agv_brn_qty as Quantity
,agv_orderliness as Orderliness
,agvb_importance as Importance
FROM AGENTS_VISITS_BRANDS
WHERE (AGENTS_VISITS_BRANDS.agvb_agv_id = @agv_id)
AND (AGENTS_VISITS_BRANDS.agvb_cat_id = @dad_id)
ORDER BY agvb_brn_name

if i open resultSet with such qury it looses its updatable option.

If i remove from where 1 param (no mater which) everything runs ok.

View 1 Replies


ADVERTISEMENT

BindingSource ListChanged Does Not Fire For New Records In SqlCeResultSet With Sensitive Option

Nov 6, 2007

I have an updateable and sensitive SqlCeResultSet that is bound to a DataGrid via a BindingSource. All updates to the resultset are programmatic. Changes to an existing record in the resultset are visible in the DataGrid and I am getting corresponding ListChanged events fired from the BindingSource. When I perform an database update independent of the resultset that changes resultset membership, I can peform a ReadLast and see the additional records; however the ListChanged event does not fire. Also, when I explicitly "create" a new record in the resultset I do not get a ListChanged event and in both cases the DataGrid does not display the records. Re-binding to the existing resultset does not appear to work either. Only when I create a new resultset and bind do the new or records display.
Using Compact Framework 2.0.

Any suggestions are appreciated.

View 3 Replies View Related

Report Run OK W/ Default Params But Not Changed Params

Mar 25, 2008

I have a set of reports that run just fine with the default parameters (Country = US). The report returns data within 60 seconds. However, if I change the default parameters, say to Country = UK, the report will run and won't seem to stop. The user will be prompted every few minutes to relogin to the domain (which they are not prompted when they first run the report). On the server, the report is taking up 1 of the four CPU's and is using a huge amount of disk paging.

Here's the kicker. If I go in, change the default parameters to Country = UK and deploy the report, it will run in 60 seconds with the new default parameters. Now I try to run the report by changing the country = US and it locks up when it is executed.


Anybody run into something like this?

Rob

View 5 Replies View Related

Passing Params To SP Using OPEN QUERY

Oct 8, 2014

How to pass parameter values to Stored Procedure using Openquery ?

DECLARE @CenterNumber nvarchar(8)
DECLARE @CenterName nvarchar(100)
DECLARE @tblLeads table(
LeadCount int)
SET @CenterNumber = '98454152'
SET @CenterName = 'neck'

[code]....

View 3 Replies View Related

Getting Data (rows) And Output Params From A Query

May 15, 2005

I have a stored procedure that I use to return Purchase Orders from our PO system. It returns the data rows for PO's that match the criteria passed in (including the page to show etc.) + it returns two output params, Number of rows and Number of Pages.
Using query analyzer I can confirm the query works exactly as we want. I cannot however seem to get the data out to our ASP.net app.
Here is a function that I use in one of my classes:
<code>
Function fnListPOsByCoordinatorIDPaged(ByVal strCoordinatorID As String, ByVal intPOStatusID As Int16, _
ByVal intUserTypeID As Int16, ByVal intArchived As Int16, _ByVal intPageNum As Int32, ByVal intPerPage As Int32, _ByVal strConn As String) As SqlClient.SqlDataReader
Dim dr As SqlClient.SqlDataReader
SqlConnection1.ConnectionString = strConn
prListPOByCoordinatorPaged.Parameters("@CoordinatorID").Value = strCoordinatorIDprListPOByCoordinatorPaged.Parameters("@POStatusID").Value = intPOStatusIDprListPOByCoordinatorPaged.Parameters("@UserTypeID").Value = intUserTypeIDprListPOByCoordinatorPaged.Parameters("@Archived").Value = intArchivedprListPOByCoordinatorPaged.Parameters("@PageNum").Value = intPageNumprListPOByCoordinatorPaged.Parameters("@PerPage").Value = intPerPage
SqlConnection1.Open()dr = prListPOByCoordinatorPaged.ExecuteReader(CommandBehavior.CloseConnection)
Me.Pages = prListPOByCoordinatorPaged.Parameters("@Pages").ValueMe.Rows = prListPOByCoordinatorPaged.Parameters("@Rows").Value
If Me.Rows / intPerPage > Me.Pages Then   Me.Pages = Me.Pages + 1End If
Return dr
prListPOByCoordinatorPaged.Dispose()SqlConnection1.Close()SqlConnection1.Dispose()
End Function
</code>
It does not crash, it returns my data reader with the correct records. Unfortunately my property values are returned as 0. They should have values.
Anyone know how to do this?
Thanks.

View 1 Replies View Related

SQL 2012 :: Configuring Memory Per Query Option And Index Create Memory Option

Feb 10, 2015

So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.

From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.

View 3 Replies View Related

Remote Tables Are Not Updatable. Updatable Keyset-driven Cursors On Remote Tables Require A Transaction With The REPEATABLE_READ

May 16, 2008



Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.

Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.

DT

View 1 Replies View Related

SQLServer Agent Job Looses Password !

May 14, 2007

Hi;



I have built packages on SQL Server 2005 using the Import/Export wizard. The basic aim is to import a table from an Oracle database into my SQL database. It was running fine until recently when they Oracle database was upgraded to 10g (i think), and now something is wrong. If I run the saved package as administrator, it runs fine, but when I run it as part of a Agent schedule job, it seems to loose the password. The error message is not very descriptive ("The job has failed."), so i might be mistaken.



Help anyone ?!



Jaco

View 1 Replies View Related

Ssis Package Looses Password After Restart

Apr 4, 2008

i have a few ssis packages which are scheduled as sql server agent jobs. now after a reboot of the server all the packages jobs don't work anymore. i had to reenter all the passwords for the connection manager in order to make them run again. ist there a way to store the passwords so that the keep available even after a reboot? because it's necessary to restart the server for maintenance resasons.

View 3 Replies View Related

Output Buffer Looses Reference Before Processing All Rows

May 29, 2008

I've written an asynchronous script component and I have created the output buffer. 500,000 rows go into the input but only 1500 to 2000 rows come out berfore I get an SSIS Object reference not set to an instance of an object Error. The error occurs at the AddRow method of the outputbuffer (that's how I know it's gone). Why does this happen? Is there a way to sync up the output buffer with the input buffer?

View 25 Replies View Related

Query Option In DTS

Apr 27, 2001

Im trying to transfer data from SQL tbl to .xls file through a DTS package.

My problem is that DTS transfers all the data from the source tbl to .xls
file.

But i would like to have the selective data transfer based on some query like ;

select * from tbl where field ='val'

instead of complete data transfer.
Thanks in advance.

Regards
Siva

View 2 Replies View Related

Using Query Option In Bcp

Mar 18, 2008



Hi,

I am trying to load data from sql server to sybase. SQL Server is our source databse and I want to do this task in unix server using isql and bcp options.

I want to limit the changes on source tables and also I cannot use DTS or SSIS since we dont own SQL server.

Does any have any idea how to use query option in bcp. It is not direct table load. I need to run select statements with Joins in it.

Any ideas on how to script run and save results in to file and then bcp in to our sybase server.

I am not suposed to craete any views on source db. So just need to run sql statements and take the results to text file.

View 4 Replies View Related

Query Option In DTS - Urgent !!!

Apr 27, 2001

Im trying to transfer data from SQL tbl to .xls file through a DTS package.

My problem is that DTS transfers all the data from the source tbl to .xls
file.

But i would like to have the selective data transfer based on some query like ;

select * from tbl where field ='val'
for the val i want to pass different values .

instead of complete data transfer.
Thanks in advance.

Regards
Anand

View 3 Replies View Related

SQL Query Advanced Option

Apr 18, 2001

I want to run a query overnight through the SQL agent using osql. However I need to turn off the print header option that you normally turn off through the advanced options tab in the Query Analyzer. Does anyone know if this is possible?? Many Thanks in advance..

View 1 Replies View Related

I Need Sql Query Filtering Option

Mar 12, 2007

My table have many column.........One for Message(column).My table updating every seconds......

But I need last Message.........

Ex

Message(Column) ..............
-------
firstmsg
nextmsg
middlemsg
jsjfsjmsg
jhsffmsg
lastmsg

In table i need lastmsg--- How to get

View 7 Replies View Related

Sp_send_dbmail Query Option

Sep 22, 2007

Hello,
I'm using sp_send_dbmail with query option. Is there a way to not have "1 rows returned" in the email?
Thanks!

View 3 Replies View Related

In SqlServer Management Studio Express, Server Type Option Is Greyed Out, Also Publication Option Missing

Apr 27, 2008

Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio  2005 Setup.

View 2 Replies View Related

SqlCeResultSet

Sep 13, 2007


How can i get the current row index of SqlCeResultSet specially after calling Seek method?

View 3 Replies View Related

SQLCeResultSet + Filter

May 17, 2007

Is there anyway to filter a existing SQLCEReusltSet?

View 1 Replies View Related

Paging On SqlCeResultSet

Aug 17, 2007

Hi

I am using SqlCeResult and i want to give paging on that and read in help that you can do paging on ResultSet so . I need code example so that i can understand how to implement this in resultSet.


Thanks & Regards
Vishal

View 4 Replies View Related

'set' Query Option To Results In Text? Print Ss, Mis?

Jul 17, 2003

In TSQL, is there a way to 'Set' the query option results in text as the head of my sql statement?

kind of tired of hit contol+t to get the text result.

also, when I
print '@dateStart--' + cast(@dateStart as varchar(30))
I don't get seconds and I even need mis.
Is there a simple way instead of a combination of datepart() to get ss, mis?

thanks
David

View 5 Replies View Related

How To Disable Connect Option In Query Analyzer

Feb 28, 2004

Does anyone know how to diable the File => Connect option in Query Analyzer?
Thanks

Clarification:
The situation is that I have several users on an application that controls their access to the DB. The user ID has full rights to the DB but the application stops them from making changes to data. Now I have a user that needs to be able to create and run SELECT statements in a “Query Analyser” type of environment. I can publish a Query Analyser session with locked credentials using Citrix, but I don’t want him to be able to use the File => Connect option to reconnect with one of the application Ids thus giving him full rights in an open environment.
Any ideas?
Thanks

View 1 Replies View Related

T-SQL (SS2K8) :: New Query Option Is Not Visible In Server?

Mar 20, 2015

I just inherited a few Servers that have SQL Server 2000.

I cant find the new query Icon?

[URL]

View 2 Replies View Related

How To Make Query Select Option Results

Jul 12, 2014

I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...

I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?

Here is the form code with query :

<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST">
<select name="ManagerID" id="manager" style="width:400px" class="form_textbox">
<?
$db_connect= mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $db_connect) || die(mysql_error());
$sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";

[code]....

View 2 Replies View Related

Insert Identity Value With SqlCeResultSet

Aug 20, 2007

Hi

I have a table with an identity column. This is set to increase by 1 and start on 1, and is the first column of the table.
When I insert a record into the resultset, I get an error stating that it can't be DbNull.
Th record is created from a resultset with the query "Select * from table", so the id should be included

But as I understand it, it should be increasing automatically, right? Becuase that's the behaviour of the normal sql Insert command. I have seen an article that just left the id column in the record alone before inserting it, but it didn't help me.
How can I sidestep this issue?

View 4 Replies View Related

SQLCeResultset, Connections And Performance

Sep 25, 2007

I am currently working with typed sqlceresultsets. I noticed that each resultset manages its own databaseconnection. I am considering to change this to a situation where I can reuse a databaseconnection on more than one sqlresultset.
Will this result in better performance? for example, when I have 2 updates for wicht I need 2 resultsets, wouldn't it be better to use 1 connection and transaction instead of each resultset creating/disposing its own? I would realy like to hear some oppinions. thanks!

View 4 Replies View Related

Update Database Using Sqlceresultset

Aug 21, 2007

Hi,
I have an updateable sqlceresultset, which i use to bind to a datagrid. The user can modify values in the grid. I wish to update these values in the database only when the user has finished making these changes and clicks "Accept" button. But with the sqlceresultset, it looks like with every modification in the grid, the resultset is updated and change is written to the sdf database file . Please suggest how to work around this.

Thanks

View 1 Replies View Related

SqlCeResultSet Performance Vrs OLEDB

May 23, 2007

Hi I have written an Windows Mobile Application that insert 1000 records on SQL Compact 3.1 but I am having performance issues. If I use SqlCeResultSet and SqlCeUpdatableRecord in C# tha insert take 7 seconds to complete. But if I write a C++ application that uses OLEDB it takes only 4 seconds.



How can I improve the performance of the C# application? Also I would like to know if there is any possibility to use the same OLEDB objects that I use in C++ in a C# application.



Thanks a lot

View 4 Replies View Related

How To Update An SDF Database With A SqlCEResultSet

May 4, 2008

I have created my connection correctly
if (_deviceConn.State == ConnectionState.Closed)
_deviceConn.Open();

_deviceCMD = new SqlCeCommand("UPDATE metar SET observation='"+ Value1 +"',lastupdate='"+ Value2 +"',Valid_Time='"+ Value3+"' WHERE aerodrome_oaci_code='"+ FilterValue +"'", _deviceConn);

int RecordsAffected = _deviceCMD.ExecuteNonQuery();

The code executes fine, in fact the recordsaffected returns 1, but Nothing happend on database side. Another thing is, when i run the project on release mode, a copy of the SDF file is there, so, which of the sdf files the project is actually updating?, cause im querying both (the one in my project source path and the one copied to release folder) and both have the same data witout any modification.

My Copy to output directory setting is = Copy if newer.

If i try this with query analizer works just fine.

Any ideas?

View 1 Replies View Related

SqlCeResultSet - Problem After Deleting First Record

Oct 4, 2006



Hi,

I've noticed that after the first record from a ResultSet is deleted, HasRows property throws an exception like "The current row was deleted." Method ReadFirst() throws similar exception as well.

More details:

1. Create a ResultSet: ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable | ResultSetOptions.Sensitive)

2. Let's say, there is no records in the resultset. Insert two new ones. Then call: ReadAbsolute(0); Delete(); HasRows; and the exception is thrown.

3. Then you can call ReadFirst() or Read with the same result.

4. Now call ReadAbsolute(0). It returns true. Call HasRows, ReadFirst() or Read() and you will get the exception each time.

I tried to explain this with the fact that Delete() doesn't change the current position of the ResultSet. But I don't see why that would mess the HasRows property or why after ReadAbsolute(0) retuns true, HasRows or ReadFirst() doesn't work.

Could anybody tell me what the problem is?

Thank you.

View 7 Replies View Related

SQL Mobile - SQLCEResultSet - Fast Retreival

May 23, 2007

Hi,



I am trying to develope a Pocket PC application using MS Visual Studio 2005 (C#)and the database is in the device (.sdf file).



Am trying to retreive a single row from a table containing around 10000 rows. I would like to know the fastest method to retreive the values.



whether it is faster to use an SQLDataAdapter to retreive the values to a dataset or datatable OR retreive the values to a SQLCEResultSet OR is there any other faster retreival method.



Please advice



Thanks,



Anujith



View 3 Replies View Related

SqlCeResultSet - Problem After Deleting First Record

Oct 3, 2006

Hi,

I've noticed that after the first record from a ResultSet is deleted, HasRows property throws an exception like "The current row was deleted."   Method ReadFirst() throws similar exception as well.

More details:

1. Create a ResultSet: ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable | ResultSetOptions.Sensitive)

2. Let's say, there is no records in the resultset.  Insert two new ones.  Then call: ReadAbsolute(0); Delete(); HasRows; and the exception is thrown.

3. Then you can call ReadFirst() or Read with the same result.

4. Now call ReadAbsolute(0).  It returns true.  Call HasRows, ReadFirst() or Read() and you will get the exception each time.

I tried to explain this with the fact that Delete() doesn't change the current position of the ResultSet.  But I don't see why that would mess the HasRows property or why after ReadAbsolute(0) retuns true, HasRows or ReadFirst() doesn't work.

Could anybody tell me what the problem is?

Thank you.

 

View 1 Replies View Related

Update TextBox Binding To SQLCeResultSet

Jan 31, 2008

Hello,

I'm trying to develop a VB.NET (2005) form with just a ComBobox and a TextBox.

I bind a combobox with the SqlCeResultset (Scrollable | Updatable ). Do the same with the TextBox.
Combobox1.DataSource = rsResultSet
TextBox1.DataBindings.Add("Text", rsResultSet, "Obs")

This works (when I change the Combo item the textbox changes as well) but any changes to the textbox text do not reach the DB.If i change the Combobox item and back the old data is still there. If i look at the result
set it does appear as updateable.

How can I update the SqlCeResultset when I change the TextBox text?

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved