Problem returning two values from stored procedures

Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.

My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:\blah'

Please could anyone shed ligth on this problem?

here the code for the stored procedure:

CREATE PROCEDURE dbo.GetTillInfo
(
@.TillIdR varchar(50),
@.Password varchar(50)
)
AS

declare @.TillId int
declare @.configpath varchar(150)

IF Exists (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
BEGIN

set @.TillIdR = (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
select @.TillIdR as 'TId'

set @.configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @.login)
select @.configpath as 'ConfigPath'
END
ELSE
BEGIN
set @.TillIdR = 0
select @.TillIdR as 'TId'
set @.configpath =''
select @.configpath as 'ConfigPath'
END
GOOff the top of my head, the two results may be returned but in two tables as you are performing two selects.

To get round this you could change your select query to return the two values like:-

IF ...
 set @.TillIdR = (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
 set @.configpath = (SELECT configpath from customer,tills where
 tills.customerid = customer.id and tills.id = @.login)
 select @.TillIdR as 'TId', @.configpath as 'ConfigPath'
END
ELSE
BEGIN
 set @.TillIdR = 0
 set @.configpath =''
 select @.TillIdR as 'TId', @.configpath as 'ConfigPath'
END
GO

This is off the top of my head at work - you may have to play with the stored proc.

Rob

Problem returning SQL data

I'm having trouble with the display of data when I test my asp page. I basically retrieves or pulls data from different tables in a database. Based on an ID number (which I can change manually by typing in a value and appending it to the URL), it shows GuestName, GuestDescription and URL as text link. I can get it to do that much. But for some reason it's not showing the URL associated with the Guest Name. I dont think it's the display section of my code. I think it's the Select statement.

Here is what the output looks like in my Firefox and IE browsers when I test. Some data has been changed to protect it's integrity:

(Name in bold) Mark Crisp's author of The Bush Lexicon. His new book...

Related Links:
John Doe interviewed by Susan Smith, 123 News

What it should say in the link is this: Mark Crisp's blog (as text link)

When I test for different data I just change the end of the URL like this:
defaultprogram4.asp?ID=1234 (this number represents an ID column in a table)

Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Company\Company.UDL"
set rs = Server.CreateObject("ADODB.Recordset")

id=request.querystring("id") 'If this line is commented out the page will be blank.
'However you can still append a record number to the end of the URL and display that one.

IF id <> "" then id=id else id="1234" end if 'This line shows the default record of 1234. If this line is commented out the page will ONLY show the default record but will NOT allow you
'to append a different number

strSQL = "SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.ProgramID AS Expr2, T_ProgramGuests.GuestName AS Expr3, T_ProgramGuests.GuestDescription AS Expr4, T_ProgramLinks.URL AS Expr5, T_ProgramLinks.Description AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_ProgramGuests.ProgramID = '" & id & "')"

rs.Open strSQL,con 'open a connection to the database

%>

<br />
<strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>

<!-- END OF THE GUESTS AND LINKS SECTION -->

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Bottom line here is this: Why would I be seeing one name but a link not associated with that name? It's as though it's reading a name from the ProgramGuests table and a URL from the
ProgramLinks table (Except that: it shows a completely different unrelated URL to that name).

What am I missing?Is you asp code showing anything ? As you are referencing the column e.g. GuestName, it should not display anything for this attribute as it does not exists in the recordset. The attribute is renamed (in your query to Expr3, so you either will have to delete the AS Expr3 part or rename the reference of the recordset, e.g. rs("Expr3").

Jens K. Suessmeyer.

http://www.sqlserver2005.de

problem returning rows from SPROC

Heres my problem, the first part selects a row from the database, if
there is no row with the criteria it inserts a row and then returns it,
the problem is the IF statement that inserts the row, never returns the
select after it. if there is a row initially in the database, it
returns the right information, I just can't get it to return the row
after inserting it. Anyone know what the problem could be?

Stored procedure:

ALTER PROCEDURE dbo.CheckCurrentPayPeriod
(@.UserID varchar(50))
AS

BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return

DECLARE @.appStartDate DATETIME
DECLARE @.dt DATETIME
DECLARE @.rows int
SET @.appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @.dt = GETDATE()

SELECT
UserID
FROM
PayPeriod
WHERE
(PeriodStart <= CONVERT(varchar(10), @.dt, 101)) AND (PeriodEnd >=
CONVERT(varchar(10), @.dt, 101)) AND (UserID = @.UserID)

-- Inserts their new PayPeriod
DECLARE @.PayPeriodID int
if (@.@.ROWCOUNT = 0)
BEGIN
DECLARE @.sDate datetime
DECLARE @.eDate datetime
SET @.sDate = @.appStartDate
SET @.eDate = DATEADD(day, 13, @.sDate)

INSERT INTO
PayPeriod
(UserID, PeriodStart, PeriodEnd)
VALUES
(@.UserID, @.sDate, @.eDate)

/*EXEC @.PayPeriodID = InsertPayPeriod @.UserID, @.sDate, @.eDate*/

SET @.PayPeriodID = @.@.IDENTITY

SELECT * FROM PayPeriod WHERE PayPeriodID = @.PayPeriodID

RETURN

END
else
RETURN
END(dkode8@.gmail.com) writes:
> Heres my problem, the first part selects a row from the database, if
> there is no row with the criteria it inserts a row and then returns it,
> the problem is the IF statement that inserts the row, never returns the
> select after it. if there is a row initially in the database, it
> returns the right information, I just can't get it to return the row
> after inserting it. Anyone know what the problem could be?

So how do you run the procedure? If you run it from Query Analyzer,
you will see something like:

UserID
--

(0 rows affected)

(1 row affected)

PayPeriodID UserID PeriodStart PeriodEnd
---- -- ---- ---
1 <value> <value> <value
(1 row affected)

If you run this from a client program, you must be able to handle these
three result sets. This means that if you use ADO - to take an example -
you should have to use .NextRecordset to navigate through the record sets.
Note here that the second record set is closed - that record sets consists
of the rowcount only.

However, it's probably better to rewrite the procedure:

CREATE PROCEDURE dbo.CheckCurrentPayPeriod (@.UserID varchar(50)) AS
BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return
DECLARE @.appStartDate DATETIME
DECLARE @.dt DATETIME
DECLARE @.rowc int
DECLARE @.PayPeriodID int

SET NOCOUNT ON

SET @.appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @.dt = GETDATE()

SELECT @.PayPeriodID = PayPeriodID
FROM PayPeriod
WHERE PeriodStart <= CONVERT(char(8), @.dt, 112)
AND PeriodEnd >= CONVERT(char(8), @.dt, 112)
AND UserID = @.UserID
SELECT @.rowc = @.@.rowcount

-- Inserts their new PayPeriod
IF @.rowc = 0
BEGIN
INSERT INTO PayPeriod (UserID, PeriodStart, PeriodEnd)
VALUES (@.UserID, @.appStartDate, DATEADD(day, 13, @.sDate))
SET @.PayPeriodID = @.@.IDENTITY
END

SELECT PayPeriodId, UserId, PeriodStart, PeriodEnd
FROM PayPeriod
WHERE PayPeriodID = @.PayPeriodID
END

Observations:

o SET NOCOUNT ON removes the closed recordset for the rowcount from
the INSERT statement.

o Use style 112 when chopping of time from datetime values. 112 gives
you the format YYYYMMDD, which is always interpreted the same. Format
could be reinterpreted if the user has an unexpected language setting.

o Since @.@.rowcount is volatile - update after each statement, I catch
into a local variable immeidately, and glue that SELECT directly to
the SELECT I'm catching rowcount for.

o SELECT * in production is not good practice. Always explicitly list
which columns you want returned.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Problem returning OUTPUT in stored procedure

Hi, I have this output, @.RegisterFlag int OUTPUT

and I have a transaction going on, so my code (I just put some relevant code here) is:

1BEGIN TRAN2 SELECT @.getDealername = OrgNameFROM OrgWHERE OrgName = @.DealerName3If @.getDealernameisnull45ELSE6 BEGIN7 set @.RegisterFlag = 28ROLLBACK TRAN9 RETURN10 END1112COMMIT TRAN13set @.RegisterFlag = 1

The problem I am facing now is I couldn't get @.RegisterFlag = 2 return back to my asp.net code when it reached line 7, instead I got this error mesg:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

How do I fix this? Many big thanks.

Hi, it's ok already. I realize that the problem is I have both transaction code, one in asp.net and another one in stored procedure. After taken out the one in asp.net, it works now. Thanks.

problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!
ibiza wrote:

> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again!

problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!ibiza wrote:
> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again! :)

problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!ibiza wrote:

> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again!

Problem returning HTMLEncoded text.

I am storing HTML inside a text field already HTMLEncoded. (example.
<body>) When I use the FOR XML EXPLICIT to return this field I get
&lt;body&gt; My text is being HTMLEncoded again.
Is there a way to prevent this from happening?
Thanks,
Ryan
Not to my knowledge, FOR XML automatically does the encoding.
Irwin Dolobowsky
Program Manager - SqlXml
http://weblogs.asp.net/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Fiorini" <ryan.fiorini@.mutualofamerica.com> wrote in message
news:#o5U#iyFEHA.684@.tk2msftngp13.phx.gbl...
> I am storing HTML inside a text field already HTMLEncoded. (example.
> <body>) When I use the FOR XML EXPLICIT to return this field I get
> &lt;body&gt; My text is being HTMLEncoded again.
> Is there a way to prevent this from happening?
> Thanks,
> Ryan
>
|||Try to use the !xml directive in your FOR XML explicit query. This should
avoid entitization.
Best regards
Michael
"Irwin Dolobowsky[MS]" <irwind@.mail.microsoft.com> wrote in message
news:%233U1zw3FEHA.1600@.tk2msftngp13.phx.gbl...
> Not to my knowledge, FOR XML automatically does the encoding.
> --
> Irwin Dolobowsky
> Program Manager - SqlXml
> http://weblogs.asp.net/irwando
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Ryan Fiorini" <ryan.fiorini@.mutualofamerica.com> wrote in message
> news:#o5U#iyFEHA.684@.tk2msftngp13.phx.gbl...
get
>
sql

Problem returning data

I have a stored procedure (in SQL Server 2005 Express) that returns a string. The problem is when I call it from my web page I get only the first character of the string. This is my SP:

ALTER PROCEDURE

dbo.usp_CalcDeliveryCharge

@.mintDistance

int,

@.mintCustomer_ID

int= 0,

@.mintRate

int= 0OUTPUT,

@.mstrZone

nchar(10) =null OUTPUT

AS

/* SET NOCOUNT ON */SELECT@.mintRate=RATE,

@.mstrZone=ZONE

FROMtblRatesWHERECustomer_ID=@.mintCustomer_IDANDMile_Range_Min <= @.mintDistanceANDMile_Range_Max >= @.mintDistance

And this is my code:

sql_Command.CommandText =

"usp_CalcDeliveryCharge"

sql_Command.CommandType = CommandType.StoredProcedure

sql_Command.Parameters.Clear()

sql_Command.Parameters.AddWithValue(

"@.mintDistance", intApproxMiles)

sql_Command.Parameters.AddWithValue(

"@.mintCustomer_ID", Profile.CompanyID)

sql_Conn.Open()

sql_Reader = sql_Command.ExecuteReader()

While (sql_Reader.Read())Me.lblZone.Text = sql_Reader.Item(0).ToStringEndWhile

sql_Conn.Close()

sql_Reader.Close()

sql_Command.Dispose()

If you are using OUTPUT parameters you need to add the output parameters in the asp.net code also and set their direction as output and retrieve the values through those parameters not through datareader.
checkthis article if it helps.

Problem returning a timestamp column inside an TSQL Transaction

I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@.LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @.LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@.NewLastChanged"):

1select @.TimestampCheck = LastChangedfrom ADD_Addresswhere AddressId=@.AddressId23if @.TimestampCheckisnull4begin5RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1)-- AddressId does not exist.6return -17end8 else if @.TimestampCheck <> @.LastChanged9begin10RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)11return -112end131415Begin Tran Address1617Update ADD_Address18set StreetNumber= @.StreetNumber, AddressLine1=@.AddressLine1, StreetTypeId=@.StreetTypeId, AddressLine2=@.AddressLine2, AddressLine3=@.AddressLine3, CityId=@.CityId, StateProvidenceId=@.StateProvidenceId, ZipCode=@.ZipCode, CreateId=@.CreateId, CreateDate=@.CreateDate19where AddressId= @.AddressId2021select @.error_code =@.@.ERROR, @.AddressId= scope_identity()2223if @.error_code = 024begin25commit tran Address2627select @.LastChanged = LastChanged28from ADD_Address29where AddressId = @.AddressId3031if @.LastChangedisnull32begin33RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)34return -135end36if @.LastChanged = @.TimestampCheck37begin38RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)39return -140end41return 0

