I 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?
Greetings, all!
I had a question about how SRS passes multiple values to a query.
For example:
Say I had a "select box" (Sorry, can't think of the proper term right now) with the following values:
F1
F2
F3
F4
The user then selects F1 and F4.
When SRS passes the selected values to the query or procedure, how are they passed? As a delimited value?
Examples of what I mean:
@Parameter = 'F1', 'F4'
@Parameter = F1 [Some unknown delimiting symbol] F4
@Parameter = 'F1,F4'
Does anyone know how this is done? Or is it done in some other odd way?
Thanks!
I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.
I have attached the look up table data for reference.
I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color.
If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.
If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.
The exact colorid of the combination must be returned on passing the values to p1 and p2.
To elaborate; I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like
colorid p1p2p3p4p5p6p7p8
1 redred
10 redredred
20 redredred
30 redredredred
40 redredredredred
50 redredredredredred
60 redredredredredredred
70 redredredredredredredred
So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.
I need a function or procedure that will accept the arguments and provide me the result based on the values.
[URL]
Since SSIS is developed in Visual Studio is there a way to view the variable values as they are passed into SSIS? In Visual Studio I know if you hover over the variable a popup appears with the value in the variable.
I am having an issue with a variable value that is supplied through an "Execute SQL Task" it is a Directory path that is entered in through the application and used to determine where to pick up files to transfer. I have entered in the path using Fully Qualified(D:ImportExportCentralPrinting) and UNC (\SQLDEVELOPImportExportCentralPrinting) through both methods SSIS is telling me no files are available in the directory path. When I know for fact they do exists.
I would really love to see the variable values that are being used can anyone out there help?
Thank you all,
Mike
Michael Alawneh, DBA
I have a scenario where I need to compare a single DateTime field in a stored procedure against multiple values passed into the proc.So I was thinking I could pass in the DateTime values into the stored procedure with a User Defined Table type ... and then in the stored procedure I would need to run through that table of values and compare against the CreatedWhenUTC value.I could have the following queries for example:
WHERE CreatedWhenUTC <= dateValue1 OR CreatedWhenUTC <= dateValue2 OR CreatedWhenUTC <= dateValue 3
The <= is determined by another operator param passed in. So the query could also be:
WHERE CreatedWhenUTC > dateValue1 OR CreatedWhenUTC > dateValue2 OR CreateWhenUTC > dateValue3 OR CreateWhenUTC > dateValue4
I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.
Currently negative numbers are being returned because DateTime1 < DateTime2 or DateTime1 > DateTime2 .
The DateTime1 and DateTime2 values are dynamic and either of them can be bigger than the other.
Any query solution so that always positive value is returned.
I have values in my database that I'd like to display as $xxx,xxx.xx. Some of these values use the money datatype and others use float.
I'm building a string in a stored procedure that is eventually passed to Visual Basic. I want to format these values in the above format in the string that is passed. I've searched all through MSN and I couldn't find anything related to VB's FORMAT function in SQL Server.
Does Transact-SQL have any functions that could format these values for me?
Does anyone know if .NET 2.0, 3.0, or 3.5 has a method for converting quoted strings used in INSERT INTO MyTable (Field1, Field2) VALUES(1, 'SomeTestWithApostrophes')
I can write my own routine but I was hoping .NET framework would have something I could use, in the System.Data.SqlClient perhaps??
Ultimate would be a "SQLSafe" method that will convert strings into SQL safe strings.
Thanks, Rob.
Hello, I have an issue where I need to format the way my times are stored in my table to the way I can compare those times with the current time given by:
select right(convert(varchar,getdate(),100),7)
this provides a result like: 2:10PM
But my times are stored like: 2:10 P.M.
so, Im thinking i need to format the saved time in order to compare it to the current time so I can do the following select:
select * where startTime > (
select right(convert(varchar,getdate(),100),7)
)
Thanks a lot for your help!
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 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.
I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects.
ALTER PROCEDURE [USP_SelectProject]
-- Add the parameters for the stored procedure here
@ProjectNumber as int
AS
BEGIN
[code]....
Hi all - this one has me stumped... PLEASE HELP!!!
I have a back/forward navigation link that passes a URL.startrow number (lets call it n) - based on n - I only want to select the record that is the n'th record based on a sort order (gall_order) - (SQL SERVER).
<cfquery name="gallHomePic1st" datasource="id" maxrows="1">
SELECT id
FROM gall_home
ORDER BY gall_order asc
</cfquery>
For e.g. - I want the 7th (14th - 21st etc) record based on gall_order asc.
Thanks guys - this has me stumped!
I am new to sql and very familiar with access. I am using a verylarge database(130M records) in ms sql2000 and think I need to frontend it with access for reports and forms, etc..I have some questions:1) Is there a way in SQL to prompt a user for input at the running ofa query like the [what is your name] construct in an access query?2) I can't seem to create calculated fields in a view in sql. Inaccess I put "total:=hours*rate" and a new column would be createdwhich would contain the total.3) I normally would create a form in access from which the accessqueries would pull their variables and then insert those variables inthe queries or reports, etc. How would this be accomplished usingsql.4) Lastly and probably most basic. How does a "real" sql developercreate reports and forms to interact with the enduser? I am usingaccess as a front end, but am open to suggestions.Thanks,Brad
View 1 Replies View RelatedI have an sql query that has specific criteria (like state='PA' orstate = 'NJ'...) and would like to be able to have the user specifythe criteria dynamically either through the web or from MSAccess oranother tool.The query also does a GROUP BY the state and other variables that arepart of the criteria.I know how to get MSAccess and asp pages to do the sorting andselecting against an SQL tbl or view, but when access queries the sameinfo as the original sql view, the process takes much longer than whenthe sql view does all of the sorting, selecting and grouping..The table we are currently using is 5 million records and will begrowing to 250 million records shortly, so speed is of the essence.The sql views and MSAccess are both running from the same server sothere is no issue at this point of a network impacting the MSAccessquery.Any suggestions...
View 6 Replies View RelatedI have the follwoing stored procedure:ALTER procedure [dbo].[up_GetExecutionContext](@ExecutionGUID int = null) asbeginset nocount ondeclare@s varchar(500)declare @i intset @s = ''select @s = @s + EventType + ','-- Dynamically build the list ofeventsfrom(select distinct top 100 percent [event] as EventTypefrom dbo.PackageStepwhere (@ExecutionGUID is null or PackageStep.packagerunid =@ExecutionGUID)order by 1) as xset @i = len(@s)select case @iwhen 500 then left(@s, @i - 3) + '...'-- If string is too long thenterminate with '...'else left(@s, @i - 1) -- else just remove the final commaend as 'Context'set nocount offend --procedureGOWhen I run this and pass in a value of NULL, things work fine. When Ipass in an actual value (i.e. 15198), I get the following message:Invalid length parameter passed to the SUBSTRING function.There is no SUBSTRING being used anywhere in the query and thedatatypes look okay to me.Any suggestions would be greatly appreciated.Thanks!!
View 2 Replies View RelatedHi, 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?
Hello,
I need to check if the IDs in table A on the server X exist in table B (if not check table C, if not in C, check table D ) on the server Y. The problem is that the table name changes every year and I need to use the variable for the reference table name in LookUp component in the Dataflow Task.
Can anyone help me?