DataSource Controls :: Data Retrieving And Checking Queries?
Jul 28, 2010
I'm currently working on a FOREX Live Trading project that I'm implementing.
I already have a database that stores the CURRENCY PAIR, BUY/SELL, PRICE AND QTY.
But I have a huge problem.
Scenario that is:
A person BUYS EUR/USD at a PRICE of 1.29900 @ quantity 100,000. (This will be recorded in the database table)
Then he BUYS EUR/USD at a PRICE of 1.29950 @ quantity 100,000. (This will then be recorded in the database also)
Then he decides to SELL his EUR/USD at a PRICE of 1.31000 @ quantity 200,000 all out. (How can I check from the database if he previously had make a transaction of BUY/SELL, so that I can do my necessary calculations for the P/L Statement)
I am having trouble with queries because they are not returning the data needed, and I am having trouble figuring out why because I only am testing with 2 records.First I want a query to return all records for one customer as follows:
[Code]....
There are two records with the customerID of 1, but it only returns one,next I try to return only one record, the AccountPrimary or customer_contacts.IsPrimary, but when I add " AND customer_contacts.IsPrimary = 'True' "at the end of my query it then returns both records, but only one IsPrimary = True and the Other = False. Is it backwards? I also tried another method and it returned 30 of the same record and I only have 2
I have a gridview that pulls data from two tables, CLIENT and PAYMENT. I want to add the edit, delete functions to the gridview for the payment portion of the table, that is to edit or delete the payments but not the client. I've manually enabled the edit, delete functions and input the queries. I've also deleted the textboxes from the templates which draw from the CLIENT table so they cannot be edited. Everything looks good on the surface until I actually go to edit or delete anything, and it has no effect.Here's the structure of the two tables:
UPDATE [PAYMENT] SET [PaymentDate] = @PaymentDate, [PaymentAmount] = @PaymentAmount, [PaymentDetails] = @PaymentDetails WHERE [PaymentNumber] = @original_PaymentNumber AND [CLIENT$ClientID] = @original_CLIENT$ClientID AND [PaymentDate] = @original_PaymentDate AND [PaymentAmount] = @original_PaymentAmount AND (([PaymentDetails] = @original_PaymentDetails) OR ([PaymentDetails] IS NULL AND @original_PaymentDetails IS NULL))
Finally, here's the delete query:
DELETE FROM [PAYMENT] WHERE [PaymentNumber] = @original_PaymentNumber AND [CLIENT$ClientID] = @original_CLIENT$ClientID AND [PaymentDate] = @original_PaymentDate AND [PaymentAmount] = @original_PaymentAmount AND (([PaymentDetails] = @original_PaymentDetails) OR ([PaymentDetails] IS NULL AND @original_PaymentDetails IS NULL))
I'm new to asp.net, php had this function that could store sql date in an array, I can't find something similar in .net, so this is what I came up with so far.
Sub Compareid() Dim queryString As String = "Select top 2 id from my_videos order by id desc" using connection As New SqlConnection(ConfigurationSettings.AppSettings("sqlacct")
I have hard time trying to find the solution to retrieve data from multiple tables in one database.
table Kategorije has fields: idKategorija, naziv, opis table Clanak has fields: idClanak, idKategorija, naslov, autor,...
Kategorije.idKategorijais joined with Clanak.idKategorija, but that isn't the solution because I want my web site to show the Kategorije.naziv instead of Kategorije.idKategorija. This is the code I'm currently using to get idKategorije shown on my web site:
but i have a problem to retrieve value from the database in the textbox using 3-tier application. ]
in the App_data i have following folder.
1> employeeBAL in this i have 1class file called empbasicinfoBLL.cs
2> employeeDAL in this i have test_data.xsd
in the basic details folder i have one page Basic information(employee basic info) in this i want that when user will log in the site [using username and password] after successful login when user will click on the Basic information then he will able to see his [name, employee no, first name, last name] etc on the page [i don't want to use grid-view to retrieve the details becoz i am using the table in this i am using Lable and Textbox for each] and this data should come from the EmployeeBAL.
Having trouble with a web page and connecting to SQL DB. Basically I have a form view table connected up to a SQLDataSourceControl and what I want displayed in the form is only data that has been assigned to the user that is logged on now. I have a "ViewAll" page, which lists EVERY field, regardless of who it is assigned to, but the "MyTasks" page should only retrieve the data of the current user.
I have two tables in my DB, the "Users" table, with user data (including an ID number) - this is used again in the "Tasks" table as a foreign key called "AssignTo". Therefore if user Joe Bloggs with ID number 5 logs on, whenever someone assigns a task to him, they will select number 5 in the "Task" table, as this relates to his User ID.
Is there anything special you have to do to retrieve Filestream data using a SqlDataSource? I'm using VB and have this as my sqldatasource:
<asp:SqlDataSource ID="SqlDataSourceReadPD" runat="server" etc... SelectCommand="SELECT [PDIPAttachment] FROM [PositionDescriptions] WHERE ([CP_IDString] = @CP_IDString)"> <SelectParameters> <asp:Parameter Name="CP_IDString" Type="String" /> </SelectParameters> </asp:SqlDataSource>
PDIPAttachment is a FILESTREAM column in my table called PositionDescriptions
and this is some of my VB cod:
Dim dv As DataView = DirectCast(SqlDataSourceReadPD.Select(DataSourceSelectArguments.Empty), DataView) Dim dr As DataRowView For Each dr In dv Dim data as Byte() = System.Text.Encoding.ASCII.GetBytes(dr("PDIPAttachment").ToString()) Next
The Filestream data has a PDF file in it, but what I"m getting back in "data" are 13 bytes consisting of: "System.Byte[]".
i am currently facing some problem retrieving image for display on a gridview control. the Image data type in the data table is "image". the data that is stored inside the database is in this format "<Binary data>". may i ask if it is possible tojust retrieve a image data of this format and display in the gridview? or do i need to store the image in a image folder for retrieval?
SELECT 1000000*QuantityNC/Quantity AS PPM FROM [table1] However I need my WHERE clause to do thisI need the above calculation depending on BusUnit, which is linked to PartNumber, which is in the above table. also it has to be by Company (which is selected from a dropdown). And also based on the current month and year.
PartNumber and CompanyName are in [table1] BusUnitID, PartNumber, SupplierID, are in tblParts BusUnitID and BusUnit are in tblBusUnits
I need to fire sql queries in my website and get the results from the Database. for example: I will give select * from emp in a text box. It shud return the results for me. DO any of u have a sample/links of such application, through which i will be able to implement in my application share me the links/sources/app if u have any.
i have a database and binded it to a drop down list to show all the IDs on page_load. This is working fine
i have placed two textboxes and one button on my same form. when i chose a specific ID from my drop down list and click on the button the textboxes should show the respective data(name and gender). But when i click on chose an id and click the button it show datatype error...still it did not
I get from SQL Server Server Data. I need to use datas form 2 colums of my SQL query for a VB.NET. The VB.NET need 2 values for both colums and produce 1 answer. I what to display the value on the same line instead of the 2 1st values. How can i store the 2 colums wich lot of SQL values on my vb.net script and diplay the result with the other data from the SQL query ?
I know there has to be a way to do this but for some reason I keep getting it wrong. I have one query which pulls all of the cities from a zipcode database like so:
[Code]....
Then I have another which counts the records from a transactions table per city, like so:
[Code]....
Only the zipcode is stored in the transactions table, so the idea here is to get a list off all the distinct cities that reside in the state. Once I have the cities, I want a count for each city as a whole, so I need to go back in and get all of the zipcodes that make up one city, and then count the transactions for those zipcodes. It works using the first query above, looping through them and then executing the second for every pass. But shouldn't there be a way to do it all in one query?
I am planning a project for a pharmacy to enter quality control data to generate reports and graphs with Crystal Reports. There are 20 stores, I need to password protect the entrance page, giving each store their own username and password (just one per store is fine), allowing the store to only access that store's data. Each store will have an associated ID in the MSSQL table.I am using the Membership Provider. I know I need to implement user roles for Managers and Stores levels (Managers can run reports for all stores), I wonder if I can set the default store ID for insert/edit templates after that specific store logs in.Basically, I think I need to filter SQL queries based on the store ID associated with a user ID. I have seen a .NET site that, after logging in, you type in a store id into a textbox, and it gives you access only to that stores data. Something like this would work for my situation.Can anyone explain or show me an example for the best method to accomplish this with C#?
I am working on asp.net 3.5 and in MVC using Linq to SQL classes. In my MVC project i am calling stored procedure using linq to sql that conatin query joining two tables.Below is my code in Partial class
[ Function(Name="dbo.GetUserWiseThreadDetail_SP")] [ResultType(typeof(Thread))] [ResultType(typeof(Reply))] public IMultipleResults GetUserWiseThreadDetail_SP([Parameter(DbType="VarChar(30)")] string userID) { ExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)(MethodInfo.GetCurrentMethod())), userID); return ((IMultipleResults)(result.ReturnValue)); }
In one of the controller i have made one more class
public class MypostIndexData { public IEnumerable<Thread> Thrds { get; set; } public IEnumerable<Reply> Repls { get; set; } public MypostIndexData(IEnumerable<Thread> thrds,IEnumerable<Reply> repls) { this.Thrds=thrds;this.Repls=repls; } }
finally coding of controller action method
ForumDataContext pmforum = new PMForumDataContext(); public ActionResult MyPosts(string userId) { IEnumerable<Thread>thrds; IEnumerable<Reply>repls; IMultipleResults result = pmforum.GetUserWiseThreadDetail_SP(userId); (this satement doesnot give any result,if i execute stored proc in sql it give the results) thrds = result.GetResult<Thread>(); List<Thread> thrdlist = new List<Thread>(thrds); repls = result.GetResult<Reply>(); List<Reply> replylist; if (repls != null) { replylist = new List<Reply>(repls); } return View(new MypostIndexData(thrds,repls));
I am unable to understand why i am not getting any results?
I have a transaction that has 5 update statements.I am checking that the data is the same as it was when i selected it before i change it, as this will be from a web app that many people could update.
I want to check that the rowcount is 1 everytime i make an update, if it is one then I want to move on, if not I want to rollback and return.
Here is what I have, only it it rolling back on the first attempt everytime.
[Code]....
I am not sure if rowcount is available yet, if not what do I need to use to find out if anyrows are being updated before moving on?
I am accepting query/queries from user (our support team) in a text box where user can enter only one query or multiple queries. I need to display result of all queries entered in the textbox. If it is SELECT statement then result of that statement in grid which is done. If UPDATE/INSERT/DELETE then total number of rows affected which is also done but if only one UPDATE/INSERT/DELETE statement is entered in the textbox.
If user enters 2 UPDATE statements and then a SELECT statement then how can I get number of rows affected for individual UPDATE statements just like SQL Server Qury Analyzer displays messages in its result pane.
e.g.
(6 row(s) affected) -- first UPDATE statement
(4 row(s) affected) -- second UPDATE statement
(16 row(s) affected) -- for SELECT statement (grid will also be displayed along with these messages).
I tried almost everything, SqlDataSource (returns count of first statement only), SqlCommand.ExecuteNonQuery and DataAdapter.Fill (returns count of last statement only).
In general terms, which way will require more server processing power and which way will return faster queries... if there is even a difference. The client's database is still on the dev MS SQL server but is essentially but the client has decided they want to collect two more fields of information. Both fields are going to be collection text data and probably varchar(300) will be sufficient for the column data types. Would it ultimately be more efficient to
Add two additional columns in the main table so the data is entered directly into each record, or Add an additional table (or two) with a foriegn key to the primary table that holds the info for each new field The SELECT statement for the main table could be modified to either add the new columns (a.NewColumn1 and a.NewColumn2) or it could contain nested SELECT statements that pull in the data from the other tables. Custom daisy-chained Insert or Update statements would be no problem and a cascade Delete relationship would take care of any deletions. I'm just trying to figure out which would be better from a performance standpoint?
