469,927 Members | 1,866 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Problem returning a datarow

Hi,

I have a client/server app. that uses a windows service for the server and asp.net web pages for the client side. My server class has 3 methods that Fill, Add a new record and Update a record. The Fill and Add routines work as expected but unfortunately the update request falls at the 1st hurdle.

I pass two params to the remote(server) method for the update, one is the unique ID and the other is a string that is the name of the table in the database. See code below. I need the SelectedRow method to return a datarow that will then populate textbox's on another page. When the method is called I get an 'internal system error.....please turn on custom errors in the web.config file on the server for more info.(unfortunately my server is not s web server so I don't have a web.config file!!).

Can anyone see anything obvious.

Cheers. >>

Calling routine:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

System.Threading.Thread.CurrentThread.CurrentCultu re = New CultureInfo("en-GB")

hsc = CType(Activator.GetObject(GetType(IHelpSC), _
"tcp://192.168.2.3:1234/HelpSC"), IHelpSC)

Dim drEdit As DataRow
Dim intRow As Integer = CInt(Request.QueryString("item"))

strDiscipline = Request.QueryString("discipline")
drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method
strRecord = drEdit.Item(0)
txtLogged.Text = drEdit(1)
txtEngineer.Text = drEdit.Item(3)

End Sub

Remote Class Function:

Public Function SelectedRow(ByVal id As Integer, ByVal discipline As String) As System.Data.DataRow Implements IHelpSC.SelectedRow

strDiscipline = Trim(discipline)
Dim cmdSelect As SqlCommand = sqlcnn.CreateCommand
Dim drResult As DataRow
Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=" & id

cmdSelect.CommandType = CommandType.Text
cmdSelect.CommandText = strQuery

sqlda = New SqlDataAdapter
sqlda.SelectCommand = cmdSelect

ds = New DataSet
sqlda.Fill(ds, "Results")
drResult = ds.Tables(0).Rows(0)

Return drResult

End Function

Jul 23 '05 #1
3 1582
Phil (Ph**@nospam.com) writes:
I have a client/server app. that uses a windows service for the server
and asp.net web pages for the client side. My server class has 3 methods
that Fill, Add a new record and Update a record. The Fill and Add
routines work as expected but unfortunately the update request falls at
the 1st hurdle.

I pass two params to the remote(server) method for the update, one is
the unique ID and the other is a string that is the name of the table in
the database. See code below. I need the SelectedRow method to return a
datarow that will then populate textbox's on another page. When the
method is called I get an 'internal system error.....please turn on
custom errors in the web.config file on the server for more
info.(unfortunately my server is not s web server so I don't have a
web.config file!!).
I don't really have an idea, but the error message does not look
like it comes from SQL Server. Maybe you should try an ADO .Net group.
Dim intRow As Integer = CInt(Request.QueryString("item"))

strDiscipline = Request.QueryString("discipline")
drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method

Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=" & id


I don't know what this Request.QueryString implies, but this is any
sorr of user input, you have a major hole here. What if the user
specifies a table that does not exist? What if he specifies
"tbl; DROP DATABASE important; --"? This is called SQL injection,
and is a popular way for intruders to get access to things they should
have access to.

I don't know why you pass the table name as a parameter, but it's
not likely to be good design. For the CallID you should in any case
use a parameter:

Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=@id"
cmdSelect.AddParameter(@id, SqlInt, Id)

(With all reservations for the exact syntax.) Parameterizing your
SQL statements protects you from SQL injection.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Hi Erland,

Thanks for your response. Although we haven't found my problem I will just
comment on your response FWIW :_)

The QueryString property of the HTTPRequest class adds two, lets call them
parameters are passed from the calling page. These params are 'hard-coded'
items in a dropdownlist and selected row from a datagrid. So, I utterly
agree with your concerns regarding SQL injection but 'hopefully' in this
instance I'm ok....!!! The other two method calls to the database do in fact
use parameterised stored procedures (if that absolves me in any way :-).

My problem/puzzlement is that if I run the client app. with the data layer
class (with no changes, ie. still accesses the remote server), it works
perfectly. Just to clarify......the class with the data layer (ie.
interfacing directly with the dB via direct sql calls or parameterised
stored procs) normally resides on the server and the client communicates
with this class using .NET remoting. Just to remember, I have 3 methods. The
Fill method is called when the client page is 1st loaded and populates a
datagrid...this works. I also have a button on the same page as the datagrid
that calls the AddNew method to add a new record to the db, this also works
fine. Finally, the datagrid has a button column that is for edit/update of
the selected record. This is where I receive the error BUT...........it
works if I 'move' the data layer class to the client side and call the
method ....GGGrrrr....it's very frustrating!!

Thanks for your help.

Phil

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Phil (Ph**@nospam.com) writes:
I have a client/server app. that uses a windows service for the server
and asp.net web pages for the client side. My server class has 3 methods
that Fill, Add a new record and Update a record. The Fill and Add
routines work as expected but unfortunately the update request falls at
the 1st hurdle.

I pass two params to the remote(server) method for the update, one is
the unique ID and the other is a string that is the name of the table in
the database. See code below. I need the SelectedRow method to return a
datarow that will then populate textbox's on another page. When the
method is called I get an 'internal system error.....please turn on
custom errors in the web.config file on the server for more
info.(unfortunately my server is not s web server so I don't have a
web.config file!!).


I don't really have an idea, but the error message does not look
like it comes from SQL Server. Maybe you should try an ADO .Net group.
Dim intRow As Integer = CInt(Request.QueryString("item"))

strDiscipline = Request.QueryString("discipline")
drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method

Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=" & id


I don't know what this Request.QueryString implies, but this is any
sorr of user input, you have a major hole here. What if the user
specifies a table that does not exist? What if he specifies
"tbl; DROP DATABASE important; --"? This is called SQL injection,
and is a popular way for intruders to get access to things they should
have access to.

I don't know why you pass the table name as a parameter, but it's
not likely to be good design. For the CallID you should in any case
use a parameter:

Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=@id"
cmdSelect.AddParameter(@id, SqlInt, Id)

(With all reservations for the exact syntax.) Parameterizing your
SQL statements protects you from SQL injection.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #3
Phil (Ph**@nospam.com) writes:
The QueryString property of the HTTPRequest class adds two, lets call
them parameters are passed from the calling page. These params are
'hard-coded' items in a dropdownlist and selected row from a datagrid.
So, I utterly agree with your concerns regarding SQL injection but
'hopefully' in this instance I'm ok....!!!


It it was a Windows Forms client, it would be safe I guess. But you
have a web client, right? Somehow the information on what the user
select must be passed over the network. The obvious case is when the
parameter appears in a URL. But anything which is over a network port
over which an intruder has full control of his end could be susceptible.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.