locked
New on asp.net try to add data to Access Database Help.... RRS feed

  • Question

  • User-659728574 posted

    Hi im very new on asp.net im trying to insert data from a textbox to a database using this code adn works the problem is how can i chek if a duplication exist try very differents options but im very new to this.

     Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

     

     

     Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=|DataDirectory|NOVABRO1.accdb"

     Dim SqlString As String = "Insert Into NEWRECEIVE (COMPANY_NAME, WO, RECEIVED_FROM, SHIPTO,DATE_RECEIVED, PO, BLN, SHIP_VIA) Values (?,?,?,?,?,?,?,?)"

      Using conn As New OleDbConnection(ConnString)

        Using cmd As New OleDbCommand(SqlString, conn)

                    cmd.CommandType =CommandType.Text

                    cmd.Parameters.AddWithValue("COMPANY_NAME", CompanyTextBox.Text)

                    cmd.Parameters.AddWithValue("WO", MainWOTextBox.Text)

                    cmd.Parameters.AddWithValue("RECEIVED_FROM", ReceivedTextBox.Text)

                    cmd.Parameters.AddWithValue("SHIPTO", ShipToTextBox.Text)

                    cmd.Parameters.AddWithValue("DATE_RECEIVED", DateTextBox.Text)

                    cmd.Parameters.AddWithValue("PO", POTextBox.Text)

                    cmd.Parameters.AddWithValue("BLN", BLNTextBox.Text)

                    cmd.Parameters.AddWithValue("SHIP_VIA", ShipTextBox.Text)

                    conn.Open()

                    cmd.ExecuteNonQuery()

                End Using

           End Using

    Wednesday, October 30, 2013 12:04 PM

Answers

  • User177399542 posted

    Hi you can do like this:

    Before your insert statement check for duplicay like this

    Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
     
    
     
    
     Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=|DataDirectory|NOVABRO1.accdb"
    
    //===== Here you can check for duplicacy by using query like this
    SELECT COUNT(*) FROM NEWRECEIVE WHERE company_name=ValueFromTextBox 
    
    if the count is greater than 0 means there is already an company_name in the database else you can proceed with your insert statement
    
    //===== Write If Else condition here
    If status = 0 Then
    
     Dim SqlString As String = "Insert Into NEWRECEIVE (COMPANY_NAME, WO, RECEIVED_FROM, SHIPTO,DATE_RECEIVED, PO, BLN, SHIP_VIA) Values (?,?,?,?,?,?,?,?)"
    
      Using conn As New OleDbConnection(ConnString)
    
        Using cmd As New OleDbCommand(SqlString, conn)
    
                    cmd.CommandType =CommandType.Text
    
                    cmd.Parameters.AddWithValue("COMPANY_NAME", CompanyTextBox.Text)
    
                    cmd.Parameters.AddWithValue("WO", MainWOTextBox.Text)
    
                    cmd.Parameters.AddWithValue("RECEIVED_FROM", ReceivedTextBox.Text)
    
                    cmd.Parameters.AddWithValue("SHIPTO", ShipToTextBox.Text)
    
                    cmd.Parameters.AddWithValue("DATE_RECEIVED", DateTextBox.Text)
    
                    cmd.Parameters.AddWithValue("PO", POTextBox.Text)
    
                    cmd.Parameters.AddWithValue("BLN", BLNTextBox.Text)
    
                    cmd.Parameters.AddWithValue("SHIP_VIA", ShipTextBox.Text)
    
                    conn.Open()
    
                    cmd.ExecuteNonQuery()
    
                End Using
    
           End Using
    Else
    //=========== Show user message that same company name already exists.
    End If



    Now if table contains similar company name then this query will returns values greater than 0.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 31, 2013 2:34 AM
  • User-659728574 posted

    thanks for your help.

    Having issues with code the Select Count should be like this. And i need to check by WO  instead company

    Dim sqlDuplication = "SELECT COUNT(*) FROM NEWRECEIVE WHERE WO=ValueFromTextBox"

     

    if sqlDuplication = 0

     

    my code

     

    Thanks for your help again

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 1, 2013 2:01 PM

All replies

  • User177399542 posted

    Hi you can do like this:

    Before your insert statement check for duplicay like this

    Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
     
    
     
    
     Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=|DataDirectory|NOVABRO1.accdb"
    
    //===== Here you can check for duplicacy by using query like this
    SELECT COUNT(*) FROM NEWRECEIVE WHERE company_name=ValueFromTextBox 
    
    if the count is greater than 0 means there is already an company_name in the database else you can proceed with your insert statement
    
    //===== Write If Else condition here
    If status = 0 Then
    
     Dim SqlString As String = "Insert Into NEWRECEIVE (COMPANY_NAME, WO, RECEIVED_FROM, SHIPTO,DATE_RECEIVED, PO, BLN, SHIP_VIA) Values (?,?,?,?,?,?,?,?)"
    
      Using conn As New OleDbConnection(ConnString)
    
        Using cmd As New OleDbCommand(SqlString, conn)
    
                    cmd.CommandType =CommandType.Text
    
                    cmd.Parameters.AddWithValue("COMPANY_NAME", CompanyTextBox.Text)
    
                    cmd.Parameters.AddWithValue("WO", MainWOTextBox.Text)
    
                    cmd.Parameters.AddWithValue("RECEIVED_FROM", ReceivedTextBox.Text)
    
                    cmd.Parameters.AddWithValue("SHIPTO", ShipToTextBox.Text)
    
                    cmd.Parameters.AddWithValue("DATE_RECEIVED", DateTextBox.Text)
    
                    cmd.Parameters.AddWithValue("PO", POTextBox.Text)
    
                    cmd.Parameters.AddWithValue("BLN", BLNTextBox.Text)
    
                    cmd.Parameters.AddWithValue("SHIP_VIA", ShipTextBox.Text)
    
                    conn.Open()
    
                    cmd.ExecuteNonQuery()
    
                End Using
    
           End Using
    Else
    //=========== Show user message that same company name already exists.
    End If



    Now if table contains similar company name then this query will returns values greater than 0.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 31, 2013 2:34 AM
  • User-659728574 posted

    thanks for your help.

    Having issues with code the Select Count should be like this. And i need to check by WO  instead company

    Dim sqlDuplication = "SELECT COUNT(*) FROM NEWRECEIVE WHERE WO=ValueFromTextBox"

     

    if sqlDuplication = 0

     

    my code

     

    Thanks for your help again

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 1, 2013 2:01 PM