RE: ODBC query in VB code Need HELP

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

"Cynthia" wrote:

I have a query that is as follows:
exec usp_setlinenumber
the query properties are as follows:
ODBC;DRIVER={SQL
Server};SERVER=SQL-01;trustedconnection=yes;DATABASE=PRJ_2005008

I am running the query from a command button. I need to be able to pass a
variable for instance if the user selects line numbers 123 444 and 124 I
need the exec usp_setlinenumber to read exec usp_setlinenumber '123,444,124'
so the stored procedure only runs on items selected. Is there a way to do
this all in VB code and not have the query so I can concatenate the exec
line? What I do not know how to do is get the connection set up so it can
find the stored procedure which is the ODBC line in the query properties
above.

Thank you in advance for any help you can give me.
Cynthia
.

Loading