formatting a string

I have the following code, which generates insert queries

For Each f As String In Directory.GetFiles(d)
    objSQLStringBuilder.Append("insert into table1 (full_path, file_name) values ('" & f.Replace("'", "''") & "', '" & f.Remove(0, Len(d) + 1).Replace("'", "''") & "');")
Next

However, the paths which it finds are formatted as follows

c:\program files开发者_开发技巧\microsoft office\winword.exe

I need to format the paths as follows

file:///c:/program%20files/microosoft%20office/winword.exe

How can I modify the above code to do this?

As m.edmondson pointed out, you're much better off using command parameters.

Here is the basic idea:

sql = "INSERT INTO TABLE1 (full_path, file_Name) values (@full_path, @file_name)";

param = new SqlParameter("@full_path", varchar, 255);
param.Value = fullPath;

//add param for file name

command.Parameters.Add("@full_path");

command.ExecuteNotQuery(sql);

Don't write your SQL in this way if at all possible - try and use a SqlCommand object with parameters. That helps in two ways:

• takes care of the quote / space escaping etc
• helps guard against SQL injection attacks

I don't understand how the query is related to your question. Seems more like a distraction to me.

At any rate, you can use s.Replace(" ", "%20").

You can also use HttpUtility.UrlEncode(s) but that will encode characters other than just the spaces.

You can convert it to that format by writing new Uri(path).AbsoluteUri.

As everyone else mentioned, use parameters!

I think this is as simple as continuing what you were already doing with string.Replace(string, string) calls:

   For Each f As String In Directory.GetFiles(d)
        objSQLStringBuilder.Append("insert into intranet.dbo.noticeboard (full_path, file_name) values ('" & "file:///" + f.Replace("'", "''").Replace(" ", "%20").Replace("\", "/") & "', '" & f.Remove(0, Len(d) + 1).Replace("'", "''") & "');")
    Next

Also, that is a bad way to write SQL as others have mentioned.