What could be causing this while updating a DataTable?
Hi there,
I am getting this exception
"Deleted row information cannot be accessed through the row"
when trying to update a datatable with an adapter:
mAdapter.Update(mTable); <== exception here
After struggling with this for hours, I am now looking for a more generic
answer, i.e., does anyone know the possible scenarios in which this
exception might occur inside the Update method. What "mistake" on my side
could be triggering it.
The exception usually happens if I:
1) Delete all rows in the table:
for (int i = 0; i < mTable.Rows.Count; i++)
mTable.Rows[i].Delete();
2) Add new rows to the table => mTable.Rows.Add(newRow);
3) Call the update method => adapter.Update();
Thanks in advance for any opinions,
-Benton Tag: Best and quikest method to filter a dataview Tag: 124388
SqlException in Visual Studio 2005
First of all, I apologize if I'm not posting this in the appropriate group.
This is my first time using these Newsgroups, so I'm trying to find my way
around. That said, this is my problem:
I have a stored procedure I'm calling from Visual Studio 2005. The query
works just fine in query analyzer, but returns a very generic error in VS.
Here's my stored procedure:
CREATE PROCEDURE [dbo].[prc_AssetChangeVendorDetailID]
@OldVendorDetailID int,
@NewVendorDetailID int
AS
SET NOCOUNT ON
UPDATE [Asset]
SET
[VendorDetailID] = @NewVendorDetailID
WHERE
[VendorDetailID] = @OldVendorDetailID
and the code that calls it is:
//Update all asset records with the old vendor detail ID to new ID.
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@OldVendorDetailID", 1);
cmd.Parameters.AddWithValue("@NewVendorDetailID", 2);
cmd.CommandText = "[prc_AssetChangeVendorDetailID]";
cmd.ExecuteNonQuery()
I get an exception with the message of "Line 1: Incorrect syntax near
'prc_AssetChangeVendorDetailID'." Normally, I wouldn't argue with the editor,
but the fact that the procedure executes without a problem in query analyzer
is what has me stumped.
Is there something I'm doing wrong? Tag: Best and quikest method to filter a dataview Tag: 124385
Problem saving changes to table
I receive the following error when trying to update a table after editing.
System.InvalidOperationException was unhandled
Message="Update requires a valid UpdateCommand when passed DataRow
collection with modified rows."
Source="System.Data"
Here is the code:
Private Sub SaveItem_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles TestBindingNavigator.Click
Me.Validate()
Me.TestBindingSource.EndEdit()
Me.TestTableAdapter.Update(Me.DBDataSet.Test)
End Sub
Private Sub Form1tmp_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'DBDataSet.iScanLogin' table.
You can move, or remove it, as needed.
Me.TestTableAdapter.Fill(Me.DBDataSet.Test)
End Sub
In other sections of the program I am able to save changes on other tables
in the same manner without any errors.
What am I doing wrong? Tag: Best and quikest method to filter a dataview Tag: 124377
DataRowView and objects don't match?
Hi there,
I have this method to search a DataView:
public int FindIndex(string columnName, object key)
{
index = -1;
foreach (DataRowView drv in table.DefaultView)
{
if (drv[columnName].Equals(key))
{
index = i;
break;
}
i++;
}
return index;
}
So far this does not work:
int i = FindIndex("IDFIELD", textBox1.Text); <= I get a -1
However, this does work ok:
string search = textBox1.Text;
int i = SearchIndex("IDFIELD", Convert.ToInt32(textBox1.Text));
In this case I get the correct index.
Any ideas?
Regards,
-Benton Tag: Best and quikest method to filter a dataview Tag: 124375
Where to start?
* Plateform: .Net 2.0
* Question:
Is ado.net the solution? If so, what should I be looking into?
* Data:
schedule containing:
1 to n properties
collection of lines
line containing:
1 to n properties
* Data Source:
An engine that needs to take the saving of the data to keep a version
history. The design is complete for how the versioning will be kept;
it is based on doing a difference of what is saved and what is in
memory.
* Problem
How to display this data and allow the user to make changes to it.
* My thoughts
I have limited knowledge of ado.net, but I am guessing that it is
pretty straight forward to display the collection of lines, or a
DataSet table of lines, in a datagrid. What I don't understand is
how best to couple the data in the engine with a dataset. Do I simply
create a DataSet (one of those .xsd files) and create a table for the
collection of lines, have the engine populate it and have the GUI send
it back to the engine for saving? Or is there a more sophisticated way
of creating my own DataSet? Are there other ado.net classes I should
be learning about to make the engine a sort of provider for then GUI? Tag: Best and quikest method to filter a dataview Tag: 124374
DataTableReader Problem
Help: I am trying to use a datatable reader to loop through a datatable but I
get the following error message when I try:
System.InvalidOperationException was unhandled
Message="DataTableReader is invalid for current DataTable
'CABG_Transfer_to_Raw'."
Source="System.Data"
This is the code to get the reader. I preview the CABG_Transfer_to_Raw
table and the data is shows up. Any ideas
Me.CABG_Transfer_to_RawTableAdapter.Fill(Me.HQIPatientLevelDataSet1.CABG_Transfer_to_Raw)
Dim dtreader1 As DataTableReader
dtreader1 =
Me.HQIPatientLevelDataSet1.CABG_Transfer_to_Raw.CreateDataReader
If dtreader1.HasRows = True Then
Do While dtreader1.Read
Dim Message As String = dtreader1("Diag_Code")
Dim Caption As String = dtreader1("Site")
Dim Buttons As MessageBoxButtons = MessageBoxButtons.YesNo
'Displays the MessageBox
Dim msbxResultDim As DialogResult
msbxResultDim = MessageBox.Show(Message, Caption, Buttons)
Loop
End If
This is what I get:
System.InvalidOperationException was unhandled
Message="DataTableReader is invalid for current DataTable
'CABG_Transfer_to_Raw'."
Source="System.Data" Tag: Best and quikest method to filter a dataview Tag: 124371
Can we use custom function in the DataColumn.Expression
I am using VS2005. I add a column to a DataTable in a DataSet.
Can Iuse custom function in the DataColumn.Expression? Tag: Best and quikest method to filter a dataview Tag: 124369
Large Text.
I'm wanting to save and retreive text from a multiline textbox which I've
been told can become extremely large and I'm not sure how this is to be
done(c#).
I'm guessing SqlServers Text type is ok and some kind of chunking of the
lines in the textbox. Any pointers will be greatly appreciated.
Thanks.
Steve. Tag: Best and quikest method to filter a dataview Tag: 124365
Multithreaded access with TableAdapters
We have an app under development processing data from com port events. There
can be multiple com ports all sending data back to this one method on one
instance of a class â??ldâ??. This method gets a db object associated
speciffically with THAT com port. The db objects wrap a datatable and a
tableadapter.
So if we have COM1, and COM3 in use we might have something like this
ld -> retrieves -> dataAdapterObjCOM1
dataAdapterObjCOM3
If I am running with only one com port all works fine. With more than one I
get many errors in the Designer class for the db stuff. All the db objects
are separate instance of objects specifically associated with that com port.
So no two threads should be trying to access the same db wrapper objects at
the same time.
The errors include:
The connection was not closed. The connection's current state is connecting.
When trying to insert with the adapater.
Note: I am testing for this connection state right before the insert line
and sometimes it will see it sometimes it wonâ??t. I know doc says its for
future use but I am getting it now.
Object reference not set to an instance of an object. on DataTable NewRow()
I unplug the extra comport and everything works fine again.
Any suggestions? Tag: Best and quikest method to filter a dataview Tag: 124358
SQL CONTAINS clause parameter substitution problem
Hi,
I'm in the process of parameterising the SQL SELECT statements in my
ASP.NET application so that I don't directly include user input in
the SQL (to protect against SQL Injection attacks). I've run into a
problem with a statement which has a CONTAINS clause, which seems to
result from parameters within quotes not being substitued. The
following examples illustrate the results when the user inputs a single
search string ("bottle") and two search strings ("bottle" and
"corkscrew").
With user input embedded in the SQL it all works as expected for both
single and multiple search strings:
SELECT * FROM CatalogueItems WHERE (CONTAINS (Description,
'"bottle"'))
This returns all records whose Description field contains the word
"bottle" (as expected). Note the single and double quotes around
the "bottle" search string. Without the single quotes I get an
"Incorrect syntax near 'bottle'" error. The double quotes seem
optional, but I included them as they are needed if multiple search
strings are specified (see below).
SELECT * FROM CatalogueItems WHERE (CONTAINS (Description,
'"bottle" AND "corkscrew"'))
This returns all records whose Description field contains the word
"bottle" and the word "corkscrew" (as expected). Note the
single and double quotes around the search strings. The double quotes
are needed because multiple search strings are specified (without them
I get an "Incorrect syntax near the keyword 'AND'" error).
My problems began when I modified the (C#) code to use an SqlCommand
Object with parameters instead of directly embedding the user input in
the SQL...
Searching using a single parameter works fine:
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(new SqlParameter("@SearchWord1",
SqlDbType.Char, 6));
sqlCommand.Parameters["@SearchWord1"].Value = "bottle";
sqlCommand.CommandText = "SELECT * from CatalogueItems WHERE (CONTAINS
(Description, @SearchWord1))
This returns all records whose Description field contains the word
"bottle" (as expected). Note the absence of single quotes around
the "@SearchWord1" parameter. If the quotes are added, then I get
no results.
Searching with two parameters fails:
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(new SqlParameter("@SearchWord1",
SqlDbType.Char, 6));
sqlCommand.Parameters["@SearchWord1"].Value = "bottle";
sqlCommand.Parameters.Add(new SqlParameter("@SearchWord2",
SqlDbType.Char, 9));
sqlCommand.Parameters["@SearchWord2"].Value = "corkscrew";
sqlCommand.CommandText = "SELECT * from CatalogueItems WHERE (CONTAINS
(Description, '"@SearchWord1" AND '"@SearchWord2"'))
This does not return any results. Omitting single and/or double quotes
from around the parameters causes varying types of failure:
CONTAINS (Description, @SearchWord1 and @SearchWord2)
Gives "Incorrect syntax near the keyword 'AND'" error.
CONTAINS (Description, '@SearchWord1 and @SearchWord2')
Gives no hits.
It looks like enclosing the parameters in quotes prevents their
substitution, but leaving the quotes out causes SQL syntax errors.
Can anyone tell me how to do this please? Tag: Best and quikest method to filter a dataview Tag: 124357
Serialized ADO.NET 1.0 dataset compatible with ADO.NET 2.0
Is it possible to serialize an ADO.NET 1.0 dataset to xml and then to
deserialize it back to an ADO.NET 2.0 dataset ? And visa versa ?
Is this functionality supported for the Compact Framework also ?
Best regards
Kay-Christian Wessel Tag: Best and quikest method to filter a dataview Tag: 124353
Fill strongly type dataset from stored procedure
Dear all,
I have a file-strongly type dataset, that have two independent table inside
it.
I want to fill the two table in the dataset by one stored procedure(have two
select sql on it) how can i do
e.g.
create spname
..
SELECT * FROM table1
SELECT * FROM table2
GO
and
Dataadapter.fill(dataset.table1)
but how about table2 Tag: Best and quikest method to filter a dataview Tag: 124345
how to get output parameters using enterprise library 2.0
hi,
how do I get output parameters from sql 2005 query using the entlib 2,0?
I noticed the dbcommandwrapper is not there anymore.
thanks
xisco Tag: Best and quikest method to filter a dataview Tag: 124344
Using a design time connection from a class
Hi,
I've created an SQLConnection by dragging a DataAdapter onto the form. The
connection was named sqlConnection1.
When I try to reference this connection in code from another class, I cannot
find it. Intellisense only finds the classe SQLConnection. This must be a
scope issue.
How can I reference the forms connection object from code? Do I need to
create another connection in the class, or pass it to the class, or is there
a way of directly referencing it?
Many thanks for your advice in advance.
Ant Tag: Best and quikest method to filter a dataview Tag: 124339
Looking for an alternative
Current project is in .NET 2003 using SQL Server 2000. I use strongly typed
datasets in this app, created by dragging a table form Server Explorer to a
design surface, walking thru the SQL Data adapter wizard and generating
Datasets.
The database in the Server Explorer is a db designed and maintained just for
the purpose of generating strongly typed datasets. I also maintain a SQL
Script in one of my projects (generated from the template database) to
create new databases from within my app.
My problem arises when I want to modify the tables in the database. I have
to delete the Dataset, remove the Sql DataAdapter from the design surface,
modify my table, re-drag it back to the design surface and walk thru the
whole process of creating SDA's and Datasets again, and modifying the SQL
script.
Adding a column to a table ends up taking an hour or more.
Is there an alternative, a tool, a utility, etc that can help automate this
or replace it entirely?
NOTE: We will soon be migrating the app to Net 2005 and SQL Server 2005.
Will this problem go away in .NET 2005?
Michael Tag: Best and quikest method to filter a dataview Tag: 124335
DataTable.AcceptChanges error, "Destination Array was not long eno
I am encountering a situation where I'm trying to AcceptChanges() on a
DataTable and this is throwing a
Destination array was not long enough. Check destIndex and length, and
the array's lower bounds.
error from within this call. Can someone shed some light on this? What
could have been done to lead to this error being thrown on such an apparently
simple call??
Regards,
-- TB Tag: Best and quikest method to filter a dataview Tag: 124332
Generating an SqlParameter from a value in a DataSet
How can I generate a Parameter if I cannot retrieve the SqlDbType from
an item in a row of a DataSet?
Is it safe to just take the object I retrieve from the DataSet and take
the following constructor for the Parameter:
SqlParameter(string parameterName, object value)???
Regards
Stephan Tag: Best and quikest method to filter a dataview Tag: 124328
How do I set the DateTimeMode property when filling a DataTable
[ I posted this in Microsoft.Public.Data.Ado, but I haven't had a
response. I thought I would try here too. ]
I am investigating how best to manage UTC timestamps in ADO.Net. We
ran into a lot of issues with UTC DateTime serialization in .NET 1.1.
With .NET 2.0, there are new properties to help identify when a
DateTime (as part of a DataTable or as a regular object) is UTC or
local.
I have been able to handle all cases so far except one. In our
database we store all timestamps in UTC. I want to retrieve the data
into a DataTable whose column has the DateTimeMode property set to
DataSetDateTime.Utc. I can't figure out how to do this.
Any suggestions?
I have tried setting the DateTimeMode after the DataTable has been
populated, but that causes an exception. I haven't been able to find
anything in SQL Server 2005 that would allow me to specify that a
DateTime column is a "UTC" DateTime. The only way I've been able to do
it is by adding the columns manually before calling adapter.Fill(). I
hope there is a better way.
Here is my test code. I know it is poor coding, but it is only a test.
For simplicity, I just use a raw SQL statement.
SqlConnection conn = new
SqlConnection(@"server=(local);database=master;uid=sa;pwd=password;");
SqlCommand cmd = new SqlCommand("SELECT GetDate() AS local,
GetUTCDate() AS utc", conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("TryIt");
dt.Columns.Add("utc", typeof(DateTime));
dt.Columns["utc"].DateTimeMode = DataSetDateTime.Utc;
dt.Columns.Add("local", typeof(DateTime));
dt.Columns["local"].DateTimeMode = DataSetDateTime.Local;
try
{
adapter.Fill(dt);
dt.Columns["utc"].DateTimeMode = DataSetDateTime.Utc;
dt.Columns["local"].DateTimeMode = DataSetDateTime.Local;
}
catch (Exception)
{
throw;
}
DateTime local = (DateTime)dt.Rows[0]["local"];
DateTime utc = (DateTime)dt.Rows[0]["utc"]; Tag: Best and quikest method to filter a dataview Tag: 124327
How can I find the BindingSource has some changed data
Hi,
In my WinForms application, I have a set of controls that are bound to a
BindingSource control.
When the user closes the form, I need to ask him if he wants to save data.
The problem is I cannot find anyway to check if the value of any textboxes
is changed!
Obviously checking DataSet.HasChange is not adequate. The reason is that the
user might be in middle of adding or editing a record so the record has not
been reflected to underlying dataset yet, but user accidentally clicks on
form close button.
Any help would be appreciated,
Alan Tag: Best and quikest method to filter a dataview Tag: 124326
Bug in TableAdapter Code Generator?
I've run into problems using TableAdapters under VS2005 where the underlying Fill() method fails due to a constraint violation. This
occurs when there was no change to the data of the underlying table -- hence there shouldn't be a constraint problem, so far as I
can see -- but where the width of an nvarchar field changes (this occurs with SqlServer2005).
Apparently, the TableAdapter code is sensitive to width mismatches between the field in the table and the field as it is described
in the TableAdapter. That's not necessarily a bug...but the fact that reconfiguring the TableAdapter (by running its Configure
context menu command) doesn't solve the problem surely is.
Running the configuration command on a table where an nvarchar field's width has changed does not update the width specification for
the column in the TableAdapter. You have to go in and do that manually (or recreate the entire TableAdapter from scratch, which is
what I was doing early on...but that's a royal pain).
Needless to say, having to manually update for field width changes is a pain.
- Mark Tag: Best and quikest method to filter a dataview Tag: 124325
SQLAdapter Timeout doesn't work
Hi everybody,
i've a problem with loading a dataset: i need to load entire contents
of various tables into dataset, however i don't know size of this
table. If table size is too big simply i need a timeout exception and
go on. The problem is that when the table is too big, the Fill method
avanza without any timeout. Why? Mehod code is this:
private DataSet LoadDataSet ( int Table, DBtype db, out string
ErrorMsg)
{
DataSet ds = new DataSet();
SqlDataAdapter adapter = null;
string cmd;
string TableName;
ErrorMsg = "";
try
{
// Table name
TableName = GetTableName ( Table );
// Compose Select statement
cmd = "SELECT * FROM " + TableName;
// SqlDataAdapter (GetConnectionString compose the string and set
connet timeout = 10)
adapter = new SqlDataAdapter( cmd, GetConnectionString());
adapter.SelectCommand.
// Ten seconds for the command
adapter.SelectCommand.CommandTimeout = 10;
// And ten seconds for the connection (i think i don't need
because i set before)
adapter.SelectCommand.Connection.Timeout = 10;
//
// Execute
//
adapter.Fill( ds );
return ds;
}
catch ( System.Exception ex )
{
ErrorMsg = ex.ToString();
return null;
}
finally
{ if ( adapter != null ) adapter.Dispose(); }
} Tag: Best and quikest method to filter a dataview Tag: 124313
Web Service SQL Connection Model
Hi All,
I am near completion of a project consisting (broadly) of a web app, a web
service and a SQL server - the usual sort of configuration. Both the SQL
server and the web service are tasked with dealing with large amounts of
data per user; and a large number of users.
At the moment I am using a new SqlConnection object for each request to the
web service. Each of these connections is disposed of properly ...
using (SqlConnection cn = DataService.CreateNewConnection())
{
...
}
Should I, however, be opening 1 single connection at Application startup and
leaving that open for all requests from all Sessions?
What are the pros & cons of both of these approaches?
Many thanks,
Chris Tag: Best and quikest method to filter a dataview Tag: 124309
Dataset creation problem.........
Hi All,
I have two access databases. First contains a table which contains
userids. The second one contains the users records.
I tried to create a dataset which will query in these two databases and
get two datatables. I tried to add relation between these two
datatables.
But while setting the relationship it gives error because each & every
records in the first table does not contain related records in the
second table.
e.g.
Table 1 Table 2
--------------------------- --------------------------------------
Id UserId UserId UserName
--------------------------- -------------------------------------
1 1001 1001 User One
2 1001 1002 User Two
3 1002 1003 User Three
1004 User Four
Can anyone help me in this issue...............
Thanks,
Biren. Tag: Best and quikest method to filter a dataview Tag: 124306
Using XML to read data from SQL Server
Does anyone have a good, complete example code that would show the
necessary steps for using XML to read data from a SQL Server database?
I'm trying to switch from DataReaders and I have very little knowledge
in the XML area. I would be very grateful and I suspect it would help
many others to see such a posting in this news group. Tag: Best and quikest method to filter a dataview Tag: 124292
Unable to insert BLOB in Oracle
Hello,
I am unable to insert BLOB objects into Oracle 9i database from .NET. I
get the following exception:
"ORA-01460: unimplemented or unreasonable conversion requested".
I have a table in the database that accepts a number and blob object.
The blob object i am trying to insert is a compressed dataset.
Every thing seems to work fine if i insert small amount of data (compressing
around 500 records) but i get an exception
if i try to store data that has more than 500 records compressed.
Any ideas as to why this may happen?
Thanks
Bala Tag: Best and quikest method to filter a dataview Tag: 124288
ADO.NET 2.0, WebMethods and datatable strange behavior
Hi,
I am using VS2005 and .NET 2.0
I have a WebMethod that accept a typed data table. I realized that whatever
rows that client sends to the SOAP server through the datatable, the
webmethod received an empty datatable!!
After spending several hours, I found that the only kind of data table that
I can pass it to a webmethod is detached datatable. If data table is
attached and part of a dataset, then the webmethod receives an empty
datatable!
Is that true? Or I am missing something?
Thank you,
Alan Tag: Best and quikest method to filter a dataview Tag: 124282
Saving contents of datatable to Access table without writing SQL
Hello,
In ADO.NET is there anyway to save the contents of an datatable
directly to an Access table without having to loop through and generate
a SQL insert query string? I figure there's probably a way to do it as
one chunk.
I'm using VB.NET 1.1.
Thanks,
Eric Tag: Best and quikest method to filter a dataview Tag: 124275
DataSet Question
Hi,
I have a dataset that is filled with data from the db. The user has
modified that data in one of the tables where some of fields should now be
null. I have played around with delete the row and adding a new row but
have problems with the update. Do you think I should just reset the row by
looping through the row and setting all the values to null before I update
changes to the row?
Thanks Tag: Best and quikest method to filter a dataview Tag: 124274
DataColumn size
Hi everybody!
Do someone know the easiest way to obtain the datacolumn size in code (I
mean the size that
you define creating a text field in a table)? In VB6 you could get it by
means of Recordset.Field.
DefinedSize property. I have been searchin for something similar in ADO.NET
but without luck.
Thanks beforehand Tag: Best and quikest method to filter a dataview Tag: 124256
AD.NET 2.0: BindingSource and Parent Child relationship
Hi,
I have two BindingSource(s) in my form, one for parent and one for child
area.
The unique situation in my form is that the child area chooses its parent by
a ComboBox.
The problem is when I use AndNew method in the child BindingSource, I cannot
choose the parent record through the combobox and parentDataSource.Position.
The parent BindingSource complains about [Not Null] columns and throws
exception.
This doesn't make any sense to me. I shouldn't have to use
DataSet.EnforceConstraints because I do not have any NULL value at that
specific parent record and position!
I am sure that I am missing an important points, what is it?
Any help would be appreciated,
Alan Tag: Best and quikest method to filter a dataview Tag: 124248
How to write generic code access in ADO.NET
M. Chand is a .NET consultant, author and the admin and founder of C#
Corner. He has been working with .NET technology since pre beta
releases.
http://www.dotnetwire.com/frame_redirect.asp?newsid=3171
using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.Data.Odbc;
namespace GenericDataAccessApp
{
public class GenericAdoNetComp
{
private IDbConnection idbConn = null;
private IDbDataAdapter idbAdapter = null;
private DbDataAdapter dbAdapter = null;
private IDataReader iReader = null;
public GenericAdoNetComp()
{
}
// GetConnection returns IDbConnection
public IDbConnection GetConnection(int connType,
string connString)
{
switch (connType)
{
case 1: // OleDb Data Provider
idbConn = new OleDbConnection(connString);
break;
case 2: // Sql Data Provider
idbConn = new SqlConnection(connString);
break;
case 3: // ODBC Data Provider
idbConn = new OdbcConnection(connString);
break;
// case 3: // Add your custom data provider
default:
break;
}
return idbConn;
}
// GetDataAdapter returns IDbDataAdapter
public IDbDataAdapter GetDataAdapter(int connType,
string connString, string sql)
{
switch (connType)
{
case 1: // OleDb Data Provider
idbAdapter = new OleDbDataAdapter(sql, connString);
break;
case 2: // Sql Data Provider
idbAdapter = new SqlDataAdapter(sql, connString);
break;
case 3: // ODBC Data Provider
idbAdapter = new OdbcDataAdapter(sql, connString);
break;
// case 3: // Add your custom data provider
default:
break;
}
return idbAdapter;
}
}
}
public class Client
{
private void ConnectBtn_Click(object sender, System.EventArgs e)
{
GenericAdoNetComp genDP = new GenericAdoNetComp();
sql = "SELECT * FROM Employees";
if (radioButton1.Checked)
{
connString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\\Northwind.mdb";
conn = genDP.GetConnection(1, connString);
adapter = genDP.GetDataAdapter(1, connString, sql);
}
else if (radioButton2.Checked)
{
connString =
"Data Source=MCB;Initial Catalog=Northwind;user
id=sa;password=;";
conn = genDP.GetConnection(2, connString);
adapter = genDP.GetDataAdapter(2, connString, sql);
}
else if (radioButton3.Checked)
{
// Construct your connection string here
conn = genDP.GetConnection(3, connString);
adapter = genDP.GetDataAdapter(3, connString, sql);
}
try
{
conn.Open();
// Fill a DataSet
DataSet ds = new DataSet();
adapter.Fill(ds);
dataGrid1.DataSource = ds.Tables[0].DefaultView;
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
finally
{
conn.Close();
}
}
} Tag: Best and quikest method to filter a dataview Tag: 124247
SQLconnection problem
I'm pretty new to asp.net and I'm having trouble just trying to set up
a basic SQLconnection within visual studio, I'm sure the problem must
be something basic but I can't seem to find it.
The problem is that the connection string ("Integrated
Security=yes;Initial Catalog=Northwind;Data Source=(local)") is
underlined within the code editor and provides the error text 'Too many
arguements for 'Public Sub New()' when you hover over the text.
a snippet of the code is below:
Imports System.Data
Imports System.Data.SqlClient
' ----- within the page_load function ------
Dim sqlConnection As SQLConnection
sqlConnection = New SqlConnection("Integrated Security=yes;Initial
Catalog=Northwind;Data Source=(local)") Tag: Best and quikest method to filter a dataview Tag: 124246
Binary serializing of dataset in framework 2.0
I was eager to try the new binary serialization of dataset in version 2.0 of
the framework. The new serialization seems to be very fast comparede to the
"binary" serialization in framework 1.1. However I have a few problems
consering columns of the type Date:
Columns set to the value of Date.MinValue and Date.MaxValue get corruptede
depending on the what time zone my computer is set to:
1. When time zone is set to Greenwich Mean Time (GMT) everything works just
fine.
2. When time zone is set to something east of GMT for example GMT +1
(Denmark) coulms with the value Date.MinValue are corrupted during the
serialization and deserialization.
3. When time zone is set to something west of GMT for example GMT -6
(Central Time US) coulms with the value Date.MaxValue are corrupted during
the serialization and deserialization.
I also expirience problems with null valus:
Columns with null values will be set to Date.MinVaule after deserialization
when time time zone is east of GMT.
Is this a bug or am i doing something worg?
I use the code below to demostrate this behaviour:
'Create a dataset for test purpose
'--------------------------------------
Dim drNew As DataRow
Me.DatasetOriginal = New DataSet
Me.DatasetOriginal.Tables.Add("TEST")
Me.DatasetOriginal.Tables("TEST").Columns.Add("Id", GetType(Integer))
Me.DatasetOriginal.Tables("TEST").Columns.Add("Name", GetType(String))
Me.DatasetOriginal.Tables("TEST").Columns.Add("Date", GetType(Date))
Me.DatasetOriginal.Tables("TEST").Columns.Add("Number",
GetType(Integer))
Me.DatasetOriginal.Tables("TEST").Columns.Add("Bool",
GetType(Boolean))
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 1
drNew.Item("Name") = "My name"
drNew.Item("Date") = Date.MaxValue
drNew.Item("Number") = 13
drNew.Item("Bool") = True
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 2
drNew.Item("Date") = Date.MinValue
drNew.Item("Number") = 13
drNew.Item("Bool") = True
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 3
drNew.Item("Name") = "My name"
drNew.Item("Number") = 13
drNew.Item("Bool") = True
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 4
drNew.Item("Name") = "My name"
drNew.Item("Date") = Date.Now
drNew.Item("Bool") = True
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 5
drNew.Item("Name") = "My name"
drNew.Item("Date") = Date.Now
drNew.Item("Number") = 13
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
'Serialize dataset
'-------------------
Dim bin As New
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
Dim Writer2 As New System.IO.StreamWriter("C:\DatasetSerialized.dat")
Try
DatasetOriginal.WriteXml("C:\Serialized.xml",
XmlWriteMode.WriteSchema)
DatasetOriginal.RemotingFormat = SerializationFormat.Binary
bin.Serialize(Writer2.BaseStream, DatasetOriginal)
Writer2.Close()
Writer2 = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'DeSerializer dataset
'-----------------------
Dim bin As New
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
Dim Reader As New System.IO.StreamReader("C:\DatasetSerialized.dat")
Try
DatasetSerialized = New DataSet
DatasetSerialized.RemotingFormat = SerializationFormat.Binary
DatasetSerialized = bin.Deserialize(Reader.BaseStream)
Reader.Close()
Reader = Nothing
DatasetSerialized.WriteXml("C:\DeSerialized.xml",
XmlWriteMode.WriteSchema)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try Tag: Best and quikest method to filter a dataview Tag: 124244
DataColumn.Exression and string formatting
Hi,
I am truing to add a calculated column to my data table. The column must
show something like: "Date: 2005-01-23"
I underestand that we can use Convert to convert a DateTime value to string.
How can I convert it in specific date format?
Thank you,
Alan Tag: Best and quikest method to filter a dataview Tag: 124229
excel datasource and gridview sorting
Hi!
I am somewhat befuddled.
I have a gridview that I have filled using oledb datareader with
data from an excel sheet.(code at bottom)
My problem is that it does not sort and I can't figure out why.
If I import to access and create a datasource using the table in
access I imported from all is well.
But when I fill it with the reader sorting has no effect. the
code below works for gridview1 which uses a access datasource but the first
one is uneffected.
I have set allowsorting = true on both.
But only the second one is sorting both by the button and by
clicking on the hyperlink in the header.
Can anyone tell me what I need to get the excel to sort?
Thanks for your interest!
Len
Protected Sub btnSort_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSort.Click
Dim direction As SortDirection
direction = SortDirection.Ascending
ExcelOut.Sort("Attending", direction)
AccessGridView1.Sort("Attending", direction)
========PageLoad=====================================================
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim DBConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Me.MapPath("Upload/DailyInfo.xls") & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes""")
DBConnection.Open()
Dim strConnString As String = Me.MapPath("Upload/DailyInfo.xls")
Dim SQLString As String = "SELECT Hospital,[Room],[Patient
Name],DOB,Symptom,Notes,Attending,Consulting ,[Initial Date Seen],FU FROM
[INPATIENT PATIENT SVCS$]"
Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
Dim DBReader As OleDbDataReader = DBCommand.ExecuteReader()
ExcelOut.DataSource = DBReader
ExcelOut.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
Html:================================
<asp:GridView ID="ExcelOut" runat="server" Width="800px"
CellPadding="4" ForeColor="#333333" GridLines="None" AllowSorting="True"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="HOSPITAL" HeaderText="HOSPITAL"
SortExpression="HOSPITAL" />
<asp:BoundField DataField="Room" HeaderText="Room"
SortExpression="Room" />
<asp:BoundField DataField="Patient Name" HeaderText="Patient
Name" SortExpression="Patient Name" />
<asp:BoundField DataField="DOB" HeaderText="DOB"
SortExpression="DOB" />
<asp:BoundField DataField="Symptom" HeaderText="Symptom"
SortExpression="Symptom" />
<asp:BoundField DataField="Notes" HeaderText="Notes"
SortExpression="Notes" />
<asp:BoundField DataField="Attending" HeaderText="Attending"
SortExpression="Attending" />
<asp:BoundField DataField="Consulting" HeaderText="Consulting"
SortExpression="Consulting" />
<asp:BoundField DataField="Initial Date Seen"
HeaderText="Initial Date Seen" SortExpression="Initial Date Seen" />
<asp:BoundField DataField="FU" HeaderText="FU"
SortExpression="FU" />
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True"
ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True"
ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333"
HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
a..
b..
c.. Report abuse
-------------------------------------------------------------------------------- Tag: Best and quikest method to filter a dataview Tag: 124214
0(n ***Hot stuff - check this out !!! 0(n
http://www.kaneva.com/checkout/stream.aspx?assetId=2017&free=0 Tag: Best and quikest method to filter a dataview Tag: 124206
Accessing a field in untyped DS
Hello,
I have an copied data set. I'm trying to access a field.
daOrder.Fill(dsOrderSheet);
DataSet dsCopy = new DataSet();
dsCopy = dsOrderSheet.Copy();
// How do you do this part?
output = dsCopy.Tables["orders"].Rows[0].ItemArray["orderDate"];
//or
output = dsCopy.Tables[0].Rows[0].ItemArray[1];
//neither work.
How is this done?
Thanks kindly for any advice on this
Ant Tag: Best and quikest method to filter a dataview Tag: 124205
Adding a Datatable to a dataset
Hi,
I'm currently getting up to speed with ADO.NET & have a few questions on it.
This question relates to why a data table is added to a data set such:
DataTable dtEmp = ds.Tables.Add["employees"]; // From book
i would have thought it would be more like:
DataTable dtEmp = New DataTable("employees");
ds.Tables.Add(dtEmp); // The way I think it should be. Works ok
So what is going on in the first line? (from the book)
Is dtEmp being referenced to the "employees" table that is being added to
the dataset? (As opposed to dtEmp actually being added?). It doesn't seem
intuitutive to me...
Thanks for any comments
Ant Tag: Best and quikest method to filter a dataview Tag: 124203
How do I assign a typed DataTable to its typed DataSet?
Hi,
The following code:
TypedDataSet typedDS = new TypedDataSet();
TypedDataSet.TypedDataTable typedDT;
//
// Code to fill typedDT
//
typedDS.TypedDataTable = typedDT; // Colmpiler error!
gives me a compiler error:
Property or indexer cannot be assigned to -- it is read only
What is the proper way to assign a typed DataTable to its typed DataSet?
Thank you,
Alan Tag: Best and quikest method to filter a dataview Tag: 124201
Datagrid calculated column not updating
I have a datagrid (C# Windows Forms App) bound to a dataset. One column
in the dataset is a calculated column based on an expression that
simply subtracts the value in one column from the value in another
column. I created the dataset using a dataset control and the
properties dialog boxes. The dataset is not populated from a database -
the data is entered into the grid by the user.
By adding the following line of code in the form load event handler:
dtChequeNos.Columns["line_count"].Expression = "to_seq_no -
from_seq_no";
I was able to get the column to perform the calculation but it only
displays the result when the user moves to a new row in the grid. I
need the calculated column to display the result before the user moves
to a new row. Is this possible? Tag: Best and quikest method to filter a dataview Tag: 124199
Q: Updating the correct keys
Hi
I'm hoping somebody can help me with the following problem that has occurred
to me.
Suppose I have two tables in an SQL Server database. Let's call these tables
A and B. Assume that A has two fields: a primary key and another holding a
string. In table B there are three fields: a primary key, a foreign key
(which links to the primary key in A) and other field holding a string.
Suppose I load these tables into a DataSet. I populate table A with some
rows. For each of these rows I create some rows in B which are linked by the
foreign key to A i.e. there is a one to many relationship between A and B.
Once this is done, I update the DataSet via a DataAdaptor such that these
tables are transferred to the SQL database.
My question is this. As far as I can see, the values in the primary key of A
and the corresponding keys in B will not necessarily be the same in the
DataSet and the SQL database! Do you see what I mean? I can't see how the
relationship specified is still valid after the Update.
Can anybody explain this to me?
Thanks in advance
Geoff Tag: Best and quikest method to filter a dataview Tag: 124198
ODBCDataReader NO_DATA error when reading from CSV file
This is a strange one. In ASP.Net v1.1 using VB, I'm using an
ODBCDataReader to read from a CSV file.
Most of the time it works, but if the "description" field contains more
than 2046 chars, I get a NO_DATA error (see below for full error) when
trying to read that row. If this field contains 2046 chars or less,
then everything works fine.
There are about 6800 rows or lines in the CSV file. However, if I
reduce the amount of lines in the CSV to, say 25, then the description
field can contain more than 2046 chars without causing the error.
Here's a sample from my code:
'Save uploaded file to disk.
filCSV.PostedFile.SaveAs(server.MapPath("import/properties.csv"))
'Import data from CSV into MSSQL.
dim strTxtConString as string = "Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=" & Server.MapPath("import/") & ";"
dim conTxt as ODBCConnection = New ODBCConnection(strTxtConString)
dim cmdTxt as ODBCCommand
dim drTxt as ODBCDataReader
conTxt.Open()
cmdTxt = conTxt.CreateCommand()
cmdTxt.CommandText = "SELECT * FROM [properties.csv]"
cmdTxt.CommandType = CommandType.Text
drTxt = cmdTxt.ExecuteReader()
Do While drTxt.Read()
try
response.write("<br>" & drTxt("Number of characters").ToString() & "
" & drTxt("Description").ToString())
catch
response.write("<br>Error at " & drTxt("Number of
characters").ToString() & " characters")
exit do
finally
end try
Loop
drTxt.Close()
conTxt.Close()
Here's some code to generate the CSV file that causes the error:
dim i as integer
dim strDescription as string
for i = 1 to 2000
strDescription = strDescription & "a"
next
response.write(vbcrlf & """Number of characters"", ""Description""" &
vbcrlf)
for i = 1 to 1000
strDescription = strDescription & "a"
response.write(len(strDescription) & ", """ & strDescription & """" &
vbcrlf)
next
And here's the error message that occurs when the number of characters
in the description field of the CSV reaches 2047 chars:
NO_DATA - no error information available
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.Odbc.OdbcException: NO_DATA - no error
information available
Source Error:
Line 53: Do While drTxt.Read()
Line 54: 'try
Line 55: response.write("<br>" & drTxt("Number of
characters").ToString() & " " & drTxt("Description").ToString())
Line 56: 'catch
Line 57: response.write("<br>Error at " & drTxt("Number of
characters").ToString() & " characters")
Thanks very much to anyone who can shed any light on this.
Simon Tag: Best and quikest method to filter a dataview Tag: 124197
"EOF" in Data table
Hi,
I'm just coming from classic ADO (& still thinking that way) & I'm wondering
how you can determine when you get to the EOF in a data table. This is what
I've done below. It works, but I don't think it's the most elegant way of
doing it. How is this normally done? Am I thinking the totally wrong way for
ADO.NET?
Thanks for any thoughts.
Ant
int i = 0;
private void button1_Click(object sender, System.EventArgs e)
{
daEmployees.Fill(dsEmployees);
try
{
string empName = dsEmployees.Employees[i++].FirstName;
txtEmpName.Text = empName;
}
catch
{
// do nothing here
}
} Tag: Best and quikest method to filter a dataview Tag: 124193
Adding commands at runtime to generated adapters
So Iâ??ve got this dataset that was built with the designer. All is well except
that the Select command needs to be generated at runtime for app-specific
reasons.
I thought I would do something clever, which was add a version of Fill() to
the dataset class that takes the SQL string as a parameter. But much to my
dismay, my designer-built dataset class is frequently rebuilt, presumably to
keep it in synch with changes to the table structure.
Thatâ??s actually quite a neat little feature but now Iâ??m in a bind. The first
line in the generated TableAdapterâ??s Fill() does this:
this->Adapter->SelectCommand = this->CommandCollection[0];
Both CommandCollection and the underlying array _commandCollection are
private, as is the underlying SqlAdapter of my TableAdapter. Since I canâ??t
say CommandCollection[0]=this or Adapter->SelectCommand=that Iâ??m at a bit of
a loss as to how Iâ??m supposed to provide a Select statement at runtime.
Deriving yet another class from the generated TableAdapter whose sole purpose
is to override Fill() seems a little silly.
What am I missing here?
--
Scott Bruno
Shiny Entertainment Tag: Best and quikest method to filter a dataview Tag: 124188
Database with diverse data
Can anyone recommend a good whitepaper, strategy, or book on this...
I am going to implement a project where I will be getting several very
different types of data from third-party vendors; the data will often be in
a format only understood by the third-party (encoded). I would like to be
able to store the data in whatever form I get it in, plus a few key fields
for retrieval (e.g. date). I'm curious about different architectures to use
when extracting the data...and at which points to allow algorithms (aka
plug-ins) to unencode the data for either presentation or transport to the
third-party via web services. Loose coupling of the unencode algorithms
from the data is highly desireable since I may have to support lots of
unique data sets.
Amil Tag: Best and quikest method to filter a dataview Tag: 124182
Where to use connection, dataset, datareader ?
Hi,
I have some difficulties in understanding how to use the connection,
dataset, datareader in a form.
Let's suppose I want to create a datareader and when I push a button to
bring another row from the dataset.
Supposedly I create under MyForm_Load event the connection, then a command
and the reader.
SqlConnection conn = new SqlConnection(...);
conn.open();
SqlCommand cmd = conn.CreateCommnad();
cmd.CommandType = ...
cmdCommnadText = ....
SqlReader dr = cmd.ExecuteReader();
If I try to use dr.Read() in side a ButtonRead_Click event, dr is not
recognized.
So, what is the approach here, where I create and use the components to make
them work?
Thanks,
Doru Tag: Best and quikest method to filter a dataview Tag: 124178
EndCurrentEdit does not push changes into the dataset
I have a Windows Form written in C# with a non-edit DataGrid showing a
list of records. When the user selects a row in the grid, the full
details of the record display in a panel which contains textboxes and
comboboxes. The underlying datatable that the detail panel is bound to
only ever has one record at a given time.
When the user edits the detail record and then issues a save command by
clicking the save button, all works well. This is because the focus
moves from the current control and current record to the button.
However if the user issues a Save by pressing the F2 key (caught by the
form's ProcessCmdKey), the focus remains in the current control and
current record. Under these circumstances, the EndCurrentEdit will not
push the changes into the dataset.
I tried calling EndCurrentEdit() on each control (in the control leave
event) in the following ways:
control.BindingContext[dataset.Tables[datatablename]].EndCurrentEdit();
if (control.DataBindings.Count > 0)
{
control.DataBindings[0].BindingManagerBase.EndCurrentEdit();
}
I tried calling EndEdit() on the datatable row
dataset.Tables[datatablename].Rows[rownumber].EndEdit()
I then implemented a BindingManagerBase as follows:
bindMgrBase = this.BindingContext [mydataset, datatablename];
and on save called
bindMgrBase.EndCurrentEdit()
I tried
1) moving programmatically to the next textbox control
2) moving programmatically back to the datagrid
3) moving programmatically to a button
I suspect that the EndCurrentEdit() requires the user to move off the
current record. That is difficult to test as the record detail panel
only ever has one record at a given time.
Is there any way to make this work? Anyone have any ideas for me?
...Susan Tag: Best and quikest method to filter a dataview Tag: 124177
Middle tier Data access object in .net assembly or COM?
Hi,
I am new in VS.net. We try to migrate our VB6 application to .net. In our
VB6 application, we have a middle tier data access object to handle the
connection and stored procedures' calls. Do we still do middle tier DO in
COM? or in just Assembly? why?
Thanks in advance,
DW Tag: Best and quikest method to filter a dataview Tag: 124167