Data Access :: What Is Correct Usage For Processing Data Adapter Rows
Sep 9, 2015
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String
Dim pw As String
Dim em As String, fn, ln, mi As String
Dim par As String
Dim Field, n, j As Integer
Dim JJ As Integer
I hope someone has seen this one...I've got a very simple web method that returns a DataTable from my database. It worked fine until I changed this line of code:
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? ORDER BY Timestamp desc";
To this line of code:
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";
Here is the complete webmethod:
[WebMethod] public DataTable GetConfession (int ConfessionID, int CategoryID) {
DataTable dt = new DataTable(); dt.TableName = "XMLConfession";
string SqlString = @"SELECT Timestamp, Confession FROM Confessions WHERE ConfessionID = ? AND CategoryID = ? ORDER BY Timestamp desc";
using (OleDbConnection cn = new OleDbConnection (ConnectionString)) { using (OleDbCommand cmd = new OleDbCommand (SqlString, cn)) {
return dt; } To cause the error, all I did was add the AND statement into the query. Now I get this .NET error message every time I try to run this program:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at System.Data.Common.UnsafeNativeMethods.ICommandWithParameters.SetParameterInfo(IntPtr cParams, IntPtr[] rgParamOrdinals, tagDBPARAMBINDINFO[] rgParamBindInfo) at System.Data.OleDb.OleDbCommand.ApplyParameterBindings(ICommandWithParameters commandWithParameters, tagDBPARAMBINDINFO[] bindInfo) at System.Data.OleDb.OleDbCommand.CreateAccessor() at System.Data.OleDb.OleDbCommand.InitializeCommand(CommandBehavior behavior, Boolean throwifnotsupported) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
I am using SQL Server Compact Edition 3.5 and C# .NET 2.0. It's worth noting that the web service does this outside of the program as well, so I don't think the problem is in the code that calls it. Also worth noting is that the query itself works fine when I substitute values in and run it in .NET Server Explorer.
I'm trying to figure this out I have a store procedure that return the userId if a user exists in my table, return 0 otherwise ------------------------------------------------------------------------ Create Procedure spUpdatePasswordByUserId @userName varchar(20), @password varchar(20) AS Begin Declare @userId int Select @userId = (Select userId from userInfo Where userName = @userName and password = @password) if (@userId > 0) return @userId else return 0 ------------------------------------------------------------------ I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value. But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0 passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters(); Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) ); Do you guys have any idea why?
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
I have used both data readers and data adapters(with datasets) in the projects that I have worked on. I am trying to get some clarification on when I should be using which one. I think I am doing this correctly but I want to be sure I am developing good habits.
As the name might suggest, it seems like a datareader is for only reading data. I have read that the data adapter and dataset are for a disconnected architecture. Or, that they can be used for this type of set up. I have been using the data adapter and datasets when writing to a database and the datareader when reading from a database.
Is this how these should be used? Is the data reader the best choice for reading data? Am I doing this the optimal way from a performance stand point?
......................................................thanks in advance
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
Ok, I know my connection string and config file are good. Once the rest of the code is added I receive this error: "SQL Server does not exist or access denied. " I'm using the 1.1 framework and my hosting company provides MSSql 2005. Thanks for your time, Charlie Here's the code in my cond behind file. using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient;
namespace blog { /// <summary> /// Summary description for WebForm1. /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Data.SqlClient.SqlConnection sqlConnect; protected System.Web.UI.WebControls.DataList dList; protected System.Data.SqlClient.SqlDataAdapter DA; protected System.Data.SqlClient.SqlCommand sqlSelectCommand1; protected System.Web.UI.WebControls.LinkButton linkAdmin; protected System.Web.UI.WebControls.Image header; protected blog.DS ds1;
#region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); }
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.sqlConnect = new System.Data.SqlClient.SqlConnection(); this.DA = new System.Data.SqlClient.SqlDataAdapter(); this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand(); this.ds1 = new blog.DS(); ((System.ComponentModel.ISupportInitialize)(this.ds1)).BeginInit(); this.linkAdmin.Click += new System.EventHandler(this.linkAdmin_Click); // // sqlConnection String //////////////////webserver//////////////// this.sqlConnection1.ConnectionString = "Server=xxx.xxx.xxx.x;Database=myDataBase;User ID=myID;Password=myPass";
//////////////////local/////////////////// //this.sqlConnect.ConnectionString = "workstation id=HAL;packet size=4096;integrated security=SSPI;data source=HAL;pers" + //"ist security info=False;initial catalog=blog"; // // DA // this.DA.SelectCommand = this.sqlSelectCommand1; this.DA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "blogEntry", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("bodyID", "bodyID"), new System.Data.Common.DataColumnMapping("linkID", "linkID")})}); // // sqlSelectCommand1 // this.sqlSelectCommand1.CommandText = "SELECT id, wxID, dateID, titleID, bodyID, linkID FROM blogEntry ORDER BY id DESC"; this.sqlSelectCommand1.Connection = this.sqlConnect; // // ds1 // this.ds1.DataSetName = "DS"; this.ds1.Locale = new System.Globalization.CultureInfo("en-US"); this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.ds1)).EndInit();
I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.
Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?
Suppose my Test table has a timestamp (rowversion) column, and I have the following code:
INSERT INTO Test ...
SELECT @@DBTS as 'TS'
Can I be sure the TS return column value contains the value of the timestamp column of the Test table? I suspect no in general (although it will work "most of the time"), but I would like a confirmation.
If I am correct, and there may be a mismatch, is there any clever way of getting the exact value, except from doing a SELECT WHERE ...?
Hi, Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.
The query plan is already cached in SQL.
Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. Â As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. Â My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.
E.g. If I have the following data:
Main input: Key Name --- ---- 1 Steve 2 Jamie 3 Donald
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.Â
I have a temp table with a column of all the same number. When I run my select statement I need it to bring back all the other data and just the single instance of the column with all the same numbers. How would I do this in my select statement?
For example, here is my table:
Here1One There1Two Up1Three Down1Four Back1Five
I need to bring back everything from column 1 and 3, but just the single instance of 1 from column 2 since it contains all the same number. All the data is collected in a single select statement.
I'm a newbie so I'll explain what I'm trying to achieve the best I can ...
I'd like to essentially loop through a SQL table to display the correct results. The workflow is the user query's the database and returns records (by property ID). In the return there are duplicate records being returned - in this case, two property owners returned with the same property ID.
How would I loop through the SQL statement in the application (code) to identify when the property id's are the same and display only one owner for that property?
Code Block SELECT tce.TimeCardID, tce.TimeCardExpenseID, tc.DateCreated, e.LoginID, e.FirstName + ' ' + e.LastName AS FullName, tce.ExpenseAmount, tce.ExpenseDescription, op.ProjectName, op.ProjectDescription, ec.ExpenseCode FROM OPS_TimeCards tc JOIN OPS_Employees e ON e.EmployeeID = tc.EmployeeID JOIN OPS_TimeCardExpenses tce ON tc.TimeCardID = tce.TimeCardID Join OPS_Projects op ON op.ProjectID = tce.ProjectID Join OPS_ExpenseCodes ec ON ec.ExpenseCodeID = tce.ExpenseCodeID WHERE e.LoginID = 'jross' ORDER BY tc.DateCreated DESC
this query returns me the correct data....but i need to tweak the query so it does not duplicate rows....My tce.TimeCardID is a PK in its table and so is TimeCardExpenseID...but the problem is U can have many TimeCardExpenseID's for one timecard so my results look like
TimeCardID TimeCardExpenseID 1 2 1 3 1 4
I want my query to return the "TimeCardID" but i just want that one ID to represent all the TimeCardExpenseID's...but i can not get it to work and have no clue....
Hello everyone. I need help regarding the following:Given the following table:CREATE TABLE T1 (C1 nvarchar(10), C2 money)INSERT INTO T1 VALUES ('A',1)INSERT INTO T1 VALUES ('B',2)INSERT INTO T1 VALUES ('C',3)let's say that i have this table in a local server and i want to uploadit to a remote server and in the remote server upload it to a databasethat contains the same table.the uploading part can be done by another application in the remoteserver, but i want i need is a way to transfer the data at the fastestpossible way.what steps do i need to follow?tia,Rey Guerrero
With SASS Database i have created Data mining Structure Using Time series algorithm, while processing the SSAS db, Data mining  taking long time to process, so how we can  reduce processing time ???
My question is how can I query tblTargetRatio table to return correct record for the following cases:
1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8) 2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7) 3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)
I'm working on a system right now where I have a database (two,actually, but one is discarded halfway through), but it's createdand used as part of a process (reporting), rather than as theactual production data repository. I may be keeping the databasepermanantly, but it would be completely read-only; once theprocess is complete, the database will not change again. This hasme wanting to do a few things that are rather foreign to my usualexperience, and I don't know how many of them are supported.In several cases, I'm summarizing one table into another by severalfields, and then updating the original table with an ID for thesummary row each source row was summarized into (e.g., I summarizePlaceAndProductSummary into PlaceSummary, and then populatePlaceSummaryID in PlaceAndProductSummary). The update of thesource table is much faster if the summary table has a clusteredindex on the summarized fields, but all later access will be fasterif the clustered index is on the identity column. I've beenincluding an ORDER BY the summarized fields in the original insert,so the identity column is in the same order as the summarized fields,but I don't know of any way to take advantage of that in theindexing declarations.As another approach to the above situation, if I change theclustered index on a table, and the rows happen to be in thesame order by both indexes, will the table still get rebuilt?I will never do a roll-back in the process; if an action fails, Iwant to raise an error and halt (and I haven't lost any data).Is there any way to completely turn off logging?Will I gain anything by marking the database as single-user?Any indexes that I am not using while I populate the tables, I'madding at the end with FillFactor 100, to keep any slack out.Is there a way to remove all the slack from everything else, atthe end of the process? During a backup operation would be fine.Thanks,Bill
We have an XML column in a SQL Server 2005 table. Each row of this table contains one XML document.
I want to shred values from the XML documents and process these within a Data Flow. I want the Data Flow to execute once across a record set comprised of all of the XML documents.
I can shred the XML using a For-Each loop and XML Task. I'm kinda stuck on how I then get the data from variables into a Recordset or similar so that I can process this within single iteration of a Data Flow.
Or - is my approach incorrect? I seem to be building a verbose and clunky solution to this problem. I know I could accomplish the same in a pretty simple SQL statement using .value on the XML column... am I missing something? Is a SQL query just better suited to this problem?
Hi, I have one column of data which is 15.678 but in the excel, i format it to 15.68 ( two decimal place, so in excel i should see 15.68), when i am trying to import the data from excel to sql server by using odbc connection, it still getting 15.678, how can i get the data from 15.678 to 15.68 ( what i see is wat i get). Thanks for help.
This probably has been addressed before but I was unable to get the search to work properly on this site. I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example : Row 1 Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc. Row 2 Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc. Row3 Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc. Row 4 Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc. Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
I want to capture and process data inside of a stored procedure by executing another stored procedure. If proc1 calls proc2 and proc2 returns 1 or more rows, consisting of 2 or more columns, what is the best way to do this?
Currently, I know I'm returning 1 row of 3 columns, so I concatenate the data and either return it with an OUTPUT parameter or using the RETURN stmt.
Do you have to set the Error Output on DF components to "Fail Component" in order to get the errors?
What I would LIKE to do is a combination of "Ignore Failure" and "Fail Component". You see, I am using the Logging feature in my package that creates the sysdtslog90 table in the SQL database. The errors that I am logging make sense and have enough information for my purposes.
The problem is that I would like to continue processing the data and not have it stop when a data error occurs. I REALLY do not want to Redirect Rows unless it is necessary for me to do what I am asking.
Using Ignore Failure on both the source text file and destination SQL table allows the "good" data to be inserted, but I cannot get any info on the columns in error. Conversely, if I choose to Fail component, I get the info on the columns in error, but only the data that was inserted before the error was encountered is inserted into the table.
hi here´s a new one..... i´ve created a dts that is using an odbc source to connect to an oracle server, the conexion works just fine and i have no problems with it, then, i run this package from mi computer and the data transfer ends succesfully, when i upload it to my server in SQL 2005 and set it into a job.. i get errors like these:
Event Name: OnError Message: Thread "WorkThread0" has exited with error code 0xC0047039.
Event Name: OnError Message: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Event Name: OnError Message: The PrimeOutput method on component "table" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Event Name: OnError Message: The component "faccomitecedidos" (1) was unable to process the data.
Event Name: OnError Message: The "component "table" (1)" failed because error code 0x80131541 occurred, and the error row disposition on "output column "diasperm" (1707)" specifies failure on error. An error occurred on the specified object of the specified component.
why is this happening , any solutions or ideas? the data i want to extract comes from an sql command not a table this is the query:
SELECT FCNSS , FITIPOSOLICITUD , FIFOLIO , RTRIM(FCNOMBRE)||' '||RTRIM(FCAPPATERNO)||' '||RTRIM(FCAPMATERNO) AS NOMBREAFILIADO, fdfinicta as FAFIL , fdFecCedido as FCED , (fdFecCedido-fdfinicta) AS diasperm , FNSALARIOACTUAL AS SALAFIL , (SELECT DISTINCT FISDI FROM gentec_own.AFILCEDIDOS WHERE FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS salCED , (SELECT DISTINCT (FISDI/48.60) FROM gentec_own.AFILCEDIDOS WHERE FIFOLIO = gentec_own.faccomite.FIFOLIO AND FITIPOSOLICITUD = gentec_own.faccomite.FITIPOSOLICITUD AND FCNSS = gentec_own.faccomite.FCNSS AND ROWNUM = 1) AS CalSalCED , FCNUMPROMOTOR AS cod_promotor, (SELECT RTRIM(FCNOMBRES)||' '||RTRIM(FCAPEPATERNO)||' '||RTRIM(FCAPEMATERNO) FROM gentec_own.prommaestro where FCNUMPROMOTOR = gentec_own.faccomite.FCNUMPROMOTOR AND ROWNUM = 1) AS NOMPROMOTOR, FICVEENTCED as aforeorig , FCAFORECEDIDO as aforeced , FNINGCOMT , FNCTOPROMOCION , FNCTOADMON , FNCONTRIBUCION , fcCanal as Canal , FCDIVISION as Division , FCREGION as Gerencia FROM gentec_own.FACCOMITE WHERE FCCEDIDO = 1 and (to_char(fdFecCedido,'yyyymmdd')>=to_char(sysdate-8,'yyyymmdd') and to_char(fdFecCedido,'yyyymmdd')< to_char(sysdate,'yyyymmdd')) order by fdFecCedido;
I'm writing a Data Processing Extension for SSRS 2005 that allows you to use a WSS 3.0 List as a dataset. This works for one list per dataset. Now I am trying to extend the extension to allow multiple lists in a dataset because I want to join lists on a common field (INNER JOIN). The problem is that I can only return one DataTable to the Report Server with the DataReader. Does anyone know if it is possible to return multiple DataTables (without the join)?
I'm a student, working on this project. I know Visual Basic.net but only started last month with Data.
