Binding WebService as Datasource

I've painstakingly managed to get a XmlDocument from a webservice and run this to retrieve data.

However, i cannot bind any of the returned elements to anything. Any help appreciated.

Are you using the XML data processing extension? What is the Query that you are using? More specifically, what is the Element Path for the query?

For more information, take a look at the following article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/RepServXMLDS.asp

Ian

Binding error

Hi all

I have a form view which uses a SQL data source control to retrieve it's data from the sql express database

the form view is used for view,edit,delete and insert data into the database

in the insert mode I have two dropdownlists, where the second one is depending on the first one to retrieve the correct data from the data baseBUT

when selecting a value in the first dropdownlist it give me the following erro:

Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control.

Please How can do this

any help is appriciated

bye

You cannt do this using this method. There are many work arrounds out there, but the easiest I believe is the CascadingDropDown control which is part of the AJAX.NET Control Toolkit.

http://www.asp.net/ajax/ -- Install the AJAX.NET FRAMEWORK AND configure your site to use it.

You will need to:

1.) First Create a Web Service Class like as followed:

using System;using System.Web;using System.Collections;using System.Configuration;using System.Web.Services;using System.Web.Services.Protocols;using System.Collections.Generic;using AjaxControlToolkit;using System.Data;using System.Data.SqlClient;using System.Web.Script.Services;/// <summary>/// Cascading DropDownList AjaxControlToolkit Web Service/// </summary>[WebService(Namespace ="http://tempuri.org/")][WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)][System.Web.Script.Services.ScriptService]public class DataService : System.Web.Services.WebService { private string connectionString =ConfigurationManager.ConnectionStrings["CarrierDBConnectionString"].ConnectionString; public DataService () {} [WebMethod] [System.Web.Script.Services.ScriptMethod()] public CascadingDropDownNameValue[] GetCarriers(string knownCategoryValues, string category) { List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>(); using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("CARRIER_SELECT_ALL", conn)) { cmd.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { values.Add(new CascadingDropDownNameValue((string)dr["NAME"], dr["ID"].ToString())); } } } return values.ToArray(); } [WebMethod] [System.Web.Script.Services.ScriptMethod()] public CascadingDropDownNameValue[] GetSubsidiariesByCarrierID(string knownCategoryValues, string category) { List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>(); using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("CARRIER_SELECT_SUBSIDIARY_BYID", conn)) { string[] categoryValues = knownCategoryValues.Split(':', ';'); int id = Convert.ToInt32(categoryValues[1]); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@.id",id); conn.Open(); using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (dr.HasRows) { while (dr.Read()) { values.Add(new CascadingDropDownNameValue((string)dr["NAME"], dr["ID"].ToString())); } } } } }return values.ToArray(); }}

2.) Create the Actual Web Service

Click on the Project in the 'Solution Explorer' and 'Add New Item' and then select Web Service (for instance in this example: DataService.asmx)

3.) Hook up your ajax controls and dropdownlist

<asp:UpdatePanel ID="updatePnlInsert" runat="server">
<ContentTemplate>
<asp:DropDownList ID="ddlCarrierInsert" runat="server" AutoPostBack="True" Width="100%" DataSourceID="sqldsCarriersInsert" DataTextField="NAME" DataValueField="ID" SelectedValue='<%# Eval("CARRIER_ID") %>' AppendDataBoundItems="true">
<asp:ListItem Value="" Text="<select carrier>" />
</asp:DropDownList>
<br />
<asp:SqlDataSource ID="sqldsCarriersInsert" runat="server" ConnectionString="<%$ ConnectionStrings:CarrierDBConnectionString %>"
SelectCommand="CARRIER_SELECT_ALL" SelectCommandType="StoredProcedure" />

<asp:DropDownList ID="ddlSubInsert" runat="server" Width="100%" /><br />
<cc1:CascadingDropDown ID="cddCarrierInsert" runat="server"
TargetControlID="ddlSubInsert"
ParentControlID="ddlCarrierInsert"
Category="CARRIER"
LoadingText="[Loading Subsidiaries...]"
PromptText="<select subsidary>"
ServiceMethod="GetSubsidiariesByCarrierID"
ServicePath="DataService.asmx"
SelectedValue='<%# Eval("SUBSIDIARY_ID") %>'>
</cc1:CascadingDropDown>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="ddlCarrierInsert" EventName="SelectedIndexChanged" />
</Triggers>
</asp:UpdatePanel>

Hope this helps.

Binding dataset to a reportviewer control

Hi,

