Script Help .net

Glen Robson

Hi Guys,

I have been creating a website that is going to store predictions for UK Football results. I have been able to save all of the data into a database and everything is working correctly. Now its time for the hard part and thats why i need some help.

I currently have a table holding The FIXTURES, PREDICTIONS and USERS.

I have added a table called RESULTS (all of the table information will be available at the bottom of this post.

What i would like to do is at the end of every week i would like to run a Script that will check the predictions that the users have made and if they are the same as the Fixture results then i would like to add a 1 into the RESULTS table.

For example:

From the FIXTURE table i have MATCH1HR (Match 1 Home Result) and MATCH1AR (Match 1 Away Result). Lets say that in these columns they both have 1 (So the score of the match was [TeamA 1 vs 1 TeamB])

From the PREDICTIONS table the user has predicted that the results of MATCH1 would be 1 - 1 these results are stored within the PREDICTIONS table under the Columns PRE1H (Prediction Match1 Home Result) and PRE1A (Prediction Match1 Away Result) so the value in both of these columns would be 1.

What i would like to do is if PRE1H matches the same value as MATCH1HR and PRE1A matches the same value as MATCH1AR then i would like to populate the RESULTS table under the column RESULT1H with the value of 1.

I am unsure of how to do this my only guess was to create a Insert using WHERE statements but i am still unsure of how to achieve this.

There is more than 1 match within my database i am just using one for this example to test it so i know how to go about creating the SQL.

Predictions are unique by using the date, when i create a fixture list the date i added the information is inserted within the FIXTURES table and when the user goes to add a prediction they get a choice of what date to make a prediction against. Therefore when the script is ran it would need to insert records into the RESULTS table for every user that has made a prediction for the most current date.

I am creating this within .NET using C# and using a MS SQL Server.

Below are my tables within the database, if you would like anymore information please just ask.

P.S. I know this is a little confuising but if you have any questions please feel free.

FIXTURES

FIXTURE_ID
FIXTURE_DATE
MATCH1HR
MATCH1AR
MATCH2HR
MATCH2AR
MATCH3HR
MATCH3AR
MATCH4HR
MATCH4AR
MATCH1HID
MATCH1AID
MATCH2HID
MATCH2AID
MATCH3HID
MATCH3AID
MATCH4HID
MATCH4AID

USERS

USER_ID
FIRSTNAME
SURNAME
USERNAME
PASSWORD
DOB

PREDICTIONS

PREDICTION_ID
USER_ID
PRE1H
PRE1A
PRE2H
PRE2A
PRE3H
PRE3A
PRE4H
PRE4A
DATE_OF_PREDICTION

RESULTS

RESULT_ID
RESULT_DATE
RESULT1
RESULT2
RESULT3
RESULT4

Thanks Boldonglen

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Glen Robson15y ago
I have already created my site that connects to my SQL database and I have a good knowladge of programming. My tables are all connected using relationships. I have I'm sure I can create a SQL script that can do what I need?
Sam Hobbs15y ago
If I understand what you are asking, then you need to write a program that reads data from the database, then processes the data and then updates some records depending on the results of the processing for each record.

First, note that you probably need to have a SQL query that joins two or more tables; are you familiar with that?

Next you need to decide how you are going to do your database processing. There are many articles in this web site to help. You can use the ADO .Net classes directly, such as SqlConnection, SqlCommand and SqlDataAdapter. Or you could create a data source and then Visual Studio will generate some code for you that uses ADO .Net and then you only need to write less detailed code. You can create a data source that either uses TableAdapters or that uses the Entity Framework. The problem is that whatever you use, you must learn how to use it. I suggest using the Entity Framework and look here for help with that.

One problem with Entity Framework is not actually a problem with it but that is a problem with articles about it. Most articles also use LINQ, which also takes time to learn. I have not yet read something that explains LINQ well enough for me to understand LINQ. So for whatever it is worth, you do not have to use LINQ with the Entity Framework, but it might take time to find articles that explain the Entity Framework without LINQ.