CODEWITHSUNDEEP
vb.netvisual-studiosqlcommand
user3185519
user3185519

Reputation: 29

prevent insert repeated data record(3column as 1 record) -vb.net

I am doing an insert function which allow an admin to create time slot for classroom booking purpose. The column would be room,time and date. How if i want to prevent repeated data insert?

for example: room 1,1pm, 12 july 2014 can insert twice. what kind of query can i include to prevent it? Here is my code:

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    con.ConnectionString = 
    con.Open()
    cmd.Connection = con

    cmd.CommandText = "INSERT INTO [Room] ([room],[time],[date]) VALUES('" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & DateTimePicker1.Text & "' )"
    cmd.ExecuteNonQuery()
    MsgBox("Classroom and time is opened for student to book!")
    con.Close()

Upvotes: 1

Views: 1039

Answers (1)

Steve
Steve

Reputation: 216313

You could use a MERGE T-SQL command query like this. (Sql Server 2008)

cmd.CommandText = "MERGE INTO Room as Target " & _
      "USING (SELECT @rm as r, @tm as t, @dt as d) as Source " & _
      "ON Target.Room = Source.r AND Target.[time] = Source.t AND Target.[date] = Source.d " & _
      "WHEN NOT MATCHED THEN " & _
      "INSERT ([room],[time],[date]) VALUES (@rm, @tm, @dt);"

This query search the Room table for an exact match between your inputs and a record already present. If there is no match it executes the inserts. If there is a match nothing is done.

As you can see, this query doesn't use string concatenation as your example above. So you need to change also your code to use a parameterized query

cmd.Parameters.AddWithValue("@rm", ComboBox1.Text)
cmd.Parameters.AddWithValue("@tm", ComboBox2.Text)
cmd.Parameters.AddWithValue("@dt", DateTimePicker1.Text)
Dim rowInserted = cmd.ExecuteNonQuery()
If rowInserted > 0 Then
   MessageBox.Show("Row inserted")
else
   MessageBox.Show("Already booked")
End If

But this approach leads to another question.
Are you sure that your fields Time and Date (I suggest to change these names) are NVARCHAR or other text type?.
They seems to be DateTime and so you need to pass a parameter with the appropriate date type.

Upvotes: 1

Related Questions