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
 
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.
 
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.
 
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?
 
Back
Top