Data Adapters, Data Sets, Data TableMappings, and XML

dotnet framework


  • 1. ole db connection with as400
    Hello .net expert ! Error : Invalid cast from System.String to System.Byte[]. I use an oledb connection to connect to as400 to insert value in a table. I got the above error while trying to insert value in a table. above you will see my connection string I tried Force Translate=37 Force Translate=00037 Always the same error message. thanks in advance !! Esperanza SQL Package Name=;Sort Language ID=;Default Collection=Soniq;Add Statements To SQL Package=True;Hex Parser Option=0;Query Options File Library=;Use SQL Packages=False;Maximum Decimal Precision=31;Data Source=tresor;Password=*****;Sort Sequence=0;Protection Level=None;Convert Date Time To Char=TRUE;Provider="IBMDA400.DataSource.1";SQL Package Library Name=;SSL=DEFAULT;Block Fetch=True;Persist Security Info=True;Extended Properties=;Cursor Sensitivity=3;Transport Product=Client Access;Unusable SQL Package Action=1;Catalog Library List=Soniq;Trace=0;Maximum Decimal Scale=31;Force Translate=0;Data Compression=True;Sort Table Name=;User ID=ntuser;Minimum Divide Scale=0;Initial Catalog=TRESOR
  • 2. How can I check a checkbox selected more than one times in DataGrid ????
    Hi Friends, How can I check a checkbox selected more than one times in DataGrid ? My First Priority is check with Client Side(Java Script), if it is not easy than check server side (C#). Thanks Alex.
  • 3. Running Stored Procedure by Parameters
    I wanna use stored procedure by parameters (2 inputs) and I unsure I do the right coding. Stored Procedure ------------------ create procedure Opening_Balance (@Julian_Date_From, @Julian_Date_To,) as select illitm, sum(qty) as qty, sum(weight) as weight from f4111 where ildgl <> 0 and ildgl <= @Julian_Date_From and ildgl >= @Julian_Date_To and ilmcu like '%620%' and illitm = '010720' group by illitm go ASP.Net 1.1 -------------- Dim constr As String = "server='SQLSVR';Database='JDE_ERP_ARCHIVAL'" Dim sqlcon As System.Data.SqlClient.sqlconnection = New System.Data.SqlClient.sqlconnection(constr) Dim sqlcmd As System.Data.SqlClient.SqlCommand = New SqlCommand() Dim sqlda As New system.Data.SqlClient.sqldataAdapter() Dim prm As New system.Data.SqlClient.SqlParameter() Dim prm1 As New system.Data.SqlClient.SqlParameter() Try sqlcmd = sqlcon.CreateCommand sqlcmd.CommandText = "[Opening_Balance]" sqlcmd.CommandType = commandtype.StoredProcedure prm = cmd.parameters.Add("(@Julian_Date_From",SqlDbType.Int, 6) prm1 = cmd.parameters.Add("(@Julian_Date_To",SqlDbType.Int, 6) prm.value = 105101 prm1.value = 105131 sqlda.SelectCommand = sqlcmd sqlcon.Open sqlcmd.ExecuteNonQuery Finally sqlcon.Close() sqlcon.Dispose() End Try constr = Nothing sqlcon = Nothing sqlcmd = Nothing sqlda = Nothing Please advise. Many thanks.

Data Adapters, Data Sets, Data TableMappings, and XML

Postby Candi Suriano » Sun, 24 Aug 2003 04:46:17 GMT

Here's what I'm trying to do:

1. Create a data adapter with Select and Insert commands.

2. Use DataTableMappings to map XML tags into columns in a 
data base table.

3. Create a dataset

3. Read in a schema using dataset.ReadXmLSchema

4. Read in an xml file that contains the data using 
dataset.Read XML

5. Add 3 new columns to the dataset. (These columns have 
already been mapped in step 2).

6. Put data in the 3 new columns.

7. Modify some data in the other columns using xpath 
queries to find the nodes that need changing.

8. Use datadapter.update(dataset, tablename)

Everything seems to work fine until step 8. I get all the 
data, the extra columns get added, and the data in other 
columns gets modified. When I execute step 8 I get:

Run-time exception thrown : 
System.InvalidOperationException - Cannot add or remove 
columns from the table once the DataSet is mapped to a 
loaded XML document.

Any ideas about how I can either fix this or do it another 


Similar Threads:

1.problem with data adapter and data set while inserting and retrieving data

i am using a data adapter and a dataset for filling and retrieving data
into .mdb database.
following is the code.....

for the form load event
  Dim dc(0) As DataColumn
            If OleDbConnection1.State = ConnectionState.Closed Then
                MsgBox("connection can not be established")
            End If
            DA.Fill(DataSet11, "Table1")
            cmd = New OleDbCommandBuilder(DA)
            dc(0) = DataSet11.Tables("Table1").Columns("EmpID")
            DataSet11.Tables("Table1").PrimaryKey = dc
        Catch ex As Exception
        End Try
        Delete.Enabled = False
    End Sub

for  inserting values into the DB

Private Sub Insert_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Insert.Click
        Dim dr As DataRow
        dr = DataSet11.Tables("Table1").NewRow
        dr.Item(0) = Val(TB1.Text)
        dr.Item(1) = TB2.Text
        dr.Item(2) = TB3.Text
        dr.Item(3) = Val(TB4.Text)
        dr.Item(4) = TB5.Text
        DA.Update(DataSet11, "Table1")
        MsgBox("data is saved")
        rno = 0
        call filldata()

filldata function consists of the following

 With DataSet11.Tables("Table1").Rows(rno)
         TB1.Text = Trim(.Item(0))
         TB2.Text = Trim(.Item(1))
         TB3.Text = Trim(.Item(2))
         TB5.Text = Trim(.Item(4))
         End With

the error it gives is " there is no row at
0........system.nullreference...........i checked the connection and
its working fine and also the database is getting accessed........the
error is occuring at the line " With
DataSet11.Tables("Table1").Rows(rno) "

2.Passing parameter for Stored Procedure to Data Adapter/Data Set

Hey all,

   I'm trying to pass a parameter into my Stored procedure to be accessed by 
my data adapter into a dataset

            DataSet rsDataset = db2.Fill("spListMappingDetails", 

However, when i try to run it, it gives me an error 

{"Procedure 'spListMappingDetails' expects parameter '@intClientId', which 
was not supplied."}

Any help is appreciated.

I used a executescalar and executequery function and it has no problem. 

thanks in advance

3.Provider independent data access: creating a data adapter from a connection

I have written a provider independent utility class for 
executing queries. One of the routines looked like this in .net 1.1:

static public DataSet ExecuteQuery(IDbConnection connection, String sql)

   DataSet result  = new DataSet();
   IDbDataAdapter adapter = /* magic to create an adapter from a 

   // The sybase adapter's implement IDisposable, so wrap with a using. 

   using(adapter as IDisposable)
     adapter.SelectCommand.CommandText = sql;
     adapter.SelectCommand.Connection = conn;

   return result;

The magic code to create the adapter was ugly: it essentially performed 
a dictionary lookup on the type of connection, and created the resulting 
adapter type. If IDbConnection had a method called CreateAdapter, 
analagous to CreateCommand, this would have been cleaner.

With the introduction of ADO.NET 2.0 and the DbProviderFactory class, I 
was hoping that some of the ugliness would go away. Unfortunately, I am 
running into some problems. Namely

1- DbConnection still does not have a CreateAdapter method

2- DbConnection does not offer a way to get back to the 
DbProviderFactory that created it.

So I still cannot create the correct adapter given only a generic 
DbConnection. I am looking for suggestions on how to code my routine. So 
far, I have thought of these

1- Make the user pass in the DbProviderFactory that goes with the 
connection. CONS: forces users to use DBProviderFactory. We have a lot 
of code that doesn't.

2- Stick with the dictionary lookup (Dictionary<ConnType, AdapterType>. 
CONS: DLL dependencies explode.

3- Use a different dictionary lookup (Dictionary<ConnType, 
DBProviderFactory>. CONS : A little messy, but better than 2.

4- Don't use provider specific adapters at all, just use a generic one. 
Like this:

class GenericAdapter : System.Data.Common.DbDataAdapter

static public DataSet ExecuteQuery(DbConnection connection, String sql)

   DataSet result  = new DataSet();
   GenericAdapter adapter = new GenericAdapter();
   adapter.SelectCommand = conn.CreateCommand();
   adapter.SelectCommand.CommandText = sql;
   return result;

I have tested this with two databases, Sybase ASE and ASA, and it seems 
to work. Are there any serious downfalls to not using the vendor 
specific adapter type? Is this code safe?


4.Passing parameter for Stored Procedure to Data Adapter/Data Se

5.Manipulating data between data adapters


I have two data adapters bound to two separate tables. How can I;

1. Loop through all records one by one in one of them while reading column 
values, and

2. Insert a record from data adapter A into data adapter B

via code?



6. How to change data in grid after you change the SQL select for the data adapter

7. 3rd Tier data not displaying in Data Adapter Preview

8. How to change data in grid after you change the SQL select for the data adapter

Return to dotnet framework


Who is online

Users browsing this forum: No registered users and 72 guest