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"

        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")

            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

thanks happy coding to all...

6 Years
Discussion Span
Last Post by cyberdaemon

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.

Votes + Comments
effective solution.
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.