How To DO iT ?

[ameyanatu]

Hello Friends
I am newbie To Vb.net. i am building an vb.net 2005 application with ms-access 2000 as back end for practice.
i just want to know that suppose user entering new data to the database if this record already exist then user should know that how can i do that i m giving my add button code to you all

If TextBox1.Text = "" Then
            MessageBox.Show("Please Write Values In Text Boxes proper")
        ElseIf TextBox2.Text = "" And TextBox3.Text = "" And TextBox4.Text = "" And TextBox5.Text = "" Then
            MessageBox.Show("Please Write Values In Text Boxes proper")
        ElseIf TextBox2.Text.IndexOfAny("0123456789") = -1 And TextBox3.Text.IndexOfAny("0123456789") = -1 And TextBox4.Text.IndexOfAny("0123456789") = -1 Then
            MessageBox.Show("Please Write Numeric Expression")
        Else

            Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ameya\WindowsApplication2\portfolio.mdb")
            Mycn.Open()
            SQLstr = String.Format("INSERT INTO p1 VALUES('{0}','{1}','{2}','{3}','{4}')", TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text)
            Command = New OleDbCommand(SQLstr, Mycn)
            Command.ExecuteNonQuery()
            MessageBox.Show("New Record Added To your Portfolio")
            Mycn.Close()
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
            TextBox4.Text = ""
            TextBox5.Text = ""
        End If
    End Sub

the first value that is script_name that is my primary key of the table. Please Guys Help me
Thanks In advance!!!

[Oxiegen]

First. If you are using a Primary Key in Access 2000, then you don't need to provide that in your INSERT statement.
Access 2000 automatically adds a new record with a new Primary Key value.
That makes all the records in the database unique.

Second. There is probably a very clever way of doing what you ask for.
But this is how I would do it.

Create a method that takes any number of arguments that you need to identify a record as unique and has a return value type of Boolean.
Read the database from this method, and if the record exists, as defined by the supplied arguments, then the method should return true.

Private Function RecordExists(ByVal value1 As String, ByVal value2 As String....) As Boolean
            Dim bolReturnValue As Boolean = False
            Dim reader As OleDbDataReader
            Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ameya\WindowsApplication2\portfolio.mdb")
            Mycn.Open()
            SQLstr = String.Format("SELECT * FROM p1 WHERE col1 = '{0}' AND col2 = '{1}'", value1, value2)
            Command = New OleDbCommand(SQLstr, Mycn)
            reader = Command.ExecuteReader(CommandBehavior.CloseConnection)
            If reader.HasRows Then
                        bolReturnValue = True
            End If
            reader.Close()

            return bolReturnValue
End Function

Call this method from your Add button event before you execute the code for storing a new record. If Not RecordExists(TextBox1.Text, TextBox2.Text) Then 'Code for storing

[ameyanatu]

First. If you are using a Primary Key in Access 2000, then you don't need to provide that in your INSERT statement.
Access 2000 automatically adds a new record with a new Primary Key value.
That makes all the records in the database unique.

Second. There is probably a very clever way of doing what you ask for.
But this is how I would do it.

Create a method that takes any number of arguments that you need to identify a record as unique and has a return value type of Boolean.
Read the database from this method, and if the record exists, as defined by the supplied arguments, then the method should return true.

Private Function RecordExists(ByVal value1 As String, ByVal value2 As String....) As Boolean
            Dim bolReturnValue As Boolean = False
            Dim reader As OleDbDataReader
            Mycn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ameya\WindowsApplication2\portfolio.mdb")
            Mycn.Open()
            SQLstr = String.Format("SELECT * FROM p1 WHERE col1 = '{0}' AND col2 = '{1}'", value1, value2)
            Command = New OleDbCommand(SQLstr, Mycn)
            reader = Command.ExecuteReader(CommandBehavior.CloseConnection)
            If reader.HasRows Then
                        bolReturnValue = True
            End If
            reader.Close()

            return bolReturnValue
End Function

Call this method from your Add button event before you execute the code for storing a new record. If Not RecordExists(TextBox1.Text, TextBox2.Text) Then 'Code for storing

Thank You iT's Work Thanks a lot.........