Updating joined tables
Hi,
I am trying to display data from two sqls tables in a datagrid, and then
alow the user to make changes that update one of the tables. Currently I am
bringing in two tables into a dataset, merging them and binding them to a
datagrid.
I then use a comand builder on one of the original sql tables in order to
update it.
It sort of works - I cant get tables to merge that have different numbers of
records.
Is there a better way to do this?
Thanks, Tag: Oracle Client Components Tag: 111056
How do I convert a base64 encoded bytearray?
Hi!
I have a webservice which queries a table in SQLServer. The table has a
column, "Text", which is of datatype "image". When invoking the webmethod it
returns dataset with the table included. The column is returned as a
base64binary as can be seen in the declaration excerpt below:
<xs:element name="text" type="xs:base64Binary" minOccurs="0" />
When I call the webservice and loops through the rows I see that the column
"text" is represented as a bytearray as well. What I'd like to do is to
convert this bytearray into an "ordinary" string. Please note that it must
work in a .Net Compact Framework environment. I've tried a number of
approaches but none has worked so I feel a little bit "lost in binary space".
Hope you can help me!
/Peter Tag: Oracle Client Components Tag: 111054
how to update datagrid
(Type your message here)
i am trying to create tables in sql server programmatically in=
vb=2Enet=2Ei can add fields and update them programmatically=2E i need=
to know how i can update the table records when displayed in=
datagrid=2Ei want to update the datagrid/records as it is done in=
access=2Eneed to know how to get count of number of rows filled in=
datagrid=2Eplzzz help!!
--------------------------------
From: amritac20 chaudhuri
-----------------------
Posted by a user from =2ENET 247 (http://www=2Edotnet247=2Ecom/)
<Id>kSuuS0+4DU+lP+VwPUuFeA=3D=3D</Id> Tag: Oracle Client Components Tag: 111052
Updating a column in a DataTable...
Ok, I'm sure this one has been asked before, so I'll apologize upfront.
What I'd like to do is take a DataTable and run an update statement against
one of its columns.
Say for instance, my DataTable has a ProductID column, which is an integer,
and I'd like to update all values in that column to something like 33786.
What's the best way to do it? Would a SQL-like statement work combined with
a DataAdapter, or is it not that simple? Tag: Oracle Client Components Tag: 111039
null or DbNull or what?
Hello,
I am trying to find the most efficient way to send record UPDATES from a
client to my webservice. I am already using GetChanged to only send the
records that have actually been modified, but I would like to further reduce
network traffic by not sending individual columns that were not modified.
My first attempt in doing this was to use SQL stored procs that have default
values that would be values that would never actually be used for these
fields. The update statement then evaluates each parameter to determine
whether it should really update each column. This works great on the SQL
Server side, and I've discovered that by leaving the Command Parameter value
as null (not DbNull), the default values would be invoked. The problem here
lies in determining if a value in my DataTable is DbNull because it was set
to null or if it is DbNull because it was not changed. It seems that I
cannot actually set fields in a datarow to be actual null (not DbNull).
Is there something I am missing here?
Moreover, is there something bigger that I am missing? Is there a better
solution for minimizing the amount of XML being sent from my client to my
webservice? I do realize that a possible solution would be to stop sending a
DataSet and just use my own XML specification that allowed for more
flexibility in minimizing packets being sent. But that solution seems to
negate a great deal of Microsoft technology that we hoped to be leveraging.
Thank you for any suggestions,
Glenn Thimmes Tag: Oracle Client Components Tag: 111038
Reading Data From Excel Via ADO.NET
I have an application reading data from an Excel file and filling a SQL
Server table. When I read the Excel file, I want to be able to trim
trailing spaces off the data as it's read into the dataset. I'm connecting
to the Excel file via an OleDbDataAdapter & OleDbCommand object. All the
code compiles and runs fine - I just want to trim any trailing spaces off of
the data.
Here is a portion of the code I'm using:
Dim oCmdSelect As New OleDbCommand(moImportFormat.SelectFromStatement,
moConnExcel)
'Get the values in the Excel file and verify
mDSExcel = New DataSet()
Dim oDA As New OleDbDataAdapter()
oDA.AcceptChangesDuringFill = False
oDA.SelectCommand = oCmdSelect
oDA.Fill(mDSExcel, sTableName)
The SQL statement stored in moImportFormat.SelectFromStatement is:
SELECT STORENAME FROM [Sheet1$]
The trick, I believe, is to add a "trim" statement as in TRIM(STORENAME) or
RTRIM(STORENAME). However when I tried TRIM & RTRIM, I receive an error
message. When I take out the TRIM or RTRIM it works fine. So the $1
million dollar question is, is there a function that can "trim" data when
being read in with an OleDbDataAdapter / OleDbCommand?
Thanks,
Kyle Tag: Oracle Client Components Tag: 111035
good data-driven place to store default values?
so i have a database schema, a .NET object library .dll, and an ASP
classic web application.
i'm wondering where in this setup i should place default values for
fields/columns of forms/objects/rows.
SQL Sever doesn't seem like a good candidate, because i'm using stored
procedures for all inserts. these procs use input parameters for each
of the values in case there IS a value other than default, but since
the parameters are optional, they are defined with the = null. so these
mechanisms basically completely circumvent the default values, because
either a value is specified by the caller of the proc, and that value
is stored, or the value is not specified by the caller, and a null is
stored. either way, no default values from the table definition.
The object library seems like a decent place, except that i would love
for it to be data driven. i suppose each object could keep track of all
of its own default variables. part of the constructor could be to go
find its default-value definition file, and load them up that way. does
anyone foresee any major architectural problems with this?
And the last option is to put the default value definitions in the ASP
code somehow, probably through a constants file, and value comparisons.
Much less elegant than putting them in the object library, but pretty
simple, too.
Thoughts? comments? Suggestions?
Thanks,
Jason Tag: Oracle Client Components Tag: 111026
how can I create a properties collection for a user control
How can I create a properties collection for a user control? (or reference
what's there if it already exist)
I have a user control that displays an HTML table with several values. For
each field's value I have a public property that is set from a repeater's
itemdatabound event. In the ItemDataBound Sub, I use something like the
following where the datasource column name is always the same as the form's
property name:
DisplayTable.FIELD1 = DataBinder.Eval(e.Item.DataItem, "FIELD1")
I have to go through and set approx. 30 fields just like this but I'd like
to do something that is completely independent of the number of columns
returned plus I'd rather not have to name each property or field should one
be deleted or added.... Something like:
dim x as property
for each x in MyUserControlInstance.PropertiesCollection
MyUserControlInstance..x= DataBinder.Eval(e.Item.DataItem, x)
next
I've tried several different approaches but can't get it working . The
compiler identifies the .x part after the control name as the source of the
error. I can't recall right this second if the message was 'identifier
expected' or 'expression expected' but I think it was one of these.
Mike Tag: Oracle Client Components Tag: 111024
Using DataReader to count rows
Hello All:
First let me thank everyone on the forum(s) for all the great (and timely)
help!!!
I am writing a help desk app in asp.net that allows people to input work
orders for IT. I used to use data adapters and datasets until someone here
helped me to realize the performance advantage of using data reader. It
works great for displaying the info, but i would like to add the ability to
pull the *number* of open and closed work orders for a particular user as
such:
SELECT * FROM workorders WHERE extension = ('extension') and STATUS =
('Open')
SELECT * FROM workorders WHERE extension = ('extension') and STATUS =
('Closed')
then i want the data reader to tell me how many rows were selected. I tried
things similar to;
******************************************
While rdr.read()
a = a + 1
End While
labelOpenWorkOrders.text = a
******************************************
and I also tried
********************************************
while rdr.read()
do
a = a + 1
loop
end while
labelOpenWorkOrders.text = a
*******************************************
It never returns the actual row count.
i also tried
labelOpenWorkOrders.text = rdr.recordsaffected -- but this is only for
insert, update, delete.
HELP!!!!
Thanks
Steve Tag: Oracle Client Components Tag: 111018
Defaults in Column Mappings
Dear All,
I have designed a DataAdapter which reads data from a table. The table has
an auto-incrementing Primary Key. None of the columns in the table accept
Nulls although some of the columns do have defaults.
My question is - Is there an automatic method of filling these properties on
the table when I call the fill method, or do I have to manually key the
properties for each column when the table has been filled?
Thanks in advance for your assistance
Mike Tag: Oracle Client Components Tag: 111008
ConnectionState problem
Hi,
My application connects to an Access database when it is loaded, and
keeps the same connection open all the time.
There is logic in the code to ensure that the connection is
automatically opened the first time it is used:
if ((cmd.Connection.State == ConnectionState.Closed) ||
(cmd.Connection.State == ConnectionState.Broken)) cmd.Connection.Open();
However occasionally my application says this:
System.InvalidOperationException:
The connection is already Open (state=Connecting).
or it might say the same but with state=Executing.
But if the state is Executing, you can neither call .Open nor can you
execute a query. So how can I maintain an open connection without these
errors occuring?
To be honest, I don't understand why it is ever in the "Exectuting"
state, because it's a single threaded application and theres no way that
it can be doing more than one thing at once.
Thanks,
Nick... Tag: Oracle Client Components Tag: 111006
Updating typed dataset schema from code when a column is deleted
I have an existing .xsd file with typed dataset information in it. I need the
ability to update this existing schema information when a column is removed
from one of the underlying data adapters.
Currently I read the existing schema info into a temp dataset object, update
the schema for the table in question by just calling MyAdapter.FillShema and
passing it my temp dataset. I then write the schema file back to disk.
This works great for column additions, the new columns are placed in the
schema, but it does not change the schema if a column is deleted.
I've been searching through the merge documentation, the MissingSchemaAction
documentation, anything I can think of, but I'm at a loss.
Any ideas or pointers in a new direction?
TIA,
J.D. Tag: Oracle Client Components Tag: 111004
Group By
Hi,
I have a resultset with different columns which I would use to group the
data by. For examle:
Name Categ Year
AAA 1 2003
BBB 1 2004
CCC 2 2003
DDD 2 2004
I want to have differnt views of this data like this:
1 - 2003 - AAA
2004 - BBB
2 - 2003 - CCC
2004 - DDD
or
2003 - AAA
CCC
2004 - BBB
DDD
I could solve this using multiple queries and differnt filters, but I
wonder if there is a more elegant solution using ADO.Nets hierarchical
features. And hints or ideas?
regards,
Achim Tag: Oracle Client Components Tag: 111003
Change display value in a grid
Hi,
I'm using compact framework for pocket Pc and I should change the
display values of a column in a grid, so I've a xml file where there is
a field where are saved the follow value 1,2,3 but I don't want to
transfrom it like:
switch (myRow["FIELD"])
{
case 1: DisplayValue = "First";
break;
case 2:DisplayValue = "Second";
break;
case 3:DisplayValue = "Third";
break;
}
so a run time I should see the description and not the value, How can I
do this?
Thanks Tag: Oracle Client Components Tag: 110998
Tricky Ado.net Addition
Hello
I'm stuck on this one.
I have a dataset which has many tables.
amongst them I have 2 tables:
tblJob & tblCompany
In my form, I have a tab for jobs, which has a combo named "cmbCompany"
which lists company name from the table Company.
Now I have logic built into my application so that if the user enters a text
which is not in the list, it asks the user if he wants to add it. Then it
pops up another form, which populates all the information.
The tblCompany has a CompanyID field which is autoincrement and set to -1
& -1.
So the tblJob which has the foreign Key "CompanyID" gets "-1" saved in it.
When I save the database.
I get the proper ID before I merge the database, so I have to correct ID for
the "tblCompany" however the foreign key in tblJob is not updated although
I've set it to cascade..
I don't know if i've explained the issue properly, but if someone has an
idea on this, please let me know.
Thanks Tag: Oracle Client Components Tag: 110997
SQL Server problem
I have created a simple web application using ASP.NET, which queries an
SQL-Server database, but every time the application is run, I get the
following error: (truncated for brevity)
Exception Details: System.Data.SqlClient.SqlException: SQL Server does
not
exist or access denied.
Relevant code is here:
string strConnection = "Data Source=testdb;Initial
Catalog=testdatabase;User Id=testuser;Password=testpassword;";
dbConn = new SqlConnection(strConnection);
dbConn.Open();
strSQL = "SELECT Title, ISBN, Publisher " +
"FROM BOOK " +
"ORDER BY Title";
dbCmd = new SqlCommand(strSQL, dbConn);
dbReader = dbCmd.ExecuteReader();
dgQuick.DataSource = dbReader;
dgQuick.DataBind();
My setup is as follows:
IIS 5 webserver running on Windows XP
Database server is Microsoft SQL-Server 2000, running on Windows Server
2003 Enterprise
(These two servers are in seperate Windows domains)
The really odd thing is that I can connect just fine using the same
connection information in Query Analyzer running on the same machine as
the web server.
What I'm thinking is that most likely the ASP.NET worker process
running on the web server does not have the proper access rights for
accessing the database server. I'm not especially knowledgeable about
the security model on Windows 2003 server, so this is just a guess.
Anyone got any suggestions? Tag: Oracle Client Components Tag: 110994
TypedDataset
I have a typed dataset as follows,which as you can see **Dose have a primary
key**.In the application when I try to use the Find mentod on it it says
that the table dosen't have the primary key,so I traced from the begining of
object creation and I noticed when I instanciate an object from my
typedataset (EntityReport m_EntityReport=new EntityReport();) ,there is not
primary key definded there!!!!
I used these two lines of code and the length of **test** is Zero!!! Why?
DataColumn[] test=m_EntityReport.Parameters.PrimaryKey;
Debug.WriteLine(test.Length.ToString());
Thanks a lot
*****************My Typed Dataset Schema***************
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="EntityReport"
targetNamespace="http://www.adventureworks.com/EntityReport.xsd"
elementFormDefault="qualified"
xmlns="http://www.adventureworks.com/EntityReport.xsd"
xmlns:mstns="http://tempuri.org/EntityReport.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"
xmlns:NS="http://www.adventureworks.com/EntityReport.xsd"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="Report">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:string" />
<xs:element name="Name" type="xs:string" />
<xs:element name="Description" type="xs:string" nillable="true"
msprop:nullValue="_null" />
<xs:element name="Path" type="xs:string" />
<xs:element name="CreatedBy" type="xs:string" />
<xs:element name="CreatedDate" type="xs:date" />
<xs:element name="ExecutionTime" type="xs:dateTime" />
<xs:element name="CustomParams" type="xs:boolean" />
<xs:element name="SessionID" type="xs:string" nillable="true"
msprop:nullValue="_null" />
<xs:element name="ExecutionOption" type="xs:short" nillable="true" />
<xs:element name="HistoryID" type="xs:string" nillable="true"
msprop:nullValue="_null" />
<xs:element name="ExportFormat" type="xs:string" default="HTML4.0" />
<xs:element name="DeviceSettings" type="xs:string" nillable="true" />
<xs:element name="RemoteCall" type="xs:boolean" />
</xs:sequence>
</xs:complexType>
<xs:key name="IDKey" msdata:PrimaryKey="true">
<xs:selector xpath="." />
<xs:field xpath="NS:ID" />
</xs:key>
</xs:element>
<xs:element name="Parameters">
<xs:complexType>
<xs:sequence>
<xs:element name="Name" type="xs:string" />
<xs:element name="Value" type="xs:string" />
<xs:element name="ReportID" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:key name="NameKey" msdata:PrimaryKey="true">
<xs:selector xpath="." />
<xs:field xpath="NS:Name" />
</xs:key>
<xs:keyref name="ReportsParameters" refer="IDKey">
<xs:selector xpath="." />
<xs:field xpath="NS:ReportID" />
</xs:keyref>
</xs:element>
</xs:schema> Tag: Oracle Client Components Tag: 110980
ColumnMappings don't seem to work!
Hi,
I am new to column mappings and can't seem to make them work. The following
is an example:
DataTable table = new DataTable( "SYSTEM.Usernames");
table.Columns.Add( "S1", typeof(decimal) );
OracleCommand cmd = new OracleCommand();
cmd.Connection = this.oracleConnection1;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT SuperUserFlag FROM SYSTEM.Usernames WHERE
Username = 'JuanDent'";
OracleDataAdapter da = new OracleDataAdapter( cmd );
DataSet ds = new DataSet( "Temp ds");
ds.Tables.Add( table );
DataTableMapping map = da.TableMappings.Add( "Table", table.TableName );
map.ColumnMappings.Add( "S1", "SuperUserFlag" );
ds.EnforceConstraints = false;
da.MissingSchemaAction = MissingSchemaAction.Error;
try
{
da.Fill( ds, 0, 200, table.TableName );
}
catch( Exception ex )
{
MessageBox.Show( ex.Message, ex.Source);
}
No matter how I play with it, I can't get the Fill statement to work, it
always asks for a missing column "SuperUserFlag" in the table - I thought
this is precisely what column mappings did for us...
--
Thanks in advance,
Juan Dent, M.Sc. Tag: Oracle Client Components Tag: 110979
Sharing Data Saved As XML From a Dataset
If using ReadXML and WriteXML to serialize a dataset instead of using a
regular database, is it still possible to avoid the problem of one person
overwriting another person's changes...or is it not possible for data
serialized as XML from databset to be shared? Thanks. Tag: Oracle Client Components Tag: 110964
Access to MSDE
Hi,
What is the most effective way to migrate an access DB to MSDE / SQL Server
using only code, C# in this case. I do not have access to the MSDE other
than via code and I was looking for a reliable solution.
Thanks. Tag: Oracle Client Components Tag: 110959
Using a Class enumeration as a Data Source
Hello,
I have a class that contains an enumeration and I would like to bind
the members of the enumerations to a drop down list box. If have seen
some examples that use Enum.GetNames and Enum.GetValues but when ever I
pass the name of my enumeration I get an error. Let me illustrate.
--- VB.NET Code ----------
Public Class Phone
Public Enum PhoneNumberType
Home = 1
Work = 2
Mobile = 3
Fax = 4
Pager = 5
End Enum
End Class
--- ASP.NET Code -------
dropDownListBox.DataSource =
System.Enum.GetValues(Phone.PhoneNumberType)
It's here that I get an error. "PhoneNumberType is a type in Phone and
cannot be used as an expression". Any guess on what I might be doing
wrong? Thanks.
-dwok Tag: Oracle Client Components Tag: 110958
ODP Authentication problem with ADO.NET
Hi,
I'm using the latest ODP provider with an Oracle 10g database. My
connection string is setup for db authentication - contains the rdbms
userid and password. Every so often though I see a Windows
Authentication prompt dialog come up - corresponding with an internal
Oracle DB Error. All I'm doing is fetching (the same) bunch of records
back into an IE browser.
Does this ring any bells with anyone?
Thanks
rpb Tag: Oracle Client Components Tag: 110952
GenerateDBDirect
What is the difference between GenerateDBDirect and the Insert/Update/Delete
sql commands that can be generated via the advanced button in the select
table section of the TableAdapter Wizard in Dot Net 2.0. Tag: Oracle Client Components Tag: 110951
dataset from tab separated
Hello
I want to create a asp.net / winform application, which has a text box.
In the text box, the user will paste data (copied from another source), in
tab separated format.
Supposedly, all the columns are always in a predetermined order, how can I
put this into a dataset and bind to it ..
Any input would be appreciated.
Thanks
Hemang Tag: Oracle Client Components Tag: 110950
Parent expression column
Hi all,
I am trying to create a visualisation of a many-to-many
relationship in VB.NET. The tables, columns and
relationships are detailed below (Sorry for all the code).
The cables and columns match those found in the
Acess database that the application is based around.
At the end of the code is an expression column that
I wish to add tothe joining table in the middle of
my many to many relationship.
Dim mmToothType As New DataColumn("Tooth_Type",
System.Type.GetType("System.String"), "Parent(Type_Teeth).yID")
The column uses a relationship to get the parent record of the
joining table. The record is added without error. However,
when I run my code, create the tables and relationship and then
load the data from the database, the expression column
shows a NULL when loaded to a datagrid control!!!
Does anyone has any experience of this type of expression
column? Is this the best way to illustrate the many-to-many
relationship? The joining table (Teeth) contains the number of
teeth of a particular type, as well as the FK columns from the
person table and the teethtype table and I need to be able to
display this column in the same grid.
Many thanks in advance for you help.
Jason.
' Person Table
Dim dPerson As New DataTable("Person")
' Add Name column
dPerson.Columns.Add("Name",
System.Type.GetType("System.String"))
' Add PK
Dim dColumnPK As New DataColumn
dColumnPK.ColumnName = "ID"
dColumnPK.DataType = System.Type.GetType("System.Int32")
dColumnPK.AllowDBNull = False
dColumnPK.AutoIncrement = True
dColumnPK.AutoIncrementSeed = 1
dColumnPK.AutoIncrementStep = 1
dColumnPK.Unique = True
dPerson.Columns.Add(dColumnPK)
' Teeth table
Dim tTeeth As New DataTable("Teeth")
' Add the number of teeth
tTeeth.Columns.Add("tNumber",
System.Type.GetType("System.Int32"))
' Add PK
Dim tColumnPK As New DataColumn
tColumnPK.ColumnName = "tID"
tColumnPK.DataType = System.Type.GetType("System.Int32")
tColumnPK.AllowDBNull = False
tColumnPK.AutoIncrement = True
tColumnPK.AutoIncrementSeed = 1
tColumnPK.AutoIncrementStep = 1
tColumnPK.Unique = True
tTeeth.Columns.Add(tColumnPK)
' FK to Person table
Dim tColumFKPerson As New DataColumn
tColumFKPerson.ColumnName = "tdID"
tColumFKPerson.DataType = System.Type.GetType("System.Int32")
tColumFKPerson.AllowDBNull = False
tTeeth.Columns.Add(tColumFKPerson)
' Add FK to TeethType table
Dim tColumFKToothType As New DataColumn
tColumFKToothType.ColumnName = "tyID"
tColumFKToothType.DataType =
System.Type.GetType("System.Int32")
tColumFKToothType.AllowDBNull = False
tTeeth.Columns.Add(tColumFKToothType)
' TeethType table
Dim tTeethType As New DataTable("TeethType")
' Tooth name
tTeethType.Columns.Add("yName",
System.Type.GetType("System.String"))
' Add PK
Dim yColumnPK As New DataColumn
yColumnPK.ColumnName = "yID"
yColumnPK.DataType = System.Type.GetType("System.Int32")
yColumnPK.AllowDBNull = False
yColumnPK.AutoIncrement = True
yColumnPK.AutoIncrementSeed = 1
yColumnPK.AutoIncrementStep = 1
yColumnPK.Unique = True
tTeethType.Columns.Add(yColumnPK)
' Person-Teethth relationship
Dim drPersonTeeth As New DataRelation("Person_Teeth",
dColumnPK, tColumFKPerson)
' ToothType - Teethth relationship
Dim drTeethType_Teeth As New DataRelation("Type_Teeth",
yColumnPK, tColumFKToothType)
' Add tables to dataset
ds.Tables.Add(dPerson)
ds.Tables.Add(tTeeth)
ds.Tables.Add(tTeethType)
' Add relationships to dataset
ds.Relations.Add(drPersonTeeth)
ds.Relations.Add(drTeethType_Teeth)
' Calculated expression that gets the parent from the
Type_Teeth relationship
Dim mmToothType As New DataColumn("Tooth_Type",
System.Type.GetType("System.String"), "Parent(Type_Teeth).yID")
' Add new column to Teeth table
Try
tTeeth.Columns.Add(mmToothType)
Catch ex As NullReferenceException
MessageBox.Show(ex.Message)
End Try Tag: Oracle Client Components Tag: 110943
Using Crystal Report
Hi ALL,
I need a help in Crystal Report?!
When I'm using Crystal Report Command Based Report that include Graphs and
Grouping and then change the report command using
report(OBJECT).SetDataSource(Command) it doesn't retreive the correct sql
command and retreive the data of the old command that have been used in
report building,why?? are there any reason??!!
Also,when i used Report XML Based at huge number of data it gives time out
or server application Unavilable,also when i increase the time out of the
connection in the connection string.
Please Help
Walid Salah
Senior S\W Developer Tag: Oracle Client Components Tag: 110934
Mutating Record, maybe?
Hi
I'm having a problem performing updates on records in an Oracle 8.1.7
database. The table I'm updating, TABLE_1 has a update and insert trigger
which updates another table, TABLE_2, which in turn updates an archive table
ARCH_TABLE_2.
I can perform an insert and a single update within a single transaction.
But, when I try and perform an insert an update then another update the
OracleDataAdapter.Update() method fails. Incidentally, the reason for
performing multiple updates within a single transaction is for audit and
reporting purposes against the ARCH_TABLE_2.
A colleague mentioned the problem of mutating records, but I don't see the
ORA-04091 exception.
Any ideas?
Thanks in advance
Glenn Tag: Oracle Client Components Tag: 110933
Master-Detail Strategy
I have a master table that I would like to display on a form with a
navigator, and I would like to display the detail records in a grid
for each master record.
Right now, I am getting all the records for both tables using
DataAdapter.Fill and a "select * from <table>" select command. I am
then setting up a DataRelation in code between the two tables.
The problem I am having is that it is taking up to 30 seconds to fill
the detail table because of it's size. Is there a way to dynamically
retrieve and display (and edit) the detail records as each master
record is displayed?
I'm using the XtraGrid from Developer's Express for the detail records
if it makes a difference.
--Bruce Tag: Oracle Client Components Tag: 110929
ANN: Sooda 0.7 has been released!
Announcing the release of Sooda 0.7
We're proud to announce the immediate availability
of Sooda v0.7.
Version 0.7 is the initial public release made after
more than 2 years of development and testing as well
as a number of deployments in various projects.
Sooda homepage: http://www.sooda.org/
Download Sooda 0.7 here: http://www.sooda.org/en/download.html
Sooda mailing lists: http://www.sooda.org/en/mailinglist.html
About Sooda:
Sooda is an open-source easy-to-use object-to-relational
mapping software for .NET released under the terms of BSD
license with portions released under GPLv2.
This release includes:
* Sooda library
* Sooda stub generator utility
* Sooda stub precompilation utility
* Sooda Query Analyzer
Sooda supports the following O/R features:
* transparent object materialization
* lazy reads
* clean and natural syntax - the amount of boilerplate code
is reduced to the absolute minimum, the code is readable
and maintainable
* support for most data types supported by the .NET Framework
* natural mapping of one-to-many relations as referenced
objects and collections.
* natural mapping of many-to-many relations as collections
* various models of object inheritance
* differential XML serialization and deserialization for
moving data across layers
* support for long running transactions
* data caching with copy-on-write support
Sooda supports Microsoft .NET Framework version 1.1 or
later and Mono 1.x. Visual Studio.NET 2003 and NAnt 0.85
are supported build platforms. Tag: Oracle Client Components Tag: 110927
Problem with datatable compute max(value) method
I have the following code that is getting (or should get) the max value
from an xml file. Even though the max value should be 21, it is always
returning 9:
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.ReadXml(Server.MapPath"~/Home/XMLMenus/MenuCategory.xml"),XmlReadMode.InferSchema);
dt = ds.Tables[0];
string selectCommand = "Max(report_category_id)";
string filterCommand = "report_category_id > 0";
int nextNum =
Convert.ToInt32((object)dt.Compute(selectCommand,string.Empty));
DataRow dr = ds.Tables[0].NewRow();
dr["report_category_id"] = nextNum + 1;
dr["report_category"] = "Test";
ds.Tables[0].Rows.Add(dr);
DataGrid1.DataSource = dt;
DataGrid1.DataBind();
here is the xml:
<NewDataSet>
<Table>
<report_category_id>1</report_category_id>
<report_category>Accounting</report_category>
</Table>
<Table>
<report_category_id>5</report_category_id>
<report_category>Company</report_category>
</Table>
<Table>
<report_category_id>21</report_category_id>
<report_category>Continuous Improvement</report_category>
</Table>
<Table>
<report_category_id>6</report_category_id>
<report_category>Customer Care</report_category>
</Table>
<Table>
<report_category_id>7</report_category_id>
<report_category>Documentation</report_category>
</Table>
<Table>
<report_category_id>8</report_category_id>
<report_category>Employee</report_category>
</Table>
<Table>
<report_category_id>9</report_category_id>
<report_category>Engineering</report_category>
</Table>
<Table>
<report_category_id>10</report_category_id>
<report_category>Test</report_category>
</Table>
I have a feeling it is treating the xml values as strings because
removing the single digit numbers whos left char is equal to or less
than the left char of the max double digit returns the right value. In
other words 3 > 21 because 3 > 2 and 21 > 2. Any suggestions? Tag: Oracle Client Components Tag: 110921
I got an "invalid parameter used" exception while using DataGrid with Excel
Hi,
I am developing an add-in for Excel 2003 using Visual
Studio .Net 2003. I am using Windows 2000.
I created a Windows Form and loaded a DataGrid control
on it. When I ran my add-in, the exception "invalid
parameter used" is thrown inspite not writing any
code.
I want to use this DataGrid for populating it with
data from a DataSet.
What should I do? My project work is stuck due to
this.
Will it help if I move on to Windows XP?
I am desparately waiting for your early reply.
Manas A. Pathak
-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)
<Id>G6SPr3B7u06m1W1G0Aoyow==</Id> Tag: Oracle Client Components Tag: 110919
SqlDataReader & paging
Hi,
I have an <asp:DataGrid> control to which I'm trying to bind an
SqlDataReader. When I do so, I get the following error:
AllowCustomPaging must be true and VirtualItemCount must be set for a
DataGrid with ID dgrdTimesheet when AllowPaging is set to true and the
selected datasource does not implement ICollection.
If I set the datagrid's AllowCustomPaging property to true and its
VirtualItemCount property to be the number of records returned by the
SqlDataReader, the error disappears. The datagrid's paging hyperlinks
correctly show the correct number of pages, but each page contains the first
page's data. I.e.
SqlDataReader contains 76 rows
DataGrid's page size is 10 rows
DataGrid correctly shows pages 1-8
Clicking on page 1 correctly shows the first 10 records
Clicking on any other page incorrectly shows the first 10 records, not
11-20, 21-30 etc.
Of course, using a DataSet object instead of an SqlDataReader object cures
the problem instantly, but I was wondering if there is any way to use an
SqlDataReader object as the datasource of a datagrid which supports paging?
Any assistance gratefully received.
Mark Tag: Oracle Client Components Tag: 110918
How to pass a table or cursor or XML to Oracle stored procedure?
Hi,
I am trying to send set of rows from my c# web service to Oracle stored
procedure.
I think I can get this done using OpenXML in SQL Server.
How to implement this in Oracle Stored Procedure?
Can I pass a table or cursor or XML to Oracle stored procedure?
By the way, my data is in a strongly typed Dataset, I have to somehow send
the set of rows to Oracle Stored procedure.
Please help me out here with any relevant information you have got.
Thanks Tag: Oracle Client Components Tag: 110912
closing connection
Is there any advantage of always calling SqlConnection's Close method after
done using it vs. just let it go out of scope open and hope the garbage
collection will take care of it?
thanks! Tag: Oracle Client Components Tag: 110911
DA.Fill Schema Retrieval
It appears that the DA's Fill method does not grab the schema in situations
like this:
TasksCommandText = "SELECT * FROM \"tblTasks\" " + sTasksWhere +
sTasksOrderBy;
cmd.CommandText = TasksCommandText;
odbcDA_TaskActivities.SelectCommand = cmd;
dsTaskActivities.Clear();
odbcDA_TaskActivities.Fill(dsTaskActivities, "Tasks");
The DataSet Table "Tasks" doesn't get any of the constraints or PK
information from tblTasks. So what's the best way to go about getting the
schema and applying it to the DataTable? I've looked at FillSchema but it
creates tables of predefined names (Table1, Table2, etc.) with equally
useless column names. My code is written to reflect more meaningful names,
their actual names. Is there a way to get the schema and use the actual
identifiers, both column names and table names? What's the conventional way
of doing this? Tag: Oracle Client Components Tag: 110906
SQL Server Connection Best Practice
Hello, generally, is it better to quickly open and close ADO.NET a few times
or open the connection once. Perhaps my understanding of pooling and
disconnected data is not where I'd like it.
An example is let's say there is an ASP.NET page with a page_load event and
button_click event. One could open/close connections at each event or just
open the connection once.
Which is more efficient? Does the first way create more SPIDs in SQL server?
Is that bad?
Thanks in advance,
Jim Tag: Oracle Client Components Tag: 110905
Update Question
How do I ensure that the current row's changes will be included in the DA's
Update call? If I move off the row it is included, but if I make a change
then call Update, the changes are lost. Tag: Oracle Client Components Tag: 110903
ADO.NET - Excel and Column header..
I am trying to create an excel file using ADO.NET (Can not use Excel
COM interop classes). Everything seems to be working fine except the
column header of the table sheet1 is also included in the output file.
Setting the HDR=NO doesn't seem to fix the problem. Could some one
please tell me how to turn off the column header creation (created in
the first row of the sheet)?
Here is the sample code..
==================================================
OleDbConnection objConn = null;
try
{
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=D:\\temp\\Book7.xls;Extended Properties=\"Excel
8.0;HDR=NO\"";
objConn = new OleDbConnection(sConnectionString);
objConn.Open();
// Create worksheet
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
// ======================== here is the problem
// Whatever i put in the column name unloaded in the first row of the
Excel file
// Is there a way to turn it off?
objCmd.CommandText = "Create Table Sheet1 ([A$] char(255), [B$]
char(255))";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into Sheet1 " +
" values ('Bill', 'Brown')";
objCmd.ExecuteNonQuery();
objCmd.CommandText = "Insert into Sheet1 " +
" values ('Joe', 'Thomas')";
objCmd.ExecuteNonQuery();
}
catch(Exception e)
{
System.Console.WriteLine(e.Message);
}
finally
{
//Close the connection.
if (objConn != null)
objConn.Close();
}
=======================================================
Could someone please help me solve the problem?
Thanks,
poochi Tag: Oracle Client Components Tag: 110902
Oracle ODP Returns 'Data provider internal error(-3000)'
Hi All.
I am testing an app that fetches data from an Oracle 10G DB every 5-10
seconds and returns it back to a browser (IE 6). Periodically, the the
call just fails and I get back the above error.
SOMETIMES, the browser pops up a dialog to re-authenticate to the
network. EVENT THOUGH I AM USING DB AUTHENTICATION....
I have also seen the following error # show up in my Win2003 event log:
8007006d .
Has anyone else ever seen/heard of this sort of problem?
Thanks much
rpb Tag: Oracle Client Components Tag: 110901
Insert using C#
When I try to insert the second record I get the message "The Variable name
"@Parm1" has already been declared. What can I do to prevent this. The code:
try
{
sqlInsertCommand1.CommandText = "INSERT INTO services([service-code],
[service-description]" +
" ,[large-animal-cost], [medium-animal-cost], [small-animal-cost])" +
" values(@Parm1,@Parm2,@Parm3, @Parm4,@Parm5)";
sqlInsertCommand1.Parameters.Add("@Parm1",SqlDbType.Int).Value =
txtServiceCode.Text;
sqlInsertCommand1.Parameters.Add("@Parm2",SqlDbType.VarChar).Value =
txtServiceDesc.Text;
sqlInsertCommand1.Parameters.Add("@Parm3",SqlDbType.Money).Value =
Convert.ToDecimal(cbLargeAnimalCost.Text);
sqlInsertCommand1.Parameters.Add("@Parm4",SqlDbType.Money).Value =
Convert.ToDecimal(cbMediumAnimalCost.Text);
sqlInsertCommand1.Parameters.Add("@Parm5",SqlDbType.Money).Value =
Convert.ToDecimal(cbSmallAnimalCost.Text);
sqlInsertCommand1.Connection = sqlConnection1;
if (sqlConnection1.State != ConnectionState.Open)
sqlConnection1.Open();
sqlInsertCommand1.ExecuteNonQuery();
this.sqlConnection1.Close();
MessageBox.Show("Insert Complete. [services] " + txtServiceNbr.Text +
" Successful.","");
--
Norm Bohana Tag: Oracle Client Components Tag: 110900
Genning script from SQLDMO
I've heard that SQLDMO can generate scripts for a table or sproc or
group or whatever. But I can't find any examples.
Also, I have 2 databases with slightly different versions of the table.
Is there a way that SQLDMO can generate a script that syncs up the
table with the proper ALTER COLUMN statements and so forth?
Thanks. Tag: Oracle Client Components Tag: 110897
OleDbCommand should be disposed? Holding conn reference...
Hello Everybody,
Can someone help me? I'm not sure about the code below=2E It's=
working but=2E=2E=2E =2E I create a new OledbConnection, opened and=
called a method=2E This method creates a new OleDbCommand that use=
the connection (via another class; cn is a member in an data=
class)=2E But the OleDbCommand is not disposed after the method is=
finished (should I dispose it?)=2E After that, the connection is=
disposed=2E See below:
' Creates and open a connection
Dim cn as OleDb=2EOleDbConnection
cn =3D New OleDb=2EOleDbConnection(strConn)
cn=2EOpen()
' call method
retDatTable()
' Dispose connection
cn=2EDispose()
Function retDatTable(ByVal strSQL As String) As DataTable
Dim oDataTable As New DataTable()
Dim oOleDbCommand As OleDb=2EOleDbCommand
oOleDbCommand =3D New OleDb=2EOleDbCommand(strSQL, cn)
Dim oDataAdapter As New OleDb=2EOleDbDataAdapter(oOleDbCommand)
oDataAdapter=2EFill(oDataTable)
Return oDataTable
End Function
The connection was disposed=2E But=2E=2E=2E the OleDbCommand that used=
the connection was not=2E This connection will be returned to the=
pool? I'm not sure because OleDbCommand was not disposed=2E=2E=2E so,=
he continue to hold the connection reference, right? Or I'm=
missing something=2E=2E=2E??
Thanks!!
Andre
-----------------------
Posted by a user from =2ENET 247 (http://www=2Edotnet247=2Ecom/)
<Id>vFwoibWSy0qjFn+TaBlnuw=3D=3D</Id> Tag: Oracle Client Components Tag: 110891
Easy Question. Create a datagrid filled with data without any code on web page?
I am using visual studio 2003. I user the server explorer and drag a table
onto a webform.
The program creates a "Connection" and an SQLDataAdapter Generates the
"Select, Insert, Delete" statments.
I right click on the SQLDataAdapter and chose Generate Dataset.
I drag a datagrid on to the form. Chose the SQLDataAdapter and dataset in
the properties.
I then run the webpage and I get nothing. When I do a preview on the
SQLDataAdapter I see the data in the table.
So what is my problem? Is there some documentation on how to use these
controls in this manner instead of alway writing code behind the scenes?
Thanks,
Rog Tag: Oracle Client Components Tag: 110888
ConnectionState problem
Hi,
My application connects to an Access database when it is loaded, and
keeps the same connection open all the time.
There is logic in the code to ensure that the connection is
automatically opened the first time it is used:
if ((cmd.Connection.State == ConnectionState.Closed) ||
(cmd.Connection.State == ConnectionState.Broken)) cmd.Connection.Open();
However occasionally my application says this:
System.InvalidOperationException:
The connection is already Open (state=Connecting).
or it might say the same but with state=Executing.
But if the state is Executing, you can neither call .Open nor can you
execute a query. So how can I maintain an open connection without these
errors occuring?
To be honest, I don't understand why it is ever in the "Exectuting"
state, because it's a single threaded application and theres no way that
it can be doing more than one thing at once.
Thanks,
Nick... Tag: Oracle Client Components Tag: 110876
Merging Events
Is there any way i can modify specific field values of merging rows,
i basically need to perform a merge where by if a row is being modified then i dont want to use the new id value i want to keep the original, BUT i do want to modify all other changed field values.
--------------------------------
From: ananda situnayake
-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)
<Id>gI8/N9WGP0ygHCfQ5S47kQ==</Id> Tag: Oracle Client Components Tag: 110872
how to retrieve identity (Any OleDb Provider)
Hello, (sorry for my english).
I'm trying to build an oledb data access generic class and would like to get
the value of an identity column for any oleDb provider... I have seen so many
specific examples for ms-access, sql server, oracle, mySql... Can anyone tell
me if there is any general solution for this problem?
Thank you!! Tag: Oracle Client Components Tag: 110869
dataset bind not all columns
Hi all
I'm using dataset and I'm filling datagrid with this dataset and my dataset
has 6 columns as "NAME","SURNAME","ADDRESS","TEL","BIRTHDAY" and "SCHOOL
NAME"
When I bind the dataset to datagrid,all columns are shown but I want to show
only three columns are "NAME","SURNAME" and "ADDRESS" and I don't want to
change dataset.
Dataset must have 6 columns but datagrid must have only 3 columns.
How can I accomplish this? Tag: Oracle Client Components Tag: 110868
problem adding new record to access database table
Hi All
I am having trouble adding a new record to an access database
Error is 'Syntax error in INSERT INTO Statement'
Updates are OK
Here is the code
Any suggestions please. I am new to vb.net from VB6
regards
Steve
Dim path As String, myimage As Bitmap, datapath As String
Dim cn As New OleDbConnection
Dim da As New OleDbDataAdapter
Dim ds As New DataSet, x As Long, strSQl As String
Dim dt As New DataTable, photopath As String
Dim cmdbuilder As OleDbCommandBuilder
Dim cmd As OleDbCommand
If cbmembers.Text = "" Then
Exit Sub
End If
Try
path = System.Reflection.Assembly.GetExecutingAssembly.Location
datapath = System.IO.Path.GetDirectoryName(path) & "\data\u2.mdb"
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
datapath & ";Persist Security Info=False;"
cn.Open()
da = New OleDbDataAdapter("Select * from [members] where [number] = " &
cbmembers.SelectedValue, cn)
cmdbuilder = New OleDbCommandBuilder(da)
da.Fill(ds, "Members")
Dim dsRow As DataRow
If flgnew Then
dsRow = ds.Tables("members").NewRow()
Else
dsRow = ds.Tables("members").Rows(0)
End If
dsRow.Item("surname") = txtsurname.Text
dsRow.Item("name") = txtname.Text
dsRow.Item("street") = txtstreet.Text
dsRow.Item("suburb") = txtsuburb.Text
If txtcardno.Text <> "" Then
dsRow.Item("cardnumber") = CLng(txtcardno.Text)
End If
If txtdatejoined.Text <> "" Then
dsRow.Item("date") = CDate(txtdatejoined.Text)
Else
dsRow.Item("date") = DBNull.Value
End If
If txtdob.Text <> "" Then
dsRow.Item("dob") = CDate(txtdob.Text)
Else
dsRow.Item("dob") = DBNull.Value
End If
dsRow.Item("valid") = cbvalid.Checked
If flgnew = True Then
ds.Tables("members").Rows.Add(dsRow)
da.InsertCommand = cmdbuilder.GetInsertCommand
Else
da.UpdateCommand = cmdbuilder.GetUpdateCommand
End If
da.Update(ds, "Members")
ds.Tables("members").AcceptChanges()
flgnew = False
MsgBox("Member Details Saved OK")
Catch objException As Exception
MsgBox(objException.Message)
Finally
da = Nothing
ds = Nothing
cn = Nothing
End Try Tag: Oracle Client Components Tag: 110854
How To Insert A Row To A Specific Row Index
Hi all,
I tried some code below:
//**************
int row=dataGrid1.CurrentRowIndex;
dataSet1.AcceptChanges();
DataRow newRow=dataSet1.Tables[0].NewRow();
newRow["Tarih"]=DateTime.Now.ToString();
int count=dataSet1.Tables[0].Rows.Count;
dataSet1.Tables[0].Rows.InsertAt(newRow,0);
//************
But these codes adds the newRow to end of the rows, not to index 0 .
Any ideas?
Thanks for any help.
Arif Çimen Tag: Oracle Client Components Tag: 110851
Databinding and saving changes (if any) to new row
Hi there!
I am new to databinding in .Net and I think I am going to get crazy...
What I need to do is this:
1) Create a new form for the user to enter a new Product
2) Save changes (if any) when the user closes the form
But I still couldn't get what I want, even after searching in Google.
Anyone can give me a pointer/hint?
Here is the details:
1) The form FormProduct (in which textboxes are bound) gets loaded:
private void FormProduct_Load(object sender, System.EventArgs e)
{
cmProduct = (CurrencyManager)BindingContext[dsProduct,"Products"];
LoadData();
cmProduct.AddNew();
}
2) Users either enter something or just close the form
3) Before closing the form, save any changes (I am stuck here):
private void FormProduct_Closing(object sender,
System.ComponentModel.CancelEventArgs e)
{
cmProduct.EndCurrentEdit(); // Saw this in several places already
if(dsProduct.HasChanges())
SaveData();
}
The problem is this:
if I don't call cmProduct.EndCurrentEdit(), then changes will not be
detected.
But if the user hasn't entered anything, they just close the form (like
a cancel), dsProduct.HasChanges() will return true (because we added a
row in the dataset).
Any solution? Tag: Oracle Client Components Tag: 110848
Is there a way to connect to oracle 9i database from c# WITHOUT installing
the Oracle Client Components on my machine?