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 querying a TEXT field

I have a query that works, until I try to also grab one of the fields that
is set to a 'text' datatype. I can grab any field, and it works fine, but
once I try to grab the data from the 'text' field, I get the following
error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image
data types cannot be compared or sorted, except when using IS NULL or LIKE
operator.
If I add an ISNULL to this field, I still get the same error. I'm not
explicitely sorting by thie field either. I haven't found a specific
solution via google other than 'change your TEXT field to VARCHAR(7000)'
which doesn't seem like a proper solution.
-DarrelImpossible to say without seeing the query.
ML|||Are you using a DISTINCT or GROUP BY or something else that might require a
sort? Can you post the query?
HTH
Jerry
"darrel" <notreal@.hotmail.com> wrote in message
news:O8PYwpEyFHA.700@.TK2MSFTNGP11.phx.gbl...
>I have a query that works, until I try to also grab one of the fields that
> is set to a 'text' datatype. I can grab any field, and it works fine, but
> once I try to grab the data from the 'text' field, I get the following
> error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image
> data types cannot be compared or sorted, except when using IS NULL or LIKE
> operator.
> If I add an ISNULL to this field, I still get the same error. I'm not
> explicitely sorting by thie field either. I haven't found a specific
> solution via google other than 'change your TEXT field to VARCHAR(7000)'
> which doesn't seem like a proper solution.
> -Darrel
>
>sql

Problem on cross-tab

I want to insert Espacial field (Record number) in cross-tab report.
How is it possible?
It works nice on standered report but in cross-tab i can't do that.
eliasHello
Mr. Ello
u should write it details
so that i can----

write abt ur code|||hi siraj,

U know in standered report if u select the espacial field(Record Number)
and place it in details section then u can find the record number in the
report.

I want to do same thing in cross-tab report.

However I can't still solve ur question(2 see the total in individual page)

I did the solution which helped u to do that.(But if i place formula-3 in
report footer I can't see anything in individual page but only in last page)

If I place it in page footer then the same problem u written arises..

so help me......

ur ello|||Dear Ello
to solve my problem u can follw last help of Madhi

For ur problem i think u can use standard report & in standard report u can insert cross-tab where record count cn b used as u wish

i hv tested it :if u cn for ur problem u should inform me

bye

siraj

problem on blank space

please help me...

i have a table namely "list"

where field names are name,age,remark

and table display like $=blank space

name age remark
ajit 22 jksakgfjkahfhhsaklhj
raju 21 $$$$$$$$$$$$$$$$p

now the problem is that i want to remove the whole row where remarks field ended with "p" but i don't know how many $ are there before p.

please help me to solve this

Quote:

Originally Posted by madhurjya

please help me...

i have a table namely "list"

where field names are name,age,remark

and table display like $=blank space

name age remark
ajit 22 jksakgfjkahfhhsaklhj
raju 21 $$$$$$$$$$$$$$$$p

now the problem is that i want to remove the whole row where remarks field ended with "p" but i don't know how many $ are there before p.

please help me to solve this

use right() function to find the row.

delete from list where right(remark,1)='p'.

Problem linking tables

I have two tables, TableA and TableB.
Each has a date in smalldatetime format.
TableA has a qty field in int format.
TableB also has a nvarchar field in which the date is in the format
YYYYMMDD

A simple Query on TableA sum Qty returns a result of 100.
However, When I do a left outer join from TableA to TableB on Date, the
sum of Qty on TableA becomes massivley larger.

What could be causing this?

Regards,
Ciarn(chudson007@.hotmail.com) writes:
> I have two tables, TableA and TableB.
> Each has a date in smalldatetime format.
> TableA has a qty field in int format.
> TableB also has a nvarchar field in which the date is in the format
> YYYYMMDD
> A simple Query on TableA sum Qty returns a result of 100.
> However, When I do a left outer join from TableA to TableB on Date, the
> sum of Qty on TableA becomes massivley larger.
> What could be causing this?

Apparently there is more than one row in TableB with the same date
as in TableA.

This should be evident if you replace SUM(qty) with *, so that
you see all rows in the query.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Please learn that rows are not records and columns are
not fields.

>> TableB also has a nvarchar field in which the date is in the format
YYYYMMDD <<

Why? It is redundant. It is in the wrong data type. Do you actively
seek to make queries run longer and be more error prone? What
constriants do you have on that column to prevent illegal dates?

>> When I do a left outer join from TableA to TableB on Date, the sum
of Qty on TableA becomes massivel larger. <<

DATE is a reserved word in SQL and too vague to be proper data element
name. It will be because the same value appears multiple times in
TableB and you get a CROSS JOIN effect.

Problem inserting sql query into database float datatype field using SQL Transaction

I have this problem of inserting my query into database field. My code is as of below.

The @.AVERAGESCORE parameter is derived from

Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)

