Results 1 to 8 of 8

Thread: How to INSERT INTO two tables?

  1. #1
    amargagaminghd is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Mar 2017
    Posts
    3
    Reputation
    0

    How to INSERT INTO two tables?

    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

  2. #2
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,801
    Reputation
    1705
    Quote Originally Posted by amargagaminghd View Post
    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.

  3. #3
    amargagaminghd is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Mar 2017
    Posts
    3
    Reputation
    0
    When I pressed the Add button it says "System.Data.OleDb.OleDbExecption Syntax error in INSERT INTO statement"

  4. #4
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,801
    Reputation
    1705
    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.

  5. #5
    JuggaloBrotha's Avatar
    JuggaloBrotha is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Jun 2004
    Location
    Lansing, MI; USA
    Posts
    4,468
    Reputation
    1015
    Why does your insert statement have a from table and inner join?
    Currently using: VS 2010 Ultimate on Win7 Ultimate x64.

  6. #6
    amargagaminghd is offline VB.NET Forum Newbie
    .NET Framework
    .NET 4.5
    Join Date
    Mar 2017
    Posts
    3
    Reputation
    0
    To Insert two tables

  7. #7
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,801
    Reputation
    1705
    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?

  8. #8
    JuggaloBrotha's Avatar
    JuggaloBrotha is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Jun 2004
    Location
    Lansing, MI; USA
    Posts
    4,468
    Reputation
    1015
    Quote Originally Posted by amargagaminghd View Post
    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.
    Currently using: VS 2010 Ultimate on Win7 Ultimate x64.

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •