June 25, 2009 at 11:40 am
strM=O'Gorman,Cine'
If InStr(strM, "'") > 0 Then
strM = Replace(strM, "'", "''")
End If
it did not work on this case.
INSERT INTO qryAttendList ( Name ) SELECT RehiredStatusMailingList.NAME FROM RehiredStatusMailingList WHERE ( RehiredStatusMailingList.MANAGER='O'Gorman,Cine' or RehiredStatusMailingList.SUPERVISOR='O'Gorman,Cine' ) AND RehiredStatusMailingList.ATTEND=True
Thanks.
June 26, 2009 at 4:44 am
This is Visual Basic, I dont' understand what is your problem.
Can you provide more information?
What error are you getting?
Where does the error happen? Visual Basic or SQL Server?
-- Gianluca Sartori
June 26, 2009 at 4:49 am
ok - so you are using either dynamic sql (a bad thing) or you are building your sql string in your app(a bad thing)
i can't tell which from the post
but i would advise
create proc usp_somrandomprocname @name varchar(100)
as
INSERT INTO qryAttendList ( Name ) SELECT RehiredStatusMailingList.NAME FROM RehiredStatusMailingList WHERE ( RehiredStatusMailingList.MANAGER=@name or RehiredStatusMailingList.SUPERVISOR=@name) AND RehiredStatusMailingList.ATTEND=True
Go
then you call the proc from your code..... this is what procs are designed for (amongst other things) and it seems like what you are attempting to do is prevent a legitimate charachter from entering the system because best practice is not being followed...
use the easy fix - stored procs and no dynamic sql
MVDBA
June 26, 2009 at 6:10 am
Ok, I think I got it from Mike's reply. Your question is a bit hard to understand indeed.
You can work with a stored procedure as Mike suggested, or build a parametric sql to use in adodb.command. This should avoid problems with unattended characters and sql injection:
Set cmd = new ADODB.Command
Set cmd.ActiveConnection = myConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = _
"INSERT INTO qryAttendList ( Name ) " & _
"SELECT RehiredStatusMailingList.NAME " & _
"FROM RehiredStatusMailingList " & _
"WHERE ( RehiredStatusMailingList.MANAGER= ? or RehiredStatusMailingList.SUPERVISOR= ?) " & _
"AND RehiredStatusMailingList.ATTEND=True "
cmd.parameters.Append cmd.CreateParameter("name", adVarChar, adParamInput, 50, strName)
cmd.Execute
Hope this helps
Gianluca
-- Gianluca Sartori
June 26, 2009 at 6:20 am
Thank you for your time. It is in MS ACCESS. I have combo box which is working fine if the combo text is just text without special character. One person's name is O'Gorman,Cine. Then the application threw me a run time error 3075 in MS ACCESS . it is syntax error -missing operator in the query express. so.. I do not think it would have the sp for those. Thx.
Dim strM As String
Me.cmbManager.SetFocus
strM = Me.cmbManager.Text
strM=O'Gorman,Cine'
If InStr(strM, "'") > 0 Then
strM = Replace(strM, "'", "''")
End If
Dim strA As String
strD = "delete from qryOPandOSNotAttendList "
CurrentDb.Execute (strD)
'rehired
strA = "INSERT INTO qryOPandOSNotAttendList ( Name )SELECT RehiredStatusMailingList.NAME FROM RehiredStatusMailingList " _
& " WHERE ( RehiredStatusMailingList.STATUS ='OS') and (RehiredStatusMailingList.SUPERVISOR='" & strM & "' or RehiredStatusMailingList.MANAGER='" & strM & "');"
CurrentDb.Execute (strA)
June 26, 2009 at 7:04 am
I tried what mark suggestion ,
something like : put the chr etc. it did not work.
strM = "(RehiredStatusMailingList.SUPERVISOR= " & Chr(34) & strM & Chr(34) & "' or RehiredStatusMailingList.MANAGER=" & Chr(34) & strM & Chr(34) & ");"
June 26, 2009 at 7:08 am
Did you try with ADODB.Command as I suggested?
-- Gianluca Sartori
June 26, 2009 at 7:23 am
as I stated before. it is in MS ACCESS. all the code there is using dao.
while I used this. myConnection is not defined.
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = myConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = _
"INSERT INTO qryAttendList ( Name ) " & _
"SELECT RehiredStatusMailingList.NAME " & _
"FROM RehiredStatusMailingList " & _
"WHERE ( RehiredStatusMailingList.MANAGER= ? or RehiredStatusMailingList.SUPERVISOR= ?) " & _
"AND RehiredStatusMailingList.ATTEND=True "
cmd.Parameters.Append cmd.CreateParameter("name", adVarChar, adParamInput, 50, strName)
cmd.Execute
June 26, 2009 at 7:47 am
you say it's access - is it an ADP (access data project)? or a standard access file.
if you convert to an adp file you can use stored procs to get around this issue
MVDBA
June 26, 2009 at 7:48 am
or why not convert it to SQL express??????
MVDBA
June 26, 2009 at 7:49 am
You can use ADO or DAO in Access. It only depends on the references you add to the Tools-->References menu in the VBA menu bar.
See this for more info: http://msdn.microsoft.com/en-us/library/aa164825(office.10).aspx?ppud=4
MyConnection was supposed to be your ADO connection, that you can retrieve with:
Set MyConnection = CurrentProject.Connection
As a side note, I understood you were working with Access even before you put it bold and underline...
-- Gianluca Sartori
June 26, 2009 at 8:17 am
the application has been around for long time and it used by the nuns and those are just part of the functionality of the application-enhancement I do not want to devote more time to learn adp and educate the end users if I do not have to.
I changed to this. Have data come out. I would double check to see whether it is OK. Thx.
Set MyConnection = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = MyConnection
cmd.CommandType = adCmdText
cmd.CommandText = _
"INSERT INTO qryAttendList ( Name ) " & _
"SELECT RehiredStatusMailingList.NAME " & _
"FROM RehiredStatusMailingList " & _
"WHERE ( RehiredStatusMailingList.MANAGER= ? or RehiredStatusMailingList.SUPERVISOR= ?) " & _
"AND RehiredStatusMailingList.ATTEND=True "
cmd.Parameters.Append cmd.CreateParameter("name", adVarChar, adParamInput, 50, strM)
cmd.Parameters.Append cmd.CreateParameter("name", adVarChar, adParamInput, 50, strM)
cmd.Execute cmd.CommandText
thx. It worked prefectly.:-D:-D:-D:-D:-D:-D
have a nice weekend.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply