RE: ODBC query in VB code Need HELP

Cynthia

Reading is good, learning is really good but confidence, alas, is better it
seems ;)

Everything for ADO is in the first 2 messages that I gave you. You are
mixing DAO code and ADO code with pseudo code it seems. The way things are
going at Microsoft at the moment I think we could see Office ditching VBA and
utilising VB.NET as its core language. On those terms I would have preferred
you understand the ADO route since ADO is not that different to ADO.Net in
terms of connection settings and object creation / disposal.

However, I liked the way you thought to change the text of your pass-through
query using DAO. Since your pass-through query already exists (including the
connection) you might as well use that query.

No need for the ado recordset object or ado for that matter then. You could
simply try using DAO once you set your querydef string since it already has
its connection set up

dim qd as dao.querydef

set qd=currentdb.querydefs("USP_mypassthru")
dq.sql=strtxt
qd.execute

since you already have the means of dynamically changing the query text I
will not repeat it here.

One of the really good things about Microsoft - they really help you to use
and understand their products. I learned all my stuff (except for database
theory and SQL Server) from books and Microsoft help about 12yrs ago. Take
some time to go through it and I'm sure you'll understand it all.

Best of luck
Anthony Case aka antointhe

"Cynthia" wrote:

I am trying to get a stored procedure to run on info I send it I do not need
any info back. You mentioned using oledb driver instead of odbc I do not
know how to do that.
I played around and got the code below to work using a pass-through query,
(it runs the stored procedure and makes the changes in the database) but I
don't know if it is really the way to go, I also get an error on the rs.close
line saying it is not open??

Dim STRSQL As String
Dim strpass As String
strpass = "462416%"
Dim stdocname As String
Dim cn As Object
Dim rs As Object
Dim strTxt As String
'strpass is the lines the user selected on a list in the form so it would
look something like '1234,2344,2344' I did not past that code in.

strTxt = "EXEC usp_setisogenbom '" & strpass & "'"
Set cn = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
CurrentDb.QueryDefs("USP_mypassthru").SQL = strTxt
stdocname = "Usp_mypassthru"
DoCmd.SetWarnings False
DoCmd.OpenQuery stdocname, acNormal, acEdit
rs.Close
Set rs = Nothing

"antointhe" wrote:

PS - You could use an 'in' clause rather than 'where line =', e.g.,

str="select * from table where line IN ("

for each value you can simply say
if i=0 then
str=str &line

elseif i>0 then
str=str &"," &line

end if

and when running the command button

str=str &")"

I prefer the or beacause although it can be expensive in terms of scans, it
has a greater likelihood of foiling injection techniques.

oh and by the way I meant if i=0 in the first message - thats what happens
when you start coding after 20hrs awake.

g'night

Anthony Case aka antointhe

"antointhe" wrote:

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
.