Hi, I have a dataset created via the Execute SQL Statement control and wish to pass it into a script control that within the vb.net script I wish to perform different functions to it.
The main goal is to take the dataset loop through it and record values into an array then take that array and loop through that so I can send an email containing the information within the array.
Can anyone help?
Hi,
I'm developing an ASP.NET application that creates on-demand reports with Reporting Services 2005 in report server.
I use a custom data set that contains the data that I want to bind to the report, and I use a WebService to pass that DataSet to the reporting services to accomplish my purpose.
The query passed to the reporting services is:
<Query>
<Method Namespace=http://myNameSpace.com Name="GetDataSetForReport"/> <SoapAction>http://myNameSpace.com/WebService/GetDataSetForReport</SoapAction>
<ElementPath IgnoreNamespaces="True">GetDataSetForReportResponse{}/GetDataSetForReportResult{}/diffgram{}/ReportDataSet{}/MyTable{Fields1,Fields2,Fields3,..,FieldsN,Image}</ElementPath>
</Query>
This works well and all the fields are displayed in the report fine except the Image Fields, that are not showing (the red X appears instead of the image).
The issue is that all the data that is bind to the dataset is contained in a SQL Server 2005 database, and the Image column in the table of that database is a VARBINARY(MAX) field (I insert the image into the database table).
Also, when I created the dataset in .NET, the column type of the dataset I declared for the Image field was Byte[], but that didn't work for me. I've also tried to declare as the field type Byte,SqlByte,Image,SqlBinary, etc without succesfull results.
I've also tried creating a XML Schema, declaring the Image field as base64Binary, then do DataSet.ReadXMLSchema(myschema) and then retrievied the data from sql server and filled the dataset, Again, the Images doesn`t display.
For finnishing my unsatisfactory tests, I've also tried to handle the image data with a MemoryStream object:
MemoryStream mem = new MemoryStream();
MemoryStream oStream = new MemoryStream();
mem.Write((Byte[])dr["Image"],0 , ((Byte[])dr["Image"]).Length); //dr is the datareader used to read from the DB
Image image = Image.FromStream(mem);
try
{
image.Save(oStream, ImageFormat.Png);
}
catch{}
newRow["Image"] = oStream.GetBuffer(); //newRow is a row of the custom DataSet.Table passed to the SSRS.
But again, the Image is not showing.
Any suggestions?
Thanks for you time.
So I have a report that uses an MDX query to fetch the main data for the tablix. Then I have a custom row added which pulls data in via a Lookup function and references a different dataset. The report has 3 parameters. Each dataset uses those 3 parameters in its underlying query. However, the dataset referenced in the lookup function doesnt seem to be updating when change the parameters and re-run the report.
View 2 Replies View RelatedHi
Finding this forum really useful...I wonder if you could help me with this.
I've got a very simple script component and I just want to use a Dataset in it.
When I declare the Dataset i get a warning and then consequently an error. herers an image grab of whats happening:
http://www5.webng.com/hopelist/error.jpg
If you can't see the image please let me know.
Essentially VS gives me a hint to add a required assembly which it needs....but when I click to add I get a 'Visaul Basic Compiler has encountered a problem and needs to close' type error.
Anyone got any idea whats going on and why I'm having such a hard time just accessing a Dataset??
thanks
Andy
Hello,
I have created SSIS package programmatically, I want to add Lookup transformation,
How can I add column from reference dataset to the transformation?
I have try to add new output column but it gives me an validation error, I write following coed to add new output column to lookup.
IDTSOutputColumn90 outputColumn = this.lookup.OutputCollection[0].OutputColumnCollection.New();
outputColumn.Name = col.Name;
outputColumn.Description = "Staging table output";
outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
outputColumn.ErrorOrTruncationOperation = "Copy Column";
outputColumn.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);
Please suggest other way to add column from reference dataset to transformation output.
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 0 Replies View RelatedI have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows.
Table 2: Large table with CLOB, 10,000,000 rows
select CLOB
from table2
where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
View 2 Replies View Related
Is there any way to display this information in the report?
Thanks
Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.
ALTER PROCEDURE [dbo].[MyStorProc]
(
@Status smallint,
@RowCount int = NULL,
@FacilityId numeric(10,0) = NULL,
@QueueID numeric (10,0)= NULL,
@VendorId numeric(10, 0) = NULL
)
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
If @Status = 0
BEGIN
SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END
i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer.
but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
Hi,
I have two datasets in my report, D1 and D2.
D1 is a list of classes with classid and title
D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.
I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.
Is there any way to do this in RS2005?
Using this IIF statement:
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
I'm getting a "Input string was not in a correct format." when I'm running a insert statement against my SQL Server 2005 db table. This helps me zilch as I cant see the actual SQL statement to see which one wasnt right. Using a SQLDatasource and a Formview btw.
Datasource is called xSqlIB and formview is called fmvIB.
Any ideas?
I want to update a record that has a datetime field in it. But what if there is no parameter given for that field, i.e. what is the correct type/value to pass as parameter?
Example: A DOB field for a user profile, but the user doesn't enter his birthday
This is some example code that I use to update:
Private Sub UpdateDOB(ByVal dob As Date)
Dim parameters As SqlParameter() = { _
New SqlParameter("@Birthday", SqlDbType.DateTime, 8)}
parameters(0).Value = dob
'Run Stored Procedure
This gives me the exception "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM" when I don't pass a valid date. I tried passing Nothing but same error (in this case it convert dob to "#12:00:00 AM#")
The only thing I can think of is to use Date.MaxValue and then check in application logic, but there must be a better way!
finally on the road to becoming a full blown ass kicking mcdba!
passed it before christmas with the help of my girlfriend.
brilliant or what!
failed it last time back in 2004
all the best and have a cracking 2006 all!
is there a way in t-sql to pass a db name as a parameter, so that select * from [@passedDB].[dbo].[tableName] would work? Without dynamically building and executing the sql statement?
View 1 Replies View RelatedI have a generic page with some fields on it, I would like to be able to "groom" the values so that the data is nice and consistent.
Example: input 1 -- user types in "bob" or "BOB" or "Bob" or "JOE" etc....
the resulting record in Sql would be "Bob", "Fred", "Joe"
nice and formted out.
Any suggestions?
I have tested the following code, and it works for me:
DECLARE @XML XML
SET @XML = '
<DocumentElement>
<data>
<item code="ABCDEFG" quantity="1" sort="0" />
<item code="XCFVGBF" quantity="1" sort="0" />
<item code="ABCDEFG" quantity="10" sort="0" />
</data>
</DocumentElement>'
SELECT SUM(x.qty * ISNULL(p.price_mn,0))
FROM (SELECT [Code] = A.A.value('@code','varchar(20)'),
[Qty] = A.A.value('@quantity','INT')
FROM @xml.nodes('/DocumentElement/data/item') AS A(A)) X
LEFT JOIN productprice_t P ON p.code_id = x.code
The question is, how can I do this if the XML is in a different format & doesn't use any attributes?
So it looks like this (which I'm getting from an ADO.Net datatable using .WriteXML):
<DocumentElement>
<data>
<code>ABCDEFG</mercurycode>
<quantity>1</quantity>
<sort>0</sort>
</data>
<data>
<mercurycode>XCDFEG</mercurycode>
<quantity>2</quantity>
<sort>0</sort>
</data>
<data>
<code>ABCDEFG</mercurycode>
<quantity>10</quantity>
<sort>0</sort>
</data>
</DocumentElement>
I have a report in SQL that passes parameters at runtime entered by the user for two date ranges (beginning and ending). I'm trying to write a formula that will print a specific field *only if* the specified date range entered by the user is BETWEEN a specific value (like 200401). This is kind of reverse of a normal WHERE, BETWEEN clause.
I tried a standard BETWEEN predicate in my WHERE clause like:
IF '200401' BETWEEN ?BegPer and ?EndPer then salesanal.ptdbud01 else 0
But, it's returning an error that my Then statement is missing. I can't use a normal statement like 'IF ?BegPer >= '200401' and ?EndPer <= '200401', then.....' because users could enter a RANGE of periods, so it would be difficult to code all of the possible combinations this way. I'm actually doing this in Crystal, but if someone can give me a standard MSSQL example, I can translate that over to Crystal.
Thanks in advance,
Michelle
I am quite new to SSRS and am having some difficulties in trying to develop a new report via Business Intelligence Studio based on a stored procedure which requires the input of 1 parameter, and also has an optional parameter which I default to NULL in the sproc.
When I create my dataset I select the given sproc I want and when I attempt to execute it, I am prompted for the parameters the sproc expects.
However, when I enter a value in the dialog for the required Parameter I get a SQL error indicating that the parameter the sproc expects was not supplied. I have profiled the call and see the attempt to execute the sproc, but no parameter value.
Can some one tell me why the value I enter is not being passed to the sproc in my database ? Is there some special syntax that I need to use ?
I have scanned a number of sites & through the books I have and can't find anything on this. From what I have read, when I exec my sproc the parameters get recongnized & I can just enter my values. This doesn't seem to be the case.
Any help and/or suggestions are appreciated !!!
Thanks.
I have a requirement where, I need to get the value of parameter from a query string of url and use it in my report.
Ex:- http://www.mysite.com?name=bobby
From the above url, I have to take the value of name and be able to use it in my report query or assign to a parameter.
Please let me know, how can I acheive this.
we remount archives in special situations to one or more databases.
We'd like our embedded RS reports to be flexible enough to learn at run time which database to use as a source. Are there ways to do this in 2005, 2000 or both?
I have a boolean parameter on my report that the user will enter. Currently, it shows as an option button with True/False beside each option as the words used. Is it possible to have a check box instead of an option button for the user instead?
Thanks in advance!
Hi All,
I have following issue.
Currently, whether values from parameters (there are 20 parameters) passed to one stored procedure will have following script to handle
INSERT INTO log table for all value from TABLEA where ID=@ID
UPDATE TABLEA SET COLUMNA=@COLUMNA and etc,STATUSDATE=getdate() WHERE ID=@ID
However, this creates the problem when the user submit same records, 40 times a day. As a result, statusdate is being updated frequently. This has created the problem in our daily report.
Thus, the user requested to have checking on each column. If any of column has different value, then the update can be done.
what i am thinking at this moment is
BEGIN TRAN
INSERT INTO log table for all value from TABLEA where ID=@ID
INSERT INTO TEMP TABLE (CHANGE_IN_COLUMNA)
UPDATE TABLEA SET COLUMNA=@COLUMNA OUTPUT CASE WHEN DELETED.COLUMNA <> INSERTED.COLUMNA THEN 'YES' ELSE 'NO END and etc,STATUSDATE=getdate() WHERE ID=@ID
IF ANY COLUMN HAS YES VALUE THEN COMMIT TRAN
OTHERWISE ROLLBACK TRAN
Do you think that this is a good solution?
Thanks for the assistance
I have been attempting to build a search engine that searches a database full of invoices. The user will enter in a invoice number and then the results will be returned if the exact invoice exists and if the record belongs to the user.
My first attempt has involved the use of a SQL Datasource to pass a stored procedure based on what is in the search textbox and the information in the cookie about the user. I was hoping that on the btnsearch.click event that I could somehow return the stored procedure in the SQL DataSource. My next step was to make the result (if exists) part of the query string like this:
Response.Redirect("~/Invoice.aspx?Invoice=?"+ SQLRETURNEDVALUE)
I'm looking for any help to a possible way to use my idea or any other ideas to get a invoice number and place it into a query string so that the result can be fetched on the next page. Thanks
I am trying to pass multiple values as parameters into my update command:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@selectedusersparam)));I develop my parameter (@selectedusersparam) using the following subroutine: Private Sub btnAddUsersToDeployment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddUsersToDeployment.ClickDim iVal As Integer = 0Dim SelectedCollection As StringSelectedCollection = ""If (lsbUsersAvail.Items).Count > 1 ThenFor iVal = 0 To lsbUsersAvail.Items.Count - 1If lsbUsersAvail.Items(iVal).Selected = True ThenSelectedCollection = SelectedCollection & "," & lsbUsersAvail.Items(iVal).ValueEnd IfNextSelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))Session.Item("SelectedCollectionSession") = SelectedCollectionSqlDataSource4.Update()ltlUsersMessage.Text = String.Empty 'UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (@selectedusersparam))'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"ElseltlUsersMessage.Text = "Select users before adding to deployment. Hold Control for multiselect"End IfEnd SubFor some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the queryI don't understand why.
View 4 Replies View Relatedhello all, im trying to run a select statement using a parameter, but am having extreme difficulties. I have tried this about 50 different ways but i will only post the most recent cause i think that im the closest now than ever before ! i would love any help i can get !!!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim pageID As StringpageID = Request.QueryString("ID")
TextBox13.Text = pageID 'Test to make sure the value was stored
SqlDataSource1.SelectParameters.Add("@pageID", pageID)
End Sub
....
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName=System.Data.SqlClient ConnectionString="Data Source=.SQLEXPRESS;Initial Catalog=software;Integrated Security=True;User=something;Password=something" 'SelectCommand="SELECT * FROM Table1 WHERE [ClientID]='@pageID' ></asp:SqlDataSource>
The error that i am getting, regardless of what i put inside the ' ' is as follows:
"Conversion failed when converting the varchar value '@pageID' to data type int."
anyone have any suggestions ?
I am using two drop downs, like so:
<asp:DropDownList ID="ChurchStateDrop" runat="server" DataSourceID="ChurchStateDropData" DataTextField="State" DataValueField="State" AutoPostBack="True" onselectedindexchanged="ChurchStateDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All States" /></asp:DropDownList>
<asp:DropDownList ID="ChurchCityDrop" runat="server" DataSourceID="ChurchCityDropData" DataTextField="City" DataValueField="City" AutoPostBack="True" onselectedindexchanged="ChurchCityDrop_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="?????" Selected="True" Text="All Cities" /></asp:DropDownList>
I have ????? in the value fields because I don't know what value needs to be passed to negate filtering (to choose all). The Dropdowns have the following SQLDatasources:<asp:SqlDataSource ID="ChurchStateDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [State] FROM [ChurchView]" DataSourceMode="DataReader"></asp:SqlDataSource>
<asp:SqlDataSource ID="ChurchCityDropData" runat="server" ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>" SelectCommand="SELECT DISTINCT [City] FROM [ChurchView] WHERE ([State] = @State)" DataSourceMode="DataReader"> <SelectParameters> <asp:ControlParameter ControlID="ChurchStateDrop" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource>
Now, lets say I wanted to pass a value to the WHERE statement in ChurchCityDropData to coincide with 'All States', what would I replace value="??????" with? Now you may think I'm crazy to do such a thing, but this actually has to do with adding a Denomination Dropdown to show Denominations from all states or all cities. I will figure out the best logic for that later, I just want to know the wildcard to pass to the parameter to choose all states (negate filtering).
I have a stored proc that has several parameters. (using SQL server)
i call this proc and add the parameters etc and execute it etc
the problem is that how can i see the string that is being passed to the db? eg the calling command?
cheers
I am trying to created a stored procedure/query to return all fields from any of my 43 look-up tables in my database. I have all of my look-up table names listed in a VB grid. I want the user to select a particular table, click edit (which pass's the table name) and have one stored procdure return all of the field in the table and populate them in a second VB form with the fields listed in a new grid.
Can one pass the "SQL string" to excute/ create a temp table.... A regular SP requires a specife table name to query from. Is there a way to do this without creating a temp table
Thanks for the help
EM.