Re: wcf - Database Discussion Boards

Jassim Rahma28-Jul-12 11:06

28-Jul-12 11:06

I am developing C# winForm application. I have a textbox for searchign customer name but I want the user to enter the customer name like this:

john mathews

Or:

Robert Duff

then in MySQL I want to have a dynamic sql to search search the customer_name like this:

SQL

SELECT * FROM customers WHERE customer_name CONTAINS john AND customer_name CONTAINS mathews

Re: help with MySQL dynamic sql

Mycroft Holmes28-Jul-12 13:59

Mycroft Holmes

28-Jul-12 13:59

See the sticky at the top, it tells you how to get an answer to your QUESTION, ou actually need to ask a question rather than make a statement.

Never underestimate the power of human stupidity
RAH

Re: help with MySQL dynamic sql

Richard MacCutchan28-Jul-12 21:20

Richard MacCutchan

28-Jul-12 21:20

Get the value of the texbox, split it into a string[] and use the elements of the array as parameters into your SQL query. Note, do not use string concatenation to create your query but proper SQL parameterisation.

One of these days I'm going to think of a really clever signature.

Re: help with MySQL dynamic sql

Jassim Rahma29-Jul-12 8:37

Jassim Rahma

29-Jul-12 8:37

this is in C#.. but can this be done in SQL level?

Re: help with MySQL dynamic sql

Richard MacCutchan29-Jul-12 9:10

Richard MacCutchan

29-Jul-12 9:10

In your opening question:

jrahma wrote:
I am developing C# winForm application.

So which is it to be?

One of these days I'm going to think of a really clever signature.

Re: help with MySQL dynamic sql

Eddy Vluggen29-Jul-12 9:41

Eddy Vluggen

29-Jul-12 9:41

Invest some time here[^].

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

wcf

PraveenSura27-Jul-12 18:36

PraveenSura

27-Jul-12 18:36

what is self hosting and IIS hosting?is there any difference of it

Re: wcf

jschell28-Jul-12 6:44

jschell

28-Jul-12 6:44

One difference of of course is that in one case one uses IIS (Internet Information Server) and in the other you don't.

Besides that, having used self hosting quite a bit but having never used IIS (in any way) my take (guess) is that there is some non-trivial part of WCF that is more apt for IIS than for self hosting. For example, the idea that a service 'restarts' isn't applicable for self hosting because the host app itself is either running or not running and that has nothing to do with WCF.

Also there is a WCF forum.

Re: wcf

Wes Aday28-Jul-12 9:11

Wes Aday

28-Jul-12 9:11

From your QA question: wcf information and web service information[^]

Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Please stand in front of my pistol, smile and wait for the flash - JSOP 2012

SQL - Finding same product in different waehouse where price is not the same

Richard.Berry10026-Jul-12 1:24

Richard.Berry100

26-Jul-12 1:24

Hi
I need to run a query on a stock table. Each product may exist in either one or many warehouses. I am trying to find all items where the cost of the product is not the same in warehouse 01 and 02.

Example

Wh Prod Cost
01 A 10.00
02 A 10.00
01 B 64.00
01 C 98.00
02 C 10.00

In the example data above, I would like to get the last two rows returned.

This is what I have tried, but get lots of the same product code

SELECT stockm.warehouse, stockm.product, stockm.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B,
scheme_stockm AS stockm
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY stockm.product, stockm.warehouse

Re: SQL - Finding same product in different waehouse where price is not the same

David Mujica26-Jul-12 2:12

David Mujica

26-Jul-12 2:12

I think you are close ... but try this:

SQL

SELECT A.warehouse, A.product, A.standard_material,B.warehouse, B.product, B.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY A.product, A.warehouse

I believe you included the scheme_stockm too many times in your original query.

Re: SQL - Finding same product in different waehouse where price is not the same

Richard.Berry10026-Jul-12 4:47

Richard.Berry100

26-Jul-12 4:47

Thanks David

I had to add warehouse in the two select statements for warehouse '01' and warehouse '02' since they were referred to in the outer SELECT as below and now it works.

SELECT A.warehouse, A.product, A.standard_material,B.warehouse, B.product, B.standard_material FROM
(SELECT warehouse, product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT warehouse, product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY A.product, A.warehouse

I also got mine to work by adding an additional 'join' as below on stockm.product = A.product, but I prefer yours, since the result is one record per error, whereas mine is two records per error

SELECT stockm.warehouse, stockm.product, stockm.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B,
scheme_stockm AS stockm
WHERE (A.product = B.product AND A.standard_material <> B.standard_material AND stockm.product = A.product)
ORDER BY stockm.product, stockm.warehouse

Cool

David Mujica26-Jul-12 7:03

David Mujica

26-Jul-12 7:03

Glad it worked ouy for you. Thumbs Up | :thumbsup:

Re: SQL - Finding same product in different waehouse where price is not the same

Michael Potter27-Jul-12 9:41

Michael Potter

27-Jul-12 9:41

Your query will only work if you have 2 warehouses. What happens if there are 3 or more? Try:

SQL

SELECT
    a.warehouse,
    a.product,
    a.standard_material
FROM
    scheme_stockm AS a
WHERE
    EXISTS (SELECT * 
            FROM scheme_stockm 
            WHERE product = a.product AND 
                  standard_material <> a.standard_material)
ORDER BY
    a.product,
    a.warehouse

Re: SQL - Finding same product in different waehouse where price is not the same

Richard.Berry10028-Jul-12 6:35

Richard.Berry100

28-Jul-12 6:35

Hi Michael

I had wondered about multiple warehouses Smile | :)

