Parameter For Where In (@Par1) In Typed Dataset

Aug 21, 2007

Hi,

 Is there any work around to passing a set of strings to a parameter in a Typed Dataset for example I am pasing '4226222172004','4212012182004' which I build on my code the number of items will vary passed on the user selection but since the Typed Dataset uses sp_executesql and the parameters are change to '''4226222172004'',''4212012182004'''

 Any ideas how I can format the Parameter I am passing.so that it will end like where in ( '4226222172004','4212012182004') instead of where in ('''4226222172004'',''4212012182004''') I again the number of parameters will very.

Thanks

Julio D

View 1 Replies


ADVERTISEMENT

How To Use Aliases In Typed Dataset

Jun 27, 2007

I have a SELECT query with an alias in it.
The intellisense shows all field except the alias one.
What goes wrong?

View 2 Replies View Related

Strongly Typed Dataset To SQL File

Nov 23, 2004

I have a strongly-typed that I fill w/in my app, then use the dataset to insert rows into a SQL table.

3 fields look like this:
RegHrs - decimal
OTHrs - decimal
TotHrs - decimal

When I insert the fields using parameters, the params look like this:
paramValues(10) = New SqlParameter("@RegHrs", SqlDbType.Decimal)
paramValues(11) = New SqlParameter("@OTHrs", SqlDbType.Decimal)
paramValues(12) = New SqlParameter("@TotHrs", SqlDbType.Decimal)

The fields in the SQL table are defined as:
RegHrs - decimal (9,2)
OTHrs - decimal (9,2)
TotHrs - decimal (9,2)

Before I insert the row (from w/in the VB code), I stop the code and verify that the values being placed into the params are:
5.85
0
5.85

After the SQL insertion, the values w/in the SQL Table contain:
6
0
6

What am I missing?????

Thanx
JerryK

View 3 Replies View Related

Typed Dataset Failed To Enable Constraints

Sep 11, 2006

Hi, I am  trying to use a typed dataset created using the Query builder which returns the data correctly when I use 'Execute Query' in query builder but as soon as I attempt to return a dataset using the GetData method created I get the following error:         Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. Which is not very helpful, any ideas where the problem may be? I have tried switching off enforce constraints and setting the NullValue property of all strings to 'Emtpy' bu that has no effect. Thanks in advance 

View 2 Replies View Related

Typed Dataset Schema Throwing Error

Apr 8, 2006

Hello,I'm using a typed dataset to access my database (SQL Express); I have a table that returns data, which two fields are null; I'm getting this error:{"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."}There are no foreign-key or unique key constraints, so it has to be a non-null issue; I changed the two fields in the table to have a null value of Empty instead of Throw Exception, but I don't understand what the problem is coming from...  I'm at a loss.Thanks.

View 2 Replies View Related

User-defined Types (UDT) In Typed Dataset

Dec 24, 2006

Hi!

I'm need to use typed Dataset with CLR UDT, but when I'm trying to create TableAdapter in Dataset designer, I'm gettng error message: User-defined data types are not supported in DataSet designer

Is any way to get it working?

My UDT defined in separate assembly as:

[Serializable]
[XmlRoot(Namespace = NS)]
[StructLayout(LayoutKind.Sequential)]
[SqlUserDefinedType(Format.Native, Name = DataType, IsByteOrdered = true, ValidationMethodName = "Validate")]
public struct Coordinate
: INullable,
IXmlSerializable
{
.....
}

Working environment: Visual Studio 2005 SP1, MS SQL 2005 SP1 + Hotfix.

View 2 Replies View Related

Insert Unicode Data Into The Database With Typed DataSet

Dec 11, 2006

Hi all,I am using a Strongly Typed DataSet (ASP.NET 2.0) to insert new data into a SQL Server 2000 database, types of some fields in db are nvarchar. All thing work fine except I can not insert unicode data(Vietnamese language) into db.I can't find where to put prefix N. Please help me!!!   

View 1 Replies View Related

Generating Strong Typed DataSet From An SP That Returns Two Tables.

Dec 26, 2007

Hi,
 I have  a little question. I searched google, and could not find good answer for this one.
I have a stored procedure that returns two tables. Usually I generate a dataset out of a stored procedure by dragging it to the dataset.
When I drag this one it creates a DS with only one table, the first one.  How can I make it use both tables?
 
Thank you.

View 2 Replies View Related

CE 3.5, VS 2008, Typed Dataset: Get The Updated Identity Of Inserted Row

Sep 15, 2007



Hello,

Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why?

also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text."


(Should I post this in Orcas forum?!)

Regards,
Parham.

View 5 Replies View Related

