Cynthia
I am assuming you are using vba I am a little rusty having been playing
around with MySQL 4&5 for the last 2 years and I dont have time to check all
of this so bear that caveat in mind
The vb part could be to set up a query string by concatenation or to go
directly to the stored procedure (sproc) . I will lay out both methods here
with the concat adhoc query method:
dim str as string, i as integer
If you do that at module level, you can build the string as you select line
numbers
sub resetline() ' use this to reset counters and strings - connect to a new
query
or call when you press your command button
str="Select * from table where "
i=0
end sub
then simply add a line each time one is selected as follows
sub selectline(line) 'or you can use an event of your listbox - use this
when a line is selected
if 1=0
str=+ " linenumber=" &line
i=i+1
else
str= str & " or linenumber=" &line
i=i+1
end if
end sub
Assuming you are doing it in Access you will need to set up an
adodb.connection object and set properties to your connection object, i.e.,
the connection string, cursor location, etc. I suggest the oledb driver for
SQL Server rather than odbc as it is made for sql server and as such is
muuuuch quicker.
Once your line is built, you can execute the query against a connection to
return an ado recordset.
sub cmd_onclick
dim rd as adodb.command, rc as adodb.connection, ap as adodb.parameter
set rc = new adodb.connection
'if using sql server 2000 use this
rc.connectionsting="Provider='sqloledb';Data Source='SQL-01';" & _
"Initial Catalog='PRJ_2005008';Integrated Security='SSPI';"
'if using SQL2005 you can use the native provider as follows:
"Provider=SQLNCLI;Server=SQL-01;Database=PRJ_2005008;Integrated
Security=SSPI;DataTypeCompatibility=80" 'the last parameter is necessary for
ado
compatibility
the command object is used for sprocs especially where you dont need a
recordset returned so if using a sproc with a defined number of parameters
then you can use the following in the reset subroutine after declaring the
command at module level:
set ad=new adodb.command
ad.commandtext="spocname"
ad.commandtype=adcmdstoredproc
or if using the query string I set up earlier you could use:
set ad=new adodb.command
ad.commandtext=str
ad.commandtype=adcmdtext
setting the parameters takes the form
set ap=ad.createparameter(Name, Type, Direction, Size, Value)
'you can add as many parameters as in your sproc and then open your
connection. Once you have opened your connection you can make it the active
connection of the comand
rc.open
ad.activeconnection=rc
to simply excecute a query that returns no data you can use:
ad.execute
or if requesting a recordest:
dim recset as adodb.recordset
set recset = ad.execute
dont forget to close the objects and set their refences to nothing or you
will develop memory leaks in your apps that will gobble up memory
so recset.close
set recset=nothing
if not (rc is nothing) then 'I prefer this construct
rc.close
set rc=nothing
end if
if not (rd is nothing) then set rd=nothing
If creating adhoc queries at the client or a business object you lose the
speed advantage of a precompiled sproc which is a good to have if there are a
high number of users running that particular query.
I must say to the observers that a little knowledge is a dangerous thing ( I
really fear for you!)
And it seems that I have done cynthia's homework / assignment for her...
....but at least I know that lecturers read the newsgroups too
Good luck
Anthony Case aka antointhe
