Prevent dublication of Data in SQL Server 2008

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 3322 Views - Last Post: 17 July 2011 - 12:22 PM Rate Topic: -----

#1 kevin_911   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 130
  • Joined: 02-April 09

Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 11:19 AM

Hi Guys,

I have the following code for SQL insertion, but how can I prevent data dublication
in vb.net so that the same data is not entered twice? I mean can I add a exception
in my code to check if one of the field in the sql server table contains the same
data?

Please advice :)

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim dsNewRow As DataRow
        Dim cmb As SqlCommandBuilder = New SqlCommandBuilder(da)
        cmb.GetUpdateCommand()
        Dim dateToDisplay As DateTime = DateTime.Now

        If tb_URL.Text = Nothing OrElse tb_Parse.Text = Nothing Then
            MessageBox.Show("Please make sure TextBoxes are not empty!", _
                            "Error", MessageBoxButtons.OK, _
                            MessageBoxIcon.Error, _
                            MessageBoxDefaultButton.Button1)
            Exit Sub
        End If

        dsNewRow = ds.Tables("search_result").NewRow

        dsNewRow.Item("URL") = tb_URL.Text
        dsNewRow.Item("page_content") = tb_Parse.Text
        dsNewRow.Item("date_created") = dateToDisplay.ToString 

        ds.Tables("search_result").Rows.Add(dsNewRow)
        da.UpdateCommand = cmb.GetUpdateCommand
        da.Update(ds, "search_result")

        MessageBox.Show("New record added to the database!", _
               "Success", MessageBoxButtons.OK, _
               MessageBoxIcon.Exclamation, _
               MessageBoxDefaultButton.Button1)
    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Prevent dublication of Data in SQL Server 2008

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,698
  • Joined: 12-June 08

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 11:21 AM

Make a select against the database using your key data. If it returns a row count of 1 or greater than you have a duplicate. If not you are good to go!
Was This Post Helpful? 0
  • +
  • -

#3 kevin_911   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 130
  • Joined: 02-April 09

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 11:27 AM

View Postmodi123_1, on 15 July 2011 - 12:21 PM, said:

Make a select against the database using your key data. If it returns a row count of 1 or greater than you have a duplicate. If not you are good to go!


Can you please demonstrate?
Here is my select statement which I carryout in the beganning of my code:
Private da As New SqlDataAdapter("SELECT * FROM search_result", cs)

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,698
  • Joined: 12-June 08

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 11:43 AM

It's a pretty trivial task that is a great learning exercise!

1. determine what columns are your "keys".
2. Make SQL select query with a SELECT COUNT(*) or SELECT COUNT(1) and a where statement that compares the columns against the data you have.

select count(1)
from <table>
where <column1> = <data1> and <column2> = <data2> 



4. if that returns a value other than nothing or something greater than zero then you have a duplciate!
Was This Post Helpful? 0
  • +
  • -

#5 Ionut   User is offline

  • D.I.C Lover
  • member icon

Reputation: 386
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 12:15 PM

Or add to the field a unique constraint
Unique contraint from MSDN
Was This Post Helpful? 0
  • +
  • -

#6 kevin_911   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 130
  • Joined: 02-April 09

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 02:13 PM

View Postmodi123_1, on 15 July 2011 - 12:43 PM, said:

It's a pretty trivial task that is a great learning exercise!

1. determine what columns are your "keys".
2. Make SQL select query with a SELECT COUNT(*) or SELECT COUNT(1) and a where statement that compares the columns against the data you have.

select count(1)
from <table>
where <column1> = <data1> and <column2> = <data2> 



4. if that returns a value other than nothing or something greater than zero then you have a duplciate!


Hmm..thanks for your reply! I understand what you mean about the count.

Basically I have four fields in my dB table and would like to check the fields URL and page_content. If the URL and page_content are the same then do-not enter data.

This post has been edited by kevin_911: 15 July 2011 - 02:14 PM

Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,698
  • Joined: 12-June 08

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 02:20 PM

There you go! Just check the inputted values against the columns in the where statement and you on the way!
Was This Post Helpful? 0
  • +
  • -

#8 kevin_911   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 130
  • Joined: 02-April 09

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 02:51 PM

View Postmodi123_1, on 15 July 2011 - 03:20 PM, said:

There you go! Just check the inputted values against the columns in the where statement and you on the way!


