Database Discussion Boards

Re: How do I do this in SQL

Arjan Einbu10-Sep-07 22:58

Arjan Einbu

10-Sep-07 22:58

You could create the inner query like this:

SELECT oi.CustomerID
FROM OrderItems oi
	JOIN Products p
WHERE p.ProductID IN ('Product1', 'Product2')
GROUP BY oi.CustomerID
HAVING COUNT(*) = 2

You'll have to dynamically create the WHERE p.ProductID IN (...) part, and the value for the HAVING COUNT(*) = 2 should use a parameter.

Joined with the customer table, the results could look something like this:

DECLARE @NumberOfProducts int
SET @NumberOfProducts = 2

SELECT
	c.*
FROM Customer c
	JOIN
	(
		SELECT oi.CustomerID
		FROM OrderItems oi
			JOIN Products p
		WHERE p.ProductID IN ('Product1', 'Product2')
		GROUP BY oi.CustomerID
		HAVING COUNT(*) = @NumberOfProducts
	) AS t
		ON t.CustomerID = c.CustomerID

Re: How do I do this in SQL

Yitzchok Dev17-Sep-07 6:44

Yitzchok Dev

17-Sep-07 6:44

Thank you

analysis service sql2005

ABBASI_RA9-Sep-07 20:14

ABBASI_RA

9-Sep-07 20:14

Notify when a special partition in analysis service must update?

how i can triger a program to update a special partition of a cube at a required time?

or

how to found a way to know if a special partition need to be update?

thanks

connecting the sql db in the server

prasadbuddhika9-Sep-07 19:01

prasadbuddhika

9-Sep-07 19:01

when you want to connect to the db in the server do i have to use sqloledb provider , can't i use sqlclient provider. Smile | :)

Re: connecting the sql db in the server

Blue_Boy9-Sep-07 21:02

Blue_Boy

9-Sep-07 21:02

http://wwww.connectionstrings.com

I Love SQL

Re: connecting the sql db in the server

Rami Said Abd Alhalim9-Sep-07 23:34

Rami Said Abd Alhalim

9-Sep-07 23:34

you want to add port on firewall port number = 1433 to you can use sqlclient

Rami Abdalhalim

Re: connecting the sql db in the server

Frank Kerrigan10-Sep-07 9:24

Frank Kerrigan

10-Sep-07 9:24

sqlclient uses a an ole connection and you can use to connect it to SQL Server, this is the prefered method and is used in most examples.

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

a mistake with the "Like" statement..help!..

keremburak9-Sep-07 8:23

keremburak

9-Sep-07 8:23

dear friends,

I develop an Win application by using MS Access.I have got a problem in SQL statement.I have got a form that has

got 2 ComboBox and a DataGridView.The name of the first is cmbCategories and the second one is cmbContent.Content

is listing the content of categories.I use an array to transfer letters when I choose the category in

cmbCategories as it is Alphabetical Array.When I choose a letter from cmbContent like A,B or another one,but no

records is listing in the DataGridView.I try to use "Like" statement for this action.It is like this:

string strSQL = "select FilmName from films where FilmName like" + "'" + cmbContent.Text + "*'";

cmbContent has got the Alphabetical letters by sequence.I want to list the FilmName that is beginning as

alphabetic.For example,when I choose letter A,the films begins A must be listed.Does anybody help me?Thanks for

your help.

Re: a mistake with the "Like" statement..help!..

Blue_Boy9-Sep-07 8:33

Blue_Boy

9-Sep-07 8:33

string strSQL = "select FilmName from films where FilmName like '" + cmbContent.Text + "*'";

I Love SQL

Re: a mistake with the "Like" statement..help!..

Eddie Y Chen9-Sep-07 17:17

Eddie Y Chen

9-Sep-07 17:17

You should use parameterized SQL commands to prevent SQL injections... Smile | :)

Re: a mistake with the "Like" statement..help!.. [modified]

keremburak9-Sep-07 23:47

keremburak

9-Sep-07 23:47

I tried,but it doesnt work.I wrote the whole code below.maybe it helps what I want to do.

private DataView Get_DataAlphabetic()
{
string constr = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Environment.CurrentDirectory + "\\mdb\\film_db.mdb";

OleDbConnection con_ole = new OleDbConnection(constr);

string strSQL = "select FilmName from films where FilmName like " + "'" + cmbContent.Text + "*'";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, con_ole);
DataTable dt = new DataTable();
da.Fill(dt);
DataView dv = new DataView(dt);
dv.Sort = "FilmName ASC";
da.Dispose();
con_ole.Close();
return dv;
}

I send it to a datagrid's datasource property in another method as I use cmobobox's SelectedIndexChanged method.Hope it is clear.I wait for your replies

-- modified at 5:58 Monday 10th September, 2007

Re: a mistake with the "Like" statement..help!..

Blue_Boy10-Sep-07 22:59

Blue_Boy

10-Sep-07 22:59

Why you aren't trying to use Filter instead of getting directrly from database.

