I have a DAL that uses Typed DataSets (not directly, the DAL references and calls the dataset methods) and I am receiving a "There is already an open DataReader associated with this Command which must be closed first." error when I render an ASP.NET page where two imgs need to retrieve two different versions of an image (a thumbnail and a full sized image).
I am using SQL Server 2005, ASP.NET 2.0, and my connection string includes the MARS attribute set to true.
This is the method in the SqlServer DAL, _images is a typed table adapter:
public override Image SelectImage(Guid? id)
{
// TODO: Find out why MARS feature isn't working!
DataSet.ImageDataTable dt = _images.SelectImageById(id); <-- Exception occurs here
if ((dt != null) && dt.Count == 1)
{
DataSet.ImageRow row = dt[0];
Guid? keyId = null;
if (!row.IsKeyIdNull())
{
keyId = row.KeyId;
}
// This is the method that is called for both images, which calls the DAL
public class EntitySearch
{
public static Image ImageById(Guid id)
{
return _dal.SelectImage(id);
}
}
// These are the code-behind methods that the image response strings will come from...
imgThumbnail.ImageUrl = String.Format("../Services/ImageBroker.aspx?img={0}&mode=Thumbnail", image.Id);
imgFullImage.ImageUrl = String.Format("../Services/ImageBroker.aspx?img={0}&mode=Image", image.Id);
// And finally here is the code in the broker class that saves the images to the response stream
Image image = EntitySearch.ImageById(imageId);
using (Picture picture = (mode == ImageMode.Image) ? image.Picture : image.Thumbnail)
{
Picture.Save(response.OutputStream, ImageFormat.Jpeg);
}
So the final snippet of code is being called twice, and it crashes. I have tried closing the connection and reopening it as a workaround but this only yields strange results, though it intermittently does load the images.
Can anyone spot the issue?
What's the benefit of Multiple Active Result Sets? When would you use it. Read the BOL article but still not quite sure what its use is. there doesn't seem to be a performance gain. So what's the purpose?
We have had a SQL server 2005 database up and running for some time and now we are getting this error:
[The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.]
We use MARS in our Windows Application (.NET 2.0) and can not seem to figure out what is going on...
After this error shows up in the event viewer, we get a second error (milliseconds later) in our application. "A severe error occurred on the current command. The results, if any, should be discarded."
i really need help on this one... it seems it is not with our SP but the connection to SQL Server 2005 Express and using MARS... i double checked the connection string and it is all setup correctly and we are in Multi-User Mode.
I am trying to make a connection to an SQL database held on myserver I am able to connect through a machine data source using access using the credentials as below the attempt fails at con.open with following error:
System.Runtime.InteropServices.COMException was caught ErrorCode=-2147467259 Message="Named Pipes Provider: Could not open a connection to SQL Server [53]. " Source="Microsoft SQL Native Client" StackTrace: at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options) at Client_Access.JobRequest.AddJob_Click(Object sender, EventArgs e) in C:Documents and SettingsRobertMy DocumentsVisual Studio 2005Projects Client Access Client AccessJobRequest.vb:line 392
Dim con As New ADODB.Connection Dim rst As New ADODB.Recordset Dim sPassword As String, sUserID As String sPassword = "abcde" sUserID = "cClient" Try con.ConnectionString = "Provider=SQLNCLI;" _ & "Server=(myserver);" _ & "Database=transportRecs;" _ & "Integrated Security=SSPI;" _ & "DataTypeCompatibility=80;" _ & "UID=" & sUserID & ";" _ & "PWD=" & sPassword & ";" _ & "MARS Connection=True;" Dim mySQL As String
mySQL = "SELECT * FROM dbo_jobitem " '& _ '" WHERE [Custid] ='" & strTag & "'"
con.Open()
rst = New ADODB.Recordset With rst .ActiveConnection = con .CursorLocation = ADODB.CursorLocationEnum.adUseClient .CursorType = ADODB.CursorTypeEnum.adOpenStatic .LockType = ADODB.LockTypeEnum.adLockBatchOptimistic .Open(mySQL) .MoveLast() .MoveFirst() .MoveLast() .MoveFirst() Debug.Print(.RecordCount) End With
Catch ex As Exception MsgBox(ex.ToString) Finally If (con.State = ConnectionState.Open) Then con.Close() End Try
Is it possible to return typed data in an Endpoint for an ASP.Net Web Reference to Proxy? If so, is there any specific terminology I should be aware of to target my search?
I realize there is a choice between Object (returns a dataset or error) or dataset, but the automatically generated WebReference Proxy in ASP.Net (2.0) is untyped and we can't change the typing there as you have to remove the entire WebReference to pick up new WebMethods (or changes to signitures I'd assume).
I'm able to create my own typed proxy as a psuedo-DAL assembly which takes the WebReference and cast rows/objects into typed rows/objects one at a time, but this seems like a lot of work and probobly not the best practice.
Hi, Is there any work around to passing a set of strings to a parameter in a Typed Dataset for example I am pasing '4226222172004','4212012182004' which I build on my code the number of items will vary passed on the user selection but since the Typed Dataset uses sp_executesql and the parameters are change to '''4226222172004'',''4212012182004''' Any ideas how I can format the Parameter I am passing.so that it will end like where in ( '4226222172004','4212012182004') instead of where in ('''4226222172004'',''4212012182004''') I again the number of parameters will very. Thanks Julio D
I am using typed data sets. When a certain line of C# code is reached, I get the following error message: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. Here is my source code. I altered the table to remove all contraints and primary keys, but I still get the error message. The erroroccurs when the last line of code is reached. (_DeltaTable = myAdaptor.GetData();) The code is listed below. Does anyone know whatis going on? calculateValuesAndAveragesTableAdapters.FundamentalsTableAdapter myAdaptor = new calculateValuesAndAveragesTableAdapters.FundamentalsTableAdapter(); calculateValuesAndAverages.FundamentalsDataTable _DeltaTable; _DeltaTable = myAdaptor.GetData(); // Error occurs on this line
I dragged two tables into the dataset designer. And I have a query with a resultset over both of the tables (joined).Is there ANY way I can have a strongly typed datatable out of the resultset?The automatically created dataset adapters won't allow me to create a tableadapter or datatable for the joined resultset but for one single table at a time only.Any solution to that? Theoretically it should be possible since I don't "lose" any type information with a join.. why can't I have a strongly typed datatable over the resultset?The join is not very sophisticated. In fact it couldn't be any more simple.(I'm refering to my previously posted question at http://forums.asp.net/t/1220481.aspx)
When I insert the fields using parameters, the params look like this: paramValues(10) = New SqlParameter("@RegHrs", SqlDbType.Decimal) paramValues(11) = New SqlParameter("@OTHrs", SqlDbType.Decimal) paramValues(12) = New SqlParameter("@TotHrs", SqlDbType.Decimal)
The fields in the SQL table are defined as: RegHrs - decimal (9,2) OTHrs - decimal (9,2) TotHrs - decimal (9,2)
Before I insert the row (from w/in the VB code), I stop the code and verify that the values being placed into the params are: 5.85 0 5.85
After the SQL insertion, the values w/in the SQL Table contain: 6 0 6
I'm looking to serialize some NVP data into an XML blob. I plan to put a primary xml index on the column, but my question is, would putting an XSD on the column speed up any queries, or would it just ensure format? I know that with selective xml indices, you can do things like specify the datatype associated with the xpath which can further optimize retrieval.
Hi, I am trying to use a typed dataset created using the Query builder which returns the data correctly when I use 'Execute Query' in query builder but as soon as I attempt to return a dataset using the GetData method created I get the following error: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. Which is not very helpful, any ideas where the problem may be? I have tried switching off enforce constraints and setting the NullValue property of all strings to 'Emtpy' bu that has no effect. Thanks in advance
Hello,I'm using a typed dataset to access my database (SQL Express); I have a table that returns data, which two fields are null; I'm getting this error:{"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."}There are no foreign-key or unique key constraints, so it has to be a non-null issue; I changed the two fields in the table to have a null value of Empty instead of Throw Exception, but I don't understand what the problem is coming from... I'm at a loss.Thanks.
I'm need to use typed Dataset with CLR UDT, but when I'm trying to create TableAdapter in Dataset designer, I'm gettng error message: User-defined data types are not supported in DataSet designer
I would like to extact data from a source system even if it has errors. Then I can transform it and handle the errors in the appropriate manner. Are there any loosely-typed Data Flow Destinations?
Hi all,I am using a Strongly Typed DataSet (ASP.NET 2.0) to insert new data into a SQL Server 2000 database, types of some fields in db are nvarchar. All thing work fine except I can not insert unicode data(Vietnamese language) into db.I can't find where to put prefix N. Please help me!!!
Hi, I have a little question. I searched google, and could not find good answer for this one. I have a stored procedure that returns two tables. Usually I generate a dataset out of a stored procedure by dragging it to the dataset. When I drag this one it creates a DS with only one table, the first one. How can I make it use both tables?
<DetailRows> <DetailRow> <MonthNumber></MonthNumber> <Amount></Amount> </DetailRow> </DetailRows>If my variable contains following xml document as un-typed xml
[Code] ....
However, if I use a typed xml variable that is based on above schema, I cannot use OPENXML. What is the correct way of achieving same result with a typed xml doc? I am using SS2K5.
Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why?
also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text."
I'm at loss how I'm supposed to work with typed datasets and Sql Server Compact 3.5, when inserting records and I need to update my datatables with the primary key of newly inserted rows.
I've tried adding a RowUpdated handler to all tableadapters that look like this:
I've previously used this type of approach when working with an OleDbDatabase, which works just fine. But it doesn't work with Sql Server CE 3.5, and since it doesn't support stored procedures I can't fix it that way either. And it doesn't support commands in a batch (i.e appending the Insert command of the adapter with ";SELECT @@IDENTITY") so that doesn't work either...
So how are we supposed to use Sql Server CE 3.5? It's impossible together with datasets? Or am I missing something obvious?
I am trying to upload a file in ASP.net 2.0 to a SQL database using the FileUpload control. I am doing this by way of a typed dataset.Here is my code.Dim rta As New ResponseTableAdapterDim rdt As ResponseDataTable = rta.GetResponseByID(1)Dim rr As ResponseRow = rdt(0)Dim fs As New FileStream(fu.PostedFile.FileName, FileMode.Open, FileAccess.Read)Dim br As New BinaryReader(fs)Dim image() As Byte = br.ReadBytes(fs.Length)br.Close()fs.Close()rr.UploadFile = imagerr.NameFile = fs.Namerta.Update(rdt)The code runs without an error but afterwards I find that nothing has been stored in the UploadFile cell in the database but the file name has been stored in the NameFile cell in the database. Any ideas?Your help is much appreciated. James
Like the subject says, I'm using strongly typed datasets. I'm using to designer to create the datasets and the methods. I can select and insert, but I can't update or delete. I right click on the adapter bar and select Add Query. I sleect 'Use SQL Statements'I select 'Update' (or 'Delete')I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'I can click on next, but anything else gives me errors. I'd list them, but I'm clearly doing something wrong and it's probably obvious. Diane
Using VS 2008 I got a Windows Smart Device project targeting WM6 Standard and using .NET CF 3.5. I add a new database file = creating an empty Compact 3.5 database (creates an sdf-file in my project). Then create an empty dataset in the wizard (creates an xsd-file in my project). Then I add a couple of tables in the database. After that I want to use my earlier created dataset. I mark the xsd-file and looks in Poperties. I get the error when I try to change Custom Tool value from 'MSDataSetGenerator' to 'MSResultSetGenerator'. Why????
I typically use DataAdapters and Datasets from the toolbox when connecting to a SQL database. Since switching to VS 2005, I am not able to find (or add) these items to the tool box for web site development. Any ideas on how I can get them back? Or is there a better alternative to retrieving the data so I can process it? Any help or suggestions would be appreciated.
Can anyone advise me on how to view the contents of a dataset in VS dotnet 2003..
I had filled a dataset from a query...But i don know what happens after filling the qry results.It might be helpful if someone tells me to view the contents of the dataset
I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?
Is there any possibility of combining data from two datasets to a single report item. If there is any thing that we can do, can any one please tell me what to do, so that my problem will be solved.
I have a table which has over 450,000 records in it, I have now split this into 4 so each table has around 100,000 records in it but I'm still having the problem of the data being returned really slowly.
What I need to do to this data is group it by a code and show the total for each code for every month of the year (this is currently based on one column and selecting the data accordingly). I have created views and put some indexes onto my table but the results are still being returned slowly. Does anyone have any suggestions of how I can speed this up?
I have a report with four different datasets that slice certain record by different days of the week. I can do the individual summing for each dataset by putting a , "datasetname" at in the expression. I also have 4 matrixes in the report that are grouped on certain transactions within each timeslice. My problem is that if I use the dataset name in the fields, the groups get ignored. Do I have to put the dataset name in each group expression? for example, here is my group expression...
Hello all, I have a report with a table and a chart. It uses dataset1 as the data source. All works fine. I create a new dataset called dataset2. The queries are exactly the same. The only differences between the 2 datasets is the database server and the fact that one of the columns is a smallint (in dataset2) and an int(in Dataset1) I change the datasetName property of both the table and the chart to use dataset2. When I run the report I get a conversion error stating that there was an overflow of int2 while using dataset1. I have verified the report is not using dataset1 anywhere. If I delete dataset1 and run the report the error goes away. If I add it back, I get the error again. Why is the report looking at dataset1 if it is not referenced at all in the report? Does SQL RS cache the datasets and verify each when it compiles?