and the value returned is (averagescore.toString("0.00"))

However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?

Try Dim iAs Integer For i = 0To arraySql.Count - 1 myCommand =New SqlCommandDim consolidatedobjitemAs ConsolidatedObjItem = arraySql(i) myCommand.CommandText = sqlStr myCommand.Connection = myConnection myCommand.Transaction = myTransWith myCommand.Parameters .Add(New SqlParameter("@.AVERAGESCORE", consolidatedobjitem.getaveragescore))End With myCommand.ExecuteNonQuery()Next myTrans.Commit() myConnection.Close()Catch exAs Exception Console.Write(ex.Message) myTrans.Rollback() myConnection.Close()End Try

In your code I did not where you creat the transaction. I use the code below, it works fine. I guess you shoul put the code in to a try catch block. if you want to use it

Dim averageAs Decimal = 1.23Dim mycommandAs SqlCommand =New SqlCommand()Dim myconnAs SqlConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString) mycommand.CommandText ="Insert into Table1 values(1, @.Average)" myconn.Open() mycommand.Connection = myconnDim transAs SqlTransaction = myconn.BeginTransaction("new transaction") mycommand.Transaction = trans mycommand.Parameters.Add(New SqlParameter("@.Average", average)) mycommand.ExecuteNonQuery() mycommand.Transaction.Commit() mycommand.Connection.Close()

Hope this help|||

Thanks for replying. My problem lies with the equation I got my averagescore from.

