DataSource Controls :: How To Read Data Values Of A Table Using Loop In Sqlserver
Feb 10, 2010
I am using vs2008, Sqlserver2008.
my table: PostGL having data like thi
AutoIdx TxDate Id AccountLink Description Debit Credit
3 2010-01-22 JL 2 bcb 0 35.09
5 2010-01-28 JL 2 g 3.51 0
select AutoIdx,TxDate,Id,AccountLink,Description,Debit,Credit,(Debit-Credit) as Actual from PostGL where Id='JL' and AccountLink=2
AutoIdx TxDate Id AccountLink Description Debit Credit Actual
3 2010-01-22 JL 2 bcb 0 35.09 -35.09
5 2010-01-28 JL 2 g 3.51 0 3.51
I am trying in view like this
declare @cnt int
declare @i int
set @budget=20
set @cnt= (select COUNT(*) from PostGL where AccountLink=2 )
set @i=1
WHILE (@i<=@cnt )
BEGIN
set @tdebit=??
END
I am taking count the no of rows having AccountLink=2
I need to read all debit,credit and add all debit into totaldebit,all credit into totalcredit independently from table.
then i need to show the totaldiff as Actual.
i need ....totaldiff=totaldebit-totalcredit
View 3 Replies
Similar Messages:
Feb 18, 2010
i am Using sql server 2005. i want to copy the data of the excel file to the table.
i wrote the query like :
INSERT INTO test(empId,empName,empMailId,empContactNo)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C: estExcel.xls',
'SELECT * FROM [Sheet1$]')
got the following error :
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
then did this
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
again am trying the same above query ,now am getting the following error:
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
i want to copy the data of the excel sheet to the table.
View 2 Replies
Mar 9, 2010
I am using asp.net3.5,sqlserver2008. I have retrieving data from Accounts,BUDGETS like this.
select a.AccountLink,a.Master_Sub_Account,b.* from Accounts a inner join BUDGETS b
on a.AccountLink=b.GLLink where a.AccountLink=1
output of my query...
AccountLink Master_Sub_Account GLLink AccType Budget01 Budget02 Budget03
[Code]....
View 1 Replies
Apr 10, 2010
I'm using vwd 2008 express
I need to display a single field from an sqlserver table, do I need to use sqlDataSource ect. or is there a simpler way (like msaccess's LookUp) for single values.
View 1 Replies
Mar 19, 2010
im learning t-sql, infact i needed its while-loop to loop through records of a table, i found this code in some book:
[Code]....
i understand here that setting the rowcount variable to 1 will force the select statements to return only one record, do all above select statements return one record only?
if the underlined select statement returns one record everytime it is executed, isn't it supposed to return the same first record of the table everytime? is there a better way to loop through a table's records?
View 9 Replies
May 18, 2010
Say for example, I have script like below whereby everything is same except the table name and each table name will append an incremental number, is it possible for me to have a for loop to dynamically add the incremental number?
CREATE TABLE Persons1
(
P_Id int,
[code]...
View 5 Replies
Feb 8, 2010
Here is my code, using LinQ to get the data from database.
public IEnumerable categoryData()
{
db = new DBModelDataContext();
var cat = from c in db.Categories
select new { c.categoryname,c.categoryid };
return cat;
}
trying to get the values (categoryname and categoryid) into textfiled and valuefield of dropdown list.
dt = new clsData();
ddlCategory.DataSource = dt.categoryData();
what is the code that i need to write to assign the categoryname to textfield and categoryid to valuefield. I mean how to loop through the values coming from the above function.
ddlCategory.DataBind();
View 2 Replies
Mar 24, 2011
How do I loop the gridview to read or insert one row by one row?I have a database which has a Primary key. Currently,all the rows seems to appear at one time.Even when I use a counter,it did read one row from a textbox(which is for my testing to read one row) but in the database,it still inserts the whole rows from the gridview.I can't click button twice as it will occur an error "Primary Constraint".
View 6 Replies
Jan 27, 2010
Is there a way to copy a row from one table and insert to another but at the same time insert extra values to destination table?Let's say source table Item has values:
ItemID
ItemType
ItemProducer
ItemModel
Destination table ItemHist:
ItemID
ChangeDate
ChangeReason
ChangedBy
ItemType
ItemProducer
ItemModel
I want to copy row from source table where itemid is 25, insert it into destination table and add extra values: ChangeDate, ChangeReason, ChangedBy. I tried:
INSERT INTO ItemHist (ItemID, ChangeDate, ChangeReason, ChangedBy, ItemType, ItemModel) VALUES (ItemHist.ChangeDate=GetDate(), ItemHist.ChangeReason="why not", ItemHist.ChangedBy="me")
SELECT ItemID, ItemType, ItemProducer, ItemModel FROM Item WHERE ItemId=25
but obviously it didn't work ("The name 'ChangeDate' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.").
View 4 Replies
Mar 23, 2010
I would like to be able to read the first row of a table and then each successive row sequentially individually. So, is there a way to read the first row of a table, whatever the primary key may be, and then read the next sequential row in the table until the last row has been read? I need to be able to do this in order to create a list of column entries in each row for a listbox control that a user can select from.
View 3 Replies
Jun 17, 2010
[Code]....
My question is how do i use SqlDataReader to read the last row added to a table getting the contents of a column and storing it as a c# varaible
View 2 Replies
Jun 14, 2010
i am trying but i am unable to do this one.
View 2 Replies
Apr 10, 2010
I have been able to successfully read a row of data from an SQL table using two column parameters. Now I would like to update this row with new information. Is this possible since the reader does not specify which row was read with the 'reader.Read();' command?
View 7 Replies
Jan 28, 2010
The below query produce the below output.
[code]...
select r.Name,c.Name,s.Name,(select count(p1.id)from profiles p1 where r.id=p1.religion and c.id=p1.caste and s.id=p1.subcaste and p1.Gender=1)as Male,(select count(p1.id)from profiles p1 where r.id=p1.religion and c.id=p1.caste and s.id=p1.subcaste and p1.Gender=2)as Female from religion r,profiles p,caste c,subcaste s where r.id=p.religion and c.id=p.caste and s.id=p.subcaste group by r.Name,c.Name,s.Name,r.id,c.id,s.id
View 3 Replies
Jun 16, 2010
I want use select * from sample (tablename) ,which returns only those columns from sample table which does not have xml data type.
note : sample table contains some columns which have xml data type .
View 6 Replies
Apr 16, 2010
i am unable to handle null values in while .even i tried to handle condition is breaking and coming null value comming inside loop.
[code]....
View 3 Replies
Sep 15, 2010
I have the following code to generate table from User inputs for ROWs and COLUMNs and i Have created a new array. I need read the text value from table , convert it to int and save it in the array.
Table tbl = new
Table();// Add the table to the placeholder control
PlaceHolder1.Controls.Add(tbl);
// Now iterate through the table and add your controls
[Code].....
When I run this It throws an error that - Input string was not in correct format
View 9 Replies
Sep 13, 2010
How can I make the following code shorter and more efficient.
I want to loop through the check box list for all true values.
I want to get the check list item and complare with database value then check the box.
[code]....
View 4 Replies
Jun 9, 2010
how can we store the value which i entered in a textbox to table dynamically.
View 3 Replies
Jun 30, 2010
I have a table with a lot of records - it has a column called CallID I have another table with no records, but one that also has a CallID column I want to insert all the values from the CallID column in the first table into the CallID column of the second table.
View 6 Replies
Feb 6, 2010
I am creating an online employment application, and instead of creating a table with 20 columns for job duties that may or may not receive values, I simply want to create 3 columns
ApplicantID
EmployerName (Name of Work history Employer)
JobDuty
I want to create a form on a page with 10 textboxes so the applicant can type in some job duties they had at that job.
Is there a way in C# or SQL to then cycle through and insert the textbox values as separate rows?
View 4 Replies
Jun 1, 2010
in my button4 click event, i want to insert checboxlist items to data table. first i want to delete all of the exisiting and then insert again.
my code is below but it is not working if you could explain anyone ?
in page load I have written code so that existing checkbox items are selected.
[code]...
View 5 Replies
Jan 31, 2011
I am dealing with dynamic fields in a for loop and now I need to retrieve the values that are being typed in the dynamic fields, any idea how can I do that? 2)also when I click the submit button, upon postback, the dynamic fields have gone missing, have been trying to slove that, but i did not, anyone knows how to deal with this? kinda urgent :x
3) and How do you compare the 2 dynamic fields? For eg: Im comparing the tbSch[i] to tbSch[i], so that the dates cannot be the same, am using a compare validator, but it throws me error that im comparing the same fields :) even though i change to ("tbSch" + i) for both, sorry Will provide my code snippet as follow :D tbNum is a textbox where user enter numbers and click generate
protected void btnGen_Click(object sender, EventArgs e)
{
for (int i = 0; i < Convert.ToInt32(tbNum.Text); i++)
{
// label and textbox needed
Label lblSch = new Label();
TextBox tbSch = new TextBox();
Label lblWsGrp = new Label();
DropDownList ddlWsGrp = new DropDownList();
CompareValidator cvSame = new CompareValidator();
cvSame.ID = "cvSame" + i;
cvSame.ErrorMessage = "Date cannot be the same";
cvSame.Operator = ValidationCompareOperator.NotEqual;
cvSame.Type = ValidationDataType.Date;
cvSame.Display = ValidatorDisplay.Dynamic;
cvSame.EnableViewState = true;
cvSame.ControlToValidate = (tbSch.ID);<<<<< what should be the value in here?
cvSame.ControlToCompare = (tbSch.ID);<<<<<<<<<< and here?
//schdule date
lblSch.ID = "lblSch" + i;
lblSch.Text = "Session Date : ";
lblSch.EnableViewState = true;
tbSch.ID = "tbSch" + i;
tbSch.Width = 210;
tbSch.Height = 27;
tbSch.EnableViewState = true;
//workshop grp
lblWsGrp.ID = "lblwsgrp" + i;
lblWsGrp.Text = "Workshop Group : ";
lblWsGrp.EnableViewState = true;
ddlWsGrp.ID = "ddlWsGrp" + i;
ddlWsGrp.Height = 27;
ddlWsGrp.Width = 210;
ddlWsGrp.EnableViewState = true;
ddlWsGrp.Items.Add("1");
ddlWsGrp.Items.Add("2");
//table to store data
TableRow trow = new TableRow();
TableRow trow2 = new TableRow();
//row1
TableCell tc1 = new TableCell();
TableCell tc2 = new TableCell();
TableCell tc3 = new TableCell();
TableCell tc4 = new TableCell();
//row2
TableCell tc5 = new TableCell();
TableCell tc6 = new TableCell();
TableCell tc7 = new TableCell();
TableCell tc8 = new TableCell();
TableCelltc9 = new TableCell();
tc1.Controls.Add(lblSch);
tc2.Controls.Add(tbSch);
tc3.Controls.Add(lblWsGrp);
tc4.Controls.Add(ddlWsGrp);
tc5.Controls.Add(space);
tc6.Controls.Add(CeSess);
tc7.Controls.Add(space);
tc8.Controls.Add(space);
tc9.Controls.Add(cvSame)
trow.Cells.Add(tc1);
trow.Cells.Add(tc2);
trow.Cells.Add(tc3);
trow.Cells.Add(tc4);
trow2.Cells.Add(tc5);
trow2.Cells.Add(tc6);
trow2.Cells.Add(tc7);
trow2.Cells.Add(tc8);
trow2.Cells.Add(t9);
this.table3.Rows.Add(trow);
this.table3.Rows.Add(trow2);
}
}
View 11 Replies
Jan 16, 2010
I need to fix the table so values are stored ok.
Now I think I should have the values of music feature set by default to 0
What should I modify in the below table. as well as profileid should be a foreign key constraint.
[code]....
View 1 Replies
Jan 5, 2010
how to check if the particular values in a table exists?
if it is not existing i need to insert that values in the table?
View 4 Replies
