Good day,

i have a problem regarding about adding a record to my database, i want to insert a record that not exist on my table, the record that i want to add to a field is not related to other table. This field should be related to other table which is related by 1-M relation.


I Just dont want to duplicate a record of this field from the table
here's my codes

con.ConnectionString = "sqlserver path"
        con.Open()

        cmd.Connection = con
        cmd.CommandText = "Select subj_code from tbl_curriculum where subj_code = '" & txtScode.Text & "'"
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        If dr.HasRows Then

            MessageBox.Show("Subject Code already exist")

        Else
            'con.Open()
            cmd.Connection = con

            cmd.CommandText = "Insert into tbl_curriculum (course_code,subj_code) values ('" & txtCode.Text & "','" & txtScode.Text & "')"
            cmd.ExecuteNonQuery() 'There is already an open DataReader associated with this Command which must be closed
                                  
            MessageBox.Show("One record has been added")
        End If
        dr.Close() 
        con.Close()

thanks happy coding to all...

You can change the original cmd.CommandText text so that you incorporate both the insert and the select query using the EXISTS condition.
Like so:

cmd.CommandText = "INSERT INTO tbl_curriculum (course_code,subj_code) VALUES ('" & txtCode.Text & "','" & txtScode.Text & "') WHERE NOT EXISTS (SELECT subj_code FROM tbl_curriculum WHERE subj_code = '" & txtScode.Text & "')"

This will both save you some coding and also get rid of that IF statement.

commented: effective solution. +9

@Oxiegen thanks.. it helps me a lot...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.