Question How to INSERT INTO two tables?

amargagaminghd

New member
Joined
Mar 8, 2017
Messages
3
Programming Experience
Beginner
I have an errors

Here's my codes

DATABASE USING ACCESS

tblstudents
Student_ID | PK
Student_Fname |Short Text
Student_lname |Short Text
Course_ID |FK


tblCourses
Course_ID |PK
Course_Code |Short Text
Course_Name |Short Text





Public Class frmStudents
Dim sqlcode As String
Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jim Clinton Amarga\Desktop\Class Records\Class Records\bin\Debug\dbClassRecords.accdb"



-------------------------------------------
Sub loaddata()

ListView1.Items.Clear()


Try
sqlcode = "SELECT tblStudents.Student_Fname, tblStudents.Student_Lastname FROM tblStudents"


Dim sqlcmd As New OleDb.OleDbCommand(sqlcode)
sqlcmd.Connection = New OleDb.OleDbConnection(connstring)
sqlcmd.Connection.Open()
sqlcmd.ExecuteNonQuery()
Dim da As New OleDb.OleDbDataAdapter(sqlcmd)
Dim ds As New DataSet
da.Fill(ds, "tblStudents")
Dim itemcollection(100) As String

For r = 0 To ds.Tables("tblStudents").Rows.Count - 1
For c = 0 To ds.Tables("tblStudents").Columns.Count - 1
itemcollection(c) = ds.Tables("tblStudents").Rows(r)(c).ToString
Next
Dim LVI As New ListViewItem(itemcollection)
ListView1.Items.Add(LVI)
Next


Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

Try
sqlcode = "SELECT tblCourses.Course_code, tblCourses.Course_name from tblCourses "


Dim sqlcmd As New OleDb.OleDbCommand(sqlcode)
sqlcmd.Connection = New OleDb.OleDbConnection(connstring)
sqlcmd.Connection.Open()
sqlcmd.ExecuteNonQuery()
Dim da As New OleDb.OleDbDataAdapter(sqlcmd)
Dim ds As New DataSet
da.Fill(ds, "tblCourses")
Dim itemcollection(100) As String

For r = 0 To ds.Tables("tblCourses").Rows.Count - 1
For c = 0 To ds.Tables("tblCourses").Columns.Count - 1
itemcollection(c) = ds.Tables("tblCourses").Rows(r)(c).ToString
Next
Dim LVI As New ListViewItem(itemcollection)
ListView1.Items.Add(LVI)
Next


Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

Private Sub btnStudADD_Click(sender As Object, e As EventArgs) Handles btnStudADD.Click
Try
sqlcode = "INSERT INTO tblStudents(Student_Fname,Student_Lastname,Course_code, Course_name) FROM tblCourses INNER JOIN tblStudents ON tblCourses.Course_Id = tblStudents.Course_ID VALUES ('" & txtFname.Text & "','" & txtLname.Text & "','" & txtSC1.Text & "','" & txtSC2.Text & "')"


Dim sqlcmd As New OleDb.OleDbCommand(sqlcode)
sqlcmd.Connection = New OleDb.OleDbConnection(connstring)
sqlcmd.Connection.Open()
sqlcmd.ExecuteNonQuery()
MsgBox("ADD SUCCESSFULLY")
Call loaddata()
Catch ex As Exception
MessageBox.Show(ex.ToString)


End Try
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,111
Location
Sydney, Australia
Programming Experience
10+
I have an errors
Then tell us where and what. We shouldn't have to waste our time working that out when you can tell us and we can go straight to looking for a reason and a solution.
 

amargagaminghd

New member
Joined
Mar 8, 2017
Messages
3
Programming Experience
Beginner
When I pressed the Add button it says "System.Data.OleDb.OleDbExecption Syntax error in INSERT INTO statement"
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,111
Location
Sydney, Australia
Programming Experience
10+
So, if the issue is a syntax error in your INSERT statement, you really only needed tell us that and show us your INSERT statement. Have you actually looked at what that SQL code contains after you've built it? That should have been the first thing you did. Assuming what you've posted is accurate, I can see a fairly obvious mistake but there may be more. You should examine the contents of that SQL code and make sure that it is what you think it should be.
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,518
Location
Lansing, MI; USA
Programming Experience
10+
Why does your insert statement have a from table and inner join?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,111
Location
Sydney, Australia
Programming Experience
10+
Wow. After JB's post I took another look at your SQL and I can see some rather glaring issues that I didn't notice the first time around as I didn't look closely enough. I think that you need to go back and read some documentation on the SQL INSERT statement and what it supports. Are you trying to insert values directly or to pull them from another table? If the former then why are you specifying FROM other tables and if the latter then why are you specifying VALUES?
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,518
Location
Lansing, MI; USA
Programming Experience
10+
To Insert two tables
So you're trying to put a value into two tables? Or pull data from one table into another table?

As jmc has stated you should read up on sql statements as what you have isn't going to work.
 
Top Bottom