String Appending Problem.

[lucyv]

I have a class that records information on websites that a user visits (URL, Start Time and Ending Time). I then have a sub procedure that inserts this information into database.

The problem I am having is that when I'm building the SQL statement the string variable I am using is not being populated correctly.

Dim pstrSQL As String
pstrSQL = "INSERT INTO tblWebsites (fldVisitID, fldWebsiteURL, fldWebsiteStartTime, fldWebsiteEndTime) VALUES (" & mlngVisitID & ", '" & aWebsite.URL, & "', '" & aWebsite.StartTime & "', '" & aWebsite.EndTime & "')"

When I look at what's in the pstrSQL variable I find this:
INSERT INTO tblWebsites (fldVisitID, fldWebsiteURL, fldWebsiteStartTime, fldWebsiteEndTime) VALUES (100, 'http:\\

http://www.google

This is the error message I get:
Incorrect systax near 'http:\\

http://www.google.com'

Unclosed quotation mark before the character string 'http:\\www.google.com'

I think I've narrowed down the problem to the aWebsite.URL statement. If I leave out this property and pass an empty string instead, the SQL statement is built correctly. But when I include the URL, the string doesn't build correctly.

I know I could do this using a stored procedure, but I'm wondering why this simply string concatenation isn't working. I've also tried using a StringBuilder variable as well.

Does anyone know why this could be happening?

-lucyv

 

[JohnYingling]

& aWebsite.URL,
Take off the comma

http://www.moldyoldy.com/VBComparedemo

- free online Compare/Diff of snippets

 

[ThatRickGuy]

I would highly, neigh, EXTREMELY recommend using SQLParameters to do that. Expecially if your getting data from the web. search the forum for 'SQL parameter' there have been many examples and descriptions. Orelse, Chrissie and E&F will probrably be able to post a good code sample. Me, I'm taking Monday off! Ha!

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[lucyv]

John: sorry, but the comma (,) was a typo into this post. I do not have one in my code.

Rick: I've thought about using SQLParameters, and I'm using them a few time in other parts of the program. I was just wondering why this simple operation is not working. I consider myself pretty good at SQL, especially when it comes to writing simple INSERT & UPDATE statements. I just don't know why this is not working.

Does anyone else have an explaination as to why this is happening?

-lucyv

 

[earthandfire]

lucy, I've tried this and no problems:

Public Class Class1WebSite

  Public URL As String
  Public StartTime As DateTime
  Public EndTime As DateTime

End Class

  Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

    Dim mlngVisitID As Integer = 100
    Dim aWebSite As New Class1WebSite
    aWebSite.URL = "http:\\[URL unfurl="true"]www.google.com"[/URL]
    aWebSite.StartTime = Now
    aWebSite.EndTime = aWebSite.StartTime.AddHours(1)

    Dim pstrSQL As String
    pstrSQL = "INSERT INTO tblWebsites (fldVisitID, fldWebsiteURL, fldWebsiteStartTime, fldWebsiteEndTime) VALUES (" & mlngVisitID & ", '" & aWebSite.URL & "', '" & aWebSite.StartTime & "', '" & aWebSite.EndTime & "')"


    MessageBox.Show(pstrSQL)

  End Sub

The output of the MessageBox is:

"INSERT INTO tblWebsites (fldVisitID, fldWebsiteURL, fldWebsiteStartTime, fldWebsiteEndTime) VALUES (100, 'http:\\

http://www.google.com',

'29/08/2005 12:48:03', '29/08/2005 13:48:03')"

which is pretty much what you would expect to see.


However, when I tried replacing the & with a + I got a number of Option Strict errors.

Personally, I would use the + and:
mlngVisitID.ToString
aWebSite.StartTime.ToString
aWebSite.EndTime.ToString

I'm not sure whether that is relevant to this problem or not, but I can't see anything else that could be wrong.

As Rick says, parameters are the better approach, but string concatenation should work.

Hope this helps.

 

[ThatRickGuy]

Yeah, just imagine if you went to the web site:

http://www.SQLInjection.com/index?');DELETE

FROM tblWebsites WHERE 1=1;

depending on how the SQL is executed, all sorts of bad stuff can be injected.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chiph]

If you simply must use string concatenation (and you shouldn't, but if you must...) then the StringBuilder class would be a better way to go. From this:

pstrSQL = "INSERT INTO tblWebsites (fldVisitID, fldWebsiteURL, fldWebsiteStartTime, fldWebsiteEndTime) VALUES (" & mlngVisitID & ", '" & aWebSite.URL & "', '" & aWebSite.StartTime & "', '" & aWebSite.EndTime & "')"

To this:

Dim sb As New StringBuilder()
sb.Append(" INSERT INTO tblWebsites")
sb.Append("  (fldVisitID, fldWebsiteURL,")
sb.Append("   fldWebsiteStartTime, fldWebsiteEndTime)")
sb.Append(" VALUES (")
sb.Append(mlngVisitID.ToString).Append(", ")
sb.Append("'").Append(aWebSite.URL).Append("', ")
sb.Append("'").Append(aWebSite.StartTime.ToString()).Append("', ")
sb.Append("'").Append(aWebSite.EndTime).Append("')")
pstrSQL = sb.ToString()

This makes the code more maintainable, plus reduces the number of intermediate string constants which get created (and must be destroyed by the garbage collector).

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first