Download all data at first time from database then use filter property.

I Love SQL

Re: a mistake with the "Like" statement..help!..

keremburak13-Sep-07 6:12

keremburak

13-Sep-07 6:12

thanks buddy maybe sometimes you must look at it in a true point.I have done it.

Thnx.

Re: a mistake with the "Like" statement..help!..

Blue_Boy13-Sep-07 20:36

Blue_Boy

13-Sep-07 20:36

You are welcome...

I Love SQL

Re: a mistake with the "Like" statement..help!..

PIEBALDconsult10-Sep-07 5:08

PIEBALDconsult

10-Sep-07 5:08

zmskerem wrote:
"*'";

Try a percent sign (%).

Re: a mistake with the "Like" statement..help!..

Blue_Boy10-Sep-07 22:54

Blue_Boy

10-Sep-07 22:54

Database is Access, not SQL and condition % always return null value.
Instead of % is used *

I Love SQL

Re: a mistake with the "Like" statement..help!..

rohitsrivastava10-Sep-07 19:42

rohitsrivastava

10-Sep-07 19:42

hi friend
you are right there is mistake in sql please correct it

right
string strSQL = "select FilmName from films where FilmName like '" & cmbContent.Text & "%'" ;

wrong this is your
"select FilmName from films where FilmName like" + "'" + cmbContent.Text + "*'";

Re: a mistake with the "Like" statement..help!..

Blue_Boy10-Sep-07 22:55

Blue_Boy

10-Sep-07 22:55

rohitsrivastava wrote:
right
string strSQL = "select FilmName from films where FilmName like '" & cmbContent.Text & "%'" ;

false
_________________

rohitsrivastava wrote:
wrong this is your
"select FilmName from films where FilmName like" + "'" + cmbContent.Text + "*'";

true

I Love SQL

Re: a mistake with the "Like" statement..help!..

DerekFL12-Sep-07 8:19

DerekFL

12-Sep-07 8:19

Use "%" instead of "*"

Problem with LDF files ( database log file )

Developer6119-Sep-07 1:45

Developer611

9-Sep-07 1:45

Hi there .

I have a problem with SqlServer 2005 Log file .
I have an application and i want to attach my database to SqlServer , I did it by SqlConnection and ConnectionString ,in many times it gives me an error.

I know that when we use AttachDbFilename property in Connection for attach DB we don't need database log file, But most of times it gives me this error:

"Unable to open physical file 'filpath_log.LDF'. Operation system error 5: "5(error not found)".<br />
Could not open new database 'DbName'. CREATE DATABASE is aborted.<br />
Cannot attach the file 'FilPath.mdf' as database 'DbName'.<br />
File activation failure. The physical file name 'filpath_log.LDF' may be incorrect.<br />
The log cannot be rebuilt when the primary file is read-only.'

I wonder to say sometimes attachment works but sometimes not. WTF | :WTF:

I already did this attachment process with SQLServer 2000 and it never gives me error Dead | X|

DMASTER

Re: Problem with LDF files ( database log file )

Frank Kerrigan10-Sep-07 9:33

Frank Kerrigan

10-Sep-07 9:33

What are you trying to do ?????

Load a log file via a connection ????

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Re: Problem with LDF files ( database log file )

rohitsrivastava10-Sep-07 19:54

rohitsrivastava

10-Sep-07 19:54

hello friend

where u get the back of data base it is not saved full some thing is missing so again take back up and there is no error tell me how to creat database with sqlscript or enterprisemanager if u take backup of enterprisemanager is best this is the

step
(1) open Enterprisemanager
(2) right click on database name
(3) All Task
(4) Attach database
(5) MDF File database to attach (Browse the path)
(6) click mdf file and ok button

you have succesfully attach in the server database

then your problem is solve connection to database coding

have a nice day

How to find tree path in a table with parent cod

sepel9-Sep-07 1:39

sepel

9-Sep-07 1:39

i have a table with a tree organiz.
each record have a unic id and a parent id that refrenc to aanother id on this table. so each record have a parent or more
i want to find the fulll path of a record such as :
human/body/leg
i want to do it with a function in mysql;
every one can help me?

-- modified at 7:44 Sunday 9th September, 2007

sepel

Re: How to find tree path in a table with parent cod

Chetan Patel9-Sep-07 21:25

Chetan Patel

9-Sep-07 21:25

Use Recursive function each time it add parent untill the parent is null.

Best Regards,
Chetan Patel

How to implement Transaction in a MySql Stored Procedure with Cursor ?

fearless stallion8-Sep-07 19:16

fearless stallion

8-Sep-07 19:16

Hi

I am using a MySql 5.0 DB. am trying to implement a transaction inside a stored procedure which uses cursors. but the problem is the way the cursor closes it self. it throws an exception, i have written a handler for any sqlexception inside the procedure which simply rolls back, and because of the cursor , transaction gets rolled back without completing the task.

Has anybody ever worked on MySql with transactions. Please Help.

thanks

KSS

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.