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 FunctionPhil (Phil@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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" <esquel@.sommarskog.se> wrote in message
news:Xns964E55D31DC2Yazorman@.127.0.0.1...
> Phil (Phil@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Phil (Phil@.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, esquel@.sommarskog.se

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

Problem print report from vb app

we have a vb app that displays reports yet when you try and print the report it either doesn't do so (although it appears to queue it) or it prints but with info missing and control info in odd spots. Anyone have any clues as to how to resolve this? My reports do me no good if the developers can't make the apps access them via the applications they are developing.Do you use Report Viewer control to display reports?

problem on running replication on pocket pc

hi i tried to view a replication on a pocket pc app that i created. and i used the following code

private void Sync()
{
public string AppPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
SqlCeReplication repl = new SqlCeReplication();

repl.InternetUrl = @."http://naomi/sqlmobile/sqlcesa30.dll";
repl.Publisher = @."Naomi";
repl.PublisherDatabase = @."SQLMobile";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @."sa";
repl.PublisherPassword = @."<...>";
repl.Publication = @."SQLMobile";
repl.Subscriber = @."SQLMobile";
repl.SubscriberConnectionString = @."Data Source=""" + AppPath + @."\SqlMobile.sdf"";Max Database Size=128;Default Lock Escalation =100;";
try
{
repl.AddSubscription(AddOption.ExistingDatabase);
repl.Synchronize();
}
catch (SqlCeException e)
{
MessageBox.Show(e.ToString());
}

when i run it i keep on getting the error:
The SQL Mobile Subscription already exists. Publisher, PublisherDatabase, and Publication for this subscription should be different from any existing subscription.

However, i can still view the contents of the datagrid that refers to the database subscription.

1. What should i do in order to stop getting the error above?
2. If i edit the contents of the datagrid in the pocket pc app how will i update the replication in the sql server? thanks

With the line of code "repl.AddSubscription(AddOption.ExistingDatabase)" you are effectively trying to add a new subscription to your local SQL Mobile database every time your synchronize. You only need to perform the AddSubscription the first time you synchronize with the server.

What I typically do is use AddOption.CreateDatabase, which dynamically creates the SQL Mobile database (if it does not already exist) the first time synchronization occurs.

-Darren

Problem migrating from sql7 to sql2000 - connection failure

We have an inhouse app written in Delphi which connects to a local server
running SQL7. When we migrated the database to a server running SQL2000 we
ran into problems.
When a user uses the Delphi app to browse data, it seems ok, but when the
user insert records, the app responded "Connection failure".
In the application log of the event viewer, we noticed the following:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
I did a search on support.microsoft.com for this message. It seems there is
some information regarding this on some complicated cause which will be
addressed in service pack 4.
I wonder should I wait for service pack 4 to solve it, or is there something
I can do?LL
From Erland
The message indicates a protocol error in the dialog between client and
server. There are several possible causes for this. The most likely
causes are 1) network problems 2) bug in the client library.
"LL" <LL@.discussions.microsoft.com> wrote in message
news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> We have an inhouse app written in Delphi which connects to a local server
> running SQL7. When we migrated the database to a server running SQL2000 we
> ran into problems.
> When a user uses the Delphi app to browse data, it seems ok, but when the
> user insert records, the app responded "Connection failure".
> In the application log of the event viewer, we noticed the following:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> I did a search on support.microsoft.com for this message. It seems there
is
> some information regarding this on some complicated cause which will be
> addressed in service pack 4.
> I wonder should I wait for service pack 4 to solve it, or is there
something
> I can do?|||I dont know how it relates to network problem, because the error condition
only happens when we use a user account to insert/change data via the app.
The only thing that i can say is that the sql 2000 database resides on the
domain controller server, whereas, before the sql 7 database resides on a
workgroup server. i will need to follow up with the programmer to see if
there exist some hardcoded machine name inside the app that causes the
failure.
As for 'bug in client library', i dont know why a failure only happens
during write. but it sounds like i can only try to get the programmer to
check the app or see if there are other compatible versions of delphi that
works with sql2000.
thanks for your reply anyways...
"Uri Dimant" wrote:
> LL
> From Erland
> The message indicates a protocol error in the dialog between client and
> server. There are several possible causes for this. The most likely
> causes are 1) network problems 2) bug in the client library.
>
> "LL" <LL@.discussions.microsoft.com> wrote in message
> news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> > We have an inhouse app written in Delphi which connects to a local server
> > running SQL7. When we migrated the database to a server running SQL2000 we
> > ran into problems.
> >
> > When a user uses the Delphi app to browse data, it seems ok, but when the
> > user insert records, the app responded "Connection failure".
> >
> > In the application log of the event viewer, we noticed the following:
> >
> > Error: 17805, Severity: 20, State: 3
> > Invalid buffer received from client.
> >
> > I did a search on support.microsoft.com for this message. It seems there
> is
> > some information regarding this on some complicated cause which will be
> > addressed in service pack 4.
> >
> > I wonder should I wait for service pack 4 to solve it, or is there
> something
> > I can do?
>
>|||Dear LL :
I also got this error when using delphi 7 to update or changing the
record. My database is also migrating from sql7 to sql2k, please send me
some information if you can solve this problem , tkx so much
George

