Working With DataAdapter Values

Aug 14, 2007

 I have done some reading on the dataadapter class,  but do not have a good handle on how to work  with dataadapter after it has been filled by a dataset.  For instance,  should I use an if statement such as this to update the value in the dataadapter?
 
if (dsSunbelt.Tables[0].Columns[39].Equals(""))
        {
            DataRow drUpdate = dsSunbelt.Tables[0].Columns[39].Equals(DateTime.Now);
        }
        
I am sure it is not hard to see what I am doing, and I hope that I am missing something small, but how do I isolate a dataadapter value to update in the corresponding database? 

View 2 Replies


ADVERTISEMENT

Multi-value Parameter Default Values Not Working

Jul 12, 2007



I have a parameter that gets it's available values from a dataset.



I use this exact same data to populate the default values.



When I run the report, the available values get populated; however the default values are not being selected.



This works on other parameters on the same report. However, on this parameter, it is not working.



I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...



Here is my query:




Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@ClientID)

Order by CityName





The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.



I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.



Any ideas??

Thanks!!



Thanks

View 3 Replies View Related

Comparing Values In Two Tables In Order To Do Insert -- Comparision Is Not Working!!

Jul 23, 2005

I have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table.  Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set.  Thanks in advance everyone.  Regards,RB

View 1 Replies View Related

DataReader And DataAdapter

Feb 28, 2007

Hi,    What is the difference b/w sqldatareader and sqldataadapter? For what purpose are they used in a database connection & how do they differ from each other? Pls explain me in detail.Regards Vijay.

View 1 Replies View Related

DataAdapter Does NOT Load Relations From DB

Oct 11, 2006

Why DataAdapter does NOT load relations from DB ?the relations are made using SQL SERVER MANAGEMENTbut when a fill a datatable using dataadapter the relations are not loaded .why ? what is the solution to this problem ?

View 1 Replies View Related

Data Binding-DataAdapter

May 17, 2007

Hi i'm a new to ASP.NET and for some reason when i click the Next button in the code below, the pageIndex does not change. Please assist, Basically what i'm trying to do is to use DataAdapter.fill but passing in the start index and the number of records to pull from the dataset table.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
public partial class Home : System.Web.UI.Page
{
//ConnectionOleDbConnection dbConn;
//discount that can be change by user using a gui interface
//CurrentPageint pageIndex = 0;double discount = 0.15 ;
 protected void Page_Load(object sender, EventArgs e)
{
// homeGridView.Visible = true;
 
BindList();
 
 
}protected string getSpecial(string price,object sale)
{String special = "";if (sale.ToString().CompareTo("True") == 0)
{special = String.Format("{0:C}",double.Parse(price) * (1-discount));
}return special;
}
protected void BindList()
{
//Creating an object for the 'PagedDataSource' for holding the data.
 
//PagedDataSource objPage = new PagedDataSource();
try
{
//open connection
openConnection();
//sql commandstring columns = "*";
string SqlCommand = "Select " + columns + " from Books";
//create adapters and DataSetOleDbDataAdapter myAdapter = new OleDbDataAdapter(SqlCommand, dbConn);DataSet ds = new DataSet("bSet");
 
//create tableDataTable dt = new DataTable("Books");myAdapter.Fill(ds, pageIndex, 9, "Books");
 
Response.Write("Page Index: "+pageIndex);
//create table data viewDataView dv = new DataView(ds.Tables["bTable"]);
booksDataList.DataSource = ds;
booksDataList.DataBind();
 
myAdapter.Dispose();
dbConn.Close();
}catch (Exception ex)
{
 Response.Write("Exception thrown in BindList()");
dbConn.Close();throw ex;
}
 
}
 
 public void openConnection()
{string provider="Microsoft.Jet.OLEDB.4.0";
string dataSource = "C:/Documents and Settings/Owner/My Documents/Visual Studio 2005/WebSites/E-BookOnline/App_Data/BooksDB.mdb";dbConn = new OleDbConnection("Provider =" + provider + ";" + "Data Source =" + dataSource);
dbConn.Open();
}protected void nextClick(object sender, EventArgs e)
{
pageIndex=pageIndex+1;Response.Write("In nextClick"+pageIndex);
BindList();
}protected void prevClick(object sender, EventArgs e)
{if (pageIndex > 0)
{
pageIndex=pageIndex-1;
BindList();
}
}
}
 
 

View 1 Replies View Related

DataAdapter Update Problems

Sep 25, 2007

Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows"
I've looked and I can see what the problem is, can anybody help please?
 Private Sub FillData()
'SQL connection
Me.sqlconnection = New System.Data.SqlClient.SqlConnection
Me.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")

Dim sql As String
If Request("action") = "gen" Then
sql = "select item from tblPickList where GroupName = 'xxx'AND Category = '" & Category2 & "' ORDER BY item ASC"
ElseIf Request("action") = "fav" Then
sql = "select identifiertext as item from tblfavourites where HRID = " & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC"
ElseIf Request("action") = "spec" Then
sql = "select item from tblPickList where GroupName = '" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC"
End If

ds = New DataSet
da = New SqlClient.SqlDataAdapter(sql, sqlconnection)

Try
sqlconnection.Open()
da.Fill(ds, "tblPickListTemp")
sqlconnection.Close()
Catch ex As Exception
sqlconnection.Close()
End Try

ds.Tables(0).Columns.Add("HRID")
ds.Tables(0).Columns.Add("Flag")
'may possibly add a favourites column here at a later date to ensure no duplication

Dim dr As DataRow
For Each dr In ds.Tables("tblPickListTemp").Rows
dr("HRID") = Session("hrid")
dr("Flag") = 0
Next

DgrdFavourites.DataSource = ds
DgrdFavourites.DataBind()
End Sub

Public Sub CreateTempRecords()

'SQL connection
Me.sqlconnection = New System.Data.SqlClient.SqlConnection
Me.sqlcommand = New System.Data.SqlClient.SqlCommand
Me.da = New SqlClient.SqlDataAdapter
Me.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")

Dim sql As String
sql = "Insert into tblPickListTemp (Item, HRID, Flag) values (@Item, @HRID, @Flag)"
sqlcommand.CommandText = sql
sqlcommand.Connection = sqlconnection

sqlcommand.Parameters.Add("@Item", "Item")
sqlcommand.Parameters.Add("@HRID", "HRID")
sqlcommand.Parameters.Add("@Flag", "Flag")
da.InsertCommand = sqlcommand
da.Update(ds.Tables(0))
End Sub 

View 10 Replies View Related

Fill DataAdapter With DATATABLE

Jan 31, 2008

I'm reading XML data into a DataTable that is populated into a datagrid.  From here I need to update database and wanted to know how to Fill a DATA ADAPTER with a DATA TABLE?
I'm familiar with updating using a SQL command, but in this case, i have the dataTable already created from XML.
Please help.
Thanks,

View 3 Replies View Related

Error On DataAdapter.fill

Apr 11, 2006

i have this code :

if(! IsPostBack)
            {
           
    string strConnection = "server=localhost; uid=sa;
pwd=sasasa; database=northwind";
                string strCommand = "Select * from Customers";

           
    SqlDataAdapter dataAdapter = new
SqlDataAdapter(strCommand, strConnection);

                DataSet dataset = new DataSet();

                dataAdapter.Fill(dataset, "Products");
           
    SqlCommandBuilder bldr = new
SqlCommandBuilder(dataAdapter);

                DataTable dataTable = dataset.Tables[0];
                dgCustomer.DataSource = dataTable;
                dgCustomer.DataBind();
            }

when i run this code, error like this appear :


Server Error in '/Registeration' Application.


SQL Server does not exist or access denied. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.Source Error:



Line 41: DataSet dataset = new DataSet();Line 42: Line 43: dataAdapter.Fill(dataset, "Products");Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);Line 45: Source File:
e:asp.net
egisteration
egister.aspx.cs    Line: 43
Stack Trace:



[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) Registeration.WebForm1.Page_Load(Object sender, EventArgs e) in e:asp.net
egisteration
egister.aspx.cs:43 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()

Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573

how can i solve this problem??? thank you...

View 1 Replies View Related

Error In DataAdapter.Fill

Apr 11, 2006

i have this code :

private void Page_Load(object sender, ....)
{

if(! IsPostBack)

            {
           
    string strConnection = "server=localhost; uid=sa;
pwd=**secret**; database=northwind";

                string strCommand = "Select * from Customers";


           
    SqlDataAdapter dataAdapter = new
SqlDataAdapter(strCommand, strConnection);



                DataSet dataset = new DataSet();



                dataAdapter.Fill(dataset, "Products");
           
    SqlCommandBuilder bldr = new
SqlCommandBuilder(dataAdapter);



                DataTable dataTable = dataset.Tables[0];

                dgCustomer.DataSource = dataTable;

                dgCustomer.DataBind();

            }
}



when i run this code, error like this appear :




Server Error in '/Registeration' Application.


SQL Server does not exist or access denied. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.Source Error:



