am using vb.net and sql database.
My tables are as follows:
Details:
Details_ID (PK)
Address_1
Address_2
City
Postcode
Country
Email

And Customers:
Cust_ID
Customer_Name
Notes
Details_ID(FK)

When i insert data, it fills in the tables but the Details_ID comes as Null. I need the Details ID to correspond with the Customer. All this to happen in one form.
My code is as show below:

Private Sub btn_submit_Click(sender As System.Object, e As System.EventArgs) Handles btn_submit.Click

 
Dim COn As String = "Insert into [Details] ( Address_1, Address_2, City, Postcode, Country, Email) VALUES ('" & txt_add1.Text & "','" & txt_add2.Text & "','" & txt_city.Text & "','" & txt_postcode.Text & "','" & txt_country.Text & "','" & txt_email.Text & "')"

COn &= "Insert into [Customers] (Customer_Name, Notes) VALUES ('" & txt_cust_name.Text & "', ' " & txt_notes.Text & "')"

        Dim dr As Data.SqlClient.SqlDataReader = GetDataReader(gCOn)

        'cmd.ExecuteNonQuery()

        'txt_security_level_ID.Clear()

        'txt_position.Clear()

        Try
        Catch ex As Exception

            MessageBox.Show("Error while inserting record." & ex.Message, "Insert Records")

       End Try
   Me.Close()

        Add_Customer.ShowDialog()

    End Sub

any help would be appreciated :)

Recommended Answers

All 7 Replies

It looks like some code has been removed.
You're saying this code actually updates the database?
In either case, the SqlCommand object is missing.

It looks like some code has been removed.
You're saying this code actually updates the database?
In either case, the SqlCommand object is missing.

yes it works, i have my sqlCommand in my modules. The issue however is not the connection, it is to do with the FK and PK which does not seem to link accordingly. I hope I am phrasing my questions correctly?

This SQL looks like it is trying to do two inserts simultaneously.
Is that what you're trying to do?

If so, they need to be separated into two different commands.

Why do you have the details in a second table. It doesn't look like you need it, just move all the fields in the details table into the Customers table. If you wish to persist with the design you have then the insert into customers table at line 7 does not include the the Details_Id. YOu need to work out the ID of the record you have inserted into the Details table and include that in the INSERT into the Customers table.

Why do you have the details in a second table. It doesn't look like you need it, just move all the fields in the details table into the Customers table. If you wish to persist with the design you have then the insert into customers table at line 7 does not include the the Details_Id. YOu need to work out the ID of the record you have inserted into the Details table and include that in the INSERT into the Customers table.

Well I have normalised the database. I have another table called suppliers that uses Details table to collect the information for suppliers. I thought instead of asking both questions on here if i got the customers to work I would be able to implement it on suppliers too.

I have a brief understanding, I need to get the last details ID to automatically link to my customers Details ID if that makes sense?. I just dont know what command to use :( . Kindly advice if you have an idea.

Thanks in advance

This SQL looks like it is trying to do two inserts simultaneously.
Is that what you're trying to do?

If so, they need to be separated into two different commands.

Well I was thinking since the tables are different and both the tables need to be filled in the inserts should be simultaneous, however after trying it out it didnt work. Thats why I have posted the code up. Do you reckon i should seperate the commands, and then after find a link for the Details ID's?. Thanks

Yes, they should be separated.
The one with the constraint goes last.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.