?
Solved

Use table for random search

Posted on 2011-03-22
13
Medium Priority
?
242 Views
Last Modified: 2012-05-11
The attached code works and displays a random number as long as I comment out the "int SearchInfo" line.  How can I add a list I create to the code for it to search?  I'm trying to insert the result of a Select statement Instead of using (1,2108).  Obviously it's not working.  My environment is Visual Studio, the language is C# and I'm attached to a SQL Server db.
I'm really new to C# so some working code would be greatly appreciated.
private void button1_Click(object sender, EventArgs e)
        {
            int SearchInfo = SELECT CompanyId FROM dbo.Company WHERE TimeZone = 6;
            Random r = new Random();            
            int EmpNo = r.Next (1,2108);
            txtEmpNo.Text = Convert.ToString(EmpNo);   
        }

Open in new window

0
Comment
Question by:BobRosas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 3

Assisted Solution

by:AndrewSkoraro
AndrewSkoraro earned 500 total points
ID: 35191657
If your goal here is to use LINQ to SQL then its your syntax.  Your select statement will return multiple results.  You will also need to create a connection and setup objects for your database.  Its easy.

var SearchInfo = FROM c IN dbo.Company
                            WHERE TimeZone = 6
                            SELECT c;

From this, you will be able to get the number of results.
Take a look at this link for detailed instructions. http://www.mssqltips.com/tip.asp?tip=1534 
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35191772
__NO POINTS__

var SearchInfo = FROM c IN dbo.Company
                            WHERE TimeZone = 6
                            SELECT c;

Do keep in mind that C# uses the double-equals for equality comparison:

var SearchInfo = FROM c IN dbo.Company
                            WHERE TimeZone == 6
                            SELECT c;

Open in new window


Also in Linq2Sql, the SELECT comes at the end, as demonstrated in AndrewSkoraro's post.
0
 

Author Comment

by:BobRosas
ID: 35194009
Thank you both for your code, link and comments.  It was all very helpful!  
I attached code that now complies and runs.  But it only runs if I use the existing criteria of (1, 2108) how do I tell it to now use the new data I created?
Thanks again!
var dbTimeClockPlus =new TimeClockPlusDataContext() ; 
            var SearchInfo = from c in dbTimeClockPlus.Companies where c.TimeZone == 6 select c;
            Random r = new Random();
            int CoNo = r.Next (1, 2108);
            txtCoNo.Text = Convert.ToString(CoNo);

Open in new window

0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35194056
Which field of the table has the value you want to use as the upper bound of the random function?
0
 

Author Comment

by:BobRosas
ID: 35194078
The field name is Company.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35194102
Assuming Company is of integer type, you should be able to do something like:
var dbTimeClockPlus =new TimeClockPlusDataContext() ; 
var SearchInfo = (from c in dbTimeClockPlus.Companies where c.TimeZone == 6 select c.Company).FirstOrDefault();
Random r = new Random();
int CoNo = r.Next (1, SearchInfo + 1);  // +1 because FirstOrDefault() returns zero as "default"
txtCoNo.Text = Convert.ToString(CoNo);

Open in new window

0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 1500 total points
ID: 35194124
Actually, let me change the "+1" part:
var dbTimeClockPlus =new TimeClockPlusDataContext() ; 
var SearchInfo = (from c in dbTimeClockPlus.Companies where c.TimeZone == 6 select c.Company).FirstOrDefault();
Random r = new Random();
int CoNo = SearchInfo == 0 ? 1 : SearchInfo;

CoNo = r.Next (1, CoNo);
txtCoNo.Text = Convert.ToString(CoNo);

Open in new window

0
 

Author Comment

by:BobRosas
ID: 35194278
Thank you again!
Unfortunately, in order to make this easier for me (or so I thought) I used a table with only 2 records...
Company 1
Company 2
When I run the code (6 times) I only get company 1.  Do I need more to choose from for this to work?  I tried changing the code to the actual table but then I get other errors because I went from int to Boolean and Float in my criteria.  I've increased points (you have been GREAT) but I'd also be glad to post a related question.

var dbTimeClockPlus = new TimeClockPlusDataContext();
            var SearchInfo = (from c in dbTimeClockPlus.EmployeeLists where c.Suspend == false select c.EmployeeId).FirstOrDefault();   //Suspend is Boolean, EmployeeId is Float
            Random r = new Random();
            int EmpId = SearchInfo == 1 : SearchInfo;
            EmpId = r.Next(1, EmpId);
            txtCoNo.Text = Convert.ToString(EmpId);

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35194352
Does the column actually have "Company 1" as its data, or does it have "1"? In other words, is the field type a string or an integer?
0
 

Author Comment

by:BobRosas
ID: 35194362
Sorry for the confusion.  That was not very clear.  The field "Company" is an integer.
1
2
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35195015
The upper bound on Next() is exclusive--meaning the maximum value returned can be 1 less than what you put. If the 2nd parameter is 2, then the only number ever returned by Next() would be 1. Adding more records should resolve the issue  = )
0
 

Author Comment

by:BobRosas
ID: 35199973
When I  changed your code to use on another table I couldn't get it to work because the field I chose (EmpId) is of type Float.  So I used the field RecordId (type int) and it works like a charm.  The returned result is of no use but I know the code works so I will max out points (you were so helpful) close this question and post a related question.
Thanks again!
0
 

Author Closing Comment

by:BobRosas
ID: 35199986
I couldn't have done it without your help!
Thanks again!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

777 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