I need to bind my custom dataset, which i retrieve by executing some query to a reportviewer control. I don't want to create a typed dataset in the application. Is there any way to do it? The dataset that i bind will be built in a seperate class. If I plan to use a Object Data source, what are the steps to follow. Pls help.

thanks,

Saravanan

www.gotreportviewer.com provides information about how to use object data sources specifically at: http://www.gotreportviewer.com/objectdatasources/index.html

-- Robert

|||

Hi Robert,

Thanks for the reply. I already read through that article. What i really need is to bind a dataset ( which i create in my application) to be bound to the report viewer. The dataset will have some columns from the database ( thro a select query), some columns may be added by me. I want to bind this custom dataset to the report viewer control (local mode processing). Please let me know if this can be done and also some steps to do it.

Thanks in advance,

Saravanan.

BinaryWrite

I succesfully uploaded various file into my SQL database. My question is, does anyone know how to retrieve or display these files such as jpg, word document... in VB.netThere are lots of resources on this. Essentially you either stream the data directly to the client or you bounce it off the disk, I prefer the former.

Try this for starters...
http://www.ftponline.com/vsm/2002_07/online/hottips/esposito/

Binary_double Oracle data type support

I'm using RS 2000 and I'm getting an "unsupported Oracle data type 101 encountered" error when attempting to retrieve data from a dataset based on a plsql stored procedure. I'm assuming this is related to a recent update on our Oracle 10g database to convert columns from number to binary_double.

Can anyone confirm if this is the case, and suggest a work-around? Is binary_double supported in RS 2005?

Thanks

RS uses MS .NET provider for Oracle which currently does not support binary_float/double.

The possible workaround is to cast binary_float columns to number in your SQL statement

select cast(my_bin_float_col as number) from ...

Binary files in DB : set name on retrieve

Hello,

I have a table that stores binary files. When I serve them up to the user, I call the following page (serveDocument.aspx?DocumentID=xxx) which holds only the following code and pass the document ID:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load
Dim DocumentIDAs Integer = Convert.ToInt32(Request.QueryString("DocumentID"))
'Connect to the database and bring back the image contents & MIME type for the specified picture Using myConnectionAs New SqlConnection(ConfigurationManager.ConnectionStrings("kelly_lmConnectionString1").ConnectionString)
Const SQLAs String ="SELECT [Content_Type], [Document_Data] FROM [lm_Service_Detail_Documents] WHERE [Document_id] = @.Document_id"Dim myCommandAs New SqlCommand(SQL, myConnection)
 myCommand.Parameters.AddWithValue("@.Document_id", DocumentID)
 myConnection.Open()
Dim myReaderAs SqlDataReader = myCommand.ExecuteReader
If myReader.ReadThen Response.ContentType = myReader("Content_Type").ToString()
 Response.BinaryWrite(myReader("Document_Data"))
End If myReader.Close() myConnection.Close()End Using
End Sub

It works perfectly. But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'. Is there any way toinjectthe filename that I want to save the document as?

Thank you.

Add this Header also

context.Response.ContentType = "application/octet-stream";
context.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + filename+".txt" + "\"");

------------------

Mark as Answer if you feel

|||

What does 'context' refer to?

And, are you saying I shouldn't set the ContentType to the actual type of file?

|||

REmove the context

it uses to html encode when you are using http handler

|||

Well, that partially works. When a user clicks the linkthen saves the file, the correct name is present. But, my issue isn't that... I want the user to be able to right click the link and 'Save Link As...' with the correct filename. When I do that, it still says 'documentServe.aspx'.

|||

Hi,

But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'.

From your description, it seems that when you right click on the link and choose the 'save target as' in IE7, you can got the whole url with parameters, but not in FireFox, right?

If so, I'm afraid that the behavior is related to the Explorer, and based on my knowledge, there's not any workaround to fix the problem, since working mechanism for IE and FireFox is just not the same.

Thanks.

sql

Binary field usage in SQL Server

Can anyone point me in the right direction to find documentation for the problem below?
I need to store and retrieve ten fields of 16-bits each for testing 16 true-false conditions (a total of 160 bits in each record) so I think I'd like to use ten 2-byte binary fields (160 "bit" fields would be quite unmanageble, if even possible [I think there is some kind of limit to the number of fields in a single record]).
I'm not quickly finding in the SQL Server's online documentation how to test for, use and update binary fields. I'll keep looking, but can anyone point me in the right direction? I'm using VB, if that makes any difference.

The post below is from SQL Server BOL (books online) documentation on BIT, BINARY and VARBINARY data types. I am assuming you know BIT is proprietry because of three valued logic there is no Boolean data type in ANSI SQL. If you need more information post again. Hope this helps.