In this particular case the product can exist in other warehouses, however the cost in the others does not need to be the same - only warehouse 01 and 02 must be the same.

I did test your query, and it works perfectly!

Thanks as I'm sure I'll need something like this in the future

Searching Content of Documents in Database

ASPnoob24-Jul-12 11:03

ASPnoob

24-Jul-12 11:03

Hi all,
If I were an employer searching for resumes which contain skill sets that match my search criteria, how is that done from a programmer's perspective. Can we actually go inside of text documents in a database and search their contents? If not, I will have to add a Skills field to the database so that it can be searched. Any suggestions will be greatly appreciated, thanks in advance.

Re: Searching Content of Documents in Database

Eddy Vluggen24-Jul-12 12:32

Eddy Vluggen

24-Jul-12 12:32

ASPnoob wrote:
Can we actually go inside of text documents in a database and search their contents?

That wouldn't be very efficient.

What type of documents do you expect, besides PDF, Word and the OpenOffice format? Whenever your end-user uploads a document, save it twice; once the original document, once a text-only version. Each of those formats can be "read" with the help of some libraries, and the text can be extracted. The text-version of the document can easily be searched, and once you find a "match", you display the original document.

For searching the text-versions of the uploaded documents, take a look at the Full Text Search[^] article on MSDN. If you're using another version than Sql2008, you can change it to match your specific version with a drop-down list at the top of the page.

Including a "Skills" field might still be beneficial, besides the FTS. Keep in mind that users will have different labels for the same role/function/job, and that managing those is the hardest part. For example, one of the major Dutch websites has different entries for "Software Developer", "Software Engineer" and the dutch word for "Software Developer".

Hope this helps a bit Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: Searching Content of Documents in Database

Mycroft Holmes24-Jul-12 14:16

Mycroft Holmes

24-Jul-12 14:16

When I wrote one of these in the 90s we required word docs only, then we compared each word in the document with a list of skills, if there was a kit it was added to the skills set for that document.

I imagine the tech has moved on in the last 20 years Blush | :O

Never underestimate the power of human stupidity
RAH

Re: Searching Content of Documents in Database

Eddy Vluggen24-Jul-12 23:29

Eddy Vluggen

24-Jul-12 23:29

How much did the tech move forward for time-sheets? Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Re: Searching Content of Documents in Database

StianSandberg25-Jul-12 0:00

StianSandberg

25-Jul-12 0:00

If it's actual files (blob) like word, .txt, excel etc you can use iFilter in sql. http://support.microsoft.com/kb/945934[^]
This will give you a fast search of content in your files.

--------------------
When Chuck Norris' dreams come true, your worst nightmares begin.

Re: Searching Content of Documents in Database

jschell25-Jul-12 7:26

jschell

25-Jul-12 7:26

ASPnoob wrote:
Any suggestions will be greatly appreciated

How many resumes? Hundreds, hundreds of thousands, Millions?

How responsive does it need to be? How many searches at one time?
Will this database and database server be used for anything else?

The answers to those questions impact how it can be designed.

Re: Searching Content of Documents in Database

Michael Potter25-Jul-12 11:03

Michael Potter

25-Jul-12 11:03

Google 'Resume Parser'. No need to re-invent the wheel.

Re: Searching Content of Documents in Database

Mycroft Holmes25-Jul-12 13:00

Mycroft Holmes

25-Jul-12 13:00

Astonishing, it would never have occurred to me that there seems to be a whole industry around parsing the crap we see in CVs. I wonder if they should be promoted as bullshit detectors!

Never underestimate the power of human stupidity
RAH

Re: Searching Content of Documents in Database

Peter_in_278025-Jul-12 14:17

Peter_in_2780

25-Jul-12 14:17

Implement in the language of your choice:

C++

// universal CV parser
boolean is_bullsh-t(string cv)
{
  return true;
}

Cheers,
Peter

Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012

SET NOCOUNT

Member 348763223-Jul-12 23:07

Member 3487632

23-Jul-12 23:07

When I Put SET NOCOUNT ON before sql statement its giving -1 rows effected in sql server 2005.

But in my code i need rows effected 0 . How to do that ?

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