VB.Net ASP Web Service Problem

[Martina]

I have some code in a Visual Basic Web Service and am a bit stuck.

I have this piece of code so that when you type in a first name and last
name it joins them

<WebMethod()> Public Function JoinNames(ByVal FirstName As String, ByVal
LastName As String) As String
JoinNames = FirstName + " " + LastName
End Function

Ok, the rest of my methods are related to a database I have with a table
employees. At the moment you type in the surname and it either brings back
there details or it brings back all of the details under there name,

What I want to be able to do is change the following code so that you type
in the first name and surname, it joins them using the method above, then in
the methods below it uses the output from this method to search the database
and bring back the info by the joined name, not just the surname. In the
database are the columns FirstName and Surname.

IS this possible?


<WebMethod()> Public Function PickOneEmployee(ByVal name As String) As
DataSet
Dim OutputList As New DataSet()
Dim selstring As String
selstring = "SELECT BirthDate, City, EmployeeID, FirstName, LastName
FROM Employees WHERE LastName = """ + name + """"
OleDbSelectCommand1.CommandText = selstring
OleDbDataAdapter1.Fill(OutputList, "Employees")
PickOneEmployee = OutputList
End Function

<WebMethod()> Public Function PickOneEmployeeOrders(ByVal name As String) As
DataSet
Dim selstring As String
Dim empid As Int32 = 0
Dim OutputList As New DataSet()
selstring = "SELECT BirthDate, City, EmployeeID, FirstName, LastName
FROM Employees WHERE LastName = """ + name + """"
OleDbSelectCommand1.CommandText = selstring
OleDbDataAdapter1.Fill(OutputList, "Employees")
empid = CType(OutputList.Tables("Employees").Rows(0).Item("EmployeeID"),
Int32)
selstring = "SELECT * FROM Orders WHERE EmployeeID = " + CStr(empid)
OleDbSelectCommand1.CommandText = selstring
OleDbDataAdapter1.Fill(OutputList, "Orders")
PickOneEmployeeOrders = OutputList
End Function

 

[Cor Ligthert]

Martina,

When I was ready with my message I got the idea what you want to do, what is
in my eyes impossible. You cannot first create a name for a fill and than
use that while that is not in the database not in a webservice and not in
any other program.

I changed as well your code because there are some things which where not
completly right in my opinion. I typed everything in this message so watch
typos.

I have used the parameters which are more adviced than your method.
http://msdn.microsoft.com/library/d...rfsystemdataoledboledbparameterclasstopic.asp

\\\
<WebMethod()> Public Function PickOneEmployee(ByVal name As String) As
DataSet
Dim OutputList As New DataSet()
Dim selstring As String
selstring = "SELECT BirthDate, City, EmployeeID, FirstName, LastName
FROM Employees WHERE FirstName = @Firstname And LastName = @LastName"
OleDbDataAdapter1 = New OleDbDataAdapter(selstring, OleDbConnection1)
OleDbDataAdapter1.SelectCommand.Parameters.Add("@Firstname",
OleDbType.VarChar, 80).Value = "Whatever"
OleDbDataAdapter1.SelectCommand.Parameters.Add("@LastName",
OleDbType.VarChar, 80).Value = "Whatever"
OleDbDataAdapter1.Fill(OutputList, "Employees")
Return OutputList
End Function
///

I hope this helps?

Cor