Ok The statement below works but fails in different pages :(

cmd.CommandText = "SELECT * FROM [search_result] WHERE URL = '" & tb_URL.Text & "'AND page_content = '" & tb_Parse.Text & "';"

        'cs.Open()

        cmd.Connection = cs

        da.SelectCommand = cmd
        da.Fill(ds, "0")

        Dim count = ds.Tables(0).Rows.Count

        If count > 0 Then
            MsgBox("Data already exist")
            Exit Sub
        Else
            dsNewRow = ds.Tables("search_result").NewRow

            dsNewRow.Item("URL") = tb_URL.Text
            dsNewRow.Item("page_content") = tb_Parse.Text
            dsNewRow.Item("date_created") = dateToDisplay.ToString '  ToString("MM/dd/yyyy hh:mm:ss")

            ds.Tables("search_result").Rows.Add(dsNewRow)
            da.UpdateCommand = cmb.GetUpdateCommand
            da.Update(ds, "search_result")

            MessageBox.Show("New record added to the database!", _
                   "Success", MessageBoxButtons.OK, _
                   MessageBoxIcon.Exclamation, _
                   MessageBoxDefaultButton.Button1)
        End If


It complains on this
 da.Fill(ds, "0")


With the following error:

Quote

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near 'll'.
Could not locate entry in sysdatabases for database 'our'. No entry found with that name. Make sure that the name is entered correctly.

The thread '<Thread Ended>' (0x1050) has exited with code 0 (0x0).


I am not sure if it is interfering with my other select statement which I have in the begainning of
the code?

 Private cs As New SqlConnection("Data Source=KJSINGH-PC\SQLEXPRESS;Initial Catalog=ForumCrawl;Integrated Security=True")
    Private da As New SqlDataAdapter("SELECT * FROM search_result", cs)
    Private ds As New DataSet

Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,698
  • Joined: 12-June 08

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 02:55 PM

Quote

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near 'll'.
Could not locate entry in sysdatabases for database 'our'. No entry found with that name. Make sure that the name is entered correctly.

The thread '<Thread Ended>' (0x1050) has exited with code 0 (0x0).


Do you have a database called "our"? Put a break point on that first line of your code up there, execute it, and afterwards see what the command text really is. I bet'cha the URL is causing grief with your sql statement.


Debugging
Was This Post Helpful? 0
  • +
  • -

#10 kevin_911   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 130
  • Joined: 02-April 09

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 03:24 PM

View Postmodi123_1, on 15 July 2011 - 03:55 PM, said:

Quote

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near 'll'.
Could not locate entry in sysdatabases for database 'our'. No entry found with that name. Make sure that the name is entered correctly.

The thread '<Thread Ended>' (0x1050) has exited with code 0 (0x0).


Do you have a database called "our"? Put a break point on that first line of your code up there, execute it, and afterwards see what the command text really is. I bet'cha the URL is causing grief with your sql statement.


Debugging


No I dont have a database called 'our' and dont know where it picked that up from :dontgetit:

Well removed the content check code and the insert is working fine?

Dont know if having two select statements is causing it to get confused?

Dim dsNewRow As DataRow
        Dim cmb As SqlCommandBuilder = New SqlCommandBuilder(da)
        Dim cmd As New SqlClient.SqlCommand
        cmb.GetUpdateCommand()
        Dim dateToDisplay As DateTime = DateTime.Now

        If tb_URL.Text = Nothing OrElse tb_Parse.Text = Nothing Then
            MessageBox.Show("Please make sure TextBoxes are not empty!", _
                            "Error", MessageBoxButtons.OK, _
                            MessageBoxIcon.Error, _
                            MessageBoxDefaultButton.Button1)
            Exit Sub
        End If

        'cmd.CommandText = "SELECT COUNT(*) FROM search_result WHERE URL = '" & tb_URL.Text & "'AND page_content = '" & tb_Parse.Text & "';"

        ''cs.Open()

        'cmd.Connection = cs

        'da.SelectCommand = cmd
        'da.Fill(ds, "0")

        'Dim count = ds.Tables(0).Rows.Count

        'If count > 0 Then
        '    MsgBox("Data already exist")
        '    Exit Sub
        'Else
        dsNewRow = ds.Tables("search_result").NewRow

        dsNewRow.Item("URL") = tb_URL.Text
        dsNewRow.Item("page_content") = tb_Parse.Text
        dsNewRow.Item("date_created") = dateToDisplay.ToString '  ToString("MM/dd/yyyy hh:mm:ss")

        ds.Tables("search_result").Rows.Add(dsNewRow)
        da.UpdateCommand = cmb.GetUpdateCommand
        da.Update(ds, "search_result")

        MessageBox.Show("New record added to the database!", _
               "Success", MessageBoxButtons.OK, _
               MessageBoxIcon.Exclamation, _
               MessageBoxDefaultButton.Button1)
        'End If

    End Sub

Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,698
  • Joined: 12-June 08

Re: Prevent dublication of Data in SQL Server 2008

Posted 15 July 2011 - 06:39 PM

No.. honestly read that link to debugging I had posted. Try it out in your code. Good money is on that your url is screwing with the SQL statement. Go find out and see!
Was This Post Helpful? 0
  • +
  • -

#12 kevin_911   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 130
  • Joined: 02-April 09

Re: Prevent dublication of Data in SQL Server 2008

Posted 16 July 2011 - 10:23 AM

View Postmodi123_1, on 15 July 2011 - 07:39 PM, said:

No.. honestly read that link to debugging I had posted. Try it out in your code. Good money is on that your url is screwing with the SQL statement. Go find out and see!


I know how to debug ;) on how to step in/out of code etc......I did test that particular sub and it was selecting that 'select' statement for the count but for some reason was failing at