Line 41: DataSet dataset = new DataSet();Line 42: Line 43: dataAdapter.Fill(dataset, "Products");Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);Line 45: Source File:
e:asp.net
egisteration
egister.aspx.cs    Line: 43
Stack Trace:



[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) Registeration.WebForm1.Page_Load(Object sender, EventArgs e) in e:asp.net
egisteration
egister.aspx.cs:43 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()

Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573



how can i solve this problem??? thank you...

View 1 Replies View Related

Update Dataadapter With Text Box Value

Sep 16, 2004

hi

this is probably a piece of pie to fix but i be having some problems......

i have this peice of code to update the sql dataapadapter

Dstest1.Tables("test").Rows(0)("hello") = Me.textboxhello.Text
SqlDataAdapter1.Update(Dstest1, "test")

this does not work, it does work however if i replace the Me.textboxhello.Text
with a value in quotation marks or with another text box not linked to that feild in the table... it does not work with the relevant text box!!!!!!!!

thanks for all your help

cq

View 1 Replies View Related

Dataadapter.Update() OR ExecuteNonQuery()

May 18, 2007

Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!

View 5 Replies View Related

DataAdapter Always Returns 0 Or Empty Rows

May 7, 2007

 
Good day,
I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.
I thought the connection is not the problem since I got my columns right. Below is my code snippet.
Thanks!
const string COMMAND_TEXT = @"select distinct somefield as matchcode, count(somefield) "
+ "as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1";
SqlDataAdapter adapter = new SqlDataAdapter(COMMAND_TEXT, connection);
DataTable dt = new DataTable(sometablename);
adapter.Fill(dt);

 

View 13 Replies View Related

Sql Dataadapter, With A Parameter, Fill A Datatable.

Jan 28, 2008

For some reason I've had trouble with this today... can anyone provide me with a generic code snippet that programatically allows me to select data from a database limited by a parameter and then fills a datatable?

View 4 Replies View Related

DataAdapter.Update Updates Zero Records

Jul 13, 2004

I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?

View 1 Replies View Related

Create Trigger To Use AFTER DataAdapter.Update()

Feb 5, 2005

Hi,

I'm using DataAdapter.Update() to update data in a table. My question is; how do I create a trigger that works after the update has completely finished?

For example if my update adds 50 new rows to a table the trigger I've currently got fires after each new row that is added ie 50 times in total. Is it possible to get it to trigger after the last (ie 50th) row is added???

Thanks

View 2 Replies View Related

Problem In Filling A Dataset Using DataAdapter

May 8, 2006

hi friends,
i look forward an answer that solves my problem.
iam trying too populate a DropDown list . here is the codings. Previously it was working. suddenly,
it s generating error.
strConnectionString = "Provider = SQLOLEDB;Integrated Security=False; User ID=sa;Password=;Data Source=GIREESH-AC720F7;Initial Catalog=NorthWind"
 
in page_load event
dim sql as string
sql = "select AthleteNameKey from athletes"
result_adap = DbAccess.ExecuteAdaP(sql)
result_adap.Fill(result_ds, "athletes")
cboAthleteName.DataSource = "athletes"
cboAthleteName.DataTextField = "AthleteNameKey"
cboAthleteName.DataValueField = "AthleteNameKey"
cboAthleteName.DataBind()
 
 
Public Function ExecuteAdaP(ByVal sqls As String) As OleDbDataAdapter
'Dim ds As New OleDbDataAdapter
Dim da As New OleDbDataAdapter(sqls, strConnectionString)
'da.Fill(ds)
Return da
End Function

View 1 Replies View Related

DataAdapter.Update Method Question.

Nov 30, 2006

Hi,

I am trying to use DataAdapter.Update to save a file stream into SQl Express.

I have a dialog box that lets user select the file:

openFileDialog1.ShowDialog();

I want to put

openFileDialog1.OpenFile();

Into

this.documentTableAdapter.Update(this.docControllerAlphaDBDataSet.Document.DocumentColumn);

I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers.

Thanks,
Ke

View 1 Replies View Related

Insert/Update Relational Tables Using Dataadapter

May 2, 2008

Hi!

I am trying to insert data into 2 different tables. I am using dataadapter and dataset.

Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
Call ConnectionString()

Dim insertSQL As New SqlCommand()
insertSQL.Connection = sqlConn
insertSQL.CommandText = "SELECT location.CountryName, location.CityName, location.BuildingName, location.FloorID, rooms.name, rooms.FloorID AS Expr1 FROM location INNER JOIN floors ON location.FloorID = floors.id INNER JOIN rooms ON floors.id = rooms.FloorID"

