converting a query to sp?

Question

converting a query to sp?

noname-210388

Old Hand

Points: 331
More actions
August 20, 2005 at 4:03 am

#167954

i have a query that runs on my vb6 program.
for example.
S="SELECT * FROM tblUsers WHERE field1=1"
if combo1.listindex>-1 then
s = s & " AND field2=" & combo1.text
end if
if combo2.listindex>-1 then
s = s & " AND field3=" & combo2.text
end if
if combo3.listindex>-1 then
s = s & " AND field4=" & combo3.text
end if
etc...
how can i convert it to sp?
and how can i call this query from my app (vb6 or .net) i prefer vb.net.
thanx all

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

are field2, field3 etc...columns from your tblUsers - are you trying to update these ?! If you are...then you should create a stored procedure and pass the field values to the procedure....not sure what "s = s" is...however, I can give you the code for calling the procedure from vb (sorry - am still not quite familiar with vb.net syntax) and you can tweak it till you get it right....

Private Sub GetFields(intField1 as integer)
Dim objCmd As ADODB.Command
Dim rstFields As New ADODB.Recordset

Set objCmd = New ADODB.Command
Set objCmd.ActiveConnection = MyConnection
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "myProc"
objCmd.Parameters(1) = intField1

Set rstFields = objCmd.Execute

If Not rstFields.EOF Then
    if combo1.listindex > -1 then
s = s 
rstFields!Field2 = combo1.text
    end if
    if combo2.listindex > -1 then
s = s 
rstFields!Field3 = combo2.text
    end if.....etc..........
End If

Set objCmd = Nothing
rstFields.Close
Set rstFields = Nothing

End Sub

and your stored procedure would really be as simple as:

CREATE PROCEDURE myProc
@Field1 Int
As

SELECT Field1, Field2, Field3, Field4....FROM tblUsers WHERE Field1 = @Field1

GO

**ASCII stupid question, get a stupid ANSI !!!**

noname-210388 Old Hand Points: 331 More actions · Answer 2

you misunderstood me.

i want to convert this query to a stored procedure.

S="SELECT * FROM tblUsers WHERE field1=1"

if combo1.listindex>-1 then

s = s & " AND field2=" & combo1.text

end if

if combo2.listindex>-1 then

s = s & " AND field3=" & combo2.text

end if

if combo3.listindex>-1 then

s = s & " AND field4=" & combo3.text

end if

i just send parameter field2,field3,field4. but these parameters doesnt exist always. i mean the sp must be dynamic. if you look at the query above, the query can be more than one.

like these:

S="SELECT * FROM tblUsers WHERE field1=1 AND field2= & combo2.text

or

S="SELECT * FROM tblUsers WHERE field1=1 AND field2=" & combo2.text & " AND field3=" & combo3.text

or

S="SELECT * FROM tblUsers WHERE field1=1 AND field2=" & combo2.text & " AND field3=" & combo3.text & " AND field4=" & combo4.text

or etc....

there are many variations.

i want a dynamic stored proc. when i send the parameters, the sp will take a shape.

i hope i am clear.

thanx...

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

you misunderstood me.......quite completely I'm afraid..

to redeem myself though, I have a link for you where something (almost) identical was discussed a few days ago...

identical issue

**ASCII stupid question, get a stupid ANSI !!!**