Dim averagescoreAs Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)average = average.toString("0.00)average =Decimal.Round(average, 2)average = Convert.toSingle(average)average = Math.round(average, 2)

I've in vain to convert the average score to a value which has 2 decimal places using various methods.

The value that I've gotten is 1.68. But I believe the value that is being inserted into the database is not that as I have

various errors. This is because I have tried hardcoding the inserted value like you did and it works.

Is there any other way to resolve this problem?

|||

The error that I've got most of the times are,

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 18 ("@.AVERAGESCORE"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Hope this gives you a clearer idea, my guess is probably a rounding off error, but I just do not have any ideas left. I have even tried changing the database field datatype to decimal(10,2) to no effect.

|||

You could use SQL Server aggregate function Average with Decimal so you can set precision and scale. Try the link below for T-SQL Average aggregate function. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms173454.aspx

|||I afraid the AVG aggregate function woudn't help much in my case. Thanks anyway.|||

Can you print the type of averagescore. It might be become a decimal number after those manipulations, I do not think decimal can fit into float in sql server.

Hope this help

alienated:

The error that I've got most of the times are,

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 18 ("@.AVERAGESCORE"): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Hope this gives you a clearer idea, my guess is probably a rounding off error, but I just do not have any ideas left. I have even tried changing the database field datatype to decimal(10,2) to no effect.

|||Your error message is saying you are passing the wrong data type to the data protocol Tabular Data Stream. You have to convert Float to Decimal before passing it to SQL Server because there is only implicit conversion between Decimal and Numeric and Float and Real, any data passing between both pairs must be explict conversion. The reason is you can set precision and scale with the former and not the later. Hope this helps.

Problem inserting Now() into a datetime field

Hi

I am trying to insert value retrieved from Now() into a datetime field in my MSDE database, but I am getting the following error, and I have no idea what is going wrong.

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Here is the code I am using:

Dim user As String = MyContext.User.Identity.Name.ToString
Dim TimeDate As DateTime = Now()
Dim status As String = "Pending"

With SqlOrders.InsertParameters
.Item("UserName").DefaultValue = user
.Item("OrderDate").DefaultValue = TimeDate
.Item("Status").DefaultValue = status
End With
SqlOrders.Insert()

The date is being returned in this format23/03/2006 02:01:52, which is the same format as it should appear in the database.

could anyone please tell me where I am going wrong?
Datetimes don't have a format. Varchar/strings that represent a datetime have a format. Make sure your OrderDate parameter is set to a datetime datatype, and your problem should go away, probably.|||Thankyou, I added .Item("OrderDate").Type = TypeCode.DateTime and it works fine now :)

Problem inserting into a Text field in a temp table after SP3

I have a stored proc that is inserting data into a Text field. This was
working fine on Friday, but today throws the following error:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Yesterday we upgraded our SQL server from SP2 to SP3, and today we upgraded
3 to 3a after finding this problem. We found the KB article 817359 which
seems to be related, but if we change their example from nText & nVarchar to
Text and Varchar we still get the error (the same one as above). Can anyone
tell me how to fix this problem?
TIA
Ron LounsburyRon,
You should contact PSS to report this. An AV normally means a bug in
sqlserver. You should not be charged for this incident.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Ron Lounsbury" <rlounsbury AT Progeny DOT net> wrote in message
news:uwvRfPrsDHA.1788@.tk2msftngp13.phx.gbl...
> I have a stored proc that is inserting data into a Text field. This was
> working fine on Friday, but today throws the following error:
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Yesterday we upgraded our SQL server from SP2 to SP3, and today we
upgraded
> 3 to 3a after finding this problem. We found the KB article 817359 which
> seems to be related, but if we change their example from nText & nVarchar
to
> Text and Varchar we still get the error (the same one as above). Can
anyone
> tell me how to fix this problem?
> TIA
> Ron Lounsbury
>|||OJ
Thanks for the response. It was a bug that they have a fix for, but you
have to call them to get it.
Ron Lounsbury
"oj" <nospam_ojngo@.home.com> wrote in message
news:eJjF$jrsDHA.4056@.TK2MSFTNGP11.phx.gbl...
> Ron,
> You should contact PSS to report this. An AV normally means a bug in
> sqlserver. You should not be charged for this incident.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "Ron Lounsbury" <rlounsbury AT Progeny DOT net> wrote in message
> news:uwvRfPrsDHA.1788@.tk2msftngp13.phx.gbl...
> > I have a stored proc that is inserting data into a Text field. This was
> > working fine on Friday, but today throws the following error:
> >
> > ODBC: Msg 0, Level 19, State 1
> > SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005
> > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> >
> > Yesterday we upgraded our SQL server from SP2 to SP3, and today we
> upgraded
> > 3 to 3a after finding this problem. We found the KB article 817359
which
> > seems to be related, but if we change their example from nText &
nVarchar
> to
> > Text and Varchar we still get the error (the same one as above). Can
> anyone
> > tell me how to fix this problem?
> >
> > TIA
> > Ron Lounsbury
> >
> >
>

Problem inserting calculated data using sqldatasource, PLEASE HELP....!

Hi, I have created a website and I have a web form, I need to capture data and calculate fields and then insert the calculated field including the data into a sqlserver database. I created a store procedure and the parameters for the txt boxes is working fine. But the calculated fieldds are not getting inserted. Can someone please help? the code for insert is below: For the calculated field, I used txtboxes which "hold" the calculated field in the submit button event. I also tried using query string field

like this: but that did not work either. Help will be very much appreciated.

<asp:QueryStringParameterDefaultValue="1"Name="totlabchg"QueryStringField="laborcharge"Type="Decimal"/>__________________<InsertParameters><asp:FormParameterDefaultValue="0"FormField="txtaccount"Name="ActNumber"Type="String"/><asp:FormParameterDefaultValue="0"FormField="txtmeter"Name="MeterNumber"Type="String"/><asp:FormParameterDefaultValue="0"FormField="txtdov"Name="VisitDate"Type="DateTime"/><asp:FormParameterDefaultValue=""FormField="txtsvadd"Name="SvAdd"Type="String"/><asp:ParameterName="TampId2"Type="Int32"/><asp:ParameterDefaultValue="64.44"Name="charge1"Type="Decimal"/><asp:FormParameterFormField="txtq1"Name="quantity1"Type="Int32"/><asp:QueryStringParameterDefaultValue="1"Name="subtotal1"QueryStringField="sub1"Type="Decimal"/><asp:FormParameterDefaultValue="1"FormField="txtpeople"Name="labor"Type="Int32"/><asp:FormParameterDefaultValue="1"FormField="txthrs"Name="laborhrs"Type="Decimal"/><asp:FormParameterDefaultValue="1"FormField="txttotlab"Name="totlabchg"Type="Decimal"/><asp:ParameterDefaultValue="300.00"Name="flatrate"Type="Decimal"/><asp:FormParameterDefaultValue="1"FormField="txttotalcharge"Name="totalcharge"Type="Decimal"/><asp:FormParameterDefaultValue="1"FormField="txtgrandtotal"Name="grandtotal"Type="Decimal"/><asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"/></InsertParameters>

Thanks.

First, I have no idea what you're doing, so I'm working a little blind. Can you put a breakpoint in your app to see whether or not the calculated field is populated before the insert? You might have to put code into an event to see what's going on. Second, if you're using a stored procedure to do the insert, and it has the values that you're doing the calculation on, why not have it generate the calculated field? That might be easier in the short term.