Problem migrating from sql7 to sql2000 - connection failure

We have an inhouse app written in Delphi which connects to a local server
running SQL7. When we migrated the database to a server running SQL2000 we
ran into problems.
When a user uses the Delphi app to browse data, it seems ok, but when the
user insert records, the app responded "Connection failure".
In the application log of the event viewer, we noticed the following:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
I did a search on support.microsoft.com for this message. It seems there is
some information regarding this on some complicated cause which will be
addressed in service pack 4.
I wonder should I wait for service pack 4 to solve it, or is there something
I can do?
LL
From Erland
The message indicates a protocol error in the dialog between client and
server. There are several possible causes for this. The most likely
causes are 1) network problems 2) bug in the client library.
"LL" <LL@.discussions.microsoft.com> wrote in message
news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> We have an inhouse app written in Delphi which connects to a local server
> running SQL7. When we migrated the database to a server running SQL2000 we
> ran into problems.
> When a user uses the Delphi app to browse data, it seems ok, but when the
> user insert records, the app responded "Connection failure".
> In the application log of the event viewer, we noticed the following:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> I did a search on support.microsoft.com for this message. It seems there
is
> some information regarding this on some complicated cause which will be
> addressed in service pack 4.
> I wonder should I wait for service pack 4 to solve it, or is there
something
> I can do?
|||I dont know how it relates to network problem, because the error condition
only happens when we use a user account to insert/change data via the app.
The only thing that i can say is that the sql 2000 database resides on the
domain controller server, whereas, before the sql 7 database resides on a
workgroup server. i will need to follow up with the programmer to see if
there exist some hardcoded machine name inside the app that causes the
failure.
As for 'bug in client library', i dont know why a failure only happens
during write. but it sounds like i can only try to get the programmer to
check the app or see if there are other compatible versions of delphi that
works with sql2000.
thanks for your reply anyways...
"Uri Dimant" wrote:

> LL
> From Erland
> The message indicates a protocol error in the dialog between client and
> server. There are several possible causes for this. The most likely
> causes are 1) network problems 2) bug in the client library.
>
> "LL" <LL@.discussions.microsoft.com> wrote in message
> news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> is
> something
>
>
|||Dear LL :
I also got this error when using delphi 7 to update or changing the
record. My database is also migrating from sql7 to sql2k, please send me
some information if you can solve this problem , tkx so much
George

Problem migrating from sql7 to sql2000 - connection failure

We have an inhouse app written in Delphi which connects to a local server
running SQL7. When we migrated the database to a server running SQL2000 we
ran into problems.
When a user uses the Delphi app to browse data, it seems ok, but when the
user insert records, the app responded "Connection failure".
In the application log of the event viewer, we noticed the following:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
I did a search on support.microsoft.com for this message. It seems there is
some information regarding this on some complicated cause which will be
addressed in service pack 4.
I wonder should I wait for service pack 4 to solve it, or is there something
I can do?LL
From Erland
The message indicates a protocol error in the dialog between client and
server. There are several possible causes for this. The most likely
causes are 1) network problems 2) bug in the client library.
"LL" <LL@.discussions.microsoft.com> wrote in message
news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> We have an inhouse app written in Delphi which connects to a local server
> running SQL7. When we migrated the database to a server running SQL2000 we
> ran into problems.
> When a user uses the Delphi app to browse data, it seems ok, but when the
> user insert records, the app responded "Connection failure".
> In the application log of the event viewer, we noticed the following:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> I did a search on support.microsoft.com for this message. It seems there
is
> some information regarding this on some complicated cause which will be
> addressed in service pack 4.
> I wonder should I wait for service pack 4 to solve it, or is there
something
> I can do?|||I dont know how it relates to network problem, because the error condition
only happens when we use a user account to insert/change data via the app.
The only thing that i can say is that the sql 2000 database resides on the
domain controller server, whereas, before the sql 7 database resides on a
workgroup server. i will need to follow up with the programmer to see if
there exist some hardcoded machine name inside the app that causes the
failure.
As for 'bug in client library', i dont know why a failure only happens
during write. but it sounds like i can only try to get the programmer to
check the app or see if there are other compatible versions of delphi that
works with sql2000.
thanks for your reply anyways...
"Uri Dimant" wrote:

> LL
> From Erland
> The message indicates a protocol error in the dialog between client and
> server. There are several possible causes for this. The most likely
> causes are 1) network problems 2) bug in the client library.
>
> "LL" <LL@.discussions.microsoft.com> wrote in message
> news:8A487A41-DE7C-4A5A-B7B7-0397322AD79D@.microsoft.com...
> is
> something
>
>|||Dear LL :
I also got this error when using delphi 7 to update or changing the
record. My database is also migrating from sql7 to sql2k, please send me
some information if you can solve this problem , tkx so much
George