Checking Record Already exist or not

[Noorul Ariff]

Dear...

I'm doing INVENTORY PROJECT...

I'm using ACCESS as BACK-END...

My tables are PURCHASE and SALES...

In PURCHASE table, fields are CODE(primary key), PRICE, DATE, TOTAL, QTY...

when I'm entering datas in form and when i click submit, the datas stored...

but when i enter same DATA(CODE), it is showing error...

i don't want to show the DEFAULT ERROR message...

so, i want to search whether the CODE is already in database or not...

i.e When i click SUBMIT button, 1st it has to search whether the CODE is already PRESENT in table or not. if it is not there, DATAS have to be stored. otherwise, it has to DISPLAY MESSAGE BOX.

In VB6.0 After connecting to the database,

if RECORDSET!<FIELDNAME>= textbox.text then
msgbox"Already Exist"
end if

But in vb.net i don't know how to do...

Please help me...

Thanks in advance...

[roottybrian]

Well, the process is the same for vb.net. You just use the if..then. Otherwise it's good you have a primary key in the table.
You can as well, just validate your form data properly so that the only way to fail to execute an INSERT query would be incase of a duplicate entry for the primary key. You can then use the try...catch thing to catch an INSERT error and return a "Record exists" error message

[Noorul Ariff]

Dear Friends...

It is working...

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Inventory.mdb;Persist Security Info=False")
                cn.Open()
                str = "SELECT * FROM Purchase WHERE Code =" & CmbCde.Text & ""
                cmd = New OleDbCommand(str, cn)
                dr = cmd.ExecuteReader()
                If Not dr.HasRows Then
                    str = "insert into Purchase values(" & CInt(CmbCde.Text) & "," & CInt(TxtPrce.Text) & ",'" & CmbCtgry.SelectedItem & "','" & Cmbctgry2.SelectedItem & "','" & TxtDate.Text & "'," & CInt(TxtSp.Text) & ",'" & TxtBrnd.Text & "')"
                    cmd = New OleDbCommand(str, cn)
                    icount = cmd.ExecuteNonQuery
                    MessageBox.Show(icount)
                Else
                    MsgBox(" Record exists")
                End If

This is the code...

Hope it helps others...

Thanks...

[mie.ilani]

wanna ask. where should i placed the validation. i mean the code to check whether the record is exist or not

    Public Sub doSave()
        Dim con As New OleDb.OleDbConnection(My.Settings.KK3DB)
        Dim com As New OleDb.OleDbCommand
        Dim adap As New OleDb.OleDbDataAdapter
        Dim ds As New DataSet
        con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\backup project\KK3MailingSystem\KK3MailingSystem\KK3MailingSystem\db\KK3MailingSystem.mdb;Persist Security Info=True")
        con.Open()

        com.CommandText = " INSERT INTO Staff (Staff_Id,Staff_Name, Staff_IC,Staff_Ext,Staff_HP,Staff_Email,Staff_Position,Staff_Username) VALUES ('" + txtId.Text + "','" + txtName.Text + "','" + txtIC.Text + "','" + txtExt.Text + "','" + txtHP.Text + "','" + txtEmail.Text + "','" + cboPosition.Text + "','" + txtId.Text + "')"

        com.Connection = con
        com.ExecuteNonQuery()
        adap = New OleDbDataAdapter("Select * from Staff", con)
        adap.Fill(ds, "1")
        StaffDataGridView.DataSource = ds.Tables("1")
        Me.StaffDataGridView.Rows(Me.StaffDataGridView.RowCount - 2).Selected = True
        con.Close()
    End Sub

[nubie.net]

hello

from your case study, the first I create code for checking data already exist or not..
this code :

Function cek_code(ByVal code As String) As Boolean
        Call main()
        'Con.Open()
        Dim sql As String = "select count(code) from Purchase where code = '" & TextBox1.Text & "'"
        Dim cmd As New MySqlCommand(sql, Con)
        Dim dr As MySqlDataReader
        Dim value As Boolean = False
        Try
            Con.Open()
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While dr.Read
                If dr(0) > 0 Then
                    value = True
                End If
            End While
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "ERROR")
        Finally
            Con.Close()
        End Try
        Return value
    End Function

and then I create code submit transaction :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If cek_code(code.Text) = False Then
            'code insert data
        Else
            MsgBox("duplicate code", MsgBoxStyle.Information, "Informasi")
        End If
    End Sub

happy code :)

[bombils.you]

I'm using vb 2005 and I have the same problem showing that I already have a record in my db...
Here's my code:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If txtSName.Text.Length = 0 Or txtAge.Text.Length = 0 Or _
        txtSection.Text.Length = 0 Or txtGender.Text.Length = 0 Or _
        txtSBirthday.Text.Length = 0 Or txtAddress.Text.Length = 0 Or _
        txtGrLevel.Text.Length = 0 Or txtIDNumber.Text.Length = 0 Or _
        txtPName.Text.Length = 0 Then MessageBox.Show("Please enter all the data")
        If Not IsNumeric(txtAge.Text) Then
            MessageBox.Show("Please enter a numeric age")
            txtAge.Focus()
        ElseIf Not IsNumeric(txtIDNumber.Text) Then
        MessageBox.Show("Please enter your IDNumber")
            txtIDNumber.Focus()
        ElseIf Not IsNumeric(txtGrLevel.Text) Then
            MessageBox.Show("Please enter a numeric Grade Level")
            txtGrLevel.Focus()
        Else
            Dim ainfo As New clsInfo(txtSName.Text, CInt(txtAge.Text), txtSection.Text, txtGender.Text, txtSBirthday.Text, txtAddress.Text, CInt(txtGrLevel.Text), CInt(txtIDNumber.Text), txtPName.Text)
            Try
                ainfo.Add()
                info.Add(ainfo)
                lblNumberRecords.Text = info.Count
                MessageBox.Show("Record added to database.")
                btnClear.PerformClick()
            Catch
                MessageBox.Show("This student = " & txtSName.Text & _
                " already exists in the database." & vbCrLf & _
                "Please enter a new name or Press CLEAR to enter details of a new student.")
                txtSName.Focus()
            End Try
        End If
    End Sub

[Minimalist]

Hey people maybe you check how old the posts are. This original one is 4 years old.

[jeya1562]

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

    'Dim s As Integer

    Dim x As Integer
    Dim dt As New DataTable
    con.Open()


    x = InputBox("Enter Order No")

    cmd = New OleDbCommand("select * from order1 where ordno = " & x & "", con)
    ' If dr.HasRows Then
    MsgBox(con.ToString)



    cmd.ExecuteNonQuery()
    da = New OleDbDataAdapter(cmd)
    da.Fill(dt)



    ReportViewer1.Reset()
    ReportViewer1.LocalReport.DataSources.Clear()
    ReportViewer1.RefreshReport()

    ReportViewer1.LocalReport.ReportPath = "E:\JK\salesorder\WindowsApplication1\WindowsApplication1\Report1.rdlc"

    rds.Name = "DataSet1"
    rds.Value = dt

    ReportViewer1.LocalReport.DataSources.Add(rds)
    ReportViewer1.Visible = True
    Me.ReportViewer1.RefreshReport()
    con.Close()
End Sub

End Class
If there is no record means msgbox "No record" how?

[Hamza_13]

how to do in c# ??

[Steffany]

How do trappings in visual basic

[Steffany]

How do trappings in updating data which in visual basic