none
Help with ve.net set up of RRS feed

  • Question

  • Subject

    Assmt

    Exam

      English

    20

    69

    Math

    19

    70

    Chemistry

    25

    70

     

     

    I have the following lay out on vb.net form, the courses on the Subject column are on labels, the grades under Assmt and exams are in textboxes. How do I insert something like this in access table using parameterized query? I need the subject to appear  I have three tables, students (StudentID,PK) Subjects(SubjectID PK) and Grades table (StundentID, SubjectID) I need to insert the courses to and have well over 30 course. Is my set up incorrect?

    Thanks for your time
    Monday, October 23, 2017 10:47 AM

Answers

  • Hi alobi,

    Based on your description, you have three tables in Access DataBase, one is students, one is Subjects, another is Grades. Now you want to insert some courses into subjects table, and want to use parameterized query, am I right? If yes, you can following the following code to learn how to use parameterized query to insert record into table.

    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\C#\Example(VB)\Example(VB)\Database1.accdb; Persist Security Info=False;"
            Using conn As New OleDbConnection(connectionString)
                Using comm As New OleDbCommand()
                    With comm
                        .Connection = conn
                        .CommandType = CommandType.Text
                        .CommandText = "INSERT INTO [Table] (ColumnID,ColumnName,ColumnSex,ColumnNumber) VALUES(@ColumnID,@ColumnName,@ColumnSex,@ColumnNumber)"
                        .Parameters.AddWithValue("@ColumnID", TextID.Text.ToString)
                        .Parameters.AddWithValue("@ColumnName", TextName.Text.Trim())
                        .Parameters.AddWithValue("@ColumnSex", TextSex.Text.Trim())
                        .Parameters.AddWithValue("@ColumnNumber", TextNumber.Text.Trim())
    
                    End With
                    Try
                        conn.Open()
                        comm.ExecuteNonQuery()
                        conn.Close()
                        MessageBox.Show("Successful")
                    Catch e As Exception
                        MessageBox.Show(e.Message)
                    End Try
                End Using
            End Using
        End Sub

    More detailed info, please take a look:

    https://stackoverflow.com/questions/21961951/inserting-ms-access-row-into-database-using-vb-net

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by alobi Tuesday, October 24, 2017 12:19 PM
    Tuesday, October 24, 2017 5:50 AM
    Moderator

All replies

  • You would be looking at a modified version of the following

    Here are some basics which will not match exactly to your needs in regards to user interface but show how to read and modify data in MS-Access.

    both show obtaining new primary keys which is important when adding new records where most code samples on the web don't show that.

    Down and dirty code sample with one table to work with. Don't let "down and dirty" make you think it's not in any way polished, it's done to clearly show how to work with data.

    This one focuses solely on adding.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, October 23, 2017 11:28 AM
    Moderator
  • Hi alobi,

    Based on your description, you have three tables in Access DataBase, one is students, one is Subjects, another is Grades. Now you want to insert some courses into subjects table, and want to use parameterized query, am I right? If yes, you can following the following code to learn how to use parameterized query to insert record into table.

    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\C#\Example(VB)\Example(VB)\Database1.accdb; Persist Security Info=False;"
            Using conn As New OleDbConnection(connectionString)
                Using comm As New OleDbCommand()
                    With comm
                        .Connection = conn
                        .CommandType = CommandType.Text
                        .CommandText = "INSERT INTO [Table] (ColumnID,ColumnName,ColumnSex,ColumnNumber) VALUES(@ColumnID,@ColumnName,@ColumnSex,@ColumnNumber)"
                        .Parameters.AddWithValue("@ColumnID", TextID.Text.ToString)
                        .Parameters.AddWithValue("@ColumnName", TextName.Text.Trim())
                        .Parameters.AddWithValue("@ColumnSex", TextSex.Text.Trim())
                        .Parameters.AddWithValue("@ColumnNumber", TextNumber.Text.Trim())
    
                    End With
                    Try
                        conn.Open()
                        comm.ExecuteNonQuery()
                        conn.Close()
                        MessageBox.Show("Successful")
                    Catch e As Exception
                        MessageBox.Show(e.Message)
                    End Try
                End Using
            End Using
        End Sub

    More detailed info, please take a look:

    https://stackoverflow.com/questions/21961951/inserting-ms-access-row-into-database-using-vb-net

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by alobi Tuesday, October 24, 2017 12:19 PM
    Tuesday, October 24, 2017 5:50 AM
    Moderator