How to populate Foreign Key

[rEhSi_123]

Hi Guys need a bit of help!

Basically I have a parent table called users which stores all the user info with a prime key field called ID. Now in the child table which has a seperate foreign key
associated with the ID field in the parent table.

What I would like to do is be able to populate the foreign key once once the user uploads data into the child table with the user ID it has.

So far I have created the associated foreign key with ID field in the user table and also programmly through data-relation related the table but now sure on how to get the value from the relation.

The foreign key in the child table is called 'U_ID' as shown in the snipet velow.

Please help :D

Dim cs As New SqlConnection("Data Source=PC\SQLEXPRESS;Initial Catalog=ForumCrawl;Integrated Security=True")
        Dim da As New SqlDataAdapter("SELECT * FROM search_result", cs)
        Dim da2 As New SqlDataAdapter("SELECT * FROM users", cs)
        Dim ds As New DataSet
        Dim dsNewRow As DataRow
        Dim cmb As SqlCommandBuilder = New SqlCommandBuilder(da)
        cmb.GetUpdateCommand()
        Dim dateToDisplay As DateTime = DateTime.Now

        da2.Fill(ds, "users")
        da.Fill(ds, "search_result")

        Dim relation As DataRelation
        Dim table1column As DataColumn
        Dim table2column As DataColumn
        'retrieve columns
        table1column = ds.Tables("users").Columns("ID")
        table2column = ds.Tables("search_result").Columns("U_ID")

        relation = New DataRelation("relation", table1column, table2column)

        ds.Relations.Add(relation)

        MsgBox("Data relation completed")

        dsNewRow = ds.Tables("search_result").NewRow

        dsNewRow.Item("URL") = tb_URL.Text
        dsNewRow.Item("page_content") = tb_Parse.Text
        dsNewRow.Item("U_ID") =
        dsNewRow.Item("date_created") = dateToDisplay.ToString
        ds.Tables("search_result").Rows.Add(dsNewRow)
        da.UpdateCommand = cmb.GetUpdateCommand
        Try
            da.Update(ds, "search_result")
            MessageBox.Show("New record added to the database!", _
                     "Success", MessageBoxButtons.OK, _
                     MessageBoxIcon.Exclamation, _
                     MessageBoxDefaultButton.Button1)
        Catch ex As Exception
            MessageBox.Show("Data already exists in database! Please check data to avoid duplicates", _
                            "Database Error!", MessageBoxButtons.OK, _
                            MessageBoxIcon.Error, _
                            MessageBoxDefaultButton.Button1)
        End Try
        cs.Close()

[adam_k]

Why would you do something like relationship? Why not insert based on the ID you already have? (And why is the U_ID not getting a value?)

If I understand correctly, you want to store in the table search_result, the results for each users query. Questions:
1. Do you know the ID?
2. Do you know any info from the users table to help you find the key?

Please describe what you need in detail and we'll help you get there.

[rEhSi_123]

Why would you do something like relationship? Why not insert based on the ID you already have? (And why is the U_ID not getting a value?)

If I understand correctly, you want to store in the table search_result, the results for each users query. Questions:
1. Do you know the ID?
2. Do you know any info from the users table to help you find the key?

Please describe what you need in detail and we'll help you get there.

Hi adam thanks for the kind reply :D

Say I have two tables: Table1 and Table2

Table1 ----> users
Table2 ----> search_result

Now Table1 has all the user log in information with a PK field called ID.
In Table2, it stores all the information grabbed from the web with a FK field called U_ID.

Now the data is only uploaded into the dB on click_event. So I basically want to be able to store the value of the ID field of Table1 into Table2 inside the FK U_ID onces the click_event is initiated.

The purpose here to keep a record of who downloads the data......

Perhaps creating a data relation is not the correct path.

Hope this explains?

[adam_k]

If you have the ID stored in a variable, then go ahead and insert a record into the second table. No relationship is required. When you retrieve the data, you will need to use a join, but are going to use that with or without a relationship.
The FK will help you with data integrity, but not automatically populating the ID when inserting new records.
If you don't know the ID, then either find it (using a query at the users table) or store it in a variable when logging in or something.

Also 2 points regarding your SqlDataAdapters:
It's bad practise to select everything from users table (for security reasons)
and 2nd you have to take into account that your search_result table can end up holding millions of records and selecting everything will cause a huge delay (and also is a bad practise as you are transmitting "sensitive" or private data to every point between your db and your app).
Only select the data that you've got use for. If you are interested in showing the data for a user, then use the ID as a criteria in your select. If you want all searches, then you can go ahead as in your code.

[rEhSi_123]

If you have the ID stored in a variable, then go ahead and insert a record into the second table. No relationship is required. When you retrieve the data, you will need to use a join, but are going to use that with or without a relationship.
The FK will help you with data integrity, but not automatically populating the ID when inserting new records.
If you don't know the ID, then either find it (using a query at the users table) or store it in a variable when logging in or something.

Also 2 points regarding your SqlDataAdapters:
It's bad practise to select everything from users table (for security reasons)
and 2nd you have to take into account that your search_result table can end up holding millions of records and selecting everything will cause a huge delay (and also is a bad practise as you are transmitting "sensitive" or private data to every point between your db and your app).
Only select the data that you've got use for. If you are interested in showing the data for a user, then use the ID as a criteria in your select. If you want all searches, then you can go ahead as in your code.

