CODEWITHSUNDEEP
sql-servervb.net
Oualla Walid
Oualla Walid

Reputation: 11

Insert Data into Foreign Key Table VB.NET

How can I Insert Data into Foreign Key Table?

I have two tables in database Addemployees and Normal_L contain one relationship which is the ID.I want to insert data into Normal_L Table where the ID must stay related with Addemployees Table.

Dim cmd As New System.Data.SqlClient.SqlCommand
    Dim RA As Integer
    Try
        cn.Open()
        cmd.CommandType = System.Data.CommandType.Text
        If Me.RadioButton1.Checked Then
            cmd = New SqlCommand("INSERT into Normal_L (fromD,toD,DScrip) VALUES ('" & DateTimePicker2.Text & "','" & DateTimePicker1.Text & "','" & TextBox5.Text & "') where ID ='" & ComboBox1.Text & "' ", cn)
            cmd.Connection = cn
            RA = cmd.ExecuteNonQuery()
            MessageBox.Show("Process successful!", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
            cn.Close()
        End If
    Catch
        MessageBox.Show("Error!", "exit", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

    TextBox3.Clear()
    TextBox4.Clear()
    TextBox5.Clear()

End Sub

I hope that you can help me with that

Upvotes: 0

Views: 2247

Answers (1)

sapi
sapi

Reputation: 244

Alright so if you want to do it properly use a stored procedure as such : 

CREATE PROCEDURE [dbo].[YourProcName]
    -- Add the parameters for the stored procedure here
     @fromD nvarchar(50), --yourType
     @toD nvarchar(50), --Type
     @DScrip nvarchar(50), --yourType
     @ID int
 AS
 BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT into Normal_L (fromD,toD,DScrip) 
    SELECT  @fromD ,
            @toD,
            @DScrip
    FROM Addemployees 
    WHERE Addemployees.ID = @ID
END

This stored procedure will prevent not only the sql injections from your previous code but if you read closelly the select will only select something if the id in Addemployee is matched with your parameter. Again you could use a simple foreign key but since i dont know the structure of your base i cant tell.

now for your VB code : 

Dim cmd As New System.Data.SqlClient.SqlCommand
    Dim RA As Integer
    Try
        cn.Open()
        cmd.CommandType = System.Data.CommandType.StoredProcedure
        If Me.RadioButton1.Checked Then
            cmd = New SqlCommand("YourProcName", cn)
            cmd.Connection = cn
            cmd.Parameters.AddWithValue("@fromD", DateTimePicker2.Text)
            cmd.Parameters.AddWithValue("@toD",DateTimePicker1.Text )
            cmd.Parameters.AddWithValue("@DScrip",TextBox5.Text )
            cmd.Parameters.AddWithValue("@ID", ComboBox1.Text)
            RA = cmd.ExecuteNonQuery()
            MessageBox.Show("Process successful!", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
            cn.Close()
        End If
     Catch
        MessageBox.Show("Error!", "exit", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

    TextBox3.Clear()
    TextBox4.Clear()
    TextBox5.Clear()

 End Sub

you need to change the types in the stored procedure i dont know what you are using. Your final code should look roughly like that.

hope this helps

Upvotes: 1

Related Questions