HELP with SQL and strings

Hi.

I have string what codec in UTF-8.
I using in =System.Text.Encoding.UTF8.GetString(bytes)
and save it to file.

now I loaded this file into normal string, but I see this string in "add watch", and it's show me this char: " (chr(34)) in text-window.

so when I append this to SQL string, I see in "add-watch" this char " before my string.

and DB is rejected this sql string.

how to fix it?

You will need to show us your SQL code, but usually I use parameters.

something like...

Code:

---

            UpdateCommand.CommandTimeout = 10
            UpdateCommand.CommandText = "UPDATE Table SET AddressLink=?AL WHERE ID=1;"
            UpdateCommand.Parameters.Clear()
            UpdateCommand.AddWithValue("?AL", NewAddressList)
            UpdateCommand.ExecuteNonQuery()

---

Thanks for you post,

but I do like that:

strSQL="UPDATE Table SET AddressLink=" & strUTF8 & " WHERE ID=1;"

and strUFT8 in "Add-watch" look like that:

"<style></style>Wellcome to my html

In the head of this string appear this char: "

I think that is mean: "this string is utf8 format"

This error number: -2147217900
This error description:
Unclosed quotation mark before the character string '<style></style><p dir=LTR style='text-align:left;direction:ltr;unicode-bidi:
embed'>]df<span lang=HE dir=R.........


how I get it work in SQL?

Well, if you're putting a string in like that, what's going to happen if someone puts a comma in it!?!?!?!
What's happening is that the SQL is finding a quote (' or ") in the text, and is asuming that's ths start/end of the string.
It's best to go with the parameters method, but if you don't, at least put your strings into quotes.
i.e.

Code:

---

"UPDATE Table SET AddressLink=" & Chr(34) & Replace(strUTF8, Chr(34), "\" & Chr(34)) & Chr(34) & " WHERE ID=1;"


so, if
  strUTF8 = "To Be, Or Not To Be", He Said
You'd get
  UPDATE Table SET AddressLink="\"To Be, Or Not To Be\", He Said" WHERE ID=1;

---

Note that in SQL, putting a \ before a character in a double-quoted (") string 'escapes' that character, so it doesn't terminate the string.

However, you SHOULD use parameters in VB.NET - this sort of solution is really for PHP type languages!

No, I can't remove that char:

strSQL=mid(strSQL, 2)

This is not working!

but:
This:

strSQL=mid(strSQL, 1, strSQL.Legaht -1)

Thats work, and remove this char: "

But the problem is: I need to move it in parameter to webservice function before this action.

it's not getting that, I get 404 bad request error!

If you need to keep the quote, don't remove the quote - just make sure it's escaped in some way. For a web page, if you type

Code:

---

test.html?a="

---

it gets converted to

Code:

---

test.html?a=%22

---

Therefore, what you should do in your code is

Code:

---

strSQL = replace(strSQL, Chr(34), "%22")

---

No, thats not my problem.

My problem is:

I have utf-8 string (using : system.text.encoding.uft8.getstring())

now when I try to inset it to function to Webservice, it's failed.

bResult=webServiceFunc.Insert(strUTF8)
^ it's failed here, before insert return result to 'bResult' ('In insert')


The function is failed, and not enter inside the code, the result is: "The request failed with HTTP status 400: Bad Request"

This is my problem!

Please help me with that

You didn't say which SQL Server, but you can use this with MS SQL Server.

have to translate from VB6

Code:

---

Option Explicit

Private Sub Form_Load()
Dim strsql$, strlength$
Dim strFeet$, strInch$
strFeet = "10"
strInch = "11"
strlength = strFeet & "'" & "x" & strInch & Chr(34)
strsql = "INSERT items values('Board', '" & CleanText(strlength) & "')"
MsgBox strsql
Debug.Print strsql
End Sub

Public Function CleanText(strIn As String) As String
    On Error GoTo VBError
   
    Dim iAcnt As Long
    Dim strString As String
    Dim vLimit As Long
    vLimit = Len(strIn)
    For iAcnt = 1 To vLimit
        Select Case Asc(Mid$(strIn, iAcnt, 1))
        Case 10, 13
            strString = strString & Mid$(strIn, iAcnt, 1)
        Case 124
            strString = strString & "!"
        Case 39
            strString = strString & "''"
        Case 34
            strString = strString & """"
        Case Is < 32
            strString = strString & " "
        Case Is > 126
            strString = strString & " "
        Case Else
            strString = strString & Mid$(strIn, iAcnt, 1)
        End Select
        Next
    CleanText = strString
Exit Function
VBError:
    MsgBox "VBError in Sub Parse_SQL_Text : " & Err.Number & " - " & Err.Description
    Resume Next
End Function

---