bit
Integer data type 1, 0, or NULL.

Remarks
Columns of type bit cannot have indexes on them.

Microsoft? SQL Server? optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.

binary and varbinary
Binary data types of either fixed-length (binary) or variable-length (varbinary).

binary [ ( n ) ]

Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.

varbinary [ ( n ) ]

Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for varbinary is binary varying.

Remarks
When n is not specified in a data definition, or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Use binary when column data entries are consistent in size.

Use varbinary when column data entries are inconsistent in size.

binary data and sql server

Here is my task I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section. What is the best way to handle this. The code works below but it loads a new browser with the content. I need it to appear in it's tabbed section in the original aspx file. Any assistance you can give me would be greatly appreciated.

Thanks Jerry

oSQLConn.Open()

Dim myreaderAs SqlDataReader

myreader = myCommand.ExecuteReader

Response.Expires = 0

Response.Buffer =True

Response.Clear()

DoWhile (myreader.Read())

Response.ContentType = ("application/pdf")

Response.BinaryWrite(myreader.Item("img_content"))

Loop

Hi,

Well, you can add a Image control in your Tab control container, and create a page which works for displaying the image data from database.

The code snippet on that page to show the image data: (ShowImg.aspx)

// myRead is a DataReader object.Byte[] Buffer = (Byte[])myRead[0];//Outputthis.Response.Clear();this.Response.BinaryWrite(Buffer);this.Response.End();

And then, assign the ImageUrl property of the Image control.

this.Image1.ImageUrl = "showimg.aspx"

Of course, if you want to show different pictures based on the parameters like following:

this.Image1.ImageUrl = "showimg.aspx?imageid=123"

Then, you can receive the parameter on ShowImg.aspx, make a query with that parameter against the database and return the corresponding image data.

Thanks.

|||

Thanks it looks like this solution will work just fine.

BETWEEN returns invalid data

I'm using BETWEEN stmt to retrieve data from a varchar column code,
so suppose this column contains mainly numbers,( i.e. 40, 300, 400, 500)
and the stmt is:
Select * from table where code between '300' and '500'
the result returns 40 also, so this is not an effective way to retrieve such data.
so how to make this work out.

Best Regards,

Quote:

Originally Posted by theavitor

I'm using BETWEEN stmt to retrieve data from a varchar column code,
so suppose this column contains mainly numbers,( i.e. 40, 300, 400, 500)
and the stmt is:
Select * from table where code between '300' and '500'
the result returns 40 also, so this is not an effective way to retrieve such data.
so how to make this work out.

Best Regards,

Try Greater than 300 and less than 500 like the following:
Select * from table where code > '300' and code < '500'

what do you get?|||Hi ,

Plz try this . . .

Select * from table where convert(numeric,code) between 300 and 500. This query will wrk properly. or else mail me at mneduu@.gmail.com|||PLease try this

Select * from table where code between 300 and 500. This query will work.
I've already try this

and this quesry will also work

Select * from table where convert(numeric,code) between 300 and 500.

BETWEEN keyword

I need to retrieve records where the date is in between the current date and 4 days previous.

I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4
it doesn't work...

Can someone help out?You are probably having problems because both the date and the time are used in such comparisons.

See if this post helps you:view post 483129

Terri|||Ok, thanks Terri.

Best way to retrieve rows matching an array of Ids