SQL Server CE 3.5, Typed Dataset, Retrieving @@IDENTITY Of Inserts?

Mar 19, 2008

I'm at loss how I'm supposed to work with typed datasets and Sql Server Compact 3.5, when inserting records and I need to update my datatables with the primary key of newly inserted rows.

I've tried adding a RowUpdated handler to all tableadapters that look like this:





Code Snippet
void Adapter_RowUpdated(object sender, System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs e) {

if(e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) {

if(e.Row.Table.PrimaryKey.Length > 0) {
SqlCeCommand selectIdentityCommand = new SqlCeCommand("SELECT @@IDENTITY",e.Command.Connection);
if(e.Command.Connection.State == ConnectionState.Open) {

e.Row[e.Row.Table.PrimaryKey[0].Ordinal] = (int)selectIdentityCommand.ExecuteScalar();

}
}
}






I've previously used this type of approach when working with an OleDbDatabase, which works just fine. But it doesn't work with Sql Server CE 3.5, and since it doesn't support stored procedures I can't fix it that way either. And it doesn't support commands in a batch (i.e appending the Insert command of the adapter with ";SELECT @@IDENTITY") so that doesn't work either...

So how are we supposed to use Sql Server CE 3.5? It's impossible together with datasets? Or am I missing something obvious?

Any hints would be greatly appreciated! Thanks!

Cheers!

View 3 Replies View Related

Uploading A File To A SQL Database Via A Typed Dataset And Using The FileUpload Control.

Oct 2, 2006

I am trying to upload a file in ASP.net 2.0 to a SQL database using the FileUpload control. I am doing this by way of a typed dataset.Here is my code.Dim rta As New ResponseTableAdapterDim rdt As ResponseDataTable = rta.GetResponseByID(1)Dim rr As ResponseRow = rdt(0)Dim fs As New FileStream(fu.PostedFile.FileName, FileMode.Open, FileAccess.Read)Dim br As New BinaryReader(fs)Dim image() As Byte = br.ReadBytes(fs.Length)br.Close()fs.Close()rr.UploadFile = imagerr.NameFile = fs.Namerta.Update(rdt)The code runs without an error but afterwards I find that nothing has been stored in the UploadFile cell in the database but the file name has been stored in the NameFile cell in the database. Any ideas?Your help is much appreciated. James 

View 1 Replies View Related

How To Create An Update Or Delete Method In A Strongly Typed Dataset?

Mar 22, 2007

Like the subject says, I'm using strongly typed datasets.  I'm using to designer to create the datasets and the methods.  I can select and insert, but I can't update or delete.  I right click on the adapter bar and select Add Query.   I sleect 'Use SQL Statements'I select 'Update' (or 'Delete')I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'I can click on next, but anything else gives me errors.  I'd list them, but I'm clearly doing something wrong and it's probably obvious. Diane 

View 5 Replies View Related

Reporting Services :: Adding Dataset Parameter Doesn't Add Report Parameter In Visual Studio 2008 SSRS

Apr 22, 2015

I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter.  If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank.  However, sometimes this will automatically add. 

Is this a bug in Visual Studio?  How do I get around this? 

View 3 Replies View Related

Filters For Dataset From Parameter - How To Ignore If The Parameter Is NULL ?

Apr 10, 2007

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.



I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :



Left =Fields!RegionCode.Value
Operator =
Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.



Thanks

View 5 Replies View Related

Using Parameter From XML DataSet In Another XML DataSet

Apr 5, 2007

Hi every body...
I have a probleme
I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime))
I need to call this method. But my problem is how pass parameter ?
I see the tab Param but it isn't work as I wait,... maybe I do a mistake...

I want that statTime and endTime are select by the user via a calendar for example...
now idIndicator and idEq was result of an other dataSet from a xml datasource...

But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression :
=First(Fields!idEq.Value, "EquipmentDataSet")
but when i execute the query, the promter display <NULL>...
So I don't know how to do and if it is possible !
I hope someone can help me !
Thank you !

View 3 Replies View Related

