Extra coloumns
hi,
i have written a query n the oledbadapter and i keep
getting rows that should not be displayed in my datagrid.
the query is as follows:
SELECT Caravan_Inv.Caravan_Inv_No,
Caravan_Inv.Caravan_Model, Caravan_Inv.Length,
Caravan_details.Beds, Caravan_details.[Cost/day],
Caravan_details.Caravan_Model AS Expr1,
Caravan_details.Length AS Expr2 FROM Caravan_Inv INNER
JOIN Caravan_details ON Caravan_Inv.Caravan_Model =
Caravan_details.Caravan_Model
the additional coloumns are the Expr1 and Expr2. Even
when i delete them from my query, they still seem to show
up in my result.
Why do they appear? I am using the query builder of the
OLEDBAdapter.
thx Tag: SQL error Tag: 70829
connection string in web.config error
I added the connection string in web.config as follows:
<appSettings>
<add key="connString" value="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=MY_DATABASE;Data Source=IP_ADDRESS" />
</appSettings>
In mypage.aspx.vb page, I open the connection with connString variable:
Dim conn As New SqlConnection(connString)
But it has compile error:
name 'connString' is not declared.
Any ideas? Tag: SQL error Tag: 70824
timeout in sp but not in normal select
hi!
I use
- .net framework 1.1
- sql server 2000 sp 3a
- windows 2000 server
- asp.net/c#
- system.Data.SqlClient
- latest sp's & hotfixes
when I use a stored procedure (result 50 rows, execution time 2 seconds,
about 600,000 rows in the table) in a c# code I'll get a "timeout expired"
SqlException.
...
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "sp_x";
myCommand.CommandTimeout = 0;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
x = myReader["x"].toString(); // <<-- TIMEOUT
}
...
the same sql in a "normal" select statement will work.
...
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = "select x from ...";
myCommand.CommandTimeout = 0;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
x = myReader["x"].toString(); // <<-- OK
}
...
if I reduce the amount of rows (result about 5 rows, execution time less
than 0.2 seconds, about 10,000 rows in the table) the stored procedure will
work in c#.
but the "large" stored procedure will work with all other softwareproducts
(like ms access, dts wizard, and so on).
thanx for your help
mac Tag: SQL error Tag: 70823
beginner questions
Hi,
Im new to VB.net and wanted to find out about the best
way to code my connection to my access db.
I wanted to know if it is necessary to place the
connection string and create a new instance of the
olebconnection class on every page that uses a sql
statement.
Is it possible to place this code in a module and then
just use the varible the Conn String is placed in from
all the forms??
What about all the sql statements? can i also code then
in the module and call then whereever the are required in
my application??
Thx for your help Tag: SQL error Tag: 70816
Execute requires the command to have a transaction object when the connection as
This is the sequence of events :
foo() {
SqlConnection conn = conn.Open();
SqlTransaction tx = conn.BeginTransaction();
... // do something
AnotherModule.bar(conn);
}
AnotherModule.bar(SqlConnection conn) {
SqlCommand cmd = conn.CreateCommand();
.. // calls a stored procedure
..
conn.ExecuteNonQuery(); // <<== Exception here
}
The exception is :-
"Execute requires the command to have a transaction object
when the connection assigned to the command is in a
pending local transaction. The Transaction property of
the command has not been initialized."
Why is it required for the bar() method to know the
transaction. It just wants to re-use the same connection
object. In fact the transaction is handled inside the
stored procedure that it is calling.
The api expects that, in the SqlCommand that is created in
bar() method also set the same transaction that the
connection is using. Why is this required?
I do not want to tie the transaction created in foo() to
the SqlCommand created in bar(). Is not a realistic
situation? What if Im calling a third party stored
procedure?
Looks to me that the the only way it could be solved is by
opening a new connection in bar(), instead of getting the
connection object as a parameter from the caller.
Anybody faced this problem?
- shiv Tag: SQL error Tag: 70815
parent/child tables and identity problems: a scenario
I'm having strange errors with a C# app using form controls bound to a typed
dataset, which holds a parent-table with a single record and a number of
children tables with many records. As I'm new to ADO.NET, first of all I'd
like to know if I'm doing something wrong in my general approach. Here's a
simplified scenario, it's a bit long but I hope it can be useful to newbies
like me, facing the identity problems when updating a dataset. Thank you
very much for your patience!
(a) I use MSDE and the corresponding SqlClient namespace objects. All the
identity values in my tables are autonumber; in the dataset, autonumber seed
and step are set to -1 after their construction, so that the records added
in the "in memory" dataset will be granted to have a different identity from
any database record.
(b) All the insert commands use an output parameter (@newID) set to the new
identity value, e.g. the T-SQL code looks like:
ALTER PROCEDURE AnInsertCommand
(
...
@newID int OUTPUT
)
AS
INSERT INTO ... ;
SET @newID = SCOPE_IDENTITY()
The corresponding command objects include this @newID in their params
collection and map it to the table primary key field, so that when the
command is executed the dataset table will be updated to hold the "real"
autonumber value assigned by database (thus replacing the negative value
used in-memory).
(c) After generating the dataset, I have manually added the required
required foreign-key relations to it using the VS dataset designer, so that
the parent/child relationship are set up correctly: each primary key in a
child table is linked to the corresponding foreign key in the parent table.
(d) Let's say the parent table in dataset is "Customer", and the child table
is "Country". Customer has a countryID acting as the foreign key to the
Country child table. In my application, the user selects a single customer
to edit, or wants to enter a new customer; the form shows his data in bound
controls, and the data for child tables are typically shown in combo boxes
(e.g. a list of countries the user can select from). Thus, the dataset will
always contain a single parent record (=1 customer) and all the children
records (=countries list). The user can also add new countries when editing
a single customer record.
(e) When data are to be stored into database, the update policy is:
1) call BindingContext EndCurrentEdit for each table in the dataset, e.g.:
BindingContext[myDataSet, "Table Name"].EndCurrentEdit();
2) open the connection to database and begin a transaction
3) call Update for each CHILD table for NEW records only, e.g.:
countryDataAdapter.Update(myDataSet.TCountry.Select("","",DataViewRowState.A
dded));
this inserts the newly added countries only and updates the dataset Country
table so that the autonumber values reflect the "real" (non-negative) values
assigned by database; thus, a countryID = -10 becomes e.g. 67; also, the
relationships I have setup in the dataset cascade this update to the linked
parent table, so that the foreign key -10 becomes 67 too.
4) call Update for the PARENT table for NEW or MODIFIED records only
(deleted records will be processed later);
5) call Update for each CHILD table for all other records (e.g. MODIFIED or
DELETED);
6) call Update for the PARENT table for DELETED records.
7) commit the transaction and call AcceptChanges for the whole dataset.
8) close the connection anyway (in a finally {} block; in the catch{} block
the transaction is rolled back).
Now, this seems to work fine: I edit and add records, and autonumber values
are treated as expected and their changes propagated to the parent as
required. Here's the problem: when I'm editing an existing parent record and
add a new child record (e.g. a new country) and then store data, all works
fine; when I create a NEW parent record and do the same, the Update method
for the child table raises an "Object reference not set to instance of an
object" exception: as far as I can check with the debugger, all the values
in the dataset are as expected: the parent record has a negative ID, as for
the newly added child record, and both are linked correctly
(parent.countryID = child.countryID). As everything works when updating an
existing parent record, I'd expect the code to work for a new record too, as
the only difference is that a new record has a negative ID in the parent
table (which anyway should not matter for updating the child table, which is
linked via a foreign key).
Could anyone tell me if anything is "philosophically" wrong with this
approach? I cannot understand the reason for such errors... Tag: SQL error Tag: 70808
Why don't DataGrid Use DataColumn.Caption
I found following thread
source :-
http://www.dotnet247.com/247reference/msgs/16/84118.aspx
-----------------------------------------------------------
-----------------
I need to change the caption of a DataColumn and I wrote
this but it doesn't
work
DataSet ds = new DataSet();
sqlDataAdapter1.Fill(ds,"Orders");
ds.Tables[0].Columns[0].Caption = "NEW CAPTION";
dataGrid1.SetDataBinding(ds,"Orders");
instead this code work
ds.Tables[0].Columns[0].ColumnName = "NEW CAPTION";
why ?
thank Paps
-----------------------------------------------------------
-----------------
Hi Paps,
Did you want to change the DataGrid's column caption? When
binding a
DataTable to DataGrid, it will use the ColumnName of a
dataColumn as its
column caption in DataGrid.
Luke
-----------------------------------------------------------
-----------------
Hi Luke
So if I need to change the DataGrid column Caption I need
to change the
DataSet.Tables[0].Columns[0].ColumnName and not the
Caption ?
But in this way I loose consistence with my dataSoruce ?
Thanks Paps
-----------------------------------------------------------
-----------------
Hello Paps,
There seems to be an issue with datagrid where it does not
use the caption
property to set the header text for the columns.
I was able to get around that by creating my own
datagridtablestyle and
datagridcolumnstyle and setting the headertext properties.
Dim ts1 As New DataGridTableStyle()
ts1.MappingName = "Items"
Dim TextCol3 As New DataGridTextBoxColumn()
TextCol3.MappingName = "Description"
TextCol3.HeaderText = "Item Description"
TextCol3.TextBox.ForeColor = System.Drawing.Color.Brown
TextCol3.Width = 150
ts1.GridColumnStyles.Add(TextCol3)
MyDataGrid.TableStyles.Add(ts1)
Hope this helps.
Ramesh Thyagarajan
Microsoft Developer support
-----------------------------------------------------------
------
However there is lot of headache if we have to use
DataGridTableStyle to save changing the DataColumn's
column names. If there are lot of grids in project which
is very common situation this is really cumbersome task.
Also there is maintenance issue, simple addition or
removal of some column in display compels you to work on
both DAL and GUI layer. Can we avoid this in some decent
way so that we get best of both worlds, I mean I don't
need to loose the consistency of column names with
datasource while using DataColumn and also I don't need to
do extra work IN GUI layer. Tag: SQL error Tag: 70804
how to get column name for a table in MS-Access?
Hi,
Does anyone know how to get column names for a specific table in Access?
For Sql Server, if I need to get column names for a specific table, I can use SQL statement "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = *"; I wonder how if there is a similiar way to achieve the same in Access?
Thanks for your help in advance! Tag: SQL error Tag: 70802
How do I implement a "SELECT DISTINCT" equivalent?
Hi.
I have a dataset which is the result of a merge operation from several
source datasets (via the Merge method). Problem is that I dont control the
source datasets and I need to ensure that I dont get duplicate results in
the final (merged) dataset. I have a primary key on a columns on the tables
in all datasets so I can enforce a constraint on that. However, I want to
prune the results of duplicates, rather than failing outright. Effectively,
I want the equivalent of a "SELECT DISTINCT" SQL statement.
How do I do this? How do I quickly prune duplicate results to return only
distinct rows?
Thanks!
-Danny- Tag: SQL error Tag: 70798
Problems with ADO.NET Core Reference Code
All,
I'm developing a database system similar to the example show in
Chap 13 (Step 10) of David Sceppa's excellent ADO.NET book. However,
I'm having a problem with my code, and the same error is popping up in
the example code. In the example, I can add a new Order, then when I
attempt to add details to the order, I get an "Exception: Column
'ProductID' does not allow nulls." Exception. I'm not sure how to fix
this situation... Any thoughts?
Thanks,
Jeff Tag: SQL error Tag: 70788
Query and variables help.
Hi,
I wanted to know how i can pass varibales from my vb.net
interface to my Access xp database??
What i want to do is use the date selected by the user
from 2 dtpickers and use these in my OLEDataAdapter query.
(I dont know what to type in in the OLEDataAdapter query
builder).
what do i write in my query to represent these variables
in my query??
what i want to write a query that selects the rooms that
are not booked withing the date period specified by the
user.
I have 3 tables that i may need to use:
Room Details (a descrption of all the rooms in the hotel):
RoomNo
RoomName
Room Booking (a link table to handle the M:N
relationship):
RoomNo
BookingNo
Booking Table (Contains details of the guest and his
booking details):
BookingNo
DateOfArrival
DateOfDeparture
Can anyone plz help me with this. Iv been stuck for
ages :o( Tag: SQL error Tag: 70787
ConcurrencyViolation Error.... Driving me nuts!
This is more of an plea for help question. Before I go
into details, I am using Windows 2k, VS 2003, MySql Server
and an open source MySql .NET driver (MySqlDriverCS). (I
am aware that the fact that I am using an open source
driver could be the cause of my error).
I have checked many message boards about my error and have
found that people using Access, SQL Server, MySql, Oracle
all get this error and for similar reasons.
Error: Concurrency violation: the
DeleteCommand affected 0 records.
I receive the exception whenever I call the
DataAdapter.Update() method after marking DataTable Rows
for deletion.
My DeleteCommand.Text = "DELETE FROM mynames WHERE ID =
@ID"
I delete the Row, but doing a:
Dim key as new Integer(whatevermykeyis)
row = myTable.Rows.Find(key)
row.Delete()
Sometimes the row will be deleted from my Database,
sometimes it wont, but I still get the error no matter
what happens. After I get the exception I cannot do
anything else in my app and am forced to restart it.
What I believe could be the reason:
When add Parameters to mySqlCommands, I cannot add a size
property. Also, the datatypes do not properly match up
with MySql database types. In my database I used a
SmallInt(4) for my ID Column data type. But when I add
the parameters to my DeleteCommand, the best I can come up
with is DBType.Int16 (which is a 5 digit number).
Things I have tried:
1. Changed Database to NOT use a ID field and use a
varchar(16) field.
2. Changed my deleteCommand parameters from almost every
datatype I could, Int16, Int32,...
3. Changed my DeleteCommand String.
4. Many other changes to my code...etc
No matter what changes I do, I've always gotten that
exception. Can anyone fill me in, or give me some ideas
to try out. My database data doesnt change from the time
I Fill my DataSet to the time I Update my Database.
Thanks,
Dan Tag: SQL error Tag: 70785
sqldatareader.read() is false with breakpoint
I have the following code:
if (SqlDR != null)
{
while (SqlDR.Read())
{
feed indexfeed = ReturnInstance();
indexfeed.FeedID = new Guid(SqlDR["ID"].ToString());
m_feedarr.Add(indexfeed);
}
}
When i put a breakpoint on (or before) the while line (SqlDR.Read()) the
code will not step inside the while loop, and the Watch window shows the
SqlRD.Read() as true. As soon as I reach that line and step into the next
line, the SqlRead.Read() turns to false and skips over the entire while
loop.
If I put the breakpoint in the while loop, the execution will actually stop
at the breakpoint in the loop.
The question is, why is it that I cannot put a breakpoint before the .Read()
on the while loop and step into the loop, but I can put a breakpoint inside
the while loop and hit the breakpoint on the same queries?
TIA Tag: SQL error Tag: 70782
System Error Executing CREATE PROCEDURE Script with SqlCommand
I've searched for limitations of the SqlCommand object with regard to
what is valid SQL syntax. Any scripts I have with the GO batch
command don't work unless I remove the GO statement. This is easily
done. However, I used the VS.NET "Generate SQL Script" command to
create a DDL script for a stored procedure I wrote, and it blows up
with a SqlException whose details are simply "System error."
To troubleshoot, I've stripped the stored procedure down to things
I've seen work elsewhere, and I still get the same error. Can someone
please shed some light on what's going on. The text of the stored
procedure is below. Any help would be greatly appreciated.
Thanks,
Marvin
-------------------------------------
-------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[get_next_unique_id]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[get_next_unique_id]
GO
CREATE PROCEDURE get_next_unique_id
@TableName VARCHAR(50)
AS
/******************************************************************************
** File: get_next_unique_id.sql
** Name: get_next_unique_id
** Desc: This procedure returns the next unique id for the primary
key
** field of the given table.
** Auth: Marvin S. Addison
** Date: 4/5/2003
*******************************************************************************/
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
/* Initialize counters if necessary */
DECLARE @record_count INT
SELECT @record_count = COUNT(*) FROM UniqueIds
IF @record_count = 0
BEGIN
DECLARE curUsrTables CURSOR
FOR
SELECT name FROM dbo.sysobjects WHERE OBJECTPROPERTY(id,
N'IsUserTable') = 1
OPEN curUsrTables
DECLARE @name VARCHAR(50)
FETCH NEXT FROM curUsrTables INTO @name
WHILE @@fetch_status = 0
BEGIN
IF @name <> 'UniqueIDs' AND @name <> 'dtproperties'
INSERT INTO UniqueIDs (TableName, UniqueID) VALUES (@name, 0)
FETCH NEXT FROM curUsrTables INTO @name
END
CLOSE curUsrTables
DEALLOCATE curUsrTables
END
UPDATE UniqueIDs SET UniqueID = UniqueID + 1 WHERE
TableName=@TableName
SELECT UniqueID FROM UniqueIDS WHERE TableName=@TableName
COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO Tag: SQL error Tag: 70780
Anonymous Block in OleDbCommand - DataAdapter.Fill() hangs
Hello,
I would like to execute an Oracle anonymous block using the following
code. The code works just fine with a regular Insert/Update/Select
commands, but with even a very simple anonymous block the
DataAdapter.Fill() method will hang. No error is returned.
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("DECLARE myID NUMBER; BEGIN INSERT INTO
doc_pkg(doc_pkg_id) VALUES(doc_pkg_id_seq.nextval) RETURNING
doc_pkg_id INTO myID; END; ");
OleDbConnection dbcConn = new
OleDbConnection("Provider=OraOLEDB.Oracle;Data Source=mydb;User
Id=user;Password=psw");
OleDbCommand myCommand = new OleDbCommand(sbQuery.ToString(),
dbcConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myCommand);
dbcConn.Open();
myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet,"Results");
dbcConn.Close();
I have also tried Provider=MSDAORA in teh connection string, but the
results were no different.
Any ideas would be appreciated. Tag: SQL error Tag: 70778
Is there a method to convert SqlCommand text
In the SqlCommand, there is CommandText which has the parameterized
SQL. And the SqlParamterCollection which contains parameters.
Do anyone know how to convert this to a no parametric SQL string which
can be executed with isql ( not writting my own code. ).
Thanks
James Tag: SQL error Tag: 70774
Unique id
Hi
I am using access and I am looking for a safe reliable way to create unique
ids in a multi-user environment. Any ideas?
Thanks
Regards Tag: SQL error Tag: 70763
retrieve PageCount of DataGrid
I have ASPX page with DataGrid object. To fill this object I use DataTable
that contain result of stored procedure. In my case it's about 300 rows.
I would like to display this table with paging.
This is code to retrieve table:
DataTable dtMainTable = null;
DataSet Ds = SqlHelper.ExecuteDataset(strConnectionString,
CommandType.StoredProcedure, "a_test_procedure", null);
Ds.Tables[0].TableName = "PermissonFunctions";
dtMainTable = Ds.Tables[0];
this is code for data bind of datagid:
ReportGrid.DataSource = dtMainTable;
ReportGrid.AllowPaging = true;
ReportGrid.CurrentPageIndex = 0;
ReportGrid.DataBind();
This code displays cool table on ASPX page.
My problem is that I need to display the total number of pages that could be
displayed.
I'm trying to use:
ReportGrid.PageCount, but any time returns me zero (0). Please help me to
retrieve PageCount.
Thank you, David Potahinsky Tag: SQL error Tag: 70759
DataRow Index
How can I know the index of a DataRow that belongs to a
DataTable ?
I have the DataRow and I want your Rows' index.
Tanks.
Mauricio (Brazil) Tag: SQL error Tag: 70755
How to assign Expression property of a DataColumn to a custom function?
I want to assign a column of my datagrid to the return
value of a
custom function. Input parameter of the function should be
the value
of another column in the same datagrid.
Something like this:
tempDataColumn = tempDataTable.Columns.Add("MyID",
GetType(String))
tempDataColumn2 = tempDataTable.Columns.Add("MyDesc",
GetType(String))
tempDataColumn2.Expression = GetDesc("MyID")
Public Function GetDesc(ByVal ShortName As String) As
String
'//Some LOgic
Return "Desc"
End Function
Basically, I want to do some look-up before populating the
datatable
and binding it to a datagrid.
Would appreciate any help or any other way to do the same
stuff.
Thanks and Regards, Tag: SQL error Tag: 70754
How to assign DataColumn's Expression property to my own function?
I want to assign a column of my datagrid to the return value of a
custom function. Input parameter of the function should be the value
of another column in the same datagrid.
Something like this:
tempDataColumn = tempDataTable.Columns.Add("MyID",
GetType(String))
tempDataColumn2 = tempDataTable.Columns.Add("MyDesc",
GetType(String))
tempDataColumn2.Expression = GetDesc("MyID")
Public Function GetDesc(ByVal ShortName As String) As String
'//Some LOgic
Return "Desc"
End Function
Basically, I want to do some look-up before populating the datatable
and binding it to a datagrid.
Would appreciate any help or any other way to do the same stuff.
Thanks and Regards, Tag: SQL error Tag: 70753
Using a DSN with OldDB
I currenlty have my .Net code using OleDB connections with Connection
Strings. I would like to switch over to using a DSN instead of having the
strings in my code. How can I do this? Do I have to change all my OleDB code
to ODBC? Tag: SQL error Tag: 70748
Is this a datagrid/combo bug???
Hi
I have a form with a combo and a datagrid
The combo has these properties:
Datasource: DataSet11.tPersoner
Displaymember: ChristianName
It does not have any databindings
The datagrid has these properties:
Dim dv As DataView = DataSet11.tPersoner.DefaultView
DataGrid1.DataSource = dv
Because I want different bindingmanagers for the two controls.
Now when I change the current cell in the datagrid (by clicking with the
mouse) they are not synchronized (which I don´t want them to be). But if I
sort the datagrid by clicking on a column header suddenly the combo shows
the ChristianName of an (appearantly random) row in the datagrid.
(I have created two different CurrencyManagers for the controls. And they
are not synchronized. their .Position are not the same)
It seems strange to me.
Any explanations?
best regards
Jan Tag: SQL error Tag: 70741
Filtering / comparind datasets
I'm trying to find the best way to solve a problem.
I have a web program, written in VB, that loads a datafile from disket
and inserts it into a dataset, dsOriginal, which is then bound to a
datagrid for the user to edit and delete incorrect fields. Leaving
out all the details of the editting, when the user hits the submit
button, I want it to compare these datagrid records to a table from a
database to check if the records already exist or have inputted
previously. If they don't exist already, I will input these records
into that table.
The unique identifier of the records is three fields: s_number,
s_date, r_number.
Since there is usually only one file per date, it seems easy enouph to
just query the database for that date, populate datasetFromDataBase
with that info, and then compare the two datasets. If there is any
that are the same, you could create a third dataset with the
duplicates, and delete them from dsOriginal.
Or I could just create a stored procedure that inserts one record at a
time from dsOriginal to the database, and if it fails, I could
populate a new dataset with the failed records to be displayed - after
the inputting is completed. This scares me alot because it gives the
user little control over what goes in and what's left out.
I could always write a stored procedure that just checks if each
record exists against the database and doesn't even do Updating,
allowing the user to decide what they want to do with the bad record
before updating the to the database. But like I said, I could also
just compare dsOriginal to datasetFromDataBase and do something with
one's that match.
Anyone ever come across this scenerio? Any advice as to the best
method for implementing this procedure?
-Thanks, Zack Tag: SQL error Tag: 70738
Invalid attempt to Read when reader is closed.
Ok, here's a really strange one... let's see if anyone else has seen this or
if they have a fix for it.
I have some VB.NET code on an ASP.NET application... it opens a datareader,
then calls a function (passing the datareader as a parameter) to assign the
values to the objects properties.
Every once in a while, right in the middle of the Assign function (which is
just basically a bunch of
_variable = dr("sqlparam")
It will give me this error message:
Invalid attempt to Read when reader is closed.
I can hit refresh in the browser and it works fine. Then every once in a
while it'll happen again.
Now, he's the worst part - it ONLY HAPPENS when the app was built in
"Release" mode - it doesn't happen when it's built in "Debug" mode.
Any takers on this one?
Andy, green@ids.net Tag: SQL error Tag: 70732
Problems with multiplie tables in ADO.NET
Following on from my previous posting about this problem,
I have incorporated all the suggestions from the previous
three replies to my original posting (the latter posted
again below). I have changed the table name parameters on
the tablemappings method of the OleDBDataAdapters
to 'Table1' and 'Table2', and I have used Try... Catch
error-trapping to catch the error. The result is the same;
the follwing message is generated on the last but one
line, i.e. ad2.fill(ds):
Unhandled Exception: System.Data.OleDb.OleDbException:
Unspecified error: E_FAIL(0x80004005)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandl
ing(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleR
esult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText
(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand
(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal
(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader
(CommandBehavior behavior)
at
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.Execu
teReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(Object data,
Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet
dataSet)
at ASMWebDataHandler.frmParseData.CheckRecords
(Collection& colPrices, Collection& colArticles) in
C:\Development Projects\Distribution New
System\ASMWebDataHandler\ParseData.vb:line 860
at ASMWebDataHandler.frmParseData.cmdParse_Click(Object
sender, EventArgs e) in C:\Development
Projects\Distribution New
System\ASMWebDataHandler\ParseData.vb:line 627
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs
mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m,
MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage
(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc
(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback
(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at
System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW
(MSG& msg)
at
System.Windows.Forms.ComponentManager.System.Windows.Forms.
UnsafeNativeMethods+IMsoComponentManager.FPushMessageLoop
(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.ThreadContext.RunMessageLoop
(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at ASMWebDataHandler.frmParseData.Main() in
C:\Development Projects\Distribution New
System\ASMWebDataHandler\ParseData.vb:line 7
The program '[2308] ASMWebDataHandler.exe' has exited with
code 0 (0x0).
Obviously, this is not of much use to me, since the cause
of the error is 'unspecified error'. This seems to be
happening quite regularly, whenever I try to use a query
that involves more than one table, or to add two tables to
a dataset, or when I run a query that returns (correctly)
no rows or only one row containing one value. i have
encountered this 'unspecified error' problem both with
DataSets/OleDBDataAdapters, and when running some similar
queries with an OleDbDataReader object, all running with
the same Access XP back end. Any ideas what may cause this?
Thanks
Ian
--------------------------------------------------------
Previous posting
--------------------------------------------------------
I have been having a number of problems trying to do
some joined queries (i.e. involving more than one table
in an SQL statement) in ADO.NET/VB.NET, using an Access XP
database running through the OleDb classes.
As an example, the following code falls over on the second
of the two fill statements (i.e. the last but one line.)
Dim cn As OleDbConnection, com1 As OleDbCommand = Nothing,
com2 As OleDbCommand = Nothing
Dim ad1, ad2 As OleDbDataAdapter, ds As DataSet
Dim sSQL1 As String, sSQL2 As String
sSQL1 = "SELECT collection_supplier_item_iKey_model,
collection_supplier_item_iKey_fabric,
collection_supplier_item_iKey_size FROM INPUT_ARTICLES"
sSQL2 = "SELECT model, fabric, size FROM INPUT_PRICES"
ds = New DataSet()
cn = New OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=DataRepository.mdb")
com1 = New OleDbCommand(sSQL1, cn)
ad1 = New OleDbDataAdapter()
ad1.SelectCommand = com1
ad1.TableMappings.Add("Table", "INPUT_ARTICLES")
com2 = New OleDbCommand(sSQL2, cn)
ad2 = New OleDbDataAdapter()
ad2.SelectCommand = com2
ad2.TableMappings.Add("Table", "INPUT_PRICES")
cn.Open()
ad1.Fill(ds)
ad2.Fill(ds)
cn.Close()
The error message that results is :
Unhandled Exception: System.Data.OleDb.OleDbException:
Unspecified error: E_FAIL(0x80004005)
I.e it doesnt actually give any clue as to what the
problem is. Having played around with this problem for a
while, including using a data reader to try to access
these two tables by means of a single joined SQL
statement, and each time running across the same error
message, which doesnt actually say what the problem is, I
am quite mystified. Any ideas what may be causing this?
Thanks
Ian Tag: SQL error Tag: 70725
DataGrid merging rows
How can I merging the rows in my DataGrid in Windows Form?
I'm looking for something like Webform's RowSpam or like MergeRow
FlexGrid's propriety.
Thank you
Marco Tag: SQL error Tag: 70722
concurrency violation with empty date field
I am reading a dataset from a MySQL database which includes an empty date
field.
I am binding the dataset to several controls (textboxes, etc.)
When I change a value (not the empty date field) and want to update, I get a
concurrency error. This obviously occurs because of the empty date field,
because when I exclude the date field from the SQL statement everything
works fine.
How can I avoid the error?
Regrads, Christoph Tag: SQL error Tag: 70713
How to Check for a Zero Entry in a DG?
I have a master DataSet... I then select (filter) some items and display them
on a 2nd DataGrid for editing (only the 6th column gets any editing).
The following code works fine. BUT what it does not do is check for a Zero
(not a Null) value. I know I can do a search for the 7 rows (maximum) in
column #6 for a Zero and reject the entry (and give a message box)? But I
think it would be under the 'not best coding' flag.
I know I could do an IF THEN kind of check for each column #6 in each row.
But IS there a simpler way?
My current code (for accepting a Row Deletion and Edit of Column #6) is as
follows:
If DsEditData.HasChanges Then
Try
Dim exDataModified, exDataDeleted As Integer
' Edit Hours
exDataDeleted = daEditData.Update(DsEditData.tblTimeEntry)
' Delete Row
exDataModified += daEditData.Update(DsEditData.tblTimeEntry.Select("",
"", DataViewRowState.Deleted))
Catch ex As Exception
MessageBox.Show(ex.Message, "Update failed!", MessageBoxButtons.OK,
MessageBoxIcon.Error)
End Try
Else
MessageBox.Show("No Changes Made!", "SubmitChanges",
MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
Any tips appreciated.
Regards,
Bruce Tag: SQL error Tag: 70693
problem with PageCount
I have ASPX page with DataGrid object. To fill this object I use DataTable
that contain result of stored procedure. In my case it's about 300 rows.
I would like to display this table with paging.
The code like:
ReportGrid.DataSource = doData.GetTestTable();
ReportGrid.AllowPaging = true;
ReportGrid.PageSize = 20;
ReportGrid.DataBind();
Displays cool table on ASPX page.
My problem is that I need to display the total number of pages that could be
displayed.
I'm trying to use:
ReportGrid.PageCount any time returns me zero (0). Please help me to
retrieve PageCount.
Thank you, David Potahinsky Tag: SQL error Tag: 70686