My database table has approx 400,000 rows, with an integer Id as primary key
.
I have an array of ids, with N elements where N is typically between 500 and
5,000.
I want to retrieve into a collection all rows whose id matches a value in
this array.
What is the 'best' way to do this and/or what are the tradeoffs to consider?
I can imagine a number of solutions as follows:
1. A parametered query which takes a single Id parameter and returns 1 row.
Implies N round trips to the server to retrieve N rows.
2. A parametered query with an IN clause which takes n parameters "... WHERE
ID IN (@.p1, @.p2, ... , @.pn)". Implies N/n round trips to the server where
each round trip retrieves up to n rows. What is the maximum / optimal valu
e
for n in this case?
3. Build the SQL string dynamically, with an IN clause generated by
concatenating up to n Id values. Implies N/n round trips to the server wher
e
each round trip retrieves up to n rows. What is the maximum / optimal valu
e
for n in this case?
4. Create a temporary table with a single integer ID column and insert the N
Ids from my array (how?). Join the original table with this temporary table
to retrieve all matching Ids.
...
Any suggestions?You can try the solution offered in http://www.aspfaq.com/2248 ... also
check out the link there to Erland's article on array handling.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:656DF445-E0EC-495C-B62E-0A535EBC2366@.microsoft.com...
> My database table has approx 400,000 rows, with an integer Id as primary
> key.
> I have an array of ids, with N elements where N is typically between 500
> and
> 5,000.
> I want to retrieve into a collection all rows whose id matches a value in
> this array.
> What is the 'best' way to do this and/or what are the tradeoffs to
> consider?
> I can imagine a number of solutions as follows:
> 1. A parametered query which takes a single Id parameter and returns 1
> row.
> Implies N round trips to the server to retrieve N rows.
> 2. A parametered query with an IN clause which takes n parameters "...
> WHERE
> ID IN (@.p1, @.p2, ... , @.pn)". Implies N/n round trips to the server where
> each round trip retrieves up to n rows. What is the maximum / optimal
> value
> for n in this case?
> 3. Build the SQL string dynamically, with an IN clause generated by
> concatenating up to n Id values. Implies N/n round trips to the server
> where
> each round trip retrieves up to n rows. What is the maximum / optimal
> value
> for n in this case?
> 4. Create a temporary table with a single integer ID column and insert the
> N
> Ids from my array (how?). Join the original table with this temporary
> table
> to retrieve all matching Ids.
> ...
> Any suggestions?|||Very useful resources, thanks, it's a lot clearer. In my case, the number
of values is potentially greater than will fit into an 8K string. So I thin
k
the approach will be:
1. Create the temp table
2. Concatenate as many Ids as possible into a string which does not exceed
8K and call an SP which extracts integers from the string and inserts into
the temp table.
3. Repeat step 2 until all ids in my array are processed.
4. Execute a query with a join to the temp table
5. Drop the temp table.
This will be done from a .NET application. I am concerned about the
lifetime of the temporary table? If I get a SqlException while running the
above, my code will typically close its SqlConnection object - which won't
actually close the underlying connection, just return it to the connection
pool. Presumably this means the temp table could be left lying around for
the next unsuspecting user of that connection, right?
I guess the workaround would be to check for the existence of the temp table
before step 1 above and drop it if it already exists.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You can try the solution offered in http://www.aspfaq.com/2248 ... also
> check out the link there to Erland's article on array handling.
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:656DF445-E0EC-495C-B62E-0A535EBC2366@.microsoft.com...
>
>|||Arrays? Collections? Those are not SQL structures. We only have
tables. And we avoid procedural code, cursors, dynamic SQL and string
casting as much as possible -- which is about 99.99% of the time.
Load your list of ids into a one column table and use "WHERE foobar_id
IN (SELECT search_id FROM WorkingList)" in the body of the procedure.
Avoid kludges.|||I think we already established that a good approach is to load the ids into
a
(temporary) table. The question was, what is the 'best' way to load these
ids into the temporary table.
From the resources linked by Aaron Bertrand, it seems that a good approach
is to pass the array of values as a string - this means that all the values
can be passed in a single call.
Do you agree or what alternative would you recommend and why?
"--CELKO--" wrote:

> Arrays? Collections? Those are not SQL structures. We only have
> tables. And we avoid procedural code, cursors, dynamic SQL and string
> casting as much as possible -- which is about 99.99% of the time.
> Load your list of ids into a one column table and use "WHERE foobar_id
> IN (SELECT search_id FROM WorkingList)" in the body of the procedure.
> Avoid kludges.
>

Best way to retrieve Oracle data into SS2000 near real-time?

I have no idea where to post this kind of question, so here it is!

I have a requirement to retrieve oracle 10 data into SS2000 in as near real-time as possible (stupid users!) and join with resident SS data for on-demand reporting. (We use SS replication to populate some reporting tables from other SS2000 instances and this has spoiled the users as well as the developers! )

I would like to know if there are any clever ways of doing this, or if a plain-old DTS package running in some kind of loop is the practical answer. 1 minute delay is probably too long . . . I don't know if data can be pushed from the oracle side. Or if we need to write a Service and use it to suck and push.

Any suggestions?

thanks!

In a bank I worked a while back we had a DTS package that runs five hours a day five days a week taking DB2 AS400 deposits to the web first 7.0 then 2000 almost real time. The package runs as a job with the Agent using xp_cmdshell and it works like a charm. Hope this helps.

best way to retrieve one row for use in code

i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it.

assume i have a query like this.

Select A, B, C from Table1 where ID=xyz

whats the easiest way to get A, B and C in to string variables?

I know i can use the sqldatasource control but i just feel there is too much overhead there.

whats your suggestions?

Thanks

Justin

I suggest that you use aSqlDataReader.

HTH,
Ryan