Value Cannot Be Null. Parameter Name: DataSet (was SQL Help :()

Feb 11, 2005

I keep getting "Value cannot be null. Parameter name: dataSet" when I run this statement and bind it to a dataset using a sqldataadapter in ASP.NET using VB.NET.


"SELECT playerstats.playerid, playerstats.gameid, SUM(playerstats.fta), SUM(playerstats.ftm), SUM(playerstats.tpm), SUM(playerstats.rb), SUM(playerstats.fga), SUM(playerstats.fgm), SUM(playerstats.tp), SUM(playerstats.st), SUM(playerstats.a), 100(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, player.playerid, player.lname + ', ' + player.fname AS fullname FROM playerstats, player WHERE playerstats.playerid = player.playerid AND player.leagueid = " & ddlLeague.SelectedValue & " ORDER BY " & strSortField


I know there is data in the db.

Please help :confused:

View 6 Replies View Related

Basic Parameter In Two Dataset

Feb 11, 2008


Hi All

I have two datasets (for querying active directory but this is kind of irrelevant for my problem which is a basic one)

The first gets a list of groups and I want to display GroupName in a dropdown list:

SELECT DistinguishedName, GroupName, GroupDescription
FROM tvf_GetListOfGroups('LDAP://OU=Applications,OU=Groups,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree')
ORDER BY GroupName


The above is fine and works but then I want to use the corresponding DistinguishedName of the GroupName selected in
the dropdown to drive the second query to populate the report


SELECT * FROM tvf_GetGroupMembers('LDAP://' + @DistinguishedName)
ORDER BY UserLogin

My problem is how to populate @DistinguishedName with the correct value to run run the query.

How? Please?

Thanks
Pete

View 1 Replies View Related

Passing Parameter Into Dataset

Jun 15, 2007

Hello



Im trying to pass a integer parameter to my dataset query depending on the selection made from the user. It is suppose to get the Top number off records based on what number the user chooses



I get the error Incorrect syntax near '@Param_TopNo, If i take the top @param_topno out the query works



The cutdown version off the query is...



Select Top @Param_TopNo SaleDate, Consultant, Productname

From tblSales

where product = 2000


Im not sure if the method above is even achievable !!!! does anyone know how i can pass a variable/parameter into my dataset and maybe provide an example off the code.



thanks



View 1 Replies View Related

2 DataSet From Xml DataSource, And Parameter ...

Apr 5, 2007

Hi !!!
I have a problem, I don't know how to do...
I have 2 dataset from 2 differents XML dataSource...
I want the result of one dataSet to be a parameter for the second !

I try to add a parameter in param tab, with expression, I choose DataSet and
=Sum(Fields!ID.Value, "DataSetID")
but when I execute the query, the prompt for the param appear, but the value of id was <NULL>...

Someone have ideas to resolve this problem ?
thank you for your help !

View 1 Replies View Related

Default Parameter Value = First Value Of Dataset

May 31, 2007

Any way to have the default value of a parameter for a multi select parameter be the first value of the query of that parameter?

View 7 Replies View Related

Pass Hidden Parameter From One Dataset To Another

May 3, 2007

I have 2 datasets in my report and I want to pass a field from the 1st dataset as a parameter to the 2nd dataset.

How can I do this?

I tried to set the report parameter from a query using the 1st dataset but the parameter is always equal to the first record in the recordset-it never changes per row.

View 1 Replies View Related

How Do I Use A Parameter In A Dataset Field Value Using An MDX Query?

May 23, 2007

Let me simplify. The value of my dataset field Dimension_1 is



[SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION]



I want to use parameters for SOB Year and Yr Id like I do on the query.



But if I use



="["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].[" & ["& Parameters!dimension_1_column.Value &"]"



it returns null for the values. I don't know how to make these parameters





I am passing in parameters for the table and column to build an MDX query here is my query.



="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value &"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"





Anyone know what value I need to use on the dataset field to access the column?



Thanks,

Roger

View 10 Replies View Related

Parameter Cannot Set The Command Text For Dataset

May 14, 2007

Hello



I get the following error in a very quick test system i created:



An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'test'.

Error during processing of the CommandText expression of dataset 'test'.



My sql is:

= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)



There is nothing obviously wrong in the code as far as i can see



The parameter 'test1' is of type 'string'



The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'



I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine



Thanks

View 5 Replies View Related

How Can I Set Default Value Of Parameter That Come From Dataset By Using Expression?

Feb 27, 2008

Hi,

Now I can query dataset and bind to report parameter. But I have some problem about setting default value. I don't know, how to select max value from dataset by using expression.

Example, I query disticnt year from my table. The result has 2 years "2007" and "2008"
select distinct year(mytime) as myyear from mytable.

I set available parameter by using this dataset. Then, I set default value for this parameter by the other dataset ...

select max (distinct year(mytime)) as maxyear from mytable.

I want to select max value by using expression, not select max by using dataset.

How can I do?

Thank you very much.

View 4 Replies View Related

How Can I Have A Variable Number Of Parameter Values In A Dataset?

Apr 20, 2007

I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter.  The problem is I don't know how many.  I have a textbox that the user can enter search words in.  The select string is built from the string of words that are entered, like this:For iCount = 0 To UBound(sArray)    strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "Next Can I do this is a dataset method?  How?  If I can't, what are my options?Diane 

