An existing connection was forcibly closed by the remote host.
I've seen this message a lot in the groups but I haven't found a
solution for it yet.
Our setup is SQL Server 2000 on a Windows Server 2003 cluster. I
don't believe this issue happened as often when the OS was Server 2000
but that was before I started here.
We see this quite a bit from our wireless clients:
Errors=System.Data.SqlClient.SqlErrorCollection
Class=20 (0x14)
LineNumber=0 (0x0)
Number=10054 (0x2746)
Server=OurSQLServer
State=0 (0x0)
ErrorCode=-2146232060 (0x80131904)
Message=A transport-level error has occurred when sending the request
to the server. (provider: TCP Provider, error: 0 - An existing
connection was forcibly closed by the remote host.)
Data=System.Collections.ListDictionaryInternal
I initially thought it had something to do with them losing the
network connection but when I was trying to recreate (killing the
wireless connection during a long running process) that I always got
the following details:
Errors=System.Data.SqlClient.SqlErrorCollection
Class=20 (0x14)
LineNumber=0 (0x0)
Number=64 (0x40)
Server=OurSQLServer
State=0 (0x0)
ErrorCode=-2146232060 (0x80131904)
Message=A transport-level error has occurred when receiving results
from the server. (provider: TCP Provider, error: 0 - The specified
network name is no longer available.)
Data=System.Collections.ListDictionaryInternal
To recreate the original, all you have to do is run the KILL command
on the spids associated with your application from Query Analyzer.
Given this, I'm focusing on the server itself causing the errors and
not the client computers.
So now we have something to go on when we attempt to code around the
issue. I've been able to "soothe" the issue by executing
SqlConnection.ClearAllPools() when we see a SqlException with a
severity level of 20 or higher. However, I'd like to know if there's
a setup issue with my SQL Server box that I could change that would at
least lessen the frequency of these errors.
Thanks in advance for any advice,
Austin Tag: how to get row number from DataTable.Compute method Tag: 138118
Is it possible to fill a DataSet.DataTable with values from more than one database table?
Hello,
is there a way to fill a DataSet.DataTable with values from more than
one table from a single database file? The thing I'd like to achieve
is to get something similar to a result of the JOIN keyword in SQL.
Thanks for any help...
With regards
nvx Tag: how to get row number from DataTable.Compute method Tag: 138108
Urgent Memory Leak Problem Using Type Dataset. Please help! :-(
Hi all,
I'm having a baffling problem with a windows service that I'm working on.
Basically, I am using a typed dataset to insert a large number of rows
into an SQL Server 2005 database. But there's a memory leak that seems
to be to do with calling the data adapters update method. It's making
the memory usage go through the roof and ultimately the service crashes
after running out of memory.
I've used ".net memory profiler" to analyse the service. It tells me
that there are huge numbers of undisposed
@List<SQLParamter>.Enumeration@ objects. I'm guessing this is a bad thing.
I don't know what to do about it though. As far as I know, the dataset
table adapter should be cleaning up after itself once the method ends.
In case it helps, a sample of my code would
... Start a foreach loop
currentRow = tblActiveMessages.NewActiveMessagesRow();
currentRow.DownloadID = currentMessage.ID;
currentRow.TTUReference = currentMessage.VehicleID;
... Assign more properties
tblActiveMessages.Rows.Add(currentRow);
... For loop ends
da.Update(tblActiveMessages);
I've tried putting a using statement around both the data table and the
table adapter, but it had absolutely no effect.
Can anyone advise me on what I can do to get rid of these little buggers!
Sincerest thanks to anyone who can help - even a little bit! :-(
Simon Tag: how to get row number from DataTable.Compute method Tag: 138107
Stopping a Query
I have a program which allows the user to graphically create SQL statements
without having to know SQL. They just drag and drop fields to view, select
criteria and sort order. When the user indicates that they want to execute
a query, I parse the SQL statement to verify correctness and then create a
recordset and assign it to a datagrid for viewing. If the query is taking
too long to run or is retrieving too many records, I would like to have a
button to stop the query. If they click this button, how do I
programmatically stop a query while it is running?
Bill Tag: how to get row number from DataTable.Compute method Tag: 138106
Microsoft Training/ Certification Dumps /Materials Urls
Microsoft Training/ Certification Dumps /Materials Urls
Get the links from here :
http://free-tech-ebooks.blogspot.com/2007/03/microsoft-training-certification-dumps.html Tag: how to get row number from DataTable.Compute method Tag: 138099
Deployment + Vista
We use strongly typed datasets, and thus our connection string is bound to
an application setting.
Currently we can deploy and alter the app.exe.config file at installation
time to successfully install, and use some basic XML editing to allow the
target database to be changed.
Writing to the application directory in vista will throw compatibility
errors, and not persist the changes at all.
What do we do? Is there a better way of getting the connection string in
your application, or a way of telling the designer where to get your
connection setting from?? Tag: how to get row number from DataTable.Compute method Tag: 138087
Eliminating CRUD procedures
Your know the ones, you have a table and it has Create,Read,Update and
Delete Stored procedures and the app always uses these to talk to the DB
eliminating dynamic sql in your apps.
Makes perfect sense when you have a table however when you have 500 tables
its not so clever (thats 2000 stored procs to maintain)
Shouldnt the framework and sql guys talk to each other and come up with a
single proc that accepts the CRUD as an argument to a single UpdateMyTable
proc for each table. Then Hey presto 500 tables = 500 procs
It would be nice if i didnt have to code it myself and the dataset wizard
just happened to offer this option in the next framework version!
Colin Robinson. Tag: how to get row number from DataTable.Compute method Tag: 138085
Writing xml file from dataset different than the original
Hello all.
I'm reading the data from an XML file similiar to below, using the
code below. When the
file is edited in the datagridview and then re-written, it doesn't
write the xml
file as it was originally written.
So what am I doing wrong? My code and samples are below. Any help
would be greatly appreciated!!
VISUAL STUDIO 2005
.NET 2.0 FRAMEWORK, VB.NET CODE
Declare a bindings source, a dataset, a binding navigator, and set up
a datagridview where the
data is displayed & edited
'now associate them
bndgSrc1.DataSource = ds
dgvConfig.DataSource = bndgSrc1
binNav1.BindingSource = bndgSrc1
'Now read the xml file into the dataset &
ds.readxml(filename)
ds.ReadXml(My.Settings.configFile)
bndgSrc1.DataMember = "pnum"
bndgSrc1.ResetBindings(False)
I save the edited info as below:
dgvConfig.EndEdit()
bndgSrc1.EndEdit()
If ds.HasChanges Then
ds.AcceptChanges()
ds.WriteXml(My.Settings.configFile, XmlWriteMode.WriteSchema)
End If
bndgSrc1.ResetBindings(False)
!<ORIGINAL XML FILE SAMPLE BELOW>
<?xml version="1.0" standalone="yes"?>
<config>
<workstations>
<ws>pcnamexxx</ws>
<ws>pcnamexxx</ws>
</workstations>
<patches>
<pnum>1837</pnum>
</patches>
<users>
<uid>user3</uid>
<uid>user4</uid>
</users>
<ad>
<ou>OU=firstlevel,OU=secondlevel,DC=GOOGLE,DC=COM</ou>
<ou>OU=anotherou,OU=yetanother,DC=GOOGLE,DC=COM</ou>
</ad>
</config>
If I delete all the records, I just get the "<patches /> remnant left
behind:
!<SAVED (ALL PATCHES RECORDS DELETED) XML FILE SAMPLE BELOW>
<?xml version="1.0" standalone="yes"?>
<config>
<workstations>
<ws>pcnamexxx</ws>
<ws>pcnamexxx</ws>
</workstations>
<patches />
<users>
<uid>user3</uid>
<uid>user4</uid>
</users>
<ad>
<ou>OU=firstlevel,OU=secondlevel,DC=GOOGLE,DC=COM</ou>
<ou>OU=anotherou,OU=yetanother,DC=GOOGLE,DC=COM</ou>
</ad>
</config>
If I enter a new record, it doesn't nest it within the parent, as
below:
!<SAVED (NEW PATCHES RECORD INSERTED) XML FILE SAMPLE BELOW>
<?xml version="1.0" standalone="yes"?>
<config>
<workstations>
<ws>pcnamexxx</ws>
<ws>pcnamexxx</ws>
</workstations>
<patches>
<pnum>1837</pnum>
</patches>
<pnum>1838</pnum>
<users>
<uid>user3</uid>
<uid>user4</uid>
</users>
<ad>
<ou>OU=firstlevel,OU=secondlevel,DC=GOOGLE,DC=COM</ou>
<ou>OU=anotherou,OU=yetanother,DC=GOOGLE,DC=COM</ou>
</ad>
</config> Tag: how to get row number from DataTable.Compute method Tag: 138084
Insert a whole datatable
I have two access database (with identical schema). One is the master
and the other is SHOULD be a subset of master data (Let call them
MasterDB and SubDB
The user enters data into the SubDB and I need to bring the new
changes to the MasterDB. Traditionally, I can get a the new changes
(into a dataTable). Loop through all the rows and insert them into the
MasterDB ONE AT A TIME.
My question: IS THERE A BETTER WAY?
For SQL Server, there is a SqlBulkCopy but I am dealing with MS
Access.
In my case, performance is not an issue, I just want write less
code.
Thanks
John Tag: how to get row number from DataTable.Compute method Tag: 138079
Some ADO.NET Mobile Advice please.
Hi all
I am building an Windows Mobile 5 forms control in C#, for a Windows Mobile
5 application. I am using CF2.0 and SQL Mobile 2005.
The control is a Questions and answer control.
Basically I have 3 applications using the same code to dynamically display
questions and save the answers.
I am trying to consolidate it all into 1 control for easer maintenance.
I need to pass the control the questions and meta data for the questions.
And when the last question has been completed the control should hand back
the Answers for saving.
What is the best why of doing this? I really would like to get this right
the first time.
I know I am developing with the CF2 platform. So I am concerned about
getting the most efficient why of doing this.
Possible scenarios I are.
1.. Pass the control a Questions DataTable and an Answers DataTable (Flat
File), use the Questions DataTable to load up an array and loop through the
array asking questions and saving the answers to Answers DataTable. When
the Questions are finished raise an event handing the Answers table as an
Event Arg.
2.. Pass the control a QuestionsAnswers DataSet with a Questions DataTable
and an Answers DataTable in it, use the Questions DataTable to loop round
asking questions and saving the answers to Answers DataTable. When the
Questions are finished raise an event handing the QuestionsAnswers DataSet
as an Event Arg.
3.. Hand the control a Connection and get it to read the questions
directly from the Database and save the answers directly to the database.
I really am lost.
I don't what to take the wrong path and I don't have enough background
experience to make an informed decision about what the best way to handle
this with ADO.NET and the CF would be.
Any suggestions would be welcomed.
Thanks,
ink Tag: how to get row number from DataTable.Compute method Tag: 138075
Datatable
For i = 0 To dtSubOpt.Rows.Count - 1
intOptNo = dtSubOpt.Rows(i)(1)
strSelectSubOpt = "select tblOptions.strOptName as
OptionName,tblOptions.intParent ,tblMOMT.strMOTag as
MessageSentFromServer,tblMOMT.strMTTag as MessageSentToServer from
tblOptions inner join tblMOMT on tblMOMT.intOptId=tblOptions.intOptId
where intParent=" & intOptNo & " order by OptionName"
dtOptName = dbCon.GetRecordSet(strSelectSubOpt, "tblOptions")
Next
In my above coding i need to keep all the records retrieved through
looping the query in the same datatable.
In my coding every time intOptNo has changed and retrieve the records
corresponding to that particular intOptNo . The loop executed every
times and get the records ,but the records overwrite the records
already in a datatable this is my problem .So to avoid this I need to
increment the index of the datatable row .But I don't know how to
change the row index depending upon the number of rows returned by
the query .Is there any way to find the row index of the datatable and
to increment that no to keep the next records orelse tell me some
other way to keep all the records retrieved through looping the
query in the same datatable or in the same DataSet.
Kindly expecting the reply,
meka. Tag: how to get row number from DataTable.Compute method Tag: 138074
Problem saving 24 hour date/time in SQL
I have an SQL table(MS SQL 2005) with a field called SignInDate and
have created this as DateTime. When I save the current time using the
Now function, it does not save the value in 24 hour format.
For example, if it is 2:15:36 PM (i.e. 14:15:36) it saves the value in
SQL Sever as 2007-03-08 02:15:36.000 and when I read it back, it
resuls in 2:15:36 AM.
I have tried formatting as a string using HH:mm:ss for the time
portion but SQL Server still saves as shown above. How can I get it to
save as 2007-03-08 14:15:36.000 so it will be correct when I read it
back?
TIA,
John Tag: how to get row number from DataTable.Compute method Tag: 138070
SQLBulkCopy and SQL Server 2000?
Hi all,
Does .NET 2.0's SQLBulkCopy class work with SQL Server 2000?
Thanks! Tag: how to get row number from DataTable.Compute method Tag: 138064
Using Sql2005's OUTPUT
Hello all...
Here is what i want to do (in VB code)...
<code>
Dim SQL As String = "SELECT ROWID FROM (INSERT INTO STATIC_CONTENT (NAME,
CONTENT) OUTPUT INSERTED.ROWID SELECT 'Contact Us', '') SRC"
Dim newId As Integer myDbObject.ExecuteScalar(SQL)
</code>
Assume that myDbObject has already been set up with all the necessary
connection strings and junk.
The question revolves around using ExecuteScalar to return the value from an
INSERT-OUTPUT clause.
I can 100% guarantee that this is a single-row insert everytime.
This table (STATIC_CONTENT) has a trigger on it to set the update-date every
time it's updated.
Is this possible somehow? short of writing my own overloaded ExecuteScalar
that would turn this into a whole big T-SQL BEGIN/END block with temporary
variables and all that mess?
Thanks in advance,
- Arthur Dent; Tag: how to get row number from DataTable.Compute method Tag: 138058
PARSE a Blank Space???
Using ASP.NET 2.0 with SQL Server 2000
Inside my dropdown list box I am using an SQL DataSource to select the
following data
SELECT RTRIM(c.Name_First) + ' ' + RTRIM(c.Name_Last) AS Contact,
c.phone As PriPortPhone
FROM mppaContacts c
Dropdown list box works well .....
Example "Bart Simpson"
First_name = Bart
Last_name = Simpson
DropDownList -->> RTRIM(c.Name_First) + ' ' + RTRIM(c.Name_Last) AS Contact
ANS:: "Bart Simpson"
How can I parse the blank space I am trying to PARSE it based on the space
that I placed into the string
found in the DropDownlist box???
UPDATE mppaContacts
SET
[Name_First] = @FirstName
[Name_last] = @LastName
[phone] = @phone
Any ideas would be greatly appreciated........not sure how to extract the
individual names and place them into their specific fields.
Thanks in advance
~Brad
------------------------------
Brad Isaacs Tag: how to get row number from DataTable.Compute method Tag: 138057
Dynamic access to DataTable during DataAdapter.Fill
Hello,
Does anyone know if its possible to access the row/column values of a
DataTable while a DataAdapter is filling the table? Maybe using a new thread?
I am currently trying to use a seperate thread for access while the main
thread runs the DataAdapter.Fill and so so far it looks like the table and
rows/columns are not added incrementally, but instead are only filled once
entire resultset has been determined and returned.
Any advice is appreciated.
Thanks,
Andrew Tag: how to get row number from DataTable.Compute method Tag: 138051
can I alias an ADO.Net table (tablename)?
Greetings,
Is it possible to alias an ADO.Net table? I have this one table that has a
pretty long name dsDataEntry.Tblsomebigtablename.
I tried Dim dt As DataTable = ds...Tbl...
Welll, that created table dt, but it did not populate the table. I guess I
would have to loop through the original table.
So is there a way to assign a shorter name to the table I need to work with?
Thanks,
Rich Tag: how to get row number from DataTable.Compute method Tag: 138050
UPDATE a DropDownList box~ HElp Please
Using ASP.NET 2.0 with SQL Server 2000
Inside my dropdown list box I am using an SQL DataSource
SELECT RTRIM(c.Name_First) + ' ' + RTRIM(c.Name_Last) AS Contact,
c.phone As PriPortPhone
FROM mppaContacts c
Now I am trying to UPDATE the table name mppaContacts with what the user
selects from the drop down list box.
How do I UPDATE the table when the names are concatenated? I need to
select a substring for the Name_First field then for the Name_Last field.
UPDATE mppaContacts
SET
[Name_First] = @FirstName
[Name_last] = @LastName
[phone] = @phone
Any ideas would be greatly appreciated........not sure how to extract the
individual names and place them into their specific fields.
Thanks in advance
~Brad
------------------------------
Brad Isaacs Tag: how to get row number from DataTable.Compute method Tag: 138049
Parameterized query problem using Access Database in VS.NET2005
New to the VS.NET 2005 and I am writing a program using an access database as
the datasource in visual studio.NET 2005 The problem I have is I am trying
to write a parameterized query where a wildcard can be used to pull a
complete name from only one or two characters entered. If I have the exact
name I can get it to work with a ? but this defeats the purpose of the query
and the sql select for the access database doesn't reconize the * that is
used in access as a wildcard.
The satements I have right now are:
SELECT CustomerNumber, Name, Address, City, State, Zip, Phone, LastOrdDate,
Inactive
FROM Customers
WHERE Name = ?
SELECT CustomerNumber, Name, Address, City, State, Zip, Phone, LastOrdDate,
Inactive
FROM Customers
WHERE CustomerNumber = ?
and when I try to do this (or any version of it):
SELECT CustomerNumber, Name, Address, City, State, Zip, Phone, LastOrdDate,
Inactive
FROM Customers
WHERE CustomerNumber = '*'
or
WHERE CustomerNumber = ? &'*'
I get an error of this:
Error in WHERE clause near '*'.
Unable to parse query text.
I have even tried the % for sql databases and still no help. Tag: how to get row number from DataTable.Compute method Tag: 138046
problem with Ole DB on Windows XP
Hi Everybody,
I've got a strange problem on one of our clients computers:
I've got a dotnet 1.1 application that connects to a database using .Net
OleDB provider for Visual FoxPro.
On most of our clients' computers everything works as expected,
However on 1 computer running Windows XP Pro + SP2 we get this error:
** System.InvalidOperationException: The .Net Data OLE DB
Provider(System.Data.OleDb) requires Microsoft Data Access Components(MDAC).
Please install Microsoft Data Access Components(MDAC) version 2.6 or later.
---> System.Runtime.InteropServices.COMException (0x80040154): Class not
registered
at System.Data.Common.UnsafeNativeMethods.CoCreateInstance(Guid rclsid,
IntPtr pUnkOuter, Int32 dwClsContext, Guid riid)
at System.Data.OleDb.OleDbConnection.CreateInstanceDataLinks()
at System.Data.OleDb.OleDbConnection.CreateInstanceMDAC()
--- End of inner exception stack trace ---
at System.Data.OleDb.OleDbConnection.CreateInstanceMDAC()
at System.Data.OleDb.OleDbConnection.GetObjectPool()
at System.Data.OleDb.OleDbConnection.CreateProvider(OleDbConnectionString
constr)
at System.Data.OleDb.OleDbConnection.Open()
..
We tried to install MDAC 2.8, but we get a message that MDAC 2.8 is already
installed as part of the OS.
How do we solve this problem?
Thanks,
Nadav Tag: how to get row number from DataTable.Compute method Tag: 138040
Please HELP!!!!
Hi,
I have this problem,
I do an update to VFP table, using oledb
all works fine while I run the asp net application from visual net IDE
when I close the IDE (same computer) and open explorer to run the
application and try to update the table, I get the error "cannot update the
cursor 'cursor' "
as if the table is read only, of course not, because works fine inside IDE
so, Have I to share the table o make some changes to give permisions or
something to acces tables write mode?
thanks Tag: how to get row number from DataTable.Compute method Tag: 138028
Date and Time field concatination
I have field with a Date and a field with a Time; is there anyway to make a
complete DateTime using an expression in ADO.NET?
I don't have an option to do this in a database before getting the data into
the app.
Thanks,
Joe Tag: how to get row number from DataTable.Compute method Tag: 138023
Stored procedure returns two result sets
Hi everyone,
I have a stored procedure called 'PagingTable' that I use for performing
searches and returning just the results for the current 'page'. That way I
can do my paging on the server and only the data that actually gets
displayed on the webpage is returned from the database server.
The sp returns two result sets. The first result set contains a single row
and columns telling me how many total results the query retuned, how many
'pages' (based on the number of items per page that I specify) and the
current page I am on. The second result set returns the rows of data for
the current page. In 'classic' ASP I handled it this way:
strSQL = "EXECUTE PagingTable " & _
"@ItemsPerPage = 10, " & _
"@CurrentPage = " & CStr(intCurrentPage) & ", " & _
"@TableName = 'Products', " & _
"@UniqueColumn = 'ItemNumber', " & _
"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _
"@WhereClause = '" & strSQLWhere & "'"
'Open the recordset
rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table
intCurrentPage = rsItems.Fields("CurrentPage").Value
intTotalPages = rsItems.Fields("TotalPages").Value
intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset
Set rsItems = rsItems.NextRecordset
Its that last line that was key. I could then go on loop over the results
and output them. How do I do this in ASP.NET 2.0? The DataSource control
only allows a single "DefaultView". So does the DataReader. How can I bind
a repeater control to the second result set (the actual data) and use the
info in the first result set to build a "pager" UI?
Thanks,
Ken VdB Tag: how to get row number from DataTable.Compute method Tag: 138021
How to cancel execution of an asynchronously executing SqlCommand?
Hello!
ADO.NET 2.0 offers the possibility to execute SqlCommands asynchronously,
for instance SqlCommand.BeginExecuteReader. Let's assume it takes quite some
time to execute such an SqlCommand. Is there a way to cancel the execution
of an SqlCommand executing in this asynchronous manner?
Best regards,
Henrik Dahl Tag: how to get row number from DataTable.Compute method Tag: 138020
System.Data.OracleClient InvalidOperationException on Inline queri
I'm using ADO.Net (Framework 1.1) against an Oracle database.
I need a resolution to the inline query problem posted at
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=186761&SiteID=1
It appears that someone from the ADO.Net team(Alazel Acheson) posted a bug
for it (#428203) last year. but I can't find a resolution for this problem in
the forum listed above or anywhere else on the web.
Was this bug ever fixed? what 1.1 patch/updates do I need to install to get
the OracleClient to execute inline queries?
I always automatically install updates from MS so there must be another
place where I can get the fix I need.
Thank you. Tag: how to get row number from DataTable.Compute method Tag: 138019
index to foxpro files
i need to access a legacy database that uses dBase files running under
foxpro for read only purposes, within net 2.0, ado 2.0 using vb and sql
statements. i have no problem getting the info i need, but it is very slow.
i'm not using any indexes which the databases have, *.idx files, i don't
know how to access them.
the connection string i use is:
DBFConnedction = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE
IV;User ID=Admin;Password=;Data Source=""M:\Names\""
M is a network drive, Names is the folder name where the databases are.
how do i make my data access go thru the indexes, do i change the connection
string, or the sql statement, or something else? i would appreciate a code
snippet.
thanks
ray Tag: how to get row number from DataTable.Compute method Tag: 138014
Create new table in DataSet
I have a DataSet that contains one table. I want to create two new
tables each of which contains all the rows but only some of the columns
from that first table. Can I do this without going back to the original
data source?
THanks.
--
Peter Aitken Tag: how to get row number from DataTable.Compute method Tag: 138013
Updateing event of datagridview
I m using the datagridview.
Except Update event rest of the all i.e. Edit cancel, delete.
I want to read the cell content . if its a Quantity user may change it
so tht bases on tht i m going to calculet the price.
Dim tb1 as textbox
tb1 = CType(DataGrid2.row(e.rowIndex).cell(8).Control(0), TextBox)
Dim a as string
a = tb1.text
REturns only "1".....Not the cell content.
What to do to get the Cell COntent which user may change and then
click Update!
Please advice!! Tag: how to get row number from DataTable.Compute method Tag: 138006
Filling tableadapaters in master/child dataset
I have defined a strongly-typed dataset with two tableadapters in a
master/child relationship. On the master tableadapter, there is a parameter
which restricts the fill of the tableadapter to less than the entire contents
of master table.
After filling the master table, do I simply fill the child tableadapter with a
single call and it magically "knows" to fill the child based on the
relationship, or do I have to walk the master table rows and fill the child
multiple times based on the rows of the master?
Sorry for this ignorant question, but I am lost here.
Thanks, Flomo Tag: how to get row number from DataTable.Compute method Tag: 138003
Recommended:How to get control of you life and time
Get control of our own life and time is the skills to be
successful.This blog is strongly recommended.
http://linux1689.blog.sohu.com/36289314.html
Recommended:How to get control of you life and time Tag: how to get row number from DataTable.Compute method Tag: 138001
DataAdapter.Fill, ConstraintException, and Primary Keys
I am running into ConstraintExceptions with the following VB.NET code:
Dim daResult As SqlDataAdapter = New SqlDataAdapter
Dim Result As DataSet = Nothing
[code to set up the data adapter with a stored procedure,
parameters, connection, etc.]
daResult.MissingSchemaAction = MissingSchemaAction.AddWithKey
daResult.Fill(Result, tableName)
and SQL code in the called stored procedure:
SELECT '2_FieldID' = table2.idfield
FROM base_table
LEFT JOIN
(table1 INNER JOIN table2 ON table1.2_idfield =
table2.idfield)
ON base_table.idfield = table1.base_idfield
where idfield is an IDENTITY(1, 1) PRIMARY KEY field on table2 and
2_idfield is a foreign key to it on table1.
*However*, I don't get the ConstraintException when I make the SQL
statement like this:
SELECT '2_FieldID' = table1.2_idfield
FROM base_table
LEFT JOIN
table1
ON base_table.idfield = table1.base_idfield
nor do I get this error when I leave out the VB.NET line:
daResult.MissingSchemaAction = MissingSchemaAction.AddWithKey
I do need the MissingSchemaAction; I can fudge my stored procedures
to avoid selecting the idfields directly, but I can't help but feel
that I'm missing something here, especially since I'm using the same
code in a lot of places and only running into the problem in a small
handful of stored procedures which aren't, as far as I can tell, bases
on tables with any different constraints than the unproblematic ones.
(Is there something I should be looking for?)
Thanks,
Caitlin Shaw Tag: how to get row number from DataTable.Compute method Tag: 137999
FXCop and Asp.net sample programs
I tried to use FxCop on the sample of
http://asp.net/learn/dataaccess/default.aspx?tabid=63
And I got the following error. Basically the code defined a member with type
of TableAdapter and no Disposable method is defined. It sounds it's not
necessary in the samples.
Should I have a empty Dispose method always in this case?
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
// dispose managed resources, leave it empty
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
CriticalError, Certainty 95, for
TypesThatOwnDisposableFieldsShouldBeDisposable
{
Target : EmployeesBLL (IntrospectionTargetType)
Resolution : "Implement IDisposable on 'EmployeesBLL' as it
instantiates members of the following IDisposable types:
NorthwindTableAdapters.EmployeesTableAdapter"
Help :
http://www.gotdotnet.com/team/fxcop/docs/rules.aspx?version=1.35&url=/Design/TypesThatOwnDisposableFieldsShouldBeDisposable.html
(String)
Category : Microsoft.Design (String)
CheckId : CA1001 (String)
RuleFile : Design Rules (String)
Info : "Types that declare disposable members should also
implement
IDisposable. If the type does not own any unmanaged
resources, do not implement a finalizer on it."
Created : 3/5/2007 9:00:10 PM (DateTime)
LastSeen : 3/5/2007 9:00:10 PM (DateTime)
Status : Active (MessageStatus)
Fix Category : Breaking (FixCategories)
} Tag: how to get row number from DataTable.Compute method Tag: 137997
Strange Dataset update problem passing to Webservice
I've encountered a strange problem when trying to update data in two
tables using a dataset. My application is split between a client
(presentation) and webservice (business and DAL).
I have a typed dataset, HSEDataset, containing two tables related by a
foreign key constraint. I use the standard GetChanges() method to get
a dataset containing only the changes to the tables, looking at these
tables I can see my changes on the client, i.e. 1 row in table 1 and 1
row in table 2, so far so good.
When I send the dataset to my webservice, however, I get 1 row in
table 1 and 0 rows in table 2?? The first table is subsequently
updated without a problem, the second obviously updates nothing. I've
since simplified things even further by just sending the second table,
however, using GetChanges on the dataset and sending the subsequent
table still doesn't work:
Dim pChanges As DataSet = Me.HSEDataset.GetChanges
Dim mychanges As HSECoreBLL.HSEDataset.IncidentsDataTable =
pChanges.Tables(1)
But calling GetChanges directly does:
Dim mychanges2 As HSECoreBLL.HSEDataset.IncidentsDataTable =
Me.HSEDataset.Tables(1).GetChanges
Both methods show 1 row in the resultant table after calling
GetChanges on the client, but only with the second method does the
webservice receive this row!! Something must be happening when the
data is being marshalled and sent to the webservice, but I can't find
the problem replicated anywhere else, any ideas? Tag: how to get row number from DataTable.Compute method Tag: 137978
DataAdapter.Fill very slow with SqlParameters?
Hi there
I found a starnage behavior. I have a method 'GetDataFromDB', which i use
internaly in my class to get data from the db. Now if i build the query
using SqlParameters for the date my query takes up to 60 sec vs. 2 sec if I
do not work with the SqlParameters!
What can be the problem? Did anyone of you had the same problems?
SQL-Statement with DateTime-params:
SELECT @TitleID AS TitleID, StationID, COUNT(*) AS BroadcastCount
FROM tPrg WHERE TitleID = @TitleID
AND StartTime >= @DateFrom
AND StartTime < @DateUntil
GROUP BY StationID ORDER BY StationID
SQL-Statement without DateTime-params:
SELECT @TitleID AS TitleID, StationID, COUNT(*) AS BroadcastCount
FROM tPrg WHERE TitleID = @TitleID
AND StartTime >= CONVERT(DATETIME, '2007-01-1 03:00:00', 102)
AND StartTime < CONVERT(DATETIME, '2007-02-28 03:00:00', 102)
GROUP BY StationID ORDER BY StationID
The implementation of the mentioned method:
public DataTable GetDataFromDB(string sqlStatement, string tableName)
{
DataSet ds = new DataSet();
ValidateConnection();
_dbCommand.CommandType = CommandType.Text;
_dbCommand.CommandText = sqlStatement;
DbDataAdapter adap = _provider.CreateDataAdapter();
adap.SelectCommand = _dbCommand;
adap.Fill(ds);
ds.Tables[0].TableName = tableName;
return ds.Tables[0];
}
Any ideas? Thank you very much for your help!
Jonas Tag: how to get row number from DataTable.Compute method Tag: 137977
SqlParameter slower in combination with DataAdabter.Fill method???
Hi there
I found a starnage behavior. I have a method 'GetDataFromDB', which i use
internaly in my class to get data from the db. Now if i build the query
using SqlParameters for the date my query takes up to 60 sec vs. 2 sec if I
do not work with the SqlParameters!
What can be the problem? Did anyone of you had the same problems?
SQL-Statement with DateTime-params:
SELECT @TitleID AS TitleID, StationID, COUNT(*) AS BroadcastCount
FROM tPrg WHERE TitleID = @TitleID
AND StartTime >= @DateFrom
AND StartTime < @DateUntil
GROUP BY StationID ORDER BY StationID
SQL-Statement without DateTime-params:
SELECT @TitleID AS TitleID, StationID, COUNT(*) AS BroadcastCount
FROM tPrg WHERE TitleID = @TitleID
AND StartTime >= CONVERT(DATETIME, '2007-01-1 03:00:00', 102)
AND StartTime < CONVERT(DATETIME, '2007-02-28 03:00:00', 102)
GROUP BY StationID ORDER BY StationID
The implementation of the mentioned method:
public DataTable GetDataFromDB(string sqlStatement, string tableName)
{
DataSet ds = new DataSet();
ValidateConnection();
_dbCommand.CommandType = CommandType.Text;
_dbCommand.CommandText = sqlStatement;
DbDataAdapter adap = _provider.CreateDataAdapter();
adap.SelectCommand = _dbCommand;
adap.Fill(ds);
ds.Tables[0].TableName = tableName;
return ds.Tables[0];
}
Any ideas? Thank you very much for your help!
Jonas Tag: how to get row number from DataTable.Compute method Tag: 137976
Does DataSet.Copy() remove event handlers?
I'm wireing some event handlers to tables in a DataSet. This is performed in
EndInit(). Everything works well untill I use the Copy() method of the
DataSet class. I see that the EndInit is run for the copy, so that the event
handlers are correctly added. But at some time the connection between the
tables and the event handlers is lost. I suspect that Copy() does something
after calling EndInit.
Does anybody now why this happens and how I can avoid it? Maybe EndInit is
not a good place for wireing up events? Tag: how to get row number from DataTable.Compute method Tag: 137975
Closing Jet Database seems unreliable
I am having difficulty getting Jet databases to close by calling the
DbConnection.Dispose method. The method seems to work, and the
connection state changes to closed, but about 25% of the time at some
level the process is keeping hold of the connection, and not deleting
the ldb file for example. Then when I terminate my process the ldb file
gets deleted... Or sometimes if I open and close a whole series of mdb's
in sequence the ldb's will build up and up then suddenly they'll all get
deleted at once... :-\
If I open my databases exclusively (mode-share exclusive) (for safety as
the app is single user) then if I close one and then reopen it in the
same process I'll often get a message that the database is already
exclusively open.
Suspecting that connection pooling was getting in the way I tried adding
"OLE DB Services= -1" or -4 to the connection string and curiously this
seems to make things worse. It virtually guarantees that the file will
remain open whereas with pooling turned on the closure seems to work
about 75% of the time. In tests I can see no difference from my point of
view between the times when the call works and the times when the call
fails.
Does anyone know a guaranteed way in ADO.NET of killing a connection to Jet?
I want to do this so that my app can (for example) backup or restore the
Jet database from a menu prompt while the app is still running.
Is there any way of spying what is happening at the underlying Jet level?
Thank you for your help,
Tom Tag: how to get row number from DataTable.Compute method Tag: 137973
Cannot update the cursor mytable, since it is read-only
Hi,
When I test my application inside the visual studio net, all works fine, but
as soon I close IDE and test in this way:
http://localhost/myapp/default.aspx
the application open, but at time I try to update I get this error
Cannot update the cursor mytable, since it is read-only
icmd.CommandText = cmd; // the update sql "update my table...etc"
icmd.Connection = Conn;
icmd.ExecuteNonQuery(); // here the error
NOTE: this works when I run inside de visual net IDE, but, does't works out
of IDE (internet explorer http://localhost/myapp/default.aspx (Same computer)
any ideas? Tag: how to get row number from DataTable.Compute method Tag: 137966
import large text file
hi there,
i have a huge large text file (350.000 lines) that i want to import to
a MS Acccess Database, of course i don't want to use Access, but do it
with C#.
i already have tried the AddRow method or Insert, reading each line of
the text file, the problem of course is velocity, it would take more
than 4 hours to add all lines/records to the database.
any suggestion?
thank you Tag: how to get row number from DataTable.Compute method Tag: 137961
CSV (from URL) to DataSet ?
I want to pull some data from a CSV URL (from yahoo's website) directly into
a DataSet.
Is there a way to do this without having to save the CSV file on disk ?
Thanks,
Yannick L. Tag: how to get row number from DataTable.Compute method Tag: 137960
Select query question
Hi
I am using below query to retrieve records from backend sql server 2005 db;
SELECT ID, Company ....
FROM tblClients
WHERE (Status = @status)
Problem is that sometimes I need all records regardless of what is status.
How can I achieve that? Can I add something like OR (Status ="") OR (Status
isNull) in the WHERE clause? If so, would using OR make the retrieval slow?
Thanks
Regards Tag: how to get row number from DataTable.Compute method Tag: 137955
Enterprise Library and Access databases
Using Enterprise Library 2.0 (Jan 06) and .NET 2.0 (VB.Net 2005)
Dim dbPF As Data.Common.DbProviderFactory
Try
dbPF =
Data.Common.DbProviderFactories.GetFactory("System.Data.OleDb")
db = New
Microsoft.Practices.EnterpriseLibrary.Data.GenericDatabase( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=NotesManagerServer.mdb", dbPF)
db.CreateConnection()
Catch ex As Exception
MsgBox("Could not connect to system database. Application
terminating.")
End
I have a Select query in the Access database, named GetFileList and
here is the SQL copied from Access:
SELECT FileList.SrNo, FileList.Filename, FileList.CRC32,
FileList.Timestamp
FROM FileList;
This code works just FINE:
Try
Dim rs As Data.DataSet
rs =
db.ExecuteDataSet(System.Data.CommandType.StoredProcedure,
"GetFileList")
DataGridView1.DataSource = rs
DataGridView1.DataMember = rs.Tables(0).ToString
Catch ex As Exception
MsgBox("Exception occured" + ex.Message)
End Try
I have a Update query in the Access database, is named InsertFile and
here is the SQL copied from Access:
PARAMETERS Filename Text ( 255 ), CRC32 Long, [Timestamp] Long;
INSERT INTO FileList ( Filename, CRC32, [Timestamp] )
VALUES (@Filename, @CRC32, @Timestamp);
To run this query in VB.Net, I have this code that WORKS:
Dim p(2) As Data.Common.DbParameter
p(0) = New Data.OleDb.OleDbParameter
p(1) = New Data.OleDb.OleDbParameter
p(2) = New Data.OleDb.OleDbParameter
p(0).Value = "ASDF"
p(1).Value = 11
p(2).Value = 22
Dim cmd As System.Data.Common.DbCommand
cmd = db.GetStoredProcCommand("InsertFile")
cmd.Parameters.Add(p(0))
cmd.Parameters.Add(p(1))
cmd.Parameters.Add(p(2))
cmd.Connection = db.CreateConnection
cmd.Connection.Open()
cmd.ExecuteNonQuery()
My problem is that it looks so 'inelegant' and probably is. How can I
run the Access query using just the db object .. as in
db.ExecuteNonQuery ... I've tried various combinations and I get
various errors regarding parameter discovery ...
Anyone?
I just want to use the db object (GenericDatabase) to run the Insert
query.. with parameters...
Thanks in advance.
ND Tag: how to get row number from DataTable.Compute method Tag: 137947
Update and delete sql not generated by wizard
Hi
In my vb winform data app with sql server 2005 backend, when in dataset I
run the data adapter configuration wizard it generates insert and select sql
statements but not delete and update. As a result the data is not updated
when changed in form. What is the problem and how can I get the update and
delete statements generated?
Thanks
Regards Tag: how to get row number from DataTable.Compute method Tag: 137942
Error using money datatype parameter and DBNull.
Hi,
Hi,
I am getting an error executing a SQL Server stored procedure with parameters of type
money. The following code fails:
With cmd
...
If mActualCost = Decimal.MinValue Then
.Parameters.AddWithValue(PRM_ActualCost, System.DBNull.Value)
Else
.Parameters.AddWithValue(PRM_ActualCost, mActualCost)
End If
...
.ExecuteNonQuery
End With
mActualcost is defined as type decimal. PRM_ActualCost is a string constant defining
the parameter name. By convention I define a decimal value type member variable to
have the value decimal.MinValue when it's value is unknown and therefore to be stored
as null in the database table being updated.
The stored procedure being executed is doing a simple INSERT into a table (there are
other parameters but they are irrelevant to this discussion). The ActualCost column
in the table is defined as a nullable column of type money. The parameter to the
stored procedure is defined as type money.
When my mActualCost variable has a value other than Decimal.MinValue, this code
succeeds and the database table is correctly updated. However, if it has the value
Decimal.MinValue, the code above fails. An error exception is thrown on the
ExecuteNonQuery. The error is:
"Implicit conversion from datatype nvarchar to money is not allowed. Use the CONVERT
function to run this query."
If I change this code to explicitly define the parameter's datatype as:
If mActualCost = Decimal.MinValue Then
With .Parameters.Add(PRM_ActualCost, SqlDbType.Money)
.Value = System.DBNull.Value
End With
Else
.Parameters.AddWithValue(PRM_ActualCost, mActualCost)
End If
then this code succeeds when the variable value is decimal.minvalue.
I have not done exhaustive tests for all possible datatypes, but I know similar code
succeeds with datatypes such as integer and string.
What's going on here with the money datatype and DBNull?
- Jeff
-- Jeff Tag: how to get row number from DataTable.Compute method Tag: 137935
Column does not allow nulls when saving
Hi
I have a vb.net winform data app with sql server 2005 backend. The ID field
in the tblClients table is an 'int' identity column that does not allow
nulls. The problem is that when I try to save the record using the below
code;
Me.Validate()
Me.CompaniesBindingSource.EndEdit()
Me.CompanyTableAdapter.Update(Me.EMSDataSet.tblClients)
I get an 'Column 'ID' does not allow nulls' error on the second line
(EndEdit()). The first few lines of stack trace is below. What is the
problem and how can I fix it?
Thanks
Regards
----------------
Stack Trace
System.Data.NoNullAllowedException was unhandled
Message="Column 'ID' does not allow nulls."
Source="System.Data"
StackTrace:
at System.Data.DataColumn.CheckNullable(DataRow row)
at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs
args, DataRow eRow, DataRowAction eAction, Boolean fireEvent) Tag: how to get row number from DataTable.Compute method Tag: 137934
GetOrdinal problem
Hi,
Sorry for the cross post first of all. I posted to the ADO forum the
first time by accident.
I wrote a windows service in c# .net 2.0. There is a timer that fires
every second or so and when it does I start two threads (unless they are
still running from the last time).
One of the threads queries SQL server using a data reader. Every once
in a while I get a runtime related to GetOrdinal. Here is the call
stack:
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String
fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at GTS.ComAppLibrary.LXRecord.ReadRecords(SqlDataReader _dr)
I googled for this and I did find one posting on MSDN that goes back to
Nov of 2005. A number of people have had a similar problem, but nobody
seems to have a good solution.
Unfortunately I have just used a work around of supplying integers to
the GetString (and other get methods).
Is there a fix for this problem? Has MS been able to reproduce the
problem?
Thanks
Randy Tag: how to get row number from DataTable.Compute method Tag: 137915
Getting out of memory when DataSet.GetXml()
When the dataset is too large it thows OutOfMemory Exception although
that there is enough momory on the web server, is this a bug in .net
or something must be configured?! The Dataset contains near 50 000
rows. How can I escape this. Please help? Tag: how to get row number from DataTable.Compute method Tag: 137912
Weird problem inserting image in DB...
Hi all,
I'm experiencing quite a weird problem inserting an image in a database.
I've designed a small form with some fields and a picturebox. I can fill all
of them and clcik the 'create item' button, and the task is performed
wonderfully, but as soon as I try to insert the data with the image set, it
simply won't insert it.
Debuggin the application I see that the data row is allways OK (even the
image) just prior to the TableAdapter.Update(), but the update method
returns 0 and HasErros equals false.
Up until this point, I would call the problem to be a bitch, but not wird
xD The 'weirdness' comes here:
I've attached a SQL Profiler to the user instance and found that the
following entries happen JUST when I try to insert the item with the image
field binded (and not when there is no image field set)...
<profiler log>
ErrorLog 2007-03-01 21:17:09.02 spid52 Error: 17207, Severity: 16,
State: 1.
2007-03-01 21:17:09.02 spid52 FCB::RemoveAlternateStreams: Operating
system error (null) occurred while creating or opening file 'C:\Documents
and Settings\Pablo Alvarez\Mis documentos\Visual Studio 2005\Projects\Copia
de Aselbi\Aselbi\bin\Debug\GRADB.mdf'. Diagnose and correct the operating
system error, and retry the operation.
ErrorLog 2007-03-01 21:17:09.07 spid52 Starting up database
'C:\DOCUMENTS AND SETTINGS\PABLO ALVAREZ\MIS DOCUMENTOS\VISUAL STUDIO
2005\PROJECTS\COPIA DE ASELBI\ASELBI\BIN\DEBUG\GRADB.MDF'.
</profiler log>
This error repeats three times. I've checked with sysinternal's filemon and
I get an error 0xC0000042 on sqlservr.exe trying to acces 'C:\DOCUMENTS AND
SETTINGS\PABLO ALVAREZ\MIS DOCUMENTOS\VISUAL STUDIO 2005\PROJECTS\COPIA DE
ASELBI\ASELBI\BIN\DEBUG\GRADB.MDF' two times.
Why is this happening just when the image field on the row is set? any
clues?! I'm really frustrated :(
Environment Description:
- Working in a Virtual PC (host: Vista, VPC: WinXP SP2)
- The application I'm developing is an VSTO addin for Outlook 2003 (VSTO
2005SE)
- SQL Server 2005 express
- The VPC does not have antivirus nor anything that could have installed
a filter driver that holds the file
Thanks a lot in advance!
Pablo Tag: how to get row number from DataTable.Compute method Tag: 137911
Using TransactionScope and connection pool
hi,
I am trying to use TransactionScope and multiple async calls but get
Transaction aborted - transaction in doubt errors.
My program goes like this
using (TransactionScope ts = new TransactionScope())
{
do an insert on Table1 (Connection1 = new SqlConnection)
foreach table to read from aList
{
doSyncRead using Connection1
}
foreach table to insert from bList
{
do AsyncInsert passing in
DependentTransaction(BlockCommitUntilComplete) using new
SqlConnection(same connection string as Connection1)
}
Wait for all Inserts to Complete (using WaitHandle.WaitAll)
do a couple more inserts using Connection1
ts.Complete()
}
Each AsyncInsert create their own TransactionScopes using the
dependent transaction and call complete when finished.
Using Profiler I can see it gets to the TM Commit and seems to bail
out at this stage. But if I change the AsyncInserts to Synchronous
Inserts it works fine. All the inserts seem to complete - it is only
at the Commit stage that it fails with TransactionInDoubt.
The TransactionScope is in a web service. The error gets returned to
the client but then there still seems to be a TM REQUEST Command on
the SQL Server which then blocks other database requests until I
restart the DTC.
Can anyone tell me what I'm doing wrong? Is there another service I
need to enable or something?
thanks
Phil
BTW I also get a Promote Tran on Connection1 (seen via Profiler) -
starts and completes. Tag: how to get row number from DataTable.Compute method Tag: 137909
how do you get the row number from the DataTable Compute() method?