Hello,I'm a newbie and I have a little problem that's starting to work on my nerves I want to store a multi line textbox in the database, just like the way it has been entered.I used a varbinary-field.This is my code in my aspx Dim myOptions As New InterTyp.Opties() Dim myCon As SqlConnection = myOptions.getCon() Dim cmdOef As SqlCommand Dim parmOef As SqlParameter cmdOef = New SqlCommand("GetOef", myCon) cmdOef.CommandType = CommandType.StoredProcedure cmdOef.Parameters.Add("@oid", System.Data.SqlDbType.Int).Value = ID 'output parmOef = cmdOef.Parameters.Add("@otext", SqlDbType.VarChar) parmOef.Size = 8000 parmOef.Direction = ParameterDirection.Output 'execute the query myCon.Open() cmdOef.ExecuteNonQuery() txtOef.Text = cmdOef.Parameters("@otext").Value myCon.Close()In my code i call a sp:CREATE PROCEDURE dbo.GetOef @oid integer, @otext varchar OutputAS SELECT otext FROM oefening WHERE (oid = @oid) So my problem is:How do i cast the varbinary back to a text?
I need to find out how to display, edit and save SQL information using Visual Basic. I can create an SQL database and spreadsheet, but do not know how to incroporate it into my project, and make certain pieces of the information (certain cells) appear in the labels or textboxes that I designate in my VB Project.
I'm trying to create a reprot with 30+ datasets/querys. My report is made up of tables (one for each dataset), and it starts a new page whenever a new dataset is being displayed. I would like to have a header on every page, and have it display a custom name for the dataset on that page. How do I approach this? Thanks for any answers or leads.
Is it possible to align a text box to display text with rotation? For example can I display it rotated 90 degrees left so that it reads from the bottom towards the top of the page? All I can find is left/right alignment and top/middle/bottom alignment. I want to rotate the text.
Hello I was very happy to have found the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=491642&SiteID=1 that explained how to get the text to display Bottom-to-Top/Left-to-Right.
The solution was to setup a function that creates a bitmap the text to be displayed. This works well and correctly displays the text image in HTML and PDF. (Excel, XML and CVS won't export backgroud images).
To extend the solution to wrap text it requires a few additional lines...
Code Snippet Function LoadImage3(ByVal stText As String)
stText = stText.PadRight(10) stText = stText.PadLeft(10) Dim iMaxLength as Integer = 180 Dim iMaxWidth as Integer = 180 Dim f As Drawing.Font = New Drawing.Font("Arial",7, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point) Dim bmpImage As New Drawing.Bitmap(1, 1) Dim MyGraphics As Drawing.Graphics = Drawing.Graphics.FromImage(bmpImage)
Dim imageSize As Drawing.SizeF = MyGraphics.MeasureString(stText, f) Dim i As New System.Drawing.Bitmap(iMaxWidth, iMaxLength) Dim g As Drawing.Graphics = Drawing.Graphics.FromImage(i) Dim rectF1 As New Drawing.Rectangle(-(iMaxWidth/2),-(iMaxLength/2),iMaxWidth,iMaxLength ) g.FillRectangle(Drawing.Brushes.White, 0, 0, i.Width, i.Height) g.TranslateTransform((iMaxWidth/2), (iMaxLength/2) ) g.RotateTransform(270.0F) 'flip the image 270 degrees
g.DrawString(stText, f,Drawing.Brushes.Black, rectF1) g.DrawRectangle(New Drawing.Pen(Drawing.Color.White, 1), rectF1) g.Flush() Dim stream As IO.MemoryStream = New IO.MemoryStream Dim bitmapBytes As Byte() i.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg) 'Create bitmap bitmapBytes = stream.ToArray stream.Close() i.Dispose() Return bitmapBytes
End Function
Items highlighted in yellow reflect changes made to orignal solution.
I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.
What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.
1. The end user can select a radio button either "Starts with" or "Contains" 2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.
The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.
After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.
Hopefully someone can get me pointed in the right direction! Thanks for your help!
I have a sqlDatasource with 3 parameters based on the input of 3 text boxes on the page. The datasource returns sales details for a company based on a from and to date. I am attempting to set the value of the 3 parameters in the Selecting event of the datasource control but I'm not getting any data back. If I set the values literally then I get data back. Also when I step through the code I can see the 3 parameters getting their values from the textboxes and the drop down list as they should. This is driving me insane as I'm new to .net and just can't see what is stopping me retrieving the data when using the form fields to set the datasources parameters. Below is the aspx and the code behind for the page. Thanks in advance for any help. aspx..... <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="LabelSales.aspx.cs" Inherits="LabelSales" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSourceSales" runat="server" EnableCaching="false" ConnectionString="<%$ ConnectionStrings:streetwisedigitalConnectionString %>" SelectCommand="DL_GET_SALES_BY_LABEL" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSourceSales_Selecting" > <SelectParameters> <asp:Parameter Name="fromDate" /> <asp:Parameter Name="toDate" /> <asp:Parameter Name="label" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSourceLabels" runat="server" SelectCommand="select label_id, label_name from dl_label order by label_name asc" ConnectionString="<%$ConnectionStrings:streetwisedigitalConnectionString%>"> </asp:SqlDataSource> <div> <table> <tr> <td>Start Date: </td> <td> <asp:TextBox ID="FromDate" Runat="server" Width="70"/> <asp:Button ID="btnFrom" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>End Date: </td> <td> <asp:TextBox id="ToDate" Runat="server" Width="70" /> <asp:Button ID="btnTo" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>Label: </td> <td> <asp:DropDownList ID="LabelList" Runat="server" DataSourceID="SqlDataSourceLabels" DataTextField="label_name" DataValueField="label_name"> </asp:DropDownList> </td> </tr> <tr> <td colspan="2" align="right"> <asp:Button ID="Button1" OnClick="SubmitButton_OnClick" Text="View Sales" runat="server" /> </td> </tr> </table> <p> <asp:GridView ID="GridViewSales" runat="server" DataSourceID="SqlDataSourceSales" ShowFooter="True" AllowSorting="True" AutoGenerateColumns="False" OnRowDataBound="GridViewSales_RowDataBound" EmptyDataText="No data to display."> <Columns> <asp:BoundField DataField="cat_no" HeaderText="Cat No" /> <asp:BoundField DataField="artist" HeaderText="Artist" /> <asp:BoundField DataField="title" HeaderText="Title" /> <asp:BoundField DataField="remix" HeaderText="Remix" /> <asp:BoundField DataField="qty" HeaderText="Sold" /> <asp:boundfield datafield="commission" HtmlEncode="False" dataformatstring="{0:F2}" headertext="Commission"> <ItemStyle HorizontalAlign="Right" /> </asp:boundfield> </Columns> </asp:GridView> </p> </div></asp:Content>code behind...using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class LabelSales : System.Web.UI.Page{ int totalSold; decimal totalCommssion; protected void GridViewSales_RowDataBound(object sender, GridViewRowEventArgs e) { // add column totals to gridview if (e.Row.RowType == DataControlRowType.DataRow) { totalSold += (int)DataBinder.Eval(e.Row.DataItem, "qty"); totalCommssion += (decimal)DataBinder.Eval(e.Row.DataItem, "commission"); } // display the totals else if (e.Row.RowType == DataControlRowType.Footer) { e.Row.Cells[0].Text = "<b>Total</b>"; e.Row.Cells[4].Text = totalSold.ToString(); e.Row.Cells[5].Text = totalCommssion.ToString("f2"); } } protected void Page_Load(object sender, EventArgs e) { } protected void SqlDataSourceSales_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { // *** This method does not work *** e.Command.Parameters[0].Value = FromDate.Text; e.Command.Parameters[1].Value = ToDate.Text; e.Command.Parameters[2].Value = LabelList.Text; // *** This method works! *** e.Command.Parameters[0].Value = "02/01/2007"; e.Command.Parameters[1].Value = "02/01/2008"; e.Command.Parameters[2].Value = "Fat!"; } protected void SubmitButton_OnClick(object sender, EventArgs e) { SqlDataSourceSales.Select(DataSourceSelectArguments.Empty); }} Many thanks Simon
Hello. I have a multi value parameter in my report. I want to know how can I display all of the selected values in this parameter in a text box. Whan I try to use this parameter in a textBox it automaticly take the value: Parameter.param.Value(0) Whice take only the first select value.
I am trying to figure out a way to toggle the visibility of attribute data based on a parameter. Specifically, I have a report that has many columns that an end-user may not want to see, depending on what they are using the report for. I know you can toggle visibilities on individual columns easily enough, however I want the user to be able to select which fields (at the attribute level) they want visible on the report up in the parameters, via a multi-value prompt.
Which suggests that if only one of the two multivalue parameter id is selected display the same (Parameters!ServiceAttribute.Value(0)).
This works fine when i select both the attributes but throws an exception "Index was outside the bounds of the array" when i select only one of the parameters. Can anyone help me with that?
Also i want this report parameter to allow null i.e. if a user does not select anything he should still be able to view the report. In case of regular dropdowns i have added a <Null> value to the existing values and set the default to null. But in case of multi-value, it does not give an option of adding <Null>
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph? Let me know if you need a better explanation. Thanks in advance!
I have a dataadapter on my asp.net page and am having issues passing values to the sql statement. See below:
SELECT tblTemp.SensorID, tblSensor.SensorNum, tblTemp.TempTime, tblTemp.TempVal FROM tblTemp INNER JOIN tblSensor ON tblTemp.SensorID = tblSensor.SensorID WHERE (tblTemp.TempTime BETWEEN @From AND @To) AND (tblTemp.SensorID IN (@Sensor)) ORDER BY tblTemp.TempTime
@Sensor is populated in vb with the selected values from a checkboxlist control. I attempted to test this in the "Data Adapter Preview" window, but continually get an error. Is there a way to pass multiple values to a parameter? Any help would be appreciated, thanks!
I and trying to get some help with Multi-Value parameters in Reporting Services 2005 in VS 2005. I’m new to the product and struggling with the TSQL syntax.
(I have simplified my SQL statement for the purposes of this question) I have a dataset below which has 1 parameter;
="SELECT * " & " FROM dbo.Table" & Iif(Parameters!Sex.Value = "ALL",""," AND dbo.Table.Sex = '" & Parameters!Sex.Value &"'" )
The Sex parameter dataset contains F, M, U and ALL – the above parameter allows me to select on any of the 4 options. I would like to use the multi-value parameter but am struggling to grasp the syntax. Believe me I have tried a few things.
Could someone provide me with an idiots guide on how to make my basic parameter a multi-value parameter?
Greetings - I see a few postings regarding problems regarding multi-value parameters, and see no solution responses.
Am attempting to filter on data using a multiple values drop-down parameter. Dataset has been created and runs correctly. The Multi-value parameter setting has been checked. When report is run and 1 value selected from drop-down, report runs fine. If multiples (or all) are selected, then receive "Query execution failed for dataset "mydataset" incorrect syntax near ','" error.
Can someone please advise a solution to this? Tks & B/R
Hi every body! I have two reports ,in first i have one multi value parameter .in first report i select more than one value ,then in second report i want to have these values becuse i have another multi value parameter ,when i join these reports via navigation in parameters (in first report) i put the values of multi value parameter in fisrt report for value of multi value parameter in second report,but in this case fore example i have it: in first reprt-> navigation->parameters-> ParameterName ParameterVaue EmployeeID =Parameters!EmployeeID.Value(0)
but in second report i need all of my selections in first report not just first value
I know that there have been a number of Posts with reference to Multi-Value Parameters, but none have helped me resolve my issue.
I am relatively new to SSRS, and use it in it's simplist form. i.e. I do not use xml code or SQL Statements, I just use the Menus.
I have created a Multi-Value Parameter (Report > Report Parameters) and have assigned a number of Non Queried Labels and Values. I have also assigned this to the Filter Tab via Table Properties.
When previewing, if I select each parameter individually and view the Report everything is fine. If I select one or more, the report only returns values for the first parameter listed.
Have I missed a step, is there a bug or do I just need to be trainied to use SQL Statements? (Hopefully not the latter!!)
I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.
Code Snippet USE [RC_STAT] GO /****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] (@Region int = Null) AS BEGIN SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type, Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description, ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty, Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget, Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period, Forecast.SalesPerson_Purchaser_Code FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN (SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget ) AS Forecast INNER JOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code WHERE (Region.Region_Key IN( @Region)) AND (Forecast.Budget_Year = 2007)
I need help in creating a custom string for a multi-value parameter going against an Oralce 8 database. I'm trying to build a string that looks like ('1401', 'HACT', '1504') If I do it as an expression in a test report the results are perfect but in production it does not like it.
This is what I have based on some examples I have found but it complains it needs a ) These are the last few lines of the query
and f.shipto = a.shipto and f.salesman in & " ('" & Join(Parameters!Salesrep.Value,"','") & "')" GROUP BY a.company_id, a.whse#, f.salesman, .product_line, d.product_group, e.name ) order by conoxx, whsexx, slsrepxx, sortcode
can anybody tell me if it is possible in RS2005 to create a multi-value parameter in wich it is possibility to drill down? Say i want to use a multi-value parameter to show data for one or more specific years or months. Then the multivalue parameter would contain a list of years, with a + sign next to them, making it possible to drill down to the specific months.
Lets say I have two multi-value parameters on a report. One is required, the other is not. The multi-value parameter that is not required is allowed to be blank. In the WHERE clause of my query I wanted to do this:
Code Snippet AND EXISTS (SELECT * FROM SomeTable AS ST_1 WHERE (MyCode IN (@My_Codes2)) OR @My_Codes2 IS NULL)
Of course I can't do that since multi-value parameters are not allowed to be null. I see that they are allowed to be blank, but how do I check for that? I tried
Code Snippet
OR @My_Codes2=''
That doesn't work. I can set a default value of 'None' and say:
Code Snippet
OR @My_Codes2='None'
Unfortunately this doesn't work. As long as the default value is 'None' it's fine. When I fill in the parameter I get the following error:
An expression of non-boolean type specified in a context where a condition is expected, near ','.
Please note that I am not using a TSQL stored procedure here. It's just a plain old embedded SQL statement.
i have a report that can show upto 8 charts, dependant on the selection(s) made in a multi-valued parameter.
basically, the report gets loads of data back from DataCube.
if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.
but
i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart
so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)
am doing this by trying to get the selected value or "xxxxx" if noting selected
to hide the second chart when only one category has been selected, tried
But it works fine when there are > 2 categories selected
also
this works fine when there is 2 or >2 category selected
=iif(
Parameters!LOOKUPITEMCategoryDescription.count>2,
"more than 2",
"not more than 2")
it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!
I have a report that needs to use two multi-value parameters that are linked.
The report shows stock availability for the production of trial kits and the requirement is to enter multiple quantities of multiple kits as shown below:
Parameter 1 Parameter 2 Kit A 10 Kit B 15 Kit C 25
Is there a way I can select multiple kits and then enter a specific quantity for each selection?
How do you configure a multi-value parameter so it will allow the user to not enter the parameter?
I'm using integer multi-value parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".
How do you specify a multi-value parameter to allow "empty" or "null"?
I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.
Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.
For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.
SELECT distinct Ac.activityCode, Ac.ActivityName + isnull(Ac.description,'') AS ActivityName, Ac.activityStartDate, Ac.activityEndDate, LU.auditCode, LU.AuditName, St.studyCode AS StudyCode, St.StudyName AS StudyName
I am using SP as my data set to generate report and there are some multi-value parameters which past as an array back to sql server. Since sql server can not handle the array pass in , how to handle it?