View 6 Replies View Related

Referencing A Multi-valued Parameter In A Dataset

Oct 22, 2007



Hello,
I am attempting to create a cascading parameter.
Parameter's 1 & 2 are multi-valued.
Datasets 1 & 2 supply Parameter's 1 & 2.
The values for Parameter 1 as derived from Dataset 1 are 'A', 'B', 'C', 'D' and 'E'.
The potential values for Parameter 2 will only be created if value 'C' is amongst the multiple values selected for Parameter 1.
Thus I need to write my query for Dataset 2 so that it can check the values of Parameter 1 for the existence of 'C' otherwise it returns nothing.
What is the best practice for a dataset referencing the values of a multi-valued parameter in order to generate it's resultset?

Any help is appreciated.

View 2 Replies View Related

Using A Dynamically Created Parameter In Another Dataset Query

Jul 27, 2007



It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:

reportMonth - Just a non-queried list of months for the user to select
reportYear - Generated using the following query from dataset "Years":

declare @curYear int
set @curYear = 2000
declare @yearTable table (repYear int not null)
while @curYear <= year(getdate())
begin
insert into @yearTable(repYear) values (@curYear)
set @curYear = @curYear + 1
end
select * from @yearTable

This generated a list like:

2000
2001
2002
etc.

I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.

Thanks

View 10 Replies View Related

Dataset Becomes Empty After Adding Query Parameter

Mar 13, 2008



Hi all,

hope anyone can help me on this one.

I have a parameterized report based on a MDX query (so no drag and drop of filters and fields).
Now i have to ad a parameter. So i open the query parameter dialog box and i ad my parameter.
when i click ok , my dataset becomes empty , resulting in an error on my report saying that the fields on the report cannot by found and should be in the scope of the dataset.

Has anyone experienced this problem before, or am i forgetting something here?

thanks in advance

Steven J

View 1 Replies View Related

How Do I Get The OUTPUT Parameter Value From A Stored Procedure That I Am Using To Fill A Dataset?

May 10, 2007

View 6 Replies View Related

Pass Parameter Values To Stored Procedure In Dataset

Jul 10, 2007

I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)



My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string

In the dataset, I typed

=IIF(Parameters!ReportType.Value="Day", "EXEC spDetailsByDay " & Parameters!StartDateTime.Value & "," & Parameters!Day.Value & "," & Parameters!Hour.Value & "," & Parameters!Value1.Value & "," & Parameters!Value2.Value", "EXEC spDetailsByMonth")



I am getting syntax errors. Can anyone help me how to pass parameters to stored procedure in dataset.



Thanks.

View 4 Replies View Related

Preview Report Tab Is Prompting For An Input Parameter That Does Not Exist For The Dataset

Apr 11, 2008



I created a dataset based on a stored procedure. There are nine input parameters. When I click on the preview tab, it is asking me for ten input parameters.

I double checked my dataset on the data tab, and it only shows the expected nine parameters when I look at the parameters tab. I can also run the query without issue from the data tab.

I have no idea why it is asking for an extra parameter on the preview tab. It is a column name that does exist in my database, but is in no way used in the stored procedure in question.

Has anyone else run into a similar situation?

View 1 Replies View Related

Reporting Services :: Passing All Parameter Selection To Dataset Query

Nov 20, 2015

I have a report that prompts the user to select a parameter, for simplicity, let's say the parameter is for color choice, options are Red, Yellow, Blue or *. The * is for include all colors. I am passing that parameter back to the dataset query which, again for simplicity is

select
Hue from AvailableColors
whereColor
= @ColorParam.

For a specific color this works fine, for the "*" selection it returns a null. It would seem that I need to convert the * to % but I am not sure how.....

View 6 Replies View Related

Reporting Services :: Report Builder - How To Use Multiple-value Parameter In Dataset Control

Oct 25, 2015

I have used my parameter value in where clause of my dataset control SQL query. When I choose a single value in my multiple-value parameter then I get output as expected. Hovewer when I choose multuple values I get an arror:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
----------------------------
Query execution failed for dataset 'DATABASE'. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)

I am convinced that it's a syntax problem. Is there a way I can change syntax of data output from query:

select @parameter

I need output to be like: value, value, value ... etc.

The dataset control query looks something like the following:

select
ID,
a,
b,
c,
d
from DATASET.dbo.Table
where ID in (select @parameter)

I have been searching through google for weeks to solve this and got this far and got stuck. Is it possible at all to achieve what I am looking for?

View 4 Replies View Related







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