none
add a new row if two tables are linked RRS feed

  • Question

  • iwant to add a new row if two tables are linked one to one

    conect code

               Dim cmd As OleDbCommand = New OleDbCommand("SELECT tb1.ID_mail ,tb1.clien_mail, tb1.model_mail, tb2.probl_acce,tb2.main_acce,tb2.typ_acce  FROM tb1, tb2 where tb1.ID_mail and tb2.ID_acce", con)
               con.Open()
               DataAdapter1 = New OleDbDataAdapter(cmd)
               Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(DataAdapter1)
               DataSet1 = New DataSet()
               DataAdapter1.Fill(DataSet1, "tb1,tb2")
               DataGridView1.DataSource = DataSet1
               DataGridView1.DataMember = "tb1,tb2"
               con.Close()

    add code

    User added code and error occurs

    Label2.Text = (DataSet1.Tables("tb1").Compute("Max(ID_mail)", "ID_mail >= 0") + "1")
           Dim con As New OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" &
    
     Application.StartupPath & "\web_database.accdb;Jet OLEDB:Database Password=12345")
           Dim cmd As New OleDbCommand("insert into tb1 (ID_mail,clien_mail,model_mail) 
    
    values(@ID_mail,@clien_mail,@model_mail,)", con)
           cmd.Parameters.AddWithValue("@ID_mail", Label2.Text)
           cmd.Parameters.AddWithValue("@clien_mail", TextBox2.Text)
           cmd.Parameters.AddWithValue("@model_mail", TextBox3.Text)
           con.Open()
           cmd.ExecuteNonQuery()
           cmd = New OleDbCommand("insert into tb2 (ID_acce,probl_acce,main_acce,typ_acce)
    
     values(@ID_acce,@probl_acce,@main_acce,@typ_acce)", con)
           cmd.Parameters.AddWithValue("@ID_acce", Label2.Text)
           cmd.Parameters.AddWithValue("@probl_acce", TextBox4.Text)
           cmd.Parameters.AddWithValue("@main_acce", TextBox5.Text)
           cmd.Parameters.AddWithValue("@typ_acce", TextBox6.Text)
           con.Open()
           cmd.ExecuteNonQuery()
           con.Close()
    
    



    • Edited by ahmeddc Friday, December 22, 2017 12:26 AM
    Friday, December 22, 2017 12:25 AM

Answers

  • Hello,

    Read all of the following or go to the image below for at least one error.

    Indicating there was an error and not telling us is not wise, always provide more details rather than less.

    Here is a pattern to follow where I use a class to house data operations (well here just the add new record). Note it is robust with exception handling and also returns on success the new primary key. 

    Don't be concerned I'm passing hard coded values to the insert method, you can pass information any way you want so long as it compiles with Option Strict On (first line in your form and any other files or set it under Visual Studio options)

    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New Operations
            Dim id As Integer = ops.InsertNewRecord("ABC Inc", "Karen Payne")
            If ops.HasException Then
                MessageBox.Show($"Failed: {ops.LastException.Message}")
            Else
                MessageBox.Show($"new id: {id}")
            End If
        End Sub
    End Class
    Public Class Operations
        Private mHasException As Boolean
        ''' <summary>
        ''' Indicates if an operation raised an exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Private mException As Exception
        ''' <summary>
        ''' Exception raised in last method
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastException As Exception
            Get
                Return mException
            End Get
        End Property
        ''' <summary>
        ''' Here the database is in the app folder, adjust DataSource to point
        ''' to your database and don't use Application.StartupPath, use Application.StartupPath
        ''' instead and when you need to create a path use IO.Path.Combine rather than string
        ''' concatenation.
        ''' </summary>
        Private Builder As New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb"),
                    .PersistSecurityInfo = True
                }
        Public Sub New()
            Builder.Add("Jet OLEDB:Database Password", "password")
        End Sub
        Public Function InsertNewRecord(ByVal pCompanyName As String, ByVal pContactName As String) As Integer
    
            Dim Affected As Integer = 0
            Dim newIdentifier As Integer = 0
    
            Dim Statement As String = "INSERT INTO Customer (CompanyName,ContactName) " &
                                       "Values(@CompanyName,@ContactName)"
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
    
                Using cmd As New OleDbCommand(Statement, cn)
    
                    cmd.Parameters.AddWithValue("@CompanyName", pCompanyName)
                    cmd.Parameters.AddWithValue("@ContactName", pContactName)
    
                    Try
                        cn.Open()
    
                        ' insert new row
                        Affected = cmd.ExecuteNonQuery()
    
                        If Affected = 1 Then
                            ' one was inserted so get the primary key
                            cmd.CommandText = "Select @@Identity"
                            newIdentifier = CInt(cmd.ExecuteScalar)
                        End If
                    Catch ex As Exception
                        mHasException = True
                        mException = ex
                    End Try
    
                End Using
    
            End Using
    
            Return newIdentifier
    
        End Function
    End Class
    

    Here is a problem with your code a trailing comma


    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

    • Marked as answer by ahmeddc Friday, December 22, 2017 5:42 PM
    Friday, December 22, 2017 2:15 AM
    Moderator
  • Hi ahmeddc,

    There is no error in the first code? Because I have one error in the first code, I must replace "and" using "="

     SELECT tb1.ID_mail , tb1.clien_mail, tb1.model_mail, tb2.probl_acce, tb2.main_acce, tb2.typ_acce  FROM tb1, tb2 where tb1.ID_mail = tb2.ID_acce

    In addition to the mistake  Kareninstructor point out, you don't need to open sqlconnection twice, please delete the last con.open()

    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 ahmeddc Friday, December 22, 2017 5:43 PM
    Friday, December 22, 2017 7:46 AM
    Moderator

All replies

  • Hello,

    Read all of the following or go to the image below for at least one error.

    Indicating there was an error and not telling us is not wise, always provide more details rather than less.

    Here is a pattern to follow where I use a class to house data operations (well here just the add new record). Note it is robust with exception handling and also returns on success the new primary key. 

    Don't be concerned I'm passing hard coded values to the insert method, you can pass information any way you want so long as it compiles with Option Strict On (first line in your form and any other files or set it under Visual Studio options)

    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New Operations
            Dim id As Integer = ops.InsertNewRecord("ABC Inc", "Karen Payne")
            If ops.HasException Then
                MessageBox.Show($"Failed: {ops.LastException.Message}")
            Else
                MessageBox.Show($"new id: {id}")
            End If
        End Sub
    End Class
    Public Class Operations
        Private mHasException As Boolean
        ''' <summary>
        ''' Indicates if an operation raised an exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Private mException As Exception
        ''' <summary>
        ''' Exception raised in last method
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastException As Exception
            Get
                Return mException
            End Get
        End Property
        ''' <summary>
        ''' Here the database is in the app folder, adjust DataSource to point
        ''' to your database and don't use Application.StartupPath, use Application.StartupPath
        ''' instead and when you need to create a path use IO.Path.Combine rather than string
        ''' concatenation.
        ''' </summary>
        Private Builder As New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb"),
                    .PersistSecurityInfo = True
                }
        Public Sub New()
            Builder.Add("Jet OLEDB:Database Password", "password")
        End Sub
        Public Function InsertNewRecord(ByVal pCompanyName As String, ByVal pContactName As String) As Integer
    
            Dim Affected As Integer = 0
            Dim newIdentifier As Integer = 0
    
            Dim Statement As String = "INSERT INTO Customer (CompanyName,ContactName) " &
                                       "Values(@CompanyName,@ContactName)"
    
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
    
                Using cmd As New OleDbCommand(Statement, cn)
    
                    cmd.Parameters.AddWithValue("@CompanyName", pCompanyName)
                    cmd.Parameters.AddWithValue("@ContactName", pContactName)
    
                    Try
                        cn.Open()
    
                        ' insert new row
                        Affected = cmd.ExecuteNonQuery()
    
                        If Affected = 1 Then
                            ' one was inserted so get the primary key
                            cmd.CommandText = "Select @@Identity"
                            newIdentifier = CInt(cmd.ExecuteScalar)
                        End If
                    Catch ex As Exception
                        mHasException = True
                        mException = ex
                    End Try
    
                End Using
    
            End Using
    
            Return newIdentifier
    
        End Function
    End Class
    

    Here is a problem with your code a trailing comma


    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

    • Marked as answer by ahmeddc Friday, December 22, 2017 5:42 PM
    Friday, December 22, 2017 2:15 AM
    Moderator
  • Hi ahmeddc,

    There is no error in the first code? Because I have one error in the first code, I must replace "and" using "="

     SELECT tb1.ID_mail , tb1.clien_mail, tb1.model_mail, tb2.probl_acce, tb2.main_acce, tb2.typ_acce  FROM tb1, tb2 where tb1.ID_mail = tb2.ID_acce

    In addition to the mistake  Kareninstructor point out, you don't need to open sqlconnection twice, please delete the last con.open()

    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 ahmeddc Friday, December 22, 2017 5:43 PM
    Friday, December 22, 2017 7:46 AM
    Moderator
  • I made the necessary adjustments

    I want a way to find out the bigger number id and add
    The problem is now here

      Label2.Text = (DataSet1.Tables("tb1").Compute("Max(ID_mail)", "ID_mail >= 0") + "1")

    Friday, December 22, 2017 11:21 AM
  • Here you go, result is 46

    Dim DataSet1 As New DataSet
    Dim dt As New DataTable With {.TableName = "tb1"}
    DataSet1.Tables.Add(dt)
    dt.Columns.Add(New DataColumn With {.ColumnName = "ID_mail", .DataType = GetType(Integer)})
    dt.Rows.Add(New Object() {10})
    dt.Rows.Add(New Object() {5})
    dt.Rows.Add(New Object() {45})
    dt.Rows.Add(New Object() {11})
    
    Dim maxValue As Integer = DataSet1.Tables("tb1").AsEnumerable.Max(Function(row) row.Field(Of Integer)("Id_Mail"))
    Console.WriteLine(maxValue + 1)

    Apply maxValue to a label Label2.Text = (maxValue +1).ToString

    Also note sure why you need the filter for Compute e.g.

    Dim maxValue = CInt(DataSet1.Tables("tb1").Compute("Max(ID_mail)", Nothing))
    Label2.Text = (maxValue + 1).ToString


    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



    Friday, December 22, 2017 12:27 PM
    Moderator
  • Normally your database would not run if you use this as the place where it residence.

    Application.StartupPath


    Success
    Cor

    Friday, December 22, 2017 3:00 PM