Dim ds As New DataSet()
Dim da As New SqlDataAdapter()

da.SelectCommand = insertSQL
Dim scb As New SqlCommandBuilder(da)

Try
da.Fill(ds)
Dim ndr = ds.Tables("location").NewRow
Dim ndr2 = ds.Tables("rooms").NewRow

ndr("FloorID") = FloorIDDDL.SelectedValue
ndr("CountryName") = CountryNameTextBox.Text
ndr("CityName") = CityNameTextBox.Text
ndr("BuildingName") = BuildingNameTextBox.Text
ndr2("name") = RoomNameTextBox.Text
ndr2("FloorID") = FloorIDDDL.SelectedValue
ds.Tables("location").Rows.Add(ndr)
ds.Tables("room").Rows.Add(ndr2)
da.Update(ds)
ErrMsgLbl.Text = "Information saved successfully"
Catch ex As Exception
ErrMsgLbl.Text = ex.ToString
End Try

sqlConn.Close()
End Sub

The above code does not throw any error. It also does not update the tables.

Your help will be appreciated.

Thanks!

View 5 Replies View Related

DataAdapter - SELECT Statement - Items In Last 30 Days

Oct 7, 2004

I'm using DataList to return vales stored in an SQL database, of which one of the fields contains the date the record was added.

I am trying to fill the dataset with items only from the last 30 days.
I've tried a few different ways, but all the database rows are returned.

What is the WHERE clause I sholud use to do this??

Thanks

View 2 Replies View Related

Correct Type For Operation: DataAdapter Vs. DataReader

Apr 8, 2008

I am reading multiple databases into a single dataset, putting each read into a new datatable in the set. Ultimately, I want to merge the multiple tables (they are all from different DB tables in different locations using the same schema). I am also combining this with data from an Oracle database which is in yet another table in the same dataset.

Here is my question:

I will not be propagating any changes to this data back to its original source, but I will be populating it out to Active Directory. (Don't worry; I'm not asking any AD questions here.) But I do want to run SELECT, DELETE, and UPDATE queries against the data in the dataset in memory without writing it out to a separate file. Based on that type of usage, would I be better to use a DataReader or a DataAdapter to do the read from the original database? If I use a DataReader, can I still update the data in memory? In either case, what's the correct methodology to update data in a dataset in memory?

Thanks!

View 3 Replies View Related

DataAdapter.Update Is Running This Query... What Is Wrong With It?

Jan 18, 2008

My DataAdapter.Update() Method is running this query against my database and all the parameters and the formatting looks correct to me. I was wondering if anyone could identify obvious errors.... Thanks!

exec sp_executesql
N'NewUpdateCommand',
N'@Name nvarchar(50),
@PriContactFName nvarchar(50),
@PriContactLName nvarchar(50),
@PriContactWork nvarchar(20),
@PriContactFax nvarchar(20),
@PriContactCell nvarchar(20),
@PriContactEmail nvarchar(50),
@MainOfficeLocationAddr1 nvarchar(50),
@MainOfficeLocationAddr2 nvarchar(50),
@MainOfficeLocationCity nvarchar(50),
@MainOfficeLocationState nvarchar(50),
@MainOfficeLocationZip nvarchar(15),
@Description nvarchar(50),
@Original_ClientID int,
@ClientID int',
@Name=N'Brinker International',
@PriContactFName=N'Steves',
@PriContactLName=N'Maubley',
@PriContactWork=N'913-876-9876',
@PriContactFax=N'913-098-8765',
@PriContactCell=N'913-987-7654',
@PriContactEmail=N'a@b.c',
@MainOfficeLocationAddr1=N'1234 Burkwood',
@MainOfficeLocationAddr2=NULL,
@MainOfficeLocationCity=N'Tallahassee',
@MainOfficeLocationState=N'Florida',
@MainOfficeLocationZip=N'99111',
@Description=N'This is a good client.',
@Original_ClientID=1,
@ClientID=1
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'NewUpdateCommand'.

View 1 Replies View Related

Calling Three Different Variable Coming From Three Different Sql Queries Which Defined In Same Stored Procedure From DataAdapter

May 22, 2008

 
My task is to bind and show 3 different values coming from three different queries into three different columns of GridView. I had done this as mention in below. Program was successful. But I want to excute these three queries in same Stored Procedure. I can do that and stored in seperated variables. I need help how to call these three different values in data adapters and store each value in three different columns of grid view.
 
Simply I want to below statement in stored procedures and call from program.  Can any one help me plz.
 
 
 
 
con = DataBaseConnection.GetConnection();
 
DataSet ds = new DataSet();
           