adam thanks once again for your help :) The things you have pointed out are quite
useful and I am surely going to use them to fine tune the application. And yes about the FK that it is a good practice for data integrity.

For this part

If you don't know the ID, then either find it (using a query at the users table) or store it in a variable when logging in or something.

This select statement should tell me the ID for the user yah?

Dim da2 As New SqlDataAdapter("SELECT ID FROM users", cs)

and then get a copy of the data like this?

da2.Fill(ds, "users")

Will I need to create a seperate Dataset for the second table or use the same?

And then how to add it to this field?

dsNewRow.Item("URL") = tb_URL.Text
        dsNewRow.Item("page_content") = tb_Parse.Text
        dsNewRow.Item("U_ID") = <------------------ Here?
        dsNewRow.Item("date_created") = dateToDisplay.ToString
        ds.Tables("search_result").Rows.Add(dsNewRow)
        da.UpdateCommand = cmb.GetUpdateCommand

Sorry I am still getting use to inserting via data adapter and datarow so a bit confused :(

[adam_k]

By using a select without a where you are asking for all the contents of the table. This means that

SELECT ID FROM users

will return all users.
You need to change it to look more like

SELECT ID FROM users where name = '" & textbox1.text &"'"

(Please note that the above method is not a good practise, but I post it as it is the easiest way to do it).

Instead of the name you can use any field that you've got a criteria for. Most apps would have a login form for that, or a form that handles "parameters", including username and optionally password. My advice to you is to go with the login form, as the parameters will require saving them and I don't see you ready for this, perhaps version 2.0 ?

If you can't find a sample login form, read here: http://www.daniweb.com/software-development/vbnet/code/217398/1584417#post1584417
Don't worry about my comments on the weakness of security, I doubt that this will concern you for a while (remember v 2.0).

[rEhSi_123]

Thanks adam for your wise comments :)

Here the login form I use in my application:

Now how can I get the value from here to my main form for which the code is shown above so that I can populate my foreign key in my second table which selected in the second form.

Dim cmd As New SqlClient.SqlCommand
        Dim login_cs As New SqlConnection("Data Source=PC\SQLEXPRESS;Initial Catalog=ForumCrawl;Integrated Security=True")
        Dim login_da As New SqlDataAdapter
        Dim login_ds As New DataSet

        cmd.CommandText = "SELECT * FROM [users] WHERE Username = '" & tb_Username.Text & "'AND Password = '" & tb_Password.Text & "';"
        login_cs.Open()

        cmd.Connection = login_cs

        login_da.SelectCommand = cmd
        login_da.Fill(login_ds, "0")

        Dim count = login_ds.Tables(0).Rows.Count

        If count > 0 Then
            Welcome.Show()
            Me.Hide()
        Else
            MessageBox.Show("Incorrect Login! Please check username and password", _
                            "Error", MessageBoxButtons.OK, _
                            MessageBoxIcon.Error, _
                            MessageBoxDefaultButton.Button1)
            tb_Username.Clear()
            tb_Password.Clear()
        End If

[adam_k]

On the top of your startup form declare a variable that will hold the user ID. Make sure you don't use DIM for this variable.

Friend U_ID as string = ""

Then change your login sub to something like this:

Dim cmd As New SqlClient.SqlCommand
        Dim login_cs As New SqlConnection("Data Source=PC\SQLEXPRESS;Initial Catalog=ForumCrawl;Integrated Security=True")
     U_ID = "" 'Clearing the variable, just in case your program calls this form again in order to change user. 

'Changed following line to return only the ID
        cmd.CommandText = "SELECT ID FROM [users] WHERE Username = '" & tb_Username.Text & "'AND Password = '" & tb_Password.Text & "';"
        login_cs.Open()

        cmd.Connection = login_cs
try 

U_ID = cmd.ExecuteScalar() 'Don't need no DataSet or adapter. We are only interested in 1 value which is the ID. 

    catch ex as exception 
msgbox(ex.Message) 
end try   

        If U_ID <> "" Then
            Welcome.Show()
            Me.Hide()
        Else
            MessageBox.Show("Incorrect Login! Please check username and password", _
                            "Error", MessageBoxButtons.OK, _
                            MessageBoxIcon.Error, _
                            MessageBoxDefaultButton.Button1)
            tb_Username.Clear()
            tb_Password.Clear()
            U_ID = ""
        End If

Now when inserting the record use this

dsNewRow.Item("U_ID") = U_ID

to set the user's ID in your record.

You can choose to run a second SQLCommand that will retrieve the ID after a successful login, again doing the same thing - ask for only the ID and get the result with ExecuteScalar or get the ID from your dataset (I wouldn't)

[rEhSi_123]

Mr. adam_k you are a GENIUS SIR! I really like the way you explained the changes and made it look easy. :)

THANK YOU VERY MUCH

Take care

[adam_k]

Mr. adam_k you are a GENIUS SIR! I really like the way you explained the changes and made it look easy. :)

THANK YOU VERY MUCH

Take care

You are more than welcome.
Take care.