0

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...

2
Contributors
2
Replies
4
Views
6 Years
Discussion Span
Last Post by cyberdaemon
1

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.