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

Printable View

Jun 23rd, 2006, 11:44 AM
waely

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

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
Jun 23rd, 2006, 11:56 AM
techgnome

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

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

-tg
Jun 23rd, 2006, 12:47 PM
waely

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

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

thanks
Jun 23rd, 2006, 01:05 PM
techgnome

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

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
Jun 23rd, 2006, 01:25 PM
waely

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

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

thanks tg
Jun 23rd, 2006, 01:33 PM
Al42

Re: how to cast a string value if there is ' in it?
I'm not sure if VBA has the Replace function. If it does, you can
VB Code:
```
yourString = Replace(yourString, "'", "''")
```
Jun 23rd, 2006, 01:58 PM
waely

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

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,
Jun 23rd, 2006, 03:19 PM
si_the_geek

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

Quote:

Originally Posted by waely

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.

Quote:

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?

Code:

proname = Replace(proname , "'", "''")
Jun 26th, 2006, 09:25 AM
waely

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

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
Jun 26th, 2006, 09:28 AM
si_the_geek

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

Do you actually have those spaces in your code (you shouldnt), or did you just add them for "effect" here?
Jun 26th, 2006, 09:44 AM
waely

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

Quote:

Originally Posted by si_the_geek

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
Jun 26th, 2006, 10:07 AM
GaryMazzone

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

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 & " ' "
Jun 26th, 2006, 01:40 PM
waely

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

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
Jun 26th, 2006, 01:48 PM
Pradeep1210

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

Are you replacing it with double quotes or 2 single quotes? (It should be 2 single quotes!)
Jun 26th, 2006, 02:37 PM
GaryMazzone

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

What is the actual SQL string being passed?
Jun 26th, 2006, 02:44 PM
waely

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

Quote:

Originally Posted by GaryMazzone

What is the actual SQL string being passed?

it's 6 post's up. Crohn's disease
Jun 26th, 2006, 03:31 PM
GaryMazzone

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

What is the value that is in the varable? Not how you are coding it but what is actually in it.
Jun 26th, 2006, 04:34 PM
waely

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

Quote:

Originally Posted by GaryMazzone

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
Jun 26th, 2006, 06:53 PM
GaryMazzone

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

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.
Jun 26th, 2006, 08:21 PM
waely

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

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.
Jun 27th, 2006, 08:11 AM
GaryMazzone

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

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.

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

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

VB Code:

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

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

Quote:

Originally Posted by techgnome

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

VB Code:

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