SqlDataAdapter da = new SqlDataAdapter("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da.Fill(ds,"Dep");
           
SqlDataAdapter da1 = new SqlDataAdapter("select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID", con);
da1.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da1.Fill(ds,"Exp");
           
           
SqlDataAdapter da2 = new SqlDataAdapter("select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID", con);
da2.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da2.Fill(ds,"Inc");
 
string deposits = Convert.ToString(ds.Tables["Dep"].Rows[0].ItemArray[0]);
string expenses = Convert.ToString(ds.Tables["Exp"].Rows[0].ItemArray[0]);
string income = Convert.ToString(ds.Tables["Inc"].Rows[0].ItemArray[0]);
           
GridView1.DataSource = ds;
GridView1.DataBind();
GridView1.Rows[0].Cells[0].Text = "Total";
GridView1.Rows[0].Cells[1].Text = deposits
GridView1.Rows[0].Cells[2].Text = expenses;
GridView1.Rows[0].Cells[3].Text = income;
//The above program was success.
 
// This is another way I had tried. But failed. I am getting Index out of bound error. Can any solve this if possible to u.
SqlCommand cmd = new SqlCommand("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con);
cmd.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
GridView1.Rows[0].Cells[1].Text = cmd.ExecuteScalar().ToString();
 
cmd.CommandText = "select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID";
GridView1.Rows[0].Cells[2].Text = cmd.ExecuteScalar().ToString();
 
cmd.CommandText = "select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID";
GridView1.Rows[0].Cells[3].Text = cmd.ExecuteScalar().ToString();
 
ASPX Code for Grid View
<h2>Account Summary</h2><br />
  
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" emptydatatext="There are no data records to display." Width="238px" >
            <Columns>
                <asp:TemplateField >
                   
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text="Total"></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField   ><%--HeaderText="Deposits"--%>
                    <ItemTemplate>
                       
                    
                    </ItemTemplate>
                    <HeaderTemplate>
                        <a href="Deposits.aspx" >Deposits</a>
                    </HeaderTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
              
                <asp:TemplateField >
                    
                     <HeaderTemplate>
                        <a href="Expenses.aspx">Expenses</a>
                    </HeaderTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <ItemTemplate>
                  
                   
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField   >
                    <HeaderTemplate>
                        <a href="Income.aspx">Income</a>
                    </HeaderTemplate>
                  
                 
                    <ItemStyle HorizontalAlign="Center" />
                    <ItemTemplate>
                   <%-- <%# Eval("Course") %>--%>
                    
                    </ItemTemplate>
                </asp:TemplateField>
              
            </Columns>
        </asp:GridView>
 
Plz solve this.

View 4 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

Lookup / Merge Join / Script - Howto Look Up Values By Comparing To A Range Of Values?

Jun 4, 2007

Hello all,

I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:



"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _

" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _

" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _

" AND street_suff = @street_suff AND street_post = @street_post " & _

" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _

" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"



My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value



Any suggestions?



thanks for your time...

View 5 Replies View Related

NULL Values Returned When Reading Values From A Text File Using Data Reader.

Feb 23, 2007

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

View 12 Replies View Related

Power Pivot :: Calculating Values For Future Dates Based On Past Values

Nov 13, 2015

I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?

View 6 Replies View Related

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

How Do I Enter In A Default Values For A Report Parameter That Accepts Multi Values

Apr 11, 2008



I have my stored procedure set to
Territory_code IN (@Territory)

, now , how do i enter in more then one value. When i select the multi value check box, it gives me more spaces. But then doesnt recognize the values when i put in more then one. am i doing something wrong?

The field is a Varchar 20

View 1 Replies View Related

Compare Incoming Input File Row Values With Database Row Values In SSIS

Jan 23, 2008

Hi All,

I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?

Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.

I found some thing to achieve the above thing.
1. Take the input row and check in the database.
2. If the row is not found in the database then treat it as new row.
3. If row is found in the database then
a) Take the source row and prepare a concatenated string for all the columns
b) Take the database row and prepare a concatenated string for all the columns
c) Find out the hash code for the 2 strings and then compare hash codes for equal.

The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.

Can anybody suggest a good method to do this?

What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do?
Will it generates hash code based on all the columns values?

Pls clarify.

Regards
Venkat.

View 1 Replies View Related

Reporting Services :: SSRS - Get Common Values Between Two Columns Where Values Sorted Comma Separated

May 6, 2015

I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:

ColumnA :  abc,cde,efg    
ColumnB : cde,xyz,abc    

the result in    

ColumnC : cde,abc

similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved