Long-Running DataSet.Fill Ties-Up System
Hi,
Here's my situation. I developed a Microsoft Access query that polls
a remote Intersystems Caché system where all my data resides. Access
has a few features I prefer to use, in particular some agregate
queries I'd find difficult to replicate on my Caché system. Here's an
example of one query:
PARAMETERS [@qpar_Start_Date] DateTime, [@qpar_End_Date] DateTime;
SELECT SYSTEM_R001_Summary_Data_LIV.Buyer_ID,
Count(SYSTEM_R001_Summary_Data_LIV.Buyer_ID) AS [Count]
FROM SYSTEM_R002_Line_Detail_LIV INNER JOIN
SYSTEM_R001_Summary_Data_LIV ON
SYSTEM_R002_Line_Detail_LIV.Line_KeyTo_R001_Summary_Data =
SYSTEM_R001_Summary_Data_LIV.Summary_Data_R001_Key
WHERE (((SYSTEM_R001_Summary_Data_LIV.PO_Date)>=[@qpar_Start_Date] And
(SYSTEM_R001_Summary_Data_LIV.PO_Date)<=[@qpar_End_Date]) AND
((SYSTEM_R002_Line_Detail_LIV.Item_Number) Is Not Null))
GROUP BY SYSTEM_R001_Summary_Data_LIV.Buyer_ID;
I use this query as the datasource for another query. Anyhow, suffice
to say that I'm heavily invested in MS-Access for performing local
queries and I'm not likely to change that anytime soon.
These Access-based queries are bound to some ASPX files which users
interface with through our intranet website. In this particular case,
the parameters in the query (above) are filled based on data that
comes from the web site. All this works very well, with one
exception:
The user submits the form that executes the code-behind. This starts
a process whereby the query is run by way of OleDbDataAdapter. A
DataSet is filled, and then further things occur depending on the
result of the data. The problem is, when the user launches the query,
every other OleDbDataAdapter call stalls until the first one finishes.
Another problem that I believe is related is that I can't launch more
than one query at a time.
Here's a simplified bit of code that illustrates the problem. For the
sake of the example, this method would be called by a Button_Click
event:
Private Sub POsByBuyerUncat()
Dim oDs As New System.Data.DataSet
Try
With oDaPOsByBuyerUncat
.SelectCommand.Parameters("[@qpar_Start_Date]").Value = _
Convert.ToDateTime(tbStartDate.Text)
.SelectCommand.Parameters("[@qpar_End_Date]").Value = _
Convert.ToDateTime(tbEndDate.Text)
.Fill(oDs, "POsByBuyerUncat")
End With
Catch ex As Exception
lblRuntimeError.Text = ex.ToString
Finally
If Not oDaPOsByBuyerUncat Is Nothing Then
oDaPOsByBuyerUncat.Dispose()
oDaPOsByBuyerUncat = Nothing
End If
If Not oDs Is Nothing Then
oDs.Dispose()
oDs = Nothing
End If
End Try
End Sub
The problem occurs when the .Fill executes. No other .Fill statements
can run on the same IIS machine while the .Fill is running. That
includes OleDb calls in other IIS applications, in other .MDB files,
and so forth.
My remote Caché system is still running fine, and will gladly run all
kinds of other jobs so I don't think it's responsible. I have toyed
with ODBC Pooling, multithreading, file permissions, and many other
combinations of factors but I can't figure it out.
Has anyone ever seen this? And if so, how do you fix it?
Please reply, either to this newsgroup or by e-mail at
David.PyperDontSpamMeMUHC.McGill.CA.
David Tag: test Tag: 73702
General Functions for Using ADO.NET
Hi, all,
I wrote the following 3 functions in VB.NET. I think it
might be useful for programmers new to ADO.NET.
You just include this in a VB module. Then it can be
accessed in whole project.
These functions are for sql server. You can easily modify
them for oledb.
Bin Song, MCP
'Definition of CONNECTION_STRING
Public Const CONNECTION_STRING = "data
source=MYSERVENAME;initial catalog=MYDATABASENAME;user
id=MYUSERNAME;password=MYPASSWORD;"
'Function to return a dataset
'Syntax: Dim ds as DataSet = GetDataSet("Select * From
tblCustomer")
Public Function GetDataSet(ByVal sqlSelect As String)
As DataSet
Dim sqlCnn As SqlConnection
Dim sqlAdp As SqlDataAdapter
Dim sqlDst As DataSet
Try
sqlCnn = New SqlConnection(CONNECTION_STRING)
sqlCnn.Open()
sqlAdp = New SqlDataAdapter(sqlSelect, sqlCnn)
sqlDst = New DataSet()
sqlAdp.Fill(sqlDst)
GetDataSet = sqlDst
sqlAdp.Dispose()
sqlCnn.Close()
sqlCnn.Dispose()
Catch eGetDataSet As Exception
Throw eGetDataSet
End Try
End Function
'Function to return a data reader
'Syntax: Dim ds as sqlDataReader = GetDataReader("Select *
From tblCustomer")
Public Function GetDataReader(ByVal sqlSelect As
String) As SqlDataReader
'Close the datareader after Using this function to
get a datareader
Dim sqlCnn As SqlConnection
Dim sqlCmd As SqlCommand
Try
sqlCnn = New SqlConnection(CONNECTION_STRING)
sqlCnn.Open()
sqlCmd = New SqlCommand(sqlSelect, sqlCnn)
GetDataReader = sqlCmd.ExecuteReader
(CommandBehavior.CloseConnection)
Catch eGetDataReader As Exception
Throw eGetDataReader
End Try
End Function
'sub to execute a data process query
'Syntax: ExecNonQuery("Delete * From tblCustomer")
Public Sub ExecNonQuery(ByVal sqlNonQuery As String)
Dim sqlCnn As SqlConnection
Dim sqlCmd As SqlCommand
Try
sqlCnn = New SqlConnection(CONNECTION_STRING)
sqlCnn.Open()
sqlCmd = New SqlCommand(sqlNonQuery, sqlCnn)
sqlCmd.ExecuteNonQuery()
sqlCmd.Dispose()
sqlCnn.Close()
sqlCnn.Dispose()
Catch eExecNonQuery As Exception
Throw eExecNonQuery
End Try
End Sub Tag: test Tag: 73699
vb.net MDI multiple db connections?
I have multiple forms that get shown when the users clicks
on a button. Each child form has data on it the is
recieved from the same database but from different tables.
My problem is that I can't get this to work without adding
a new db connection on each form. I've tried jsut adding
a dataset on the new form but it automatically creates a
new db connection for it.
It doesn't seem very elegant to have multiple connection
to the same DB in one project. I'm new at programming so
I was hoping for a simple solution.
Thank you Tag: test Tag: 73698
Detecting and counting changes in a dataset
I have a dataset which consists of a main table and a bunch of child tables.
I want, firstly, to be able to detect if a particular record has changed,
but is there an easy and quick way to tell if the children of a datarow in
the parent table have been updated without iterating through all the
relationships and checking each one?
Currently I have this:
public bool HasChanges
{
get
{
DataRowView drv = (DataRowView)database[index.Position];
bool changed = (drv.Row.RowState == DataRowState.Modified);
foreach (DataRelation rel in database.Data.Relations)
{
if (changed)
break;
if (rel.ParentTable == database.Data.Tables["tblMain"])
{
DataRow[] child = drv.Row.GetChildRows(rel);
foreach(DataRow r in child)
{
changed = changed | r.RowState == DataRowState.Modified;
if (changed)
break;
}
}
}
return changed;
}
}
Which even with the breaks, which will let the program get out early once
it's found one change, still seems like it's horribly inefficent, especially
since most of the time there really won't be any changes. I want to be able
to display on my form (which shows one record from the main table plus info
from the child table) if this record has changed since being loaded from the
database, so I need to do this everytime the user navigates to a new record
in the database. Any ideas?
My other, related, question is how can I count how many records have been
changed? I can use GetChanges on the parent table and to get changes there
and then use GetChanges on all the child tables too. But if I add them
together I'm likely to overcount some records (where both the parent and the
child have been changed). Is there an efficient way to deal with this? This
is less of an issue than the first case since I don't expect to need to get
that number very often.
Any insight here is most welcome!
Thanks Tag: test Tag: 73685
Problem viewing XML file in VS.NET 2002
I have an XML file (data and schema) created programatically from a
DataTable. I need to open this file in VS.NET and view it in the data view
(grid view). When I open the file the first time, this works perfectly well.
However, once I close it (no edits) and try to open again, VS gives an error
as follows:
"Although this XML document is well formed, it contains structure that Data
View cannot display. Column requires a valid Data Type."
The weird thing is that this still happens even when I make the newly
created file read only. It opens in Data View the first time, then it it
fails after that. I don't see how the file could change given that it is
read only.
BTW, all columns are typed. Some are typed as enums we have defined. But if
this was a problem, it seems the file wouldn't open in Data View even once.
It opens fine the first time. I can regenerate the same file, and it again
opens fine the first time it is opened. Also, I set the read only property
immediately (before the file is ever opened.)
Anyone have any ideas what is causing this?
Thanks! Tag: test Tag: 73683
sqlConnection
if i open a connection and then open a datareader off that
connection.
then loop through the datareader attempting to perform
updates through the same connection, then i get an error
"There is already an open DataReader associated with this
Connection which must be closed first."
oh and the there is a transaction based on the connection.
should i maybe use a dataset ?
dan Tag: test Tag: 73674
Import text file to access db table (OleDB, ADO, ADO.NET)
The question of how to import a text file into the database table has
been asked many times. The code that suits me was given by Paul
Clement is the following:
Sub ImportTextToAccessADO()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlString As String
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\DB1.mdb;" & _
"Jet OLEDB:Engine Type=4;"
sqlString = "SELECT * INTO [tblSample2] FROM
[Text;HDR=NO;DATABASE=e:\My
Documents\TextFiles].[Sample2.txt]"
cnn.Execute sqlString
cnn.Close
End Sub
It works great but only if the table doesn't exist. My questions are
how to do this if the following table already exists. Will bulk Insert
like this work or do I have to use adapter to fill a dataset and then
insert into the table row by row. Also how can I check if the table
exists then I delete it and create a new one but with the same schema
as it had before. Appying the above code to the table that doesn't
exist creates a new table with columns labelled F1, F2, etc. How do I
create custom columns?
I would greatly appreciate any input on this (preferably using .NET).
Thank you. Tag: test Tag: 73672
Execute Non Query Error
I am having a problem with ExecuteNonQuery.
I have this code:
Try
strsql = "Insert into [Action] values (" &
EndItem.intItemIdentifier & ", " & intActionIdentifier
& ", '" & strOrg & "', '" & strStat & "', '" & strStatDate
& "', '" & strDesc & "', '" & strTake & "')"
Dim cmd As New OleDbCommand(strsql, conn)
cmd.ExecuteNonQuery()
Catch oledbExc As OleDbException
MessageBox.Show(oledbExc.ToString, _
"", MessageBoxButtons.OK, MessageBoxIcon.Error)
Catch ex As Exception
MessageBox.Show(ex.Message, "", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
I get this error message:
ExecuteNonQuery requires an open and available
Connection. The connection's current state is closed.
Yet, I have the connection open at the top of the form and
have used it in other instances and it runs perfectly.
Public Class ActionEdit
'Inherits System.Windows.Forms.Form
Inherits System.Windows.Forms.Form
'Public Shared CriticalParts As Form
Public Shared conStr As String
= "Provider=Microsoft.JET.OLEDB.4.0;data
source=c:\\CPRdatabase.mdb"
' Create connection object
Public Shared conn As OleDbConnection = New
OleDbConnection(conStr)
I am not sure what I am missing that gives me an error
here and not anywhere else in my form.
Thanks in advance!
Elena Tag: test Tag: 73671
Event handling
Hi.
I wrote simple C# app, that connects to SQL Server 2000 DB using ADO.NET.
I want to monitor inserts to specified table from my app. That is, when
someone inserts a row [or entire rowset] to this table, i want to catch this
"event" and handle it [rather, i want to get the inserted data]. In old C,
this was done with extended stored procedures and system events. Is there
any easier way to do this in C# and ADO.NET?
Thanks for answer.
Regards
Marek Samaj Tag: test Tag: 73670
(newbie) SQL help and how to store it
Hi-
this is not a complicated question and probably get's asked often....
with that said, here is what I am trying to do. I have 3 tables
table1 (products)
ProdId
ProdName
ProdParentId
table2 (faq_categories)
CatId
ProdId
CatName
table 3 (faq_main)
FaqId
CatId
Question
Answer
What I want to do is get join all the "faq_categories" that match certain
criteria in "products", then join all the "faq_main" that match the relevant
faq_categories"
Maybe it will make more sense if I show you how I would like to render the
data
Product1
Category 1
Q:asdasd
A:asdagt345dfg
Category 2
Q:asdflwfybsdf
A:adkbafj
Product2
Category 3
Q:asdfhkasfsd
A:sdfh34hfsdf
So, with that said... how do I do this? Is there a way to get them all at
once or do I need to do subqueries? At the least what I would like to be
able to do is get the "products" and "faq_categories" with the same query.
Looking for help, not sure what to do. Thanks!
Steve Tag: test Tag: 73668
Data Access Peroperties' permissions grayed out
I've tried to connect to SQL Server via VS.NET -- and I'm the dbo -- but the
permissions area of the Data Access Properties area is grayed out. It only
lets me view the objects, not edit/create in Server Browser. Any help is
appreciated. Thanks. Tag: test Tag: 73663
adding data,time and computername to a dataset
Hello..
I have a problem in a c# application.
I have a dataset that have a table cald tblcounter in this table I have 2
columns
Computer_name and datetime.
In the computer_name I want to store the clientnamn and in datetime I want
to store the date and time the clientnamn was enter in the table
This is the C# code I am using.
DataRow anyRow = tblcounter.NewRow();
anyRow["processor"] = perf_processor_time.NextValue();
anyRow["memory_fri"] = perf_memory_fri.NextValue();
// the code to add clientnamn and date,time
tblcounter.Rows.Add(anyRow);
---------------
lennie Tag: test Tag: 73662
ORA-01002 - fetch out of sequence HELP ME please :)
Hello people,
I have one problem with Oracle 7 and OracleClient Provider.
I have a server with Win 2k and Client of Oracle 9i. My
database is in a machine with Oracle 7.
In the begininnig the database was in Oracle 9i. and my
web application works fine.
After I'd put the application in Oracle 7. But a problem
became. Every query that I try to execute the error is
showed:
ORA-01002 - fetch out of sequence
Is anyone knows what happened?
Thanks a lot.
Steven Alexander Tag: test Tag: 73661
Datagrid problem?
Hi!
A stored procedure results records via an SqlAdapter to a dataset. I defined
a dataview to that dataset for a datagrid. Some column in the grid are
hided. The grid and the columns are readonly. And I have a textbox binded to
the same dataview.
There is a method clear the dataset, then to connect the server, fill the
dataset and close the connection. I call this method in the form Load
eventhandler.
Running the app, I can see the records in the grid and the corresponding
field in the textbox and moving in the grid by clicking on rows or by
pressing keys is fine until I choose to refresh the dateset via the method
mentioned earlier. Then clicking or pressing keys doesn't affect to the
textbox value for a small undetermined time (20 - 50 clicks), then it works
fine. While it is not working the black triangle stands on that row header,
which was reached last time.
What can I do? I need the textbox value for other computing. Tag: test Tag: 73655
datagrid-update
I have a datagrid with two columns-name,id and both are
editable.
My requirement is that I want my second column(id) as a
non updatable column.Though I can change the values of
column by editing it,upon saying update,the second
shouldn't be updated.
Do we have to set any property or do it programatically?
If so,how? Tag: test Tag: 73649
Losing strong typing when using DataRow.GetChildRows()
This is just a general query about Data Binding.
I've got a Custom (VS 2003 generated) DataSet with a
number of strongly typed Custom DataTables within. I was
trying out using DataRelations to save me doing looping
compares in my code but I found that (obviouse in
retrospect) DataRow.GetChildRows() returns DataRow[]
which means i lose the strong typing of my rows (which i
*really* dont want to do).
Anyone come across this? Anyone any ideas about working
around it? Any future plans for the VS 2003 auto-
generated classes to provide helper functions, convertion
operators or custom dataset/row contructors that take
DataSet/Row as parameters.
Thanks in advance
Jan Bannister Tag: test Tag: 73644
SqlDataAdapter.Update doesn't update
Hi,
In ASP.NET, i've got a web forms app, which does its data operations
through a middleware DataManager dll.
In one operation, the web form creates a types dataset from a stored
schema, adds a DataRow to the single DataTable object and passes this
to the dll for upload.
---begin webform function---
{
ForumData fData = new ForumData();
PeopleDataSet pds = new PeopleDataSet();
DataTable dt = pds.Tables["People"];
DataRow dr = dt.NewRow();
dr["PeopleID"] = Convert.ToInt32(this.txtPeopleID.Text);
dr["Name"] = this.txtName.Text;
dr["Email"] = this.txtEmail.Text;
dr["Password"] = this.txtPassword.Text;
dr["Sig"] = this.txtSig.Text;
dr["ShowDetail"] = Convert.ToBoolean(this.txtShowDetail.Text);
dr["Photo"] = Convert.ToBoolean(this.txtPhoto.Text);
dr["PicApprove"]= Convert.ToBoolean(this.txtPicApprove.Text);
dr["MemberApprove"] = Convert.ToBoolean(this.txtMemberApprove.Text);
dr["PlainText"] = Convert.ToBoolean(this.txtPlainText.Text);
dr["DOB"] = Convert.ToDateTime(this.txtDOB.Text);
dr["LastHere"] = Convert.ToDateTime(this.txtLastHere.Text);
dr["SignedUp"] = Convert.ToDateTime(this.txtSignedUp.Text);
dr["CountPosts"] = Convert.ToInt32(this.txtCountPosts.Text);
dr["Title"] = this.txtTitle.Text;
dr["AvatarID"] = Convert.ToInt32(this.txtAvatarID.Text);
pds.Tables["People"].Rows.Add(dr);
bool testval = fData.AlterPersonDetails(pds);
//pds.WriteXml("c:\\pdsdataset.xml");
if (testval)
{
btnChange.Text = "Success";
}
else
{
btnChange.Text = "Failed";
}
}
-- end webform function ----
That gets passed to the Public method, AlterPersonDetails, which is,
naturally, just a passthrough to a private function
--- begin Public Method ---
public bool AlterPersonDetails(DataSet dsChangedPerson)
{
bool nValue = false;
try
{
nValue = ChangePersonDetails(dsChangedPerson);
}
catch
{
nValue = false;
}
return nValue;
}
--- end Public Method ---
Now, you'll note that i've taken the precaution of writing the DataSet
XML to a file in order to check that it does indeed change. It does.
Here's the problem. When mAdapter.Update is called, it doesn't appear
to do anything at all.
--- begin private function ---
private bool ChangePersonDetails(DataSet dsChangedPerson)
{
bool returnval;
SqlConnection mCon = MakeConn();
DataSet dsPeople = new XPeopleDataset();
mCon.Open();
SqlDataAdapter mAdapter = new SqlDataAdapter("SELECT * FROM People",
mCon);
SqlCommandBuilder mComm = new SqlCommandBuilder(mAdapter);
mAdapter.Fill(dsPeople, "People");
dsPeople.Merge(dsChangedPerson.Tables["People"]);
dsPeople.AcceptChanges();
dsPeople.WriteXml("C:\\xmlversion.xml");
int g = mAdapter.Update(dsPeople, "People");
mCon.Close();
returnval = true;
return returnval;
}
--- end private function ---
So, there's my problem. No exceptions, no problems and no changes to
the database! If anyone can help, I'd really appreciate it,
--
Rich Tag: test Tag: 73641
datagrid expression-urgent
My select query is a s follows
select qty from sales;
My datagrid as two template columns with textboxex in Item
template.I want to use second template column as a
compute column in my datagrid which
depends on column 'qty' values of my select statement
compute column's value should be as follows
if( qty > 10) qty+10 else
qty-10
How can give my 'DataBinder.eval(containder.dataitem))'
expression to my textbox in html?
How can I acheive this?
How complex evaluation expressions can be given for
datagrid columns either at design time or programatically?
plzzz help me out
thanks in advance,
vijaya Tag: test Tag: 73640
datagrid expression-urgent
My select query is a s follows
select qty from sales;
My datagrid as two template columns with textboxex in Item
template.I want to use second template column as a
compute column in my datagrid which
depends on column 'qty' values of my select statement
compute column's value should be as follows
if( qty > 10) qty+10 else
qty-10
How can give my 'DataBinder.eval(containder.dataitem))'
expression to my textbox in html?
How can I acheive this?
How complex evaluation expressions can be given for
datagrid columns either at design time or programatically?
plzzz help me out
thanks in advance,
vijaya Tag: test Tag: 73639
DataSet-Generator/Typed Dataset: created class always "Dataset1"
Hello,
when i create a typed dataset from a given xsd-definition, the
ms-dataset-generator always names the dataset-class "Dataset1".
Of course it's possible, to rename the class in the source-code.
But this modification get lost after every change in the schema.
The source-code will be generated new in this case.
Any idea, where i can find the right property, to set the classname before
the source-code is generated?
I use the MS-.NET-IDE and C#.
Regards
Daniel Tag: test Tag: 73634
opening en closing Connection takes too much time!!!
Hi,
When I was looking for the reason my application was runnong so slow I
suddenly noticed that it was the opening and closing of the connection that
took almost all the time. Like in most of the samples I found I opened the
connection everytime I needed it, and closed it after my action.
But closing and opening everytime seemed to take very much time, so I opened
my connection at the start of the application, and closed it at the end: it
runned up to 20 times faster (before 1 action took 3-4 seconds, now I have 5
actions per second).
So now my question: what's wrong with opening it one time and closing it at
the end? Looking at the smaples I found this seems 'not done'? Does it
really take so much resources to leave the connection the whole time open?
Isn't the use of a property like this a nicer way to handle the
database-connection?
Property MyConn() As OleDbConnection
Get
If Not MyConn2.State = ConnectionState.Open Then
MyConn2.Open()
End If
MyConn = MyConn2
End Get
Set(ByVal Value As OleDbConnection)
'nothing?
End Set
End Property
or maybe just before every use a:
If Not MyConn.State = ConnectionState.Open Then
MyConn.Open()
End If
Any help, any idea's, any remarks or reflections would be nice. I just
thinks the much better performance justifies to leave the connection open.
Is there any offical documentation about this?
Thanks in advance!
Pieter Tag: test Tag: 73624
Common Connection
I'm working on an ADO.NET project. I'd like to declare a
Common Variable that can store the Connection String
values for a Database. I need this variable to be able to
initialise the database connection for all
OledbDataAdapters across all Form modules. Thanks! Tag: test Tag: 73620
Performance of ADO.NET
Hello All,
We are currently in feasibility study and high level design phase. We need
to choose application design according to performance and maintainance.
Means our main criteria is Performance and easy Maintainance.
We have created one template for just study purpose.
1) Layer That uses Typed DataSet and Stored Procedure
In this template, we used Typed Dataset. But somewhere i read that it is
maintainable but not giving good performance.
Our structure is :
Presentation Layer > BAL > DAL > Type Dataset
Layer > DL
I want information whether this is a good architecture or not.
Can anybody suggest me a good architecture with following criteria.
Use of Stored Procedure everywhere
Performance should be high
DAL and BAL Should be there
Easy to maintain and programmer friendly :)
Give me answers ASAP.
Best Regards, and Thank You in Advance.
Manish Tag: test Tag: 73619
IBM OleDb for db2 on Win server 2003 gives error..
I've tried to install IBMs OleDb for db2 on iSeries (Client Access) on both
Web Edition and Standard Edition. When I try to instantiate from an asp.net
app I get the error message:
*********
Non-NULL controlling IUnknown was specified, and either the requested
interface was not IUnknown, or the provider does not support COM
aggregation.
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.OleDb.OleDbException: Non-NULL controlling
IUnknown was specified, and either the requested interface was not IUnknown,
or the provider does not support COM aggregation.
********
Whats this?
Any way to make it work?
Jon Tag: test Tag: 73618
Cannot edit Databases on other SQL Servers in Visual Studio Server Explorer
I am using Visual Studio .Net 2003 Server Explorer to
make any changes to my SQL Server 7 Database that is
local to my machine. I also have SQL Server 2000 running
on my machine and have recently upgraded the database to
SQL Server 2000.
Unfortunately I don't seem to be able to make any
modifications to the SQL Server 2000 database using
Server Explorer. I have also connected to other SQL
Server 7 and 2000 databases with the Server Explorer and
do not have access to modify any of these databases
either.
I can make adjustments to the SQL Server 2000 database
using Enterprise Manager but I really like the Stored
Procedure Editor with the "Design SQL Block" that doesn't
exist in Enterprise Manager.
Thanks Tag: test Tag: 73615
What SQL Statement is being passed?
Is there a way to show what values are being passed in an SQL Statement?
I can display the commandtext of OleDbInsertCommand1 but it displays the "?"
is there a way to show the actual values of the insert statement?
Thanks
Charlie Tag: test Tag: 73614
OLEDB for FoxPro and SPROCS
Is there anyway to call a SPROC using the OLEDB provider for foxPro
inside a VB.Net class and then have the ability to reference the
column data inside a datareader or datatable? I currently only get a
boolean value back when I call a SPROC that executes a simple select
statement. HELP PLEASE!!
Thanks!
Dan Tag: test Tag: 73613
ADO .net in production environment
Hello:
I just started looking into ADO .net, and I wonder if it works well in production environment. One of the questions I have is working with multiple tables with ADO .net; that these tables are related. If there are update, insert, delete to the dataset returned from the database. When sending the modified data back to the database, is ADO .net automatically takes care of all the relationship and makes updates in all tables involved?
Thanks.
Quin Tag: test Tag: 73611
One bound form, two functions..help please
Hi,
I'm planing to have a form with 10 bound textboxes to a dataset throgh
the currency manager and bindingcontext. What I need to do is use
that same form based on the user choice. He/she could first press a
button to fill-out all textboxes from dataset and do normal navigation
through all records in the dataset, OR he/she could enter a search
criteria in the first textbox (txtEmployee ID), press the same button
to search and get that specific record only. I don't have any problem
with the first case (press the button to get records and navigate),
the problem is when the button is pressed for search, how can I search
the dataset????
Correct me if i'm wrong, I'm planing to clear the binding property of
all textboes and clear thier TEXT property. The question is How to
search for that specific record based on the field enter in the
textbox(which the choice to search)
MTIA,
Grawsha Tag: test Tag: 73605
XSD for existing XML structure.
I have an xml file that is currently being used ... I need to make the xsd file
for it for intellisense to work in the .net ide.
So, when I try to do the xsd file, it keeps giving me an error about the tables
being nested and are wrong...so after looking and sifting through google, I'll
try here as well :)
<store id="WalStore">
<product id="Paint1">
<product id="Paint3"></product>
<product id="Paint4">
<product id="Paint5"></product>
<product id="Paint6"></product>
</product>
<product id="Paint7"></product>
<product id="Paint8">
<product id="Paint9"></product>
<product id="Paint10">
<product id="Paint11">
</product>
</product>
</product>
<product id="Paint2">
<product id="Paint12"></product>
</product>
</store>
So, how can I create the XSD for the above xml? Once I can do that, my problem
*should* be solved for my application.
Thanks a bunch!
Mythran the Pro-g(r)am(m)er Tag: test Tag: 73602
Expression Column doesn't refresh
Hi,
I have a problem. I have no any clue about this. Please help me to figure
it out.
I have a Customers table and Orders table. There is a relation,named
"Customer2Orders", between Customers (parent table) and Orders (child table)
on CustomerID. One Customer may have multiple Orders. I have an expression
column 'Total' in Customers table. It is computed based on the child table's
column InvoiceAmount, i.e. Total =
Sum(Child(Customer2Orders).InvoiceAmount). If I add a child record, Total is
computed automatically. But if I delete a child record, Total is not
reflected. It still keeps the old value which is wrong.
Thanks in advance!
Fei Tag: test Tag: 73598
need XML help with nested tables
Hi-
I need to develop an FAQ section for our website. We would like to break up
the FAQ by products, then categories with each category having n
question/answer pairs.
I would like to pass parameters in the querystring based on what the
product/category the user selected, then populate a datagrid with the
correct set of question/answer
I am pretty new to XML, but basically I made something like this:
<products>
<product>
<name>prod1</name>
<partNum>prod1</partNum>
<categories>
<category>
<name>basic category</name>
<faqs>
<faq>
<question>This is a sample question</question>
<answer>this is the answer</answer>
</faq>
</faqs>
</category>
</categories>
</product>
<product>
<name>prod2</name>
<partNum>prod2</partNum>
<categories>
<category>
<name>basic something or something category</name>
<faqs>
<faq>
<question>This is another question</question>
<answer>and this is another answer</answer>
</faq>
</faqs>
</category>
</categories>
</product>
</products>
So, then I use a DataSet and the ReadXML() method, this result in 5 tables.
I was hoping that it would somehow be nested for me, but the way it is right
now I can't see an easy way to use it the way I want.
the first table lists the products(good)
the second table I can't really tell what is in there
third tables has all the categories, so on and so on.
What is confusing is... what is the point of structuring data hierarchically
in XML file if you can't maintain it? I'm sure you can, but how? Is what
I'm doing not common?
Someone please shed a little light on this for me, I would appreciate it!!
Thanks,
Steve Tag: test Tag: 73597
Adding a record using C#
This is a multi-part message in MIME format.
------=_NextPart_000_0028_01C3BA8B.EDE71230
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Can someone tell me what is wrong with this code? I am simply trying to =
add a record to a database. The error message is as follows:
An unhandled exception of type 'System.InvalidOperationException' =
occurred in system.data.dll
Additional information: Update unable to find TableMapping['Table'] or =
DataTable 'Table'.
**************************************************************
DataSet ds =3D new DataSet();
ds.Tables.Add("FundData");
// Create Access objects:=20
System.Data.OleDb.OleDbCommand cmd1 =3D new =
System.Data.OleDb.OleDbCommand("SELECT * FROM FundData", dbConn);
System.Data.OleDb.OleDbDataAdapter AdpFunds =3D new =
System.Data.OleDb.OleDbDataAdapter(cmd1);
AdpFunds.Fill(ds, "FundData");
DataRow dr =3D ds.Tables["FundData"].NewRow();
dr["Symbol"] =3D sym;
ds.Tables["FundData"].Rows.Add(dr);
ds.AcceptChanges();
AdpFunds.Update(ds);
------=_NextPart_000_0028_01C3BA8B.EDE71230
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT size=3D2>
<P><FONT face=3DArial>Can someone tell me what is wrong with this =
code? I am=20
simply trying to add a record to a database. The error message is =
as=20
follows:</FONT></P>
<P><FONT face=3DArial>An unhandled exception of type=20
'System.InvalidOperationException' occurred in =
system.data.dll</FONT></P>
<P><FONT face=3DArial>Additional information: Update unable to find=20
TableMapping['Table'] or DataTable 'Table'.</FONT></P>
<P><FONT=20
face=3DArial>************************************************************=
**</FONT></P>
<P>DataSet ds =3D </FONT><FONT color=3D#0000ff size=3D2>new</FONT><FONT =
size=3D2>=20
DataSet();</FONT></P>
<P><FONT size=3D2>ds.Tables.Add("FundData");</P>
<P></FONT><FONT color=3D#008000 size=3D2>// Create Access objects: =
</P></FONT><FONT=20
size=3D2>
<P>System.Data.OleDb.OleDbCommand cmd1 =3D </FONT><FONT color=3D#0000ff=20
size=3D2>new</FONT><FONT size=3D2> =
System.Data.OleDb.OleDbCommand("SELECT * FROM=20
FundData", dbConn);</P>
<P>System.Data.OleDb.OleDbDataAdapter AdpFunds =3D </FONT><FONT =
color=3D#0000ff=20
size=3D2>new</FONT><FONT size=3D2> =
System.Data.OleDb.OleDbDataAdapter(cmd1);</P>
<P></P>
<P>AdpFunds.Fill(ds, "FundData");</P>
<P>DataRow dr =3D ds.Tables["FundData"].NewRow();</P>
<P>dr["Symbol"] =3D sym;</P>
<P>ds.Tables["FundData"].Rows.Add(dr);</P>
<P>ds.AcceptChanges();</P>
<P>AdpFunds.Update(ds);</P></FONT></DIV></BODY></HTML>
------=_NextPart_000_0028_01C3BA8B.EDE71230-- Tag: test Tag: 73595
Error in stored procedure
Hi,
I just wanted to ask what is wrong with this stored
procedure. i keep getting the following error message:
ADO error: The column prefix 'dbo' does not match with a
table name or alias name used in the query.
ALTER PROCEDURE dbo.SPSelEquipBooked2
(@Arrival datetime,
@Departure datetime)
AS
SELECT dbo.Equip_Inv_No
FROM dbo.Equipment_Inv
WHERE dbo.equipment_Inv.Equipment_Inv_No
Not In(
SELECT dbo.Equipment_Inv.Equip_Inv_No
FROM dbo.Equipment_Booking INNER JOIN
dbo.Equipment_Inv ON
dbo.Equipment_Booking.Equip_Inv_No =
dbo.Equipment_Inv.Equip_Inv_No INNER JOIN
dbo.Caravan_booking ON
dbo.Equipment_Booking.BookingNo =
dbo.Caravan_booking.BookingNo AND
dbo.Equipment_Booking.Caravan_Inv_No =
dbo.Caravan_booking.Caravan_Inv_No INNER JOIN
dbo.Booking_Details ON dbo.Caravan_booking.BookingNo
= dbo.Booking_Details.BookingNo
WHERE (dbo.Booking_Details.Dt_of_departure >= @Arrival)
AND (dbo.Booking_Details.Dt_of_arrival <= @Departure))
thx in advance Tag: test Tag: 73591
Question about nested transaction in ADO.NET
I am using SQL Server 2000 as data storage
I have a transaction which has the following structure
connection.begintran
Process A
Process B
Process C
connection.commit
rollback when failed
In each process(A, B, C), there will also be BeginTran, Commit & Rollback
inside it.
I have such structure because process A, B, C are methods provided by
different object.
I have found that when I failed in process B, A will not be rollback...
So how can I implement a nested transaction?
Thanks Tag: test Tag: 73583
read an output parameter from a stored procedure by its name?
hi.
I cannot retrieve an output parameter from a stored procedure by name. Is
this correct?
Works not:
[ sParamValue = mycmd.Parameters["@ID"].Value.ToString(); ]
Works fine:
[ sParamValue = mycmd.Parameters[0].Value.ToString(); ]
TIA
/OF Tag: test Tag: 73578
Datagrid - upgrade / save new rows
I have an ADO.NET connection to an Access 2000 DB with 1 table. I have a
Data Grid that shows me all records when I open the Data Grid. In the
program it shall be possible to add rows to the table directly in the
grid, by adding rows. Have tried to code this, but the result is only an
empty new row. The information in the row is lost. Can anybody help?
I have OleDgDataAdapter called 'da', OleDbConnection called 'cn', a
dataset called 'ds1'.
NSR Tag: test Tag: 73572
Reading text file using ADO.net
This is a multi-part message in MIME format.
------=_NextPart_000_01AA_01C3BAA0.55E26560
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hope somebody can help...
I'm trying to load up a text file using ADO.net, as follows:
Dim TextConnectionString As String
TextConnectionString =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & _
"Data Source=3D" & FilePath & ";" & _
"Extended Properties=3D""Text;HDR=3DNO;FORMAT=3DDelimited"""
Dim TextConn As New =
System.Data.OleDb.OleDbConnection(TextConnectionString)
TextConn.Open()
Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from " & =
filename, TextConn)
Dim ds As DataSet =3D New DataSet("CSVFiles")
da.Fill(ds, filename)
When I run this, it fails at the "fill" statement with the error =
"Database
or object is read only".
The file in question can definitely be read and written (it's a short =
CSV
file). What have I done wrong???
Many thanks in advance.
Simon
------=_NextPart_000_01AA_01C3BAA0.55E26560
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Hope somebody can help...</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT><BR><FONT face=3DArial><FONT =
size=3D2>I'm trying=20
to load up a text file using ADO.net, as follows:<BR><BR></FONT><FONT=20
size=3D2><FONT face=3D"Courier New">Dim TextConnectionString As=20
String<BR>TextConnectionString =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;" =
&=20
_<BR>"Data Source=3D" & FilePath & ";" & _<BR>"Extended=20
Properties=3D""Text;HDR=3DNO;FORMAT=3DDelimited"""<BR>Dim TextConn As =
New=20
System.Data.OleDb.OleDbConnection(TextConnectionString)<BR>TextConn.Open(=
)<BR>Dim=20
da As New System.Data.OleDb.OleDbDataAdapter("Select * from " & =
filename,=20
TextConn)<BR>Dim ds As DataSet =3D New =
DataSet("CSVFiles")<BR>da.Fill(ds,=20
filename)<BR></FONT><BR><BR>When I run this, it fails at the "fill" =
statement=20
with the error "Database<BR>or object is read only".<BR><BR>The =
file in=20
question can definitely be read and written (it's a short=20
CSV<BR>file). What have I done =
wrong???<BR><BR>Many=20
thanks in=20
advance.<BR><BR><BR>Simon<BR><BR><BR><BR><BR><BR></FONT></FONT></DIV></BO=
DY></HTML>
------=_NextPart_000_01AA_01C3BAA0.55E26560-- Tag: test Tag: 73570
Queries
Hi,
I am new to ADO and still have a lot to learn from you.
In Access, I used to have make queries (sometimes based on other queries)
and display them in a form.
In ADO.NET, there is a dataset. But I could not figure out how to do this.
Is there any examples I can get? Tag: test Tag: 73563
Error whne dragging table to dataset
Hi
When I drag a table onto a dataset from the server explorer I get a Data
Adapter Configuration Error...'Error in SELECT clause: expression near
"".....'. How can I fix this? I am using FoxPro tables.
Thanks
Regards Tag: test Tag: 73562
Help with DataTable update (BeginLoadData method)
I have an ASP.NET/VB app that updates values in a DataTable over the
course of about 3 different pages. On the way out of the first of
these pages, I explicitly build the DataTable from values in a
DataGrid, and set the PrimaryKey of the DataTable to be the first cell
in the grid (which is a UserID value). I then store the DataTable in
a session object, from which it is retrieved for subsequent pages.
All this seems to be working fine.
On the second page, I retrieve the DataTable from the session object,
apply a filter to it, and display the filtered data in a DataGrid. In
this grid, the user can change the data, specifically assigning an
employee to a different supervisor. The Grid is populated with the
current employee/supervisor assignment in the database, and then any
necessary changes are made by the user. This, too, seems to be
working fine.
The problem I'm having is that on the update command of the Grid,
which is where I'm updating the DataTable (the thought being that I
would just do an "in-place" update of the DataTable and then rebind
the grid). I am able to trap the new assignment values (which are set
in a listbox within the grid) and can trap the employee ID value from
the grid (which is my primary key), but whenever I call the
LoadDataRow method, rather than updating the row based on the primary
key, a new row insert is *attempted*. I say attempted because I get
an error on the EndLoadData method, which I believe is due to not
passing in values for all the columns.
I was hoping to be able to just pass in the key value and the values
of the columns that had changed, but apparently that is not
sufficient. Which is the first question - when attempting a
LoadDataRow call, do I need to pass in valid values for *all* of the
columns in the datatable (except, of course, those for which there are
default values, of which I have none in this instance). You'll see in
my example code that I'm just passing in the first, eighth and ninth
columns, which I suspect is the problem.
However, the *real* question is why isn't the LoadDataRow method
finding the pre-existence of the key value in the table? I've checked
and the value returned by my code is exactly the same as the one in
the table, length is the same, etc. I set the primary key when I
first build the table on the previous page, but I thought perhaps that
attribute somehow gets lots when I set the DataTable to a session
object so, as you will see in the example, I explicitly set the
primary key again on this page, which seems to work, but the code
still inserts a new row (or attempts to).
Here is the relevant code from the page in question:
Dim DDL As DropDownList = CType(e.Item.Cells(2).Controls(1),
DropDownList)
Dim NewID As Integer = DDL.SelectedIndex
Dim SupervisorID As String = DDL.Items(NewID).Value
Dim SupervisorName As String = DDL.Items(NewID).Text
Dim NewRow(9) As Object
Dim Key As TextBox = e.Item.Cells(1).Controls(0)
Dim RowID As String = Key.Text
Dim dsTechnicianList As DataTable
dsTechnicianList =
CType(HttpContext.Current.Session("ILApplicators"),
System.Data.DataTable)
Dim dcFirst As DataColumn = dsTechnicianList.Columns(0)
Dim dcPrimary(1) As DataColumn
dcPrimary(0) = dcFirst
dsTechnicianList.PrimaryKey = dcPrimary
NewRow(0) = RowID
NewRow(7) = SupervisorName
NewRow(8) = SupervisorID
Dim myRow As DataRow
dsTechnicianList.BeginLoadData()
myRow = dsTechnicianList.LoadDataRow(NewRow, True)
dsTechnicianList.EndLoadData()
Any help or ideas would be GREATLY appreciated!
TIA,
Mike Tag: test Tag: 73561
Rollback Transaction Timing Out Framework 1.0 SQL Server 2000
After aborting a long (around 10 minutes) and with numerous updates, the
Rollback function will raise an exception giving the message:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding. -- at System.Data.SqlClient.SqlTransaction.Rollback()
If the transaction is not aborted, ie the Commit function is called, a
timeout does not occur.
In extracts from source below the timeout is set to 0 (for ever). Does
anyone have experience of the Rollback timing out?
......
addCon = new System.Data.SqlClient.SqlConnection();
......
System.Data.SqlClient.SqlCommand addCom = new
System.Data.SqlClient.SqlCommand();
Try
{
.....
addCom.Connection = addCon;
addCom.CommandTimeout = 0; //go on for ever
addCon.Open();
//Start Transaction
addCom.Transaction = addCon.BeginTransaction();
transactionStarted = true;
.......
catch(Exception e)
{
ImportController.errorLevel = errorLevelMajorError;
Audit("ERROR: Update PDS " + e.ToString());
if (transactionStarted)
{
errorLevelTransactionRolledBack =
errorLevelTransactionRolledBack;
addCom.Transaction.Rollback(); <<--- Times out here
}
addCon.Close();
addCom.Dispose();
}
Thanks in advance Tag: test Tag: 73559
Append 1 Datatable to another (same schema)??
I have several datatables that are of the identical schema, each holding
different data.
There are times when it would be nice to combine the data from two
datatables into one datatable.
Would prefer not having to do this through looping.
What would be the best way to do this? Tag: test Tag: 73556
Refilling table
Hi
I have several tables in a dataset. How can I reread the data from
underlying database in only one of the tables?
Thanks
Regards Tag: test Tag: 73555
User Defined Sql Function and ADO.Net
Hi all,
Does anyone know the syntax for returning a value from a user defined
function in Sql Server using VB.Net.
Each time I do it I get:
invalid object name
We have a function which takes one parameter and returns a table.
I can find little or no documents directly addressing this subject.
Thanks in advance,
Bill Tag: test Tag: 73554
Multiple Access Databases
I want to redo my Forms in VB.NET. Now when I go and test
my Forms to make sure they work I want to set up a second
ACCESS file as a test database. When everything works and
I want to switch over to my Production database how would
I do that? Would I have to go into all my VB forms and
redo my ADO links or is there an easier way? Tag: test Tag: 73551
OleDB returning DBNull for certain cells
Hi,
I'm using OleDBConnection to read data from
Excel. I'm able to read everything except when a cell has
numeric value and the column has alphanumeric data. I get
DBNull when it encounters a numeric value. I'm sure many
people have seen this. Does anyone know how to address
this issue?
Thanks,
Paul Tag: test Tag: 73550
Can Sql Server 7 be used with ADO.Net?
Does ADO.Net work with SQL Server 7 or is it only
compatible with SQL Server 2000?
Thanks in advance
Chris Pearce Tag: test Tag: 73546
OleDB troubles reading Excel spreadsheet
I'm trying to use an OleDbConnection to read some data from an Excel
Spreadsheet. I create my connection string, and open the connection,
but when i query the sheet/table i get the error that OleDb could not
find the sheet. I've now tried to get a list of the sheets (to see if
i was using the wrong sheet name or something) but my list of sheets
comes back with 0 entries.
Any Help! this spreadsheet DOES have 2 sheets inside it... What am i
not doing? Are there any good examples/resources out there for reading
Excel in this way?
Private _connectionString as String
Dim rs As OleDbDataReader
Dim cmd As New OleDbCommand
Dim query As String
Dim table As String
Dim lookup As DataTable
'I build my connection string
newConnectionStr.Append("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=")
newConnectionStr.Append(fileName)
newConnectionStr.Append(";Extended Properties=Excel 8.0;")
_connectionString = newConnectionStr.ToString
'I open my connection and attempt to see what sheets are available
_connection.Open()
lookup = _connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
Nothing)
If lookup.Rows.Count > 0 Then
table = CStr(lookup.Rows(0)("TABLE_NAME"))
End If
_connection.Close()
_connection.Dispose()
This returns me nothing, 0 rows end up in 'lookup'.
If i try and run the query "Select * FROM [$Sheet 1$]" I get the "not
able to find" error.
Help?
Thanks!
jeffpriz Tag: test Tag: 73543