How Do I Insert Values Into 2 Tables (Foreign Keys) Simultaneously Using Visual Basic

Question

0.00/5 (No votes)

See more:

Hello Everyone,

Whenever i tried to insert values into my tables using visual studio i get a null

value in the column customersID in my Orders Table (foreign key reference Customers),

how do i get V.S to fill the column instead of a null value, am using backend

SqlServer 2008

I will be more than glad if anyone can help me out on this case

Thanx in advance.

This is the structure of my Tables

SQL

CREATE TABLE Customers(
CustomersID INT IDENTITY PRIMARY KEY,
Company NVARCHAR (50),
ContactName NVARCHAR (50),
Phone NVARCHAR (50)
)
GO

CREATE TABLE Orders(
OrdersID INT IDENTITY (1,1) PRIMARY KEY,
CustomersID INT FOREIGN KEY REFERENCES Customers(CustomersID) ON DELETE CASCADE,
OrderDate DATE,
Freight DECIMAL (10,2)
)
GO

This is my code using Visual Basic

VB

Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
    Dim Query As String
    Dim MyConn As SqlConnection
    Dim Cmd As SqlCommand


 Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
 Try
            MyConn.Open()
            Query = "INSERT INTO Customers(Company, ContactName, Phone)VALUES('" & txtCompany.Text & "','" & txtContactName.Text & "', '" & txtPhone.Text & "')"
            Cmd = New SqlCommand(Query, MyConn)
            Cmd.ExecuteNonQuery()


    Query = "INSERT INTO Orders(OrderDate, Freight)VALUES('" & txtOrderDate.Text & "','" & txtFreight.Text & "')"
            Cmd = New SqlCommand(Query, MyConn)

            txtCompany.Clear()
            txtContactName.Clear()
            txtPhone.Clear()
            txtOrderDate.Clear()
            txtFreight.Clear()

            Cmd.ExecuteNonQuery()
            MyConn.Close()
            MessageBox.Show("Data Saved")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
End Class

Result
CustomersID Company ContactName Phone
1 Vanguard Tom 1234556789

OrdersID CustomersID OrderDate Freight
1 NULL 1950-01-01 25000.00

Posted 9-Oct-14 8:03am

wole2020

Updated 9-Oct-14 8:05am

PhilLenoir

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PhilLenoir · Answer 1 · 2014-10-09T08:30:00

The easiest way to do this is to do the first insert with a stored procedure, returning the ID of the new customer record.

Stored proc:

SQL

CREATE PROCEDURE prc_AddCustomer 
	@Company NVARCHAR (50),
	@ContactName NVARCHAR (50),
	@Phone NVARCHAR (50)

AS
BEGIN
INSERT INTO Customers(Company, ContactName, Phone)VALUES(Company, @ContactName, @Phone)

RETURN @@IDENTITY

END
GO

Call the procedure (note parameterize query to avoid SQL Injection):

VB

Dim Cmd As New SqlCommand("prc_AddCustomer", MyConn)
Dim lID as Long = 0

...
Cmd.CommandType = Data.CommandType.StoredProcedure
Cmd.Parameters.AddWithValue("@Company", txtCompany.Text)
Cmd.Parameters.AddWithValue("@ContactName", txtContactName.Text)
Cmd.Parameters.AddWithValue("@Phone", txtPhone.Text)
Cmd.Parameters.Add( _
        New SqlParameter("@ReturnValue", _
        Data.SqlDbType.Int))
Cmd.Parameters.Item("@ReturnValue").Direction = _
         Data.ParameterDirection.ReturnValue
MyConn.Open()
Cmd.ExecuteNonQuery()
lID=CInt(Cmd.Parameters.Item("@ReturnValue").Value)

You can now use the returned ID that's in "lID".

Notes:
You could do both insert in the one stored procedure, but this might reduce the reusability.
Always surround your DB access with a Try/Catch block.

Manfred Rudolf Bihy · Answer 2 · 2014-10-09T08:17:00

Your SQL statement for inserting into the Orders table doesn't use the CustomersID as a value. Do you think SQL Server would somehow magically know for which customer this Order should be? To prevent creating Orders without a valid foreign key reference to Customers that field should not allow NULL values.

Cheers!

How Do I Insert Values Into 2 Tables (Foreign Keys) Simultaneously Using Visual Basic

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0