how to cast a string value if there is ' in it?

[waely]

hi,
I'm trying to pass a value to a sql query and it heppened to have ' in it and sql is complaining. I casted to string type but still does. here is my sql string:

---------------------------------
dim proname as string
proname = cStr(sheet1.cbox1.value)

SELECT *
FROM tblEmpl
Where ProjName = '" & proname & "'
----------------------------------

this works perfect if there is no ' in the value passed.
FYI. I'm using VBA with Sql server

thanks for any sugetsions

[techgnome]

read through this thread: http://vbforums.com/showthread.php?t=408670 see if it helps you any.

-tg

[waely]

not very helpful. I tried all kind of cencatenation and didn't work :-(

thanks

[techgnome]

DON'T USE concatenation... that's what that thread was all about... avoiding concatenation and using parameterized queries instead.... in SQL the ' is a string delimiter... so when it appears in your text, the SQL thinks that the string has ended and expects what follows to be more SQL stuf, which it isn't....

-tg

[waely]

I'm using vba where the example uses vb.net. I'm not sure how to translate that into vba !!! any

thanks tg

[Al42]

I'm not sure if VBA has the Replace function. If it does, you can

yourString = Replace(yourString, "'", "''")

[waely]

that will not work Al42 because I'm not inserting but selecting besed on a condition and the value that I'm comparing to has ' in it.

thanks anyways,

[si_the_geek]

I'm using vba where the example uses vb.net. I'm not sure how to translate that into vba !!!

Erm.. that was not VB.Net, it was just VB.

that will not work Al42 because I'm not inserting but selecting besed on a condition and the value that I'm comparing to has ' in it.

It will work. If I change the variable name for you, does that help?

proname = Replace(proname , "'", "''")

[waely]

Thank you si_the_geek. I trie that also but I still get an error that says:
Incorrect Syntax near 's'.

here is what I have:
proname = "Crohn's Disease"
proname = Replace(proname , " ' ", " ' ' ")
strsql = "SELECT * from tblProj where ProjectName = ' " & proname & " ' " "

this will work just fine if my proname value doesn't have ' in it.

thank you,
waely

[si_the_geek]

Do you actually have those spaces in your code (you shouldnt), or did you just add them for "effect" here?

[waely]

Do you actually have those spaces in your code (you shouldnt), or did you just add them for "effect" here?

I put them there for clarity only. one thing I don't understand. if I'm replacing the ' with '' in the Replace funtion so my proname becomes Crohn''s desease in this case and even if my code works I will not get anything back becasue the name of my project is with one ' .

thanks

[GaryMazzone]

There is a problem in the SQL string:

strsql = "SELECT * from tblProj where ProjectName = ' " & proname & " ' " "

There is an extra double qoute in there at the end. It should be:

strsql = "SELECT * from tblProj where ProjectName = ' " & proname & " ' "

[waely]

Thanks Gary. but this is not the issue. it's actually type there was a group by statement that I removed after the where clause and forgot to remove the ".

thank you the error is still " Syntax Error near 's' "

waely

[Pradeep1210]

Are you replacing it with double quotes or 2 single quotes? (It should be 2 single quotes!)

[GaryMazzone]

What is the actual SQL string being passed?

[waely]

What is the actual SQL string being passed?

it's 6 post's up. Crohn's disease

[GaryMazzone]

What is the value that is in the varable? Not how you are coding it but what is actually in it.

[waely]

What is the value that is in the varable? Not how you are coding it but what is actually in it.

the value is "crohn's disease" as I mentioned above

[GaryMazzone]

That is not what I asked. What is the complete and full string that is being passed to the database. Not what the variable contains that you are looking for.

[waely]

here is the whole block of code that is contains my sql string
-------------------------------------------------------------------
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim strconn As String
strconn = "PROVIDER=SQLOLEDB;"
strconn = strconn & "Server=mysrvname;database=mydb;uid=user; password=pass;"
conn.Open strconn
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim proname As String
proname = Replace(Sheet1.cbox1.Value, " ' ", " ' ' ") ' get my first combobox value
With rs
.ActiveConnection = conn
.Open "SELECT * from tblProj where ProjectName = ' " & proname & " ' "
Sheet1.Range("B13").CopyFromRecordset rsclinlog
.Close
End With
conn.Close
Set rs = Nothing
Set cn = Nothing
----------------------------------------------------------------------------

hope this what you're looking for.

[GaryMazzone]

No that is not it. Place a debug.print strSQL statement in the code after the SQL string is built and run it. This will place the SQL statement that is actually being built and passed in the degub window and then copy and paste that back here.

[techgnome]

If I may make a suggestion (again)....

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmdSQL as ADODB.Command
Set conn = New ADODB.Connection
Dim strconn As String
strconn = "PROVIDER=SQLOLEDB;"
strconn = strconn & "Server=mysrvname;database=mydb;uid=user; password=pass;"
conn.Open strconn
 
Set cmdSQL = New ADODB.Command
cmdSQL.CommandText = "SELECT * from tblProj where ProjectName = @ProjName"
cmdSQL.CommandType = adCommandText
 
cmdSQL.Parameters.Append cmdSQL.CreateParameter("@ProjName", adVarChar, adParameterInput, 255, Sheet1.cbox1.Value
 
cmdSQL.ActiveConnection = conn
 
Set rs = cmdSQL.Execute
Set rs.ActiveConnection = Nothing
 
conn.Close
Set rs = Nothing 'Why are you geting RS then setting it to nothing here?
Set cn = Nothing

-tg

[waely]

If I may make a suggestion (again)....

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmdSQL as ADODB.Command
Set conn = New ADODB.Connection
Dim strconn As String
strconn = "PROVIDER=SQLOLEDB;"
strconn = strconn & "Server=mysrvname;database=mydb;uid=user; password=pass;"
conn.Open strconn
 
Set cmdSQL = New ADODB.Command
cmdSQL.CommandText = "SELECT * from tblProj where ProjectName = @ProjName"
cmdSQL.CommandType = adCommandText
 
cmdSQL.Parameters.Append cmdSQL.CreateParameter("@ProjName", adVarChar, adParameterInput, 255, Sheet1.cbox1.Value
 
cmdSQL.ActiveConnection = conn
 
Set rs = cmdSQL.Execute
Set rs.ActiveConnection = Nothing
 
conn.Close
Set rs = Nothing 'Why are you geting RS then setting it to nothing here?
Set cn = Nothing

-tg

Thanks Tg. I get an error saying that I must delcare the variable @ProjName when it tries to execute the command.

thank you