Parameter asp.net question

Greetings Experts,

I need some guidance/direction as unable to get my head around this issue.

I found a way to display queries against our database within basic webmatrix pages. Now I want to experiment with allowing access to users to view own records. I have set up the membership and authentication login and all works as expected. This is linked to a basic database with just userid and email in user profile (database1).

I am hoping to retrieve the main tables from a second remote database (database2 - added to project as connection)


My question is how do I enable a user to view own records.

var db = Database.Open ("Database2");
    var SQLquery = "SELECT [WorkerId],[Email1] FROM [Table1] where WorkerId=@0";
 
 
if (!WebSecurity.IsAuthenticated) {
  Response.Redirect("~/Account/Login?returnUrl="
    + Request.Url.LocalPath);
  }

What I want to be able to write is as follows.

from the login information, ascertain userid and email (username) from database1,
cross check this email with Email1 from database 2 (unless there is another way to match users ) and if correct, display table from database2 to user.

Any help greatly appreciated.

Kind regards. SJ
sunny-jAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
Hi,

If I understand correctly, you want to present user with login screen.

User enters userid and email and then you compare that with userid and email from database1.

What would be your reason for comparing email and userid from database1 to same information from database2?
0
sunny-jAuthor Commented:
Hi Sammy,
I want to retrieve information from database 2 so I presume that I would need to link the databases. But if you feel that is not necessary and it's still possible to show user information according to profile, then pls guide on how I approach this issue. Kind regards SJ
0
sammySeltzerCommented:
Ok, I see

So, the link between database1 and database2 is email?

If it was me, an auto generated id from database1 and inserted into database2 as foreign key to database1 would have been a preferred approach.

However, if you are confident that you would not run into email duplication then I would try something like this:

select d1.userid, d1.email, d2.field1, d2.field2, d2.fieldn from database1 d1 inner join database2 d2 on d1.email = d2.email1 where d1.email = emailparam

Is that what you are after or did I miss something?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

sunny-jAuthor Commented:
Thank you Sammy, i think that will work and will try later. Pls can you kindly list the next steps required to display the user data based on login. Essentially I need some basic outline on the code needed to link a login with data, so when testing each login, I am able to retrieve the correct data. Thanks again, SJ
0
sammySeltzerCommented:
Ok, I will provide a small example and you can add to it later.

Let's say for instance that you wish to display user's first name, last name, status, address, you would have this on code behind:

        Using oconn As New SqlConnection(sqlconn)
            Dim cmd As New SqlCommand("select d1.userid, d1.email, d2.field1, d2.field2, d2.fieldn from database1 d1 inner join database2 d2 on d1.email = d2.email1 where d1.email=@email and d1.password=@pass", oconn)
        'Here we use parameterized query to keep from sql injection attack.
            cmd.Parameters.AddWithValue("@email", Session("email"))
            cmd.Parameters.AddWithValue("@pass", Session("password"))
            oconn.Open()
            Dim dr As SqlDataReader
            System.Threading.Thread.Sleep(3000)
            dr = cmd.ExecuteReader()
            While dr.Read()
               'Then pass db values to the form fieldnames. You can add more to this list
                lblFirstName.Text = dr("first_name").ToString()
                lblLastName.Text = dr("last_name").ToString()
                lblAddress.Text = dr("CustAddress").ToString()
                lblStatus.Text = dr("status").ToString()
            End While
            oconn.Close()
        End Using

Open in new window


On the markup, you would use labels unless you would like the users to make changes to the information on their profile page.

Something like:
<asp:Label ID="lblfirstName" runat="server" />

Open in new window

etc
ect
Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sunny-jAuthor Commented:
Perfect Sammy, I appreciate your swift answer, I am using web matrix and i take it this approach will work in razor c# code. Essentially I need to try and match the session email and password with websecurity.   I am away tonight and will try this out when home tomorrow, thank you again, have a good day.
0
sammySeltzerCommented:
The code is in vb but you can easily convert it to c# flavor.
0
sunny-jAuthor Commented:
Dear Sammy, thanks again for all your help. In the end I wasn't able to get my head around it, especially trying to do an inner join on the second database within WebMatrix. I am going to try again on another question, maybe approach it differently.  I hope you may be able to help again. Kind regards. SJ
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.