|
Either use sp_helpdb pprocedure or query master..sysdatabases.
The need to optimize rises from a bad design
|
|
|
|
|
i have a table of a library which contain several fields like author, publisher, isbn number etc etc. I want to write a search query in different combination if user select only author then its return result according to author, if user select multiple fields in any combination then the query must return result according to that. Can any1 help me?
Thanks in advance.
|
|
|
|
|
dream_liner_7e7 wrote: i have a table of a library which contain several fields like author, publisher, isbn number etc etc. I want to write a search query in different combination if user select only author then its return result according to author, if user select multiple fields in any combination then the query must return result according to that. Can any1 help me?
What have you tried so far? If you can post the query that you now have, and also tell us where you are stuck, people here will be more than willing to try and help you.
|
|
|
|
|
SELECT
[AIMS_Library.Reck].Reck_Code + ' ' + [AIMS_Library.Reck].Shelf_Code AS [Reck & Shelf],
[AIMS_Library.Category].Category_Name,
[AIMS_Library.BookDetails].Qty,
[AIMS_Library.BookDetails].ISBN,
[AIMS_Library.BookDetails].BookTitle,
[AIMS_Library.BookDetails].Author,
[AIMS_Library.BookDetails].Publisher,
[AIMS_Library.BookDetails].Edition,
[AIMS_Library.BookDetails].YearPublished
FROM
[AIMS_Library.BookDetails], [AIMS_Library.Category], [AIMS_Library.Reck]
where
[AIMS_Library.Reck].reck_id = [AIMS_Library.BookDetails].reck_id
and
[AIMS_Library.Category].category_id = [AIMS_Library.BookDetails].category_id
and
[AIMS_Library.BookDetails].author = 'abc' or [AIMS_Library.BookDetails].publisher = 'xyz'
the last line thats where i stuck, when from my c# form users will try to search books. Is there any 1 query solution or i have to write multiple queries?
Thanks in advance.
|
|
|
|
|
One way to solve this would be to dynamicallyl create the query based on user selection... keep the basic query (including fields needed and joins) a constant, and add to the query string for each selection made.
So, if user has selected author 'xyz' and publisher 'abc'
qry = [basic query here, including the joins]
if [author selected] then qry = constant + " and author=" + selected author
if [publisher selected] then qry = constant + " and publisher=" + selected publisher
... and so on
You might want to look into creating parameters for your SQL query, to prevent injection attacks.
HTH
|
|
|
|
|
thank you every1 thats a valuable solution
|
|
|
|
|
What ChandraRam suggested is a good way to go.
However, also notice that you may have a potential problem in your query. Since you have an OR in the middle of the conditions, but no parenthesis used the result may be unexpected and in your example lead to cartesian product.
The need to optimize rises from a bad design
|
|
|
|
|
|
I believe that sp_filestream_configure is not correct. Instead you should use sp_configure and option filestream_access_level (value 0-2).
This enables filestream accessing for the instance.
The need to optimize rises from a bad design
|
|
|
|
|
Hi Guys, I'm totally new to Crystal reports and this problem has me stumped.
Here it is, lets say i have a table of employees this table has the departments they work in too and i want to display the number of employees in each department at the end of the report(in the page footer)
Here is what i did so far
Added a new group and grouped the records according to the Department name
This gives me the total of the employees in that department but it appears within the table i created
I want these to move in to the page footer
If i do move it to the page footer i get the total of only one group
Been banging my head against this for the past couple of days now. I'm sure i'm missing something obvious. Do i need a formula Field???
Also if i still want the group totals but i need the records sorted on another field is that possible?
|
|
|
|
|
Colwin wrote: Hi Guys, I'm totally new to Crystal reports and this problem has me stumped.
Here it is, lets say i have a table of employees this table has the departments they work in too and i want to display the number of employees in each department at the end of the report(in the page footer)
Here is what i did so far
Added a new group and grouped the records according to the Department name
This gives me the total of the employees in that department but it appears within the table i created
I want these to move in to the page footer
If i do move it to the page footer i get the total of only one group
Been banging my head against this for the past couple of days now. I'm sure i'm missing something obvious. Do i need a formula Field???
Also if i still want the group totals but i need the records sorted on another field is that possible?
Quote Selected Text
A couple of things...
0) End of report != Page footer
1) Are you saying you want the information to appear only at the end of the report, or should it appear at the end of every page?
2) If at the end of every page, what if the detail section for a department requires more than one page? What should be printed at the end of the first page then?
3) If at the end of the report (Report footer), do you want just a list of the data grouped by departments? If yes, why don't you create a sub-report containing just the grouped information and stick it in the Report footer? This way, you can have your main report ordered any other way you need too...
HTH
|
|
|
|
|
Thank you for your response.
i need the information at the end of the report. The end of the report need the count of all the employees in each department
like this
Dept1 10
Dept2 14
.
.
.
.
like that. I don't know at the start how many departments are there
i will look at embedding a sub report at the end of the report
Thanks again
Colwin
|
|
|
|
|
The sub report works like a charm
Thanks
Colwin
|
|
|
|
|
You are welcome 
|
|
|
|
|
Some time back I had a requirement of using connecting string passed as command line argument to an application.
The problem was that the application was using Enterprise Library 1.1 which uses dataconfig.config file for database connection information.
After some research i was able to find a way to create Enterprise Library Database object from Connection String. I thought that this would also be useful to other developers.
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
private static Database CreateDatabaseFromConnectionString(string connString)
{
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connString);
DatabaseSettings settings = new DatabaseSettings();
DatabaseTypeData type = new DatabaseTypeData("Sql Server", "Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null");
settings.DatabaseTypes.Add(type);
ConnectionStringData connectionString = new ConnectionStringData("DBConnection");
connectionString.Parameters.Add(new ParameterData("server", builder.DataSource));
connectionString.Parameters.Add(new ParameterData("database", builder.InitialCatalog));
connectionString.Parameters.Add(new ParameterData("integrated security", "false"));
connectionString.Parameters.Add(new ParameterData("user id", builder.UserID));
connectionString.Parameters.Add(new ParameterData("password", builder.Password));
settings.ConnectionStrings.Add(connectionString);
Microsoft.Practices.EnterpriseLibrary.Data.Configuration.InstanceData instance = new Microsoft.Practices.EnterpriseLibrary.Data.Configuration.InstanceData("DBConnection", "Sql Server", "DBConnection");
settings.Instances.Add(instance);
Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationDictionary configurations = new Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationDictionary();
configurations.Add("dataConfiguration", settings);
Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationContext context = Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationManager.CreateContext(configurations);
Database database = new DatabaseProviderFactory(context).CreateDatabase("DBConnection");
return database;
}
Of course you can also set the integrated security parameter if you want.
cheers
|
|
|
|
|
i'm using a stored procedure to get two values out from the procedure after execution,usually one one value can be passed out by a procedure,can i use refernce varialbles for this,
if my procedure wants to return a set of records how can i implement the procedure
|
|
|
|
|
You can use out parameter if you have to return more than one values..
CREATE PROCEDURE GetImmediateManager
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END
|
|
|
|
|
Hi everybody.
I have a table in database Access with other font(example:VN-Times)
I want to convert all data and structure to SQL 2005 with Unicode font.
Please to help me.
Thanks for all suggetion
if you cound not try then
msgbox("you will lose")
else
msgbox("you can change yourself")
end if
|
|
|
|
|
Define the string datatype column in SQLServer table as nvarchar...
and then import data it will work..
Thanks
Hem
|
|
|
|
|
Is possible to create an XmlDataSource that has 50 columns merge an sql database with 14 columns into and force the other 36 columns except and false or any value I want with c#
Learning to Code
|
|
|
|
|
You is going to get flamed if you post the same question in multple forums.
Read the forum guidlines
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am trying to get the number of opened accounts per month in. For the most part I can get this information but if there was no account opening for March then March does not show up in the list. Here is what I have.
select Months, count(*) as 'Account Openings' from
(
SELECT
CASE
WHEN opened >= '2007-01-01' and opened <= '2007-02-01' THEN 'January'
WHEN opened >='2007-02-01' and opened <= '2007-03-01' THEN 'February'
WHEN opened >= '2007-03-01' and opened <= '2007-04-01' THEN 'March'
WHEN opened >= '2007-04-01' and opened <= '2007-05-01' THEN 'April'
WHEN opened >= '2007-05-01' and opened <= '2007-06-01' THEN 'May'
WHEN opened >= '2007-06-01' and opened <= '2007-07-01' THEN 'June'
WHEN opened >= '2007-02-01' and opened <= '2007-08-01' THEN 'July'
WHEN opened >= '2007-08-01' and opened <= '2007-09-01' THEN 'August'
WHEN opened >= '2007-09-01' and opened <= '2007-10-01' THEN 'September'
WHEN opened >= '2007-10-01' and opened <= '2007-11-01' THEN 'October'
WHEN opened >= '2007-11-01' and opened <= '2007-12-01' THEN 'November'
ELSE 'December'
END AS Months
FROM account
WHERE opened >= '2007-01-01' AND opened <= '2007-12-31'
) as OpenedPerMonth
group by Months
order by Months
I would get something like this as output
April 12
August 44
February 55
January 19
July 12
June 11
March 42
May 11
September 6
Thanks
Matt
|
|
|
|
|
Have you turned the ANSI_NULLS OFF?
Blog link to be reinstated at a later date.
|
|
|
|
|
select Months, isnull(count(*),0) as 'Account Openings' from<br />
(<br />
SELECT<br />
CASE<br />
WHEN opened >= '2007-01-01' and opened <= '2007-02-01' THEN 'January'<br />
WHEN opened >='2007-02-01' and opened <= '2007-03-01' THEN 'February'<br />
WHEN opened >= '2007-03-01' and opened <= '2007-04-01' THEN 'March'<br />
WHEN opened >= '2007-04-01' and opened <= '2007-05-01' THEN 'April'<br />
WHEN opened >= '2007-05-01' and opened <= '2007-06-01' THEN 'May'<br />
WHEN opened >= '2007-06-01' and opened <= '2007-07-01' THEN 'June'<br />
WHEN opened >= '2007-02-01' and opened <= '2007-08-01' THEN 'July'<br />
WHEN opened >= '2007-08-01' and opened <= '2007-09-01' THEN 'August'<br />
WHEN opened >= '2007-09-01' and opened <= '2007-10-01' THEN 'September'<br />
WHEN opened >= '2007-10-01' and opened <= '2007-11-01' THEN 'October'<br />
WHEN opened >= '2007-11-01' and opened <= '2007-12-01' THEN 'November'<br />
ELSE 'December'<br />
END AS Months<br />
FROM account<br />
WHERE opened >= '2007-01-01' AND opened <= '2007-12-31'<br />
) as OpenedPerMonth<br />
group by Months<br />
order by Months
Hope it will help.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
why dont you use month() function 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