Quote

da.Fill(ds, "0")
.

Anyhow I think I am gonna re-design my sub again step by step and see if it work :D

But what makes you think it might be failing on the URL?

This post has been edited by kevin_911: 16 July 2011 - 10:24 AM

Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,698
  • Joined: 12-June 08

Re: Prevent dublication of Data in SQL Server 2008

Posted 16 July 2011 - 10:26 AM

I think it is failing there because if you are getting funky table names that don't exist the first place TO look is in your SQL string. URLs have funky characters that can throw off a sql statement unless properly taken care of .
Was This Post Helpful? 0
  • +
  • -

#14 kevin_911   User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 130
  • Joined: 02-April 09

Re: Prevent dublication of Data in SQL Server 2008

Posted 16 July 2011 - 03:59 PM

I am getting the same error again despite redesigning the query :censored:
Running out of ideas now?!?! I think 'modi123_1' you are correct about the URL. What should I do?

Nowhere on my HDD is there a dB called 'our' dont exactly know where its getting that from?

  Dim con As New SqlConnection("Data Source=PC\SQLEXPRESS;Initial Catalog=ForumCrawl;Integrated Security=True")
        Dim com As SqlCommand = Nothing
        Dim queryResult As Integer
        Dim dateToDisplay As DateTime = DateTime.Now

        con.Open()
        com = New SqlCommand("SELECT COUNT(*) FROM [search_result] WHERE URL = '" & tb_URL.Text & "' AND page_content = '" & tb_Parse.Text & "'", con)
        queryResult = com.ExecuteScalar()
        con.Close()

        If queryResult = 0 Then
            con.Open()
            com = New SqlCommand("INSERT INTO [search_result] (URL, page_content, date_created) VALUES ('" & tb_URL.Text & "','" & tb_Parse.Text & "', '" & dateToDisplay.ToString("yyyy-MM-dd hh:mm:ss") & "')", con)
            com.ExecuteNonQuery()
            con.Close()
        Else
            MsgBox("Data alread exists")
        End If



Quote

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near 'll'.
Could not locate entry in sysdatabases for database 'our'. No entry found with that name. Make sure that the name is entered correctly.

The thread '<Thread Ended>' (0x1050) has exited with code 0 (0x0).



EDIT Ok! I think I might know the problem (i think) basically I am downloading page source into database which only contains plaintext and no HTML. So whatever page I visit it gives me different database name errors?!? like above was 'our' on a different page it is 'modify'. And these texts exist in the page source.

What should I do to avoid this?

This post has been edited by kevin_911: 16 July 2011 - 04:20 PM

Was This Post Helpful? 0
  • +
  • -

#15 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,698
  • Joined: 12-June 08

Re: Prevent dublication of Data in SQL Server 2008

Posted 16 July 2011 - 06:24 PM

Well you figure out what your database of choice has for key characters and replace incoming character X with modified character Y so the whole thing is still text to the database.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2