I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?
Do I need just a select statement to do this? What is the difference between the output and select statements?
Hi, I am trying to build search engin with 11 parameters in 4 different tables in the database. For example: In search.aspx I have 11 textboxes namely nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc. And in the result.aspx page I have gridview which post data from the database if the search match. I wrote this stored procedure. P.S please ignore the syntax. @name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote 1 @name var(30), 2 3 @nationality (30), 4 5 @phone int, 6 7 etc 8 9 as 10 11 12 13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c 14 15 WHERE 16 17 name LIKE '%' ISNULL(@name, '') '%' 18 19 OR nationality LIKE '%' ISNULL(@nationality,'') '%' 20 21 OR phone LIKE '%' ISNULL(@phone,'') '%' 22 23 etc 24 25
Also the error still exist. What is the best way to search for multiple parameters in multiple tables ?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
I have a problem I was hoping someone has been able to solve, since I haven't been able to. We have an application that uses a local instance of a database that we would like to update incrementally so we are switching to SQL CE from SQLExpress to take advantage of the sync functions. I've been able to recreate the database from a SQL Server 2000 .mdf file to a SQL Server CE 3.5 .sdf file and now I am trying to connect to it from my VS2008 .NET application. I have all the absolute latest patches, downloads, frameworks, etc. The problem comes when I try to open the recordset. I decided to keep the old code instead of going through this very large application and change over to a dataset or the other CE data handlers. I've read, and it makes sense to me that since it is still an SQL Server database, it should support ADO still.
Anyway, here is a snapshot of the code that tries to connect but fails:
(I use a module to abstract my calls to the database so that I can re-use the process in other places in the code. It used to work fine until I tried to switch to SQL CE)
Public Const SQLConnString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5; Data Source=C:DATATOOLS.sdf; Max Database Size=2047" 'used to be "SERVER=.SQLEXPRESS; DRIVER={SQL Server}; DATABASE=TOOLS;"
Public stSQL As String Private _Conn As ADODB.Connection Public myRS As ADODB.Recordset Const adCmdText = &H1 Const adOpenForwardOnly = 0 Const adLockReadOnly = 1 Const adStateOpen = 1
Public Sub GetSQLData(ByVal stSQL As String) Try _Conn = New ADODB.Connection myRS = New ADODB.Recordset _Conn.ConnectionString = SQLConnString _Conn.Open() '***blows up here myRS.Open(stSQL, _Conn, adOpenForwardOnly, adLockReadOnly, adCmdText) Catch ex As Exception CloseConn() End Try
End Sub
'****************************************** error message is as follows: System.Runtime.InteropServices.COMException was caught ErrorCode=-2147217887 Message="Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." Source="Microsoft OLE DB Service Components" StackTrace: at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options) at CAD.Module1.GetSQLData(String stSQL) in C:ProjectsCADCADModule1.vb:line 83 InnerException:
I also had a weird exception earlier that said my data source location string was too long - very strange - fixed it though by moving the DB to a folder closer to C.
Anyway, I hope some can decipher the "multiple OLE" errors - not sure what it is looking for. Thanks, Brian
I've created multiple select statement to pull in varies fields from a table and one of those fields is Fiscal_Year, however, after the select statement I'm adding a Where clause:
In almost all scenarios, where there is an error, it also raises 3-4 other errors like these ones below.
I'm 100% sure, the 1st one is the actual error resulting in package failure and the errors 2-5 is the result of error #1. So what ever code I have in the error handler section of the package gets executed 5 times.
How do I handle this? Can do I hard coding for the error numbers?
1. An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01858: a non-numeric character was found where a numeric was expected ".
2. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
3. Thread "SourceThread0" has exited with error code 0xC0047038.
4. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
5. Thread "WorkThread0" has exited with error code 0xC0047039.
Hi! We have installed MPS for Service Provisioning on domain controller. SQL 200 is also installed on this server. EventView logs per 15 second following error:
Source: Provisioning and Audit Recovery Service Category: None Event ID: 5896
"Error occurred while moving records to the audit log database. SQL server reported errors: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp."
I did not find the problem source and the solution. Any idea?
I have two linked server... (ServerB and ServerC) which reside on ServerA. I am able to connect to the remote database using "Select" statements without any issues.
When I run this query, It is successful:
delete [SERVERB].MyDatabase.dbo.TableName from [SERVERB].MyDatabase.dbo.TableName t1 Left join MyDatabase.dbo.TableName t2 on ( t1.ID = t2.ID and t1.EmployeeNumber = t2.EmployeeNumber and t1.AccountNumber = t2.AccountNumber) where t2.ID is null;
However, when I change [SERVERB] to [SERVERC], I receive two errors:
"Could not find server 'ELEARN-FRM-BETA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."
And
OLE DB provider "SQLNCLI" for linked server "ELEARN-FRM-BETA" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
When I run profiler on ServerC, I see traffic... mainly a whole bunch of exec "sp_cursorfetch" operations, so I know the connection is valid.
I am trying to use sqlexpress (release version) with vb6. The mdf file is located in a subdirectory of the app.path. When I run the program I get the following message:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
What am I doing wrong? Here's the code:
Dim DbPath As String, strDbConn As String DbPath = App.Path & "dbs" & DbName strDbConn = "Data Source=.SQLEXPRESS;AttachDbFilename= " _ & DbPath & ";Integrated Security=True;User Instance=True"
Set DbConn = New Connection DbConn.connectionString = strDbConn DbConn.Open
I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.
Additional Information: Visual Studio - local machine SQL Server/Analysis Services - Machine A Reporting Services - Machine B
I am not a DBA but have responsibility for a particular MSSQL 2008 R2 file server running a particular application.how to solve a database consistency check problem.The database fails dbcc checkdb with multiple 8903 errors. Unfortunately this was not discovered until well after any good backups were deleted. The good news is that the DB otherwise seems fine. We have experienced zero problems with the DB or the applications. Running the checkdb with the "repair_allow_data_loss" option does not fix the problem.
However, I would still like to fix the problem. Using a popular SQL recovery product I am able to recover the database.The original, vendor designed and supplied DB, has 2 file groups, and three files (MDF, NDF, LDF). The output of the recovery process produces 1 file group and 2 files (MDF and LDF). Vendor says they cannot support me since the recovered DB is 'non-standard' according to their design.
I am able to set up a new, blank version of the vendors database on another dev system with the proper file and filegroup structure. How can I get the data moved/copied from the recovered (MDF/LDF) database into the dev database (MDF, NDF, LDF). I've tried the import/export function but it fails (I can rerun and give details if necessary).
I have a sql that I want to execute with LIKE and parameters:I tried several options outlined that I found at http://aspnet101.com/aspnet101/tutorials.aspx?id=10%20but they all seem to return 0 records. When I try and execute my statement in Enterprise manager, it works fine.Code Snippet:...Dim sql as stringsql = "SELECT * FROM tblName WHERE First LIKE '%' + @fname + '%' AND Last LIKE '%' + @lname + '%'"Dim param(1) as sqlParametersqlParams(0) = New SqlParameter("@fname", SqlDbType.VarChar, 50)sqlParams(0).Value = Trim(fname.text)sqlParams(1) = New SqlParameter("@lname", SqlDbType.VarChar, 50)sqlParams(1).Value = Trim(lname.text)...Can anyone tell me if there is anything wrong with my code above?
I have an issue with an OLE DB command and parameters. The statement I want to run starts with a WITH statement - when I take out the parameter it runs fine. When I take out the WITH statement and leave the parameter in it runs fine. When I run the statement in SSMS it runs fine.
With both the WITH statement and the parameter in it gives the error 'Syntax error, permission violation, or other nonspecific error'.
The SQL for a dataset in ASP.NET 2.0 is as follows.... SELECT DISTINCT StudentData.StudentDataKeyFROM Student2Roster INNER JOIN StudentData ON Student2Roster.StudentDataRecID = StudentData.StudentDataRecIDWHERE (Student2Roster.ClassRosterRecID IN (@ClassRosterRecIDs) ClassRosterRecIDs are giuds At design time i use 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9' as the parameter and it works in the designer but not at runtime. At runtime I get the error Conversion failed when converting from a character string to uniqueidentifier. If I leave the quotes off and only use 1 value like b2cf594d-908b-4c0c-a67f-6364899a4d42 it works.How can I use multiple guids as an IN parameter like 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9'? Thanks
Hei,I'm currently trying to write a program in C# that will allow users toparametrize their queries.For instance, I have a query like this:SELECT * FROM Customers Where Region = @Region AND Gender > @GenderHow can I extract the Parameters names without using Stringmanipulation (which is not perfect since sql statements can alsocontain '@' in LIKE clauses for example)I tried this wayDim comm As New OleDbCommand(SqlStatement, myCon)comm.Prepare()MsgBox(comm.Parameters.Count.ToString())comm.Dispose()myCon.Close()but this always return me 0 (zero)Do you have any idea on how to solve this generic problem?RegarsPhilippe Graca
I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.
What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.
But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"
Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?
The first line of code works fine but when I try to set the value of the property I get the following excpetion:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
hello 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."
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)"; Response.Write(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
Hi there, I am trying to use the ADO technology within MS Access 2000. Basically I'd like to use parameters in a command object to insert a new record and get its newly inserted ID. But instead of it it returns error:
Run-time error '-2147217900 (80040e14) Must declare the scalar variable "@ii_file"
Isn't this varaible (and all the rest of variables) declared by setting a parameter ".Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii) "? I'd like to avoid using stored procedures in order to create the whole SQL statement from the client side. Thanks!
Darek
Public Sub save_import() Dim rs As ADODB.Recordset, cmd As ADODB.Command, rec_affected As Long
Hi all, We have Windows 2003 64 sp2 Xeon, 2005 EE SP2 64 bit... Trying to do conversion from DTS sql 2000..One package use load from excel to sql..So I tried to create same thing by myself.. Hell, so many issues.. So I used wizard, package created, I changed Run64bit to False, tried to run package, once - completed in debug mode.. Now it's time to create deployment utility and deploy package..During execution of manifest file got error:
Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
In BIDS, open up solution and tried to rerun package again - no way,".. cannot acquire connection from connection manager blah blah blah.." Even tried to fire package without debugging, it fires 32 bid execution utility, so no question about 64 bit mode.. package failed.. Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785} Message: ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The external request has completed. Start Time: 2007-11-09 09:41:25 End Time: 2007-11-09 09:41:25 End Log Error: 2007-11-09 09:41:25.95 Code: 0xC0202009 Source: Package_name loader Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H result: 0x80040E21 Description: "Multiple-step OLE DB operation generated error s. Check each OLE DB status value, if available. No work was done.". End Error Log: Name: OnError
Source Name: Data Flow Task Source GUID: {2A373E56-8AAF-40E9-B9EF-4B2BB40175F0} Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785} Message: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER . The AcquireConnection method call to the connection manager "SourceConnection Excel" failed with error code 0xC0202009. There may be error messages posted be fore this with more information on why the AcquireConnection method call failed.
hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!
Hi all. I have the problem on query. This is my query.
select D.fullname, P.religion, E.empno from pspersonaldata as P inner join hremployees as E on P.dcno = E.empdcno inner join psdatacenter as D on D.dcno = E.empdcno where P.religion in ('Born Again','Baptist', 'Catholic')
How could I make a query that returns a result for either "Born Again, Baptist, or Catholic". The parameter would depend on the input of the user depending on how many religion the user inputed. If the user inputed Born Again and Baptist, the result is the employees that have a religion of Born Again and Baptist. Thanks -Ron-
I need to pass a parameter to a stored procedure that allows the user to select all store numbers or a combination thereof. The portion that selects all works fine. I am have trouble with the later. My code looks like this:
IF @StartDate IS NULL BEGIN Set @StartDate = GetDate() Set @EndDate = (GetDate ()+90) END Declare @StoreList TABLE ( CenterID int )
-- Get our Centers IF ( @Center = 'ALL' ) or ( @Center IS NULL ) BEGIN INSERT @StoreList SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock) END ELSE BEGIN
INSERT @StoreList SELECT DISTINCT POS_Site_ID FROM LVIGP.dbo.POS40108 (nolock) WHERE POS_Site_ID = @Center END
the first if statement returns all of the store number like this CenterID 001 002 010 024 057
the second if returns nothing I tried using coalesce but it returns the store number like this: CenterID 001, 002, 010, 024, 057 This does not work with the rest of my program.
Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.
