Where Clause Problem When Passing In Data Dynamicly - HELP!
Apr 9, 2008
I have a stored proc that I am trying to use for sorting and paging below is a portion of it. When I hard code "Stan" into the Where clause I get all the records back that where posted by "Stan" but when I try to pass in "Stan" as a variable it tells me something about an invalid column name.
Here is the working version. When I hard code "Stan" into the Where Clause it works. I am using 2 single quotes on each side. See Where clause.
SET @sql ='SELECT [ThreadName],[PostID],[PostTypeID],[LanguageID],[PostAccessID],[UserID],[ThreadID],[PostParentID],[VoteSummaryID],
Hi All, Would somebody be able to help me from pulling my hair out!??I have a form with a radiobuttonlist. I would like to change my select statement depending on what radiobutton value is selected.E.g.SELECT * FROM table WHERE <<variable from radiobuttonlist>> LIKE 'Y'So,if radiobutton value selected = 1, it will select * from column A in the dbif radiobutton value select = 2, it will select from column B in the dband so on... Am i attempting to do the impossible?Thanks All,
Hi everyone I am new to this site I have a major issue I cant figure out seeing how im fairly new to asp, and vb, but i have 5 years php mysql experience. Im pulling the correct data into a grid. Then i need to make a button or some sort of link that will take the value of one field in the record set and replace it with @transid in the where statement I can enter in the value of transid into form field with that name and it will run the rest of the script correctly, I just cant get past this hurdle. If anyone can help that would be great. I tried to get this to work with java script but then realized thats not possible to transfer varaibles to asp from it. ///javascript function DisplayReciept(transactionnum) { recieptdis = transactionnum;
I have a stored procedure which, initially, I had passed a single parameter into a WHERE clause (e.g ...WHERE CustomerCode = @CustCode). The parameter is passed using a DECommand object in VB6.
I now require the sp to return values for more than one customer and would like to use an IN clause (e.g ...WHERE CustomerCode IN(@CustCode). I know I could create multiple parameters (e.g. ...WHERE CustomerCode in (@CustCode1, @CustCode2,...etc), but do not want to limit the number of customers.
If I set CustCode to be KA1001, everything works fine. If I set CustCode to be KA1001, KA1002 it does not return any records.
I think the problem is in the way SQL Server concatenates the stored procedure before execution. Is what I am attempting to do possible? Is there any particular format I need to set the string parameter to? I've tried:
KA1001', 'KA1002 (in the hope SQL Server just puts single quotes either side of the string)
111 212 313 421 522 623 725 817 932 1044 I need to run a query where I pass in an unknown number of KeywordIds that returns the PictureId. The 'IN' clause will not work because if a KeyWordId gets passed into the Stored Procudure the PictureId must have a record with each KeyWordId being passed in. For example, lets say you need to see the result of all PictureIds that have both 1 and 2, the correct result set should only be PictureId 1 and PictureId 2.
Im going crazy trying to find a simple solution for this. Please advise.
Is it possible to pass a report parameter that is defined as a string to the following SQL statement that is using an "IN" clause ?
WHERE (ANALYST.User_Bemsid IN (@Report_Parameter_Bemsid))
If I pass a single value (I.E. A) it works okay, but once I try to pass multiple values (I.E. A,B or 'A','B') it returns no data.
Using Crystal reports I can pass multiple values via a report prompt into the SQL "IN" clause and seems that SQL Reporting Services should also have this feature. What do I need to do to get it working ?
I have the following code and I'm trying to set an event dynamicly in the foreach statement... SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["BvtQueueConnectString2"]); SqlCommand select = new SqlCommand("select top 100 b.jobid as [Job ID], bq.timesubmitted as [Time Submitted], b.timereceived as [Time Received], bq.requestid as [Request ID], bq.timecompleted as [Time Completed], bq.buginfoid as [Bug Info ID], e.eventid as [Event ID], bq.closingeventid as [Closing Event ID], et.eventtype as [Event Type], e.eventtime as [State Start Time], l.twolettercode as [Language], p.projectname as [Project Name], p.packagedesignation as [Package Designation] from bvtjobs b inner join bvtrequestsnew bq on b.jobid = bq.jobid inner join eventlog e on bq.requestid = e.bvtrequestid inner join eventtypes et on e.eventtype = et.eventtypeid inner join languages l on bq.targetlanguage = l.langid inner join projects p on bq.targetplatform = p.projectid order by b.jobid asc", conn); SqlDataAdapter da = new SqlDataAdapter(select); DataSet ds = new DataSet(); conn.Open(); da.Fill(ds); conn.Close(); Table table = new Table();
foreach (DataRow dr in ds.Tables[0].Rows) { TableRow tr = new TableRow(); for (int i = 0; i < dr.ItemArray.Length; i++) { TableCell tc = new TableCell(); tc.Text = dr.ItemArray.GetValue(i).ToString(); tc.BackColor = Color.White; if (dr.ItemArray.GetValue(8).ToString() == "COMPLETE") { tc.BackColor = Color.BlueViolet; } tr.Cells.Add(tc); } table.Rows.Add(tr); } foreach (TableRow tablerow in table.Rows) { TableCell newtc = new TableCell(); Button mybutton = new Button(); newtc.Controls.Add(mybutton); mybutton.Text = "details"; //set the event here tablerow.Cells.Add(newtc); } Panel1.Controls.Add(table); DataBind();
I want to get which column that are sorted dynamicly when the report is shown. I want to use this informastion in some formulas (Like for example: Iif(<sortcolumn>(3);True;False)).
I dont't want to use parameters indicate which column to sort.
Result (While I am selecting 'Yes' in dropdown) ---------------------------------------------- incident_id usr_id item_id 10059926 191 61006 10054444 222 3232
SELECT incident.incident_id,incident.usr_id,incident.item_id FROM incident where exists (How i can write query here to check the act_type_sc=ADD_ATTCHMNTS is exists)
I have the follow, i get the right amout of checkboxes but they all have the same value(System.Data.Common.DbDataRecord) Dim objconn As New SqlConnection(connstring_MPR)Dim objcmd As SqlCommand = New SqlCommand("SELECT [Parts Master Table].COMMD_CODE as comcode FROM [Parts Master Table] INNER JOIN [Warehouse balance table] ON [Parts Master Table].PART_NUMBER = [Warehouse balance table].PART INNER JOIN POREPORT ON [Parts Master Table].PART_NUMBER = POREPORT.[Part Number] INNER JOIN [DEMAND TABLE] ON [Parts Master Table].PART_NUMBER = [DEMAND TABLE].PART WHERE POREPORT.[PO Bal] > 0 OR [DEMAND TABLE].QTY > 0 or [Warehouse balance table].ONHAND > 0 and [Parts Master Table].M_B = 1 AND [Warehouse balance table].WHSE = 'sgr' AND ([Parts Master Table].FAMILY NOT LIKE 'lam%' or [Parts Master Table].FAMILY NOT IN ('ULTCH', 'REMOT', 'MKSIN')) GROUP BY [Parts Master Table].COMMD_CODE ORDER BY [Parts Master Table].COMMD_CODE", objconn) objconn.Open() chkComCode.DataSource = objcmd.ExecuteReader(CommandBehavior.CloseConnection) chkComCode.DataBind() objconn.Close()
The names of the tables that sould be transfered from the production system to the DWH are stored in a table in the production system. Yet I haven't found a proper way to dynamicly define these tables as DataSources and DataDestinations within an Integration Services project.
I have a slight problem. I would like to pass some form of out of band data to every SP when called using ADO.NET.
A little more info is needed I think. I need to pass a unique identifier to ever SP my application calls and have that identifier available to use by the SP.
As my application has many many SP's in use I am trying to avoid adding an extra parameter to each and every one. I was really hoping for the ADO command object to have a property I could use to pass 'user defined' data to the database engine.
For example:
My SP's write to an audit trail table. The audit trail needs to record the user that executed the SP, this user might not be the same user that logged on to the database (depends on the connection string really). So, instead of adding an extra param to each SP, I want to access some data from the ADO command that was used to call the SP.
Can anyone help, or have I just confused everyone?
hello everybody, i hope u can help me with this... i want to pass the data entered in a web page to a stored procedure so as to store the data in a database... am using three layered architecture... how can i do this.... thanks in advance.............
Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?
I've read the various posts and articles regarding this matter, but I seem to have problems getting to work:
In my control flow, I start by declaring a variable named "LastJobLedgerEntryID", to identify the records I need to add to the stage. From there I would like to use this variable in the source component in my dataflow, i.e.:
"SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > " + @[User::LastJobLedgerEntryID]
But this fails? I should note that the variable LastJobLedgerEntryID is stored as a int32, and with the default value of 0
I need to convert a Crystal report that contains three subreports. Each of these subreports uses a different stored procedure to obtain data. The main report uses data from these subreports to calculate averages in the footer etc.
How do i get the data contained in these subreports so i can use it in my main report?? Thanks in advance
I am running a sql task which will pass table as object variable to the result set
I have a for each loop container which is used to loop for all the servers. I use two of the parameters to establish connection string in the for each loop task from the reasult set variables.
Now my next step is a data flow task (Data Reader Source) where i have to run a query but the table name and column names are dynamic and i dont see an option to call variables.
can someone tell me if this is possible (var1,var2,... are variables in the package scope)
Hi, I pass a paramter of text data type in sql server (which crosspnds Memo data type n Access) to a stored procedure but the problem is that I do not know the crossponding DataTypeEnum to Text data type in SQL Server.
The exact error message that occurs is:
ADODB.Parameters (0x800A0E7C) Parameter object is improperly defined. Inconsistent or incomplete information was provided.
The error occurs in the following code line: .parameters.Append cmd.CreateParameter ("@EMedical", advarwchar, adParamInput)
I need to know what to write instead of advarwchar? Thanks in advance
I've been asked to set up a data driven subscription for a number of reports which use multi value parameters. For example, show me all transactions against the following departments: IT, Building Services, Accounts.
As an interactive report it's simple, the user just selects the relevant departments, but as a data driven subscription I can't seem to find the correct format to pass the selections through.
I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.
When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.
I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.
Has anyone else experienced this issue and do you have a resolution?
Looking to coalesce a WHERE clause with XML data. I cannot supply test data.
If 472 is null, check the NEXT XML field at grab the 150, if both 472 AND 150 do NOT exist let it be NULL.
set @Date = (select top 1 XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)') from #src where coalesce(XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472', '(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150')
How can I pass a parameter used for the DataSet query? I'm using this DataSet file as data source of my .rdlc report for Windows Forms.
I've already done a .rdlc report to Web Forms where I passed the query parameter by ObjectDataSource.SelectParameters, but in Windows Forms this object was not created. I just hava the following created objects:
I am using this custom data extension from BI Studio.
Que1: I want to create report parameters from BI studio and capture them in my Data Extension.
Que2: Every report parameter has so many properties, like hidden, internal, default value etc, how do capture that information in my data extension, the parameters only give me name and value.
Que3: I know I can capture command Text. So My command can be Select * From tablename where fieldname= @myFieldname. Once I get this commandText in my data extension, How do extract parameters of the command?
Que4: If I have any custom attributes in my RDL file, Can I capture them in my Data Extension ?
SELECT * FROM bookkeep RIGHT OUTER JOIN acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
I have this simplified query below to illustrate what I'm looking for. I would like to get the data for the time between the two dates in the where clause ('01-05-2015' and '10-03-2015'). In SQL it would be a BETWEEN. I'm looking for the MDX equivalent.
I have these dates hard coded but what I eventually will be doing is an @BeginDate / @EndDate parameter in an SSRS report.
SELECT [Measures].[Ship Resale S&D Run Rate] ON COLUMNS, [Branches].[Region].[Region] ON ROWS FROM [Sales] WHERE [Time].[Date].&[01/05/2015]:[Time].[Date].&[10/30/2015] *WHERE @BeginDate : @EndDate
DECLARE @ServerCIName varchar(5000) SELECT * FROM dbo.INC WHERE Status in ('Assigned','In Progress','Pending') and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)) In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.
But @ServerName contains multiple values and it is dynamic (not constant).
