Perameterized Query on webservice?

[Steve Nihan]

I've created the following distributed application:

Solution

EDSQLWSVC EDEmployerWinClient Project3
Web Service winform app asp.net web app


My web services are fine....and I can get data in, out etc. My problem is
this:
I want to use the data adaptors in the web services for the windows client
application. Only problem is...when i want to run a parameterized query in
the client application...I get errors flagging the SQL Statement and the
Parameterization. Big ones. SO...How do I parameterize the query? I've done
it in the Data Adaptor's SQL Statement in the web service, and on the
windows form, I've tried:


Private Sub GetRecs_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles GetRecs.Click
Dim ws As New EDEmployerWinClient.EDEmployerService.EmpService
ws.Credentials = System.Net.CredentialCache.DefaultCredentials
EmpRecData.SelectCommand.Parameters("Param1").Value = Textbox1.Text
EmpRecsDa1.Clear()
EmpRecsDa1.Fill(EmpRecsData)
EmpRecsData.Merge(ws.GetRecs())
End Sub


EDEmployerService is the web reference, and EmpService is the Web Service

All I want it to do is to Select only the records based on the Parameter. In
my SQL Statement, I have "SELECT Tbl_One.* FROM Tbl_One WHERE Email =
@Param1"


This is my first time trying to parameterize a SQL Statement through web
services..so I'm a bit lost. Any ideas where someone's done something
similar?

 

[Saurabh Nandu]

Hi,
Assuming, EmpRecData is your dataset, the following code will not work
because,

EmpRecData.SelectCommand.Parameters("Param1").Value = Textbox1.Text

SelectCommand is a property of DataAdapter and it must be set on a
DataAdapter like

EmpRecsDA1.SelectCommand.Parameters("Param1").Value = Textbox1.Text


Regards
Akila
[MVP|MCAD]