Hi Guys need your help on this :(

Basically I am developing a web crawler so once the html source is converted to plaintext in a textbox, i intent to save it in dB.

But the dB seems to be having trouble excepting some characters into the
table and throwing up errors like shown below where database 'our' does
not exist and it seems to be picking up random db names when i try to
save source from different pages.

Please advice as to how I could avoid this?

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

Here is my code for store procedure:

Dim con As New SqlConnection("Data Source=KJSINGH-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

Since you are working with web pages and you don't know what kind of characters you'll be storing in your db, it's wrong to use the "whatever = '" & something.text &"'" as the something.text might have single quotes (') in it. Something like this would break your select.
Use quotename SQL function to pass the content to SQL. Quotename will easily handle signle quotes so that the statement will remain in 1 piece.

Since you are working with web pages and you don't know what kind of characters you'll be storing in your db, it's wrong to use the "whatever = '" & something.text &"'" as the something.text might have single quotes (') in it. Something like this would break your select.
Use quotename SQL function to pass the content to SQL. Quotename will easily handle signle quotes so that the statement will remain in 1 piece.

Thanks for the reply adam_k :) Perhaps could you please show me an example please or partially modify my code please.

Went over to MSDN, to get the article about quotename, but turns out that quotename function supports only 128 bits, so that's not going to do for you.
I suppose you should try the approach in this article: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Sorry.

Thanks for the reply mate!

Well I resorted back to my previous SQL statement which now inserts any sort(links that I have tested)

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

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

        MsgBox("New record added to the database!")

But now, how can I add to the above to check what data I have and then throw an
error saying that the data already exists?

I tried using the count method but somehow kept throwing errors.

I resolved the problem by creating a unique index for the field I wanted
and then put an error exception control in my code. It works now :D

Thanks for the input adam :)

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.