HELP with SQL and strings

[wizardnet]

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?

[sotoasty]

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

something like...

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

[wizardnet]

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?

[javajawa]

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.

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

[wizardnet]

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!

[javajawa]

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

test.html?a="

it gets converted to

test.html?a=%22

Therefore, what you should do in your code is

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

[wizardnet]

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

[dglienna]

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

have to translate from VB6

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