[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

C# recursive check for a column (or with a loop check) and inform the user

Posted on 2011-10-17
11
Medium Priority
?
288 Views
Last Modified: 2012-05-12
Hi there;

In MSSQL, I have a table having 2 columns, 1 is the id as primary key, whereas the other is just price and both are int.

The question is that I want the user to be informed if there is a loop in this table.

The loop can be such:

id price
1  2
3  5
5  1

As seen, the id value having 1 has the loop in the third line with price value having 1. In such case, I want the user to be informed in C#.

How can I do that? I am a little illiterate regarding connecting the database and DML in MSSQL for C#.

Would be good with a code snippet.

Kind regards.

0
Comment
Question by:jazzIIIlove
  • 7
  • 3
11 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36978464
you mean to say you need to just get result teu or false if loopong in there ?

try this

 string strConnectionString = "connetion string here";
    SqlConnection cnn = new SqlConnection(strConnectionString);
    SqlClient.SqlCommand cmd = new SqlClient.SqlCommand("SELECT count(*) FROM tablename where id=price", cnn);
    cmd.CommandType = CommandType.Text;
    int cnt;
    cnt =  cmd.ExcuteScalar();
    if(cnt > 0 )
    {
        REsponse.Write("There is loopong")
    }
0
 
LVL 45

Accepted Solution

by:
AndyAinscow earned 2000 total points
ID: 36978477
This might sound silly but what connection has ID and price?
Put another way - if you didn't want a 'loop' then check BEFORE the record is added.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36978500
Thanks pratima_mcs.

Yes, exactly AndyAinscow!

I am doing this after the record is added and have to also deal with before the record is added.

I mean in fact, what I want to achieve is that I don't want let the user add such looping in the table for those columns (but don't want to bug with you with that).

Kind regards.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36978528
I mean how can I do before then?

Kind regards.
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 36978535
Have a query before you add such as
SELECT * FROM tbl WHERE ((col1 = 1) OR (col2 = 2))
you need to replace tbl, col and 1/2 with the real values.
If the query returns any records then that value already exists.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36978740
But the values and the repetition is example. Is it still OK with the above code as the loop may appear, maybe nth row in a thousand and couple of times?

Regards and thanks for your interest and patience.
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 36978749
That is really suited for before the record is added -  a check.

For an existing table you would have to construct a different query - a self join
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36978791
Yes, that was the term, self join! (i have been thinking it for an hour) :/

How can i do that for this code you provided.

Regards.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36978986
pratima_mcs:
Your code is not reflecting the loopng or self join.

Kind regards.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36979188
ah, my mistake regarding the question is that I didn't mention the following, as I am stuck with the query:

1 2
2 4
3 5
4 5
5 1

5 1 is not allowed but the rest above is allowed, meaning since 1 is used before in the first column (1, 2), it cannot be used in the second column (5,1 case) later on.

Regards.
0
 
LVL 12

Author Comment

by:jazzIIIlove
ID: 36979254
I have to catch the not allowed one in my query (ies).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question