I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?

Thank you,

jspurlin 

No need for a transaction for that really. Just go about it a different way:

Update {fields} FROM {table} WHEREAddressID=@.AddressID ANDLastChanged=@.LastChanged

Then get the number of records affected, and the error codes.

if there were no errors, and no rows were affected then you can either raise a generic error ('record changed or does not exist'), or you can go and look and see which of the two (AddressID,LastChanged) didn't exist, although there is the possability the data may change between the update and when you go and try to figure out why it failed, in which case you may get an incorrect error message (Saying it didn't exist, when it was only changed or saying it was changed when it didn't exist).

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 query returning float with comma

I to all

i am bilding a web page, using asp and sql server

I have a few querys in the asp script. my problem is that the values from the query results to tables with float fiels, apear with a comma
and want a dot

like area= 23,5 and I would like to have area= 23.5

in the query analyser there is no problem its all dots
i have my web aplication running in 3 diferent machines and in 2 of them i dont have this problem. the query results to float fiels apear with a dot

in the 3 machines the database is the same , the odbc conection is similar. i have win xp professional in 2 machines and win 2000 server in other. the machine with this problem has xp pro

something i miss in the IIS...

i am lost

some hint would be very nice

thanks for your time and replayThere could be lots of possible ways to get this behavior. Without knowing a lot about your systems I just have to guess.

My first thought would be that two of the clients have installed English-US and the offending client has installed English-UK versions of either MDAC or IIS.

-PatP