Strange error with ODBCDataAdapter.SelectCommand
Hi,
I am attempting to create a vb.net frontend for a mysql database via
ODBC.
My problem (so far) is that I cannot get my
ODBCdatadapter.SelectCommand properities to "stay". First of all I
cannot input them at all through the DataAdapter properties window;
the SelectCommand, InsertCommand and DeleteCommand properties are all
greyed out for some reason. Okay, fine, I can code them manually:
MyODBCAdapter.SelectCommand = New ODBCCommand("Select * from
master_planner",conn)
This works, and the result are then even visible and accessible in
the properties window after I do. BUT THEY DISAPPEAR WHEN I TRY TO
BUILD!! The code I put in just vanishes! This has happened several
times; I cannot find a workaround.
As I am using the 2002 version of vb.net, I cannot use the wizard to
configure the ODBCdataadapter. Has anyone every seen this before?
Thank you,
Mike Cooper Tag: Default dates Tag: 67232
Adding new rows in child with ReadOnly parent
I need a little push on how to add new rows to a child.
The parent does not change and would be read only. Using
the Northwind database, they can add new products but can
not add new categories. I am using a Datarelation
between the two tables and using textboxes to display the
child information.
I have the databinding for the textbox going. However, I
am not sure about how to get started with getting the new
row inserted in to the database.
Any little push to get me started in the right direction
would be helpful.
Thanks,
Charles Tag: Default dates Tag: 67228
UniqueConstraint
Hi all,
I trying to make the code below work without success
Sub x()
Dim t As DataTable = New DataTable("t")
Dim c1 As DataColumn = New DataColumn("col1", GetType(String))
Dim c2 As DataColumn = New DataColumn("col2", GetType(String))
Dim c3 As DataColumn = New DataColumn("col3", GetType(String))
t.Columns.Add(c1)
t.Columns.Add(c2)
t.Columns.Add(c3)
Dim u As UniqueConstraint = New UniqueConstraint("u", New String()
{c1.ColumnName, c2.ColumnName}, True)
t.Constraints.Add(u)
End Sub
The problem is that if you inspect "u", it's properties aren't setted.
This is an overloaded constructor as documented in
help://MS.VSCC.2003/MS.MSDNQTR.2003APR.1033/cpref/html/frlrfsystemdataunique
constraintclassctortopic9.htm
I need this overload since it seems to be the only way I can create a
multi-column primary key in a DataTable on the Compact Framework, but it
can't get it to work either on the .NET Framework 1.1
Am I missing something?
Thanks in advance for any help provided.
Alfred Gary Myers Jr.. Tag: Default dates Tag: 67222
setting null values to parameters
Hello,
I use web-service, and also access for my database, and VB as my script of
web-service, and oleDBConnection to connect to database.
I have created a oleDbParameter.
I want to set it null value -
How can I do that ?
Thanks :) Tag: Default dates Tag: 67221
DataGrid and sorting
I have a DataGrid that I am using as the master in a
master-detail relationship. When the user double clicks
on a row of the DataGrid, I update the details section
(some TextBoxes, etc.) by using CurrencyManager.Position
= N, where N is the row index in the DataGrid that the
user just double clicked on.
This all works great until the DataGrid is sorted. At
this point, the row index in the DataGrid no longer
corresponds to the row's position in the underlying
dataset. What is the easiest way to get the correct index
to use when setting the position of the CurrencyManager?
Thanks,
Mike Tag: Default dates Tag: 67220
Connection Pooling is not working
Hi,
I have Windows Form application that access Sql Server 2000.
I copied this application inside a network path (Ex: h:\application).
The connection string is inside the application (hard-coded). If five
users access the application and I run sp_who in Query Analyzer it
displays five connections. The question is: Do I need to build an Enterprise
Serviced component and set the object pooling property as true to make
it work?
I say that because when I have one thread and I create two or more
connections inside this thread I know that the connection pooling will
work.
Regards,
Marco Tag: Default dates Tag: 67215
Conditionally adding/removing items to a DataGrid
Hi,
I'm working on an ASPX page which has a DataGrid. This DataGrid is bound
to a custom object. My requirement is that at rune time, I want the datagrid
to display the items that match a certain criteria.
For example, I have a custom collection named Users to which the DataGrid is
bound. The Users collection represents the entire list of users for my
system. But when displaying the datagrid, I want to display only those users
that have the "administrator" role. Is there any way in which I can do this
throuh the ItemDataBound event?
CGuy Tag: Default dates Tag: 67211
How to work with joined tables in ADO.NET
Hi.
I have (simplified) 3 tables. (See bottom). This is not my real tables but
they show my point. I want to represent data from all tree tables and i
wanna do this by joining them. The joined data will be represented in a
datagrid, where editing,inserting, deleting will be aviable.Update is ok for
now.
How do i do this the right ADO.net way? I just cant figure out an elegant
way to perform the update. Eg. i can make a view wich fetch the data, and
put it in the grid....But how do i update the right tables on an elegant
way? Eg. I edit a country tag, and want to peform an update.
Ideally i have my database, a webservive from where i get a dataset, and
where i (maybe sent som kind of a diffgram) wich enable the webservice to
perform the updates. All this i can figure out if it wasnt for the joins.
Anyone know how to work with joined tables in ADO.net or have some links? I
have used HOURS to read "all" the MSDN articles about datasets, dataadpters,
autogen commands, but nothing seems to solve my problem ....an "easy" way.
It cant be right that i have to wirte a stored-proc where i sent (see
bottom) all me variables and make it update each and every table manually?
Looking forward to some inputs.
Regards Anders
======Tables========
TablePerson
-------------
Id
Country_FKId
City_FKId
TableCountry
--------------
Id
Country
TableCity
--------------
Id
City
================= Tag: Default dates Tag: 67206
Not associated with a trusted SQL Server connection
I am new to ASP & ADO.NET and IIS web applications, but
not to SQL databases. I can successfully build Windows-
forms apps using Visual Studio ADO. However, I can't
connect for Web forms. I created a sqlconnection and
sqladapter to my SQL Server,Northwind db, which I dragged
from Server Explorer. I generate a dataset, and Preview
Data in the da, works fine. I then enter vb code on
Page_Load event:
SqlDataAdapter1.Fill(ds)
DataGrid1.DataSource = ds.Tables("Employees")
DataGrid1.DataBind()
When I build and browse (localhost), I get:
Server Error in 'myapp'...SqlException: Login failed for
user (null). Reason: Not associated with a trusted SQL
Server connection. I have since found error codes
SQLState 42000 and SQL Error 18452, which could be
permissions among other things, but haven't found anything
detailed on how to fix this. The
server mode is Windows auth. I've tried various settings
in Web.config connection string, tried configuring IIS
Virtual Directory Managment setting Integrated Security
and I downloaded the .NET framework onto SQL server. I
still get the error.
Perhaps my workstation version of IIS is not configured
properly(?); do I need an IIS-server edition? I would
appreciate any help or reference to articles concerning
this problem (IIS config - VisStudio.NET - SQL
connectivity). Tag: Default dates Tag: 67198
Batch Query with Parameters
I'm using a stored procedure to retrieve hierarchical data from a database
for read only use. The stored procedure is:
CREATE PROC GetStringTable(@Token int) AS
SELECT * FROM StringTable WHERE Token = @Token
SELECT * FROM StringTableDetail WHERE Token = @Token"
When I use the "design surface" of the data access component to generate the
SqlDataAdapter (after setting the TableMappings manually) I can set the
Parameter value @Token with no problem.
However I'd like to code this without the design surface (with everything
local so the function can be shared/static). When I set the code:
Dim da As New SqlDataAdapter("GetStringTable", New
SqlConnection(ConnectString))
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.TableMappings.Add("Table", "StringTable")
da.TableMappings.Add("Table1", "StringTableDetail")
da.SelectCommand.Parameters("@Token").Value = token
at runtime it fails, complaining that there is no parameter @Token in the
collection. Sure enough, the da.SelectCommand.Parameters collection is
initialized (not Nothing/null), but it contains zero items.
Is there any way to configure this DataAdapter to handle the multiple tables
with Parameters?
--- Jim --- Tag: Default dates Tag: 67192
Data objects as data structures
Hello there!
I'm learning ADO.NET and so far I'm quite impressed by the richness of
its object model up to the point that I started thinking about its
classes (at least those below System.Data - e.g. DataTable) as basic
data structures, unrelated to the existance of an underlying DB
instance.
What I mean is that I used to think about ADO just as an object model
hiding DB complexity from implementation; now I wonder whether some of
its classes could be used without any DB binding at all! As an example
think about a Monte Carlo application needing access to run-time
generated paths: storing this random data inside a DataTable instead
of arrays/lists/double* would allow advanced data selection,
constraints definition, creation of views... no matter whether the
data is eventually stored in a real DB (storing Monte Carlo paths
would probably be useless).
As far as I see the big odd with this approach is performance
overhead... any comment?
Thank you guys!
-- Marco -- Tag: Default dates Tag: 67191
View/Debug SQL Stmt Before Execution
Is there any way to output the completely populated SQL statement that is
about to be executed in ADO.NET? My SQL statements can contain and error
and I woulds like to debug them. Example:
Dim sqlSelect As String = "INSERT INTO Table(col1, col2) VALUES (@col1Value,
@col2Value)
Dim cmdTable As New SqlCommand(sqlSelect, cs)
...
cmdTerritory.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@col1Value", ...))
cmdTerritory.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@col2Value", ...))
' Debug Breakpoint Here
' IS THERE A WAY VIEW THE COMPLETE SQL STATEMENT TO BE EXECUTED???
cmdTerritory.ExecuteNonQuery() Tag: Default dates Tag: 67189
creating XSDs & DataSets via drag-drop
[originally attempted to post this on 9/23 but it never showed up]
me = XML newb
i am confused about one aspect of visually creating XSD's vs. XML
DataSets...
if i add a new DataSet to my project, then drag and drop the table onto the
designer, it creates a representation of that table in the designer.
simple.
BUT
if i add a new XML Schema to my project instead, then drag and drop the
table onto the designer, i get the table element as before, but it is also
parented to a 'Document' element which i didn't ask for, don't know the
purpose of, and don't want getting in my way...
why does it behave differently?
what is the purpose (advantage?) of the Document-level element?
which way is 'right'?
another question: other than this difference is there any other difference
between creating a DataSet vs. creating an .XSD then clicking 'Generate
Dataset'?
as always, any insight would be much appreciated! TIA all! =) Tag: Default dates Tag: 67188
How to Delete a child row using textboxes and datarelations
Hello,
I have a group of textboxes that display child table
information. How would I be able to delete the row from
the dataset?
Right now I have BindingContext set to my parent table
and therefore I could not so something like:
DataTable.Rows(bmbPosition.Position).Delete()
This would only return the Position of the Parent, I need
the child. I would like to keep this line in my code. I
do NOT want to do a rowfilter on an ID and then delete.
How would I delete a child row using a group of textboxes
that display the infomation that uses datarelations.
Thanks,
Charles Tag: Default dates Tag: 67187
calculated data fields could not be converted
Hello,
I use web-service, and also access for my database, and VB as my script of
web-service, and oleDBConnection to connect to database.
When I do query like this :
"select col1, col2, col3, ... from myTable"
...
mark = dr.getInteger(0) ' dr is the data-reader component.
...
... everything works fine,
but when I do a query as follows :
"select col1 + 10, col2, col3, ... from myTable"
...
mark = dr.getInteger(0) ' dr is the data-reader component.
...
... and trying running the query I get the following message :
'The data value could not be converted for reasons other than sign mismatch
or data overflow. For example, the data was corrupted in the data store but
the row was still retrievable".
What should I do in order to make things work on calculated fields ?
Thanks :) Tag: Default dates Tag: 67186
Any example to create a custom constraint?
Any example to create a custom constraint?
ForeignKeyConstraint and UniqueConstraint can not really fit my need.
DataTable.ColumnChanging and RowChanging events do not fit my need either.
Thanks for your help Tag: Default dates Tag: 67180
Need help with a query
I'm having trouble getting a particular query to work with ADO.NET. I want
to create a query where a returned field contains a certain string depending
on the which of several boolean fields in the database is true. I need a
function that will work in both Access 2000 and SQL Server, but I can't get
anything to work properly. I used to be able to do this with the Switch()
function with DAO and Access97. Does anyone know of a way to do this now
with both Access 2000 and SQL Server?
- Don Tag: Default dates Tag: 67177
OracleTransaction has no effect
Hi,
I use the OracleTransaction class. I use multiple OracleCommands
participating in the same OracleTransaction.
When one of the OracleCommands fail when executing and I do a rollback on
the OracleTransaction it does NOT rollback the previous commands. It seems
like the connection? is auto commiting each command I execute.
Does anyone know how to solve this?
Regards,
René Tag: Default dates Tag: 67172
Binding the date field to textbox
Hi,
I have the problem with binding the textbox with date field in the table of
the dataset.
The problem is that if the entered text in the textbox isn't of the correct
type (for example "dfhd" instead of "12.6.2003"), the text gets
automatically overwritten by the old value. That way the user has to type
everything all over again instead of just correcting the entered value.
Is there a way to prevent old value to overwrite the entered text?
I used the following code to bind the textbox to a dataview...
Dim objBinding As New Binding("Text", ds.NETBill.DefaultView, "Date")
AddHandler objBinding.Format, AddressOf FormatDate
txtDatum.DataBindings.Add(objBinding)
...and following code to validate entered text...
Private Sub txtDatum_Validating(ByVal sender As Object, ByVal e As
System.ComponentModel.CancelEventArgs) Handles txtDatum.Validating
If Not ControlValue.CheckValue (txtDate, ControlValue.ETip.tDate,
ds.NETRacun.DefaultView(0)) Then
e.Cancel = True
End If
End Sub
Regards,
Taras Tag: Default dates Tag: 67166
ADO OR ADO.NET
I am using VS.NET to develop one small software which based on Windowsforms.
The number of end user is less than 10. So I am wondering I should use ADO
or ADO.NET. Any suggestion is appreciated. Tag: Default dates Tag: 67153
Record locking Strategies? (ADO.NET)
Due to the disconnected architecture of the ADO.NET model
which are the recomended strategies for application which
requires record locking (for example a Online Ticket
Application)?
Thanks
Bill Tag: Default dates Tag: 67152
OracleClient special characters in column name
Hello
I am working with System.Data.OracleClient
How can I create a table with special characters in
column name ?
I have tried:
OracleCommand oCmd = new OracleCommand("CREATE TABLE
T1 ('_C1' number)");
oCmd.ExecuteNonQuery();
OracleCommand oCmd = new OracleCommand("CREATE TABLE
T1 (\"_C1\" number)");
oCmd.ExecuteNonQuery();
OracleCommand oCmd = new OracleCommand("CREATE TABLE
T1 (\\_C1 number)");
oCmd.ExecuteNonQuery();
OracleCommand oCmd = new OracleCommand("CREATE TABLE
T1 ({_}C1 number)");
oCmd.ExecuteNonQuery();
... but it does not work.
Thanks for replies. Tag: Default dates Tag: 67151
Connection pooling in Oracle
Hi,
How can I implement connection pooling in Oracle? I used .NET Framework 1.0,
Oracle .NET Provider 1.0 and Oracle Database 9i
Thanks for the help...
Eka Tag: Default dates Tag: 67149
Oracle .NET Provider 1.1
Hi all,
Where can I download Oracle .NET Provider 1.1? Is it available at all?
I need this because i got this error: "Failed to initialize distributed
transaction. Please see KB article Q322343."
I saw the KB already and it said that i need to download Oracle .NET
Provider 1.1 but I couldn't find it.
Thanks for help..
Eka Tag: Default dates Tag: 67148
Problem with the OleDb.OleDbCommandBuilder
Hi all,
Since my experience with sql is just beginning to grow
i use the commandbuilder object to generate updatecommands deletecommands
insertcommands.
With first run th update works without a problem
when i call my update function again (changing the same row again) i get
"Concurrency violation: the UpdateCommand affected 0 records."
Exception
The debugger shows that the dataset.datatable.row.rowstate = modified
But the update simply doesn't work
Does anyone know what i am doing wrong ?
any help would be greatly appreciated
Thanks & regards
Sven
Code:
For i = 0 to ds.tables.count -1
da.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM " &
ds.Tables(i).TableName, mConnection)
comm = New OleDb.OleDbCommandBuilder(da)
da.UpdateCommand = comm.GetUpdateCommand
da.DeleteCommand = comm.GetDeleteCommand
da.InsertCommand = comm.GetInsertCommand
rowsaffected = da.Update(ds.Tables(i))
Debug.WriteLine("Updated table: " & ds.Tables(i).TableName & " affected
rows: " & rowsaffected)
Next Tag: Default dates Tag: 67144
Controls missing in the forms
Hi All,
I am developing an ERP Application in VB.Net. I have used a common
dataset and all the form level datasets are used the common dataset for the
"datasetname"property. But when I open the form, its dead slow to show the
design of the form. My system is P4, 256 MB RAM.
May I know the soluation for the same or welcome any suggestions?.
Also sometimes the control are missing while loading the form in the
design time. but the same control object is available in the genereated code
of the form.
Can u help me for the same??
Thanks,
Rethish Tag: Default dates Tag: 67141
Reading Excel with ADO
Hello,
I have tried to read Excel spreadsheet through ADO. I used the sample code
provided by msdn.I don't know what the fuck is the matter with the code
cause I can't read the Excel. I tried similar system with Access and it
works fine. Here is the error and the sample code.
Error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not
find the object 'ASPTOC'. Make sure the object exists and that you spell its
name and the path name correctly.
/ASPTOC.asp, line 21
Code:
<%@Language=VBScript %>
<html>
<head>
<title> Displaying An Excel Spreadsheet in an Web Page </title>
</head>
<body bgcolor="#FFFFFF" text="#000000" >
<h1>ASP Table of Contents</h1>
<%
'Creates an instance of an Active Server Component
Set oConn = Server.CreateObject("ADODB.Connection")
'Connects to the Excel driver and the Excel spreadsheet
'in the directory where the spreadsheet was saved
strProvider = "Driver={Microsoft Excel Driver (*.xls)};
DBQ=C:\Inetpub\wwwroot\Testi_site\ASPTOC.xls;"
'Opens the connection to the data store
oConn.Open strProvider
'Selects the records from the Excel spreadsheet
strCmd = "SELECT * from `ASPTOC`"
Set oRS = Server.CreateObject("ADODB.Recordset")
'Opens the recordset
oRS.Open strCmd, oConn
'Prints the cells and rows in the table
Response.Write "<table border=1><tr><td>"
'Gets records in spreadsheet as a string and prints them in the table
Response.Write oRS.GetString (, , "</tr><td>", "</td></tr><tr><td>",
NBSPACE)
%>
</body>
</html>
It looks like ODBC error but I did the ODBC like instructed. I also
installed the .net framework and latest MDAC to the server. The sample code
instructions say that u should use Excel 98 or 2000. I'm using Excel 2002.
Can this be the problem?? I think i've tried everything but can't figure
this out. Can anyone please help me???
Thanks!
KS Tag: Default dates Tag: 67138
Confused by one more piece of IBindingList
Thanks for all the help I've been getting working on understanding
IBindingList and data binding in general.
My understanding about IBindingList is that it's primary use is for binding
custom strongly typed collections to complex controls (primarily DataGrid)..
So by grouping the parts of the interface into their various functionality
I'm coming to grips with how it works, with one exception..
Datagrid doesn't have any searching ability AFAIK, so why does IBindingList
have functionality for searches? I know that it's optional, and doesn't
have to be implemented.. But every other piece of the interface maps to a
piece of the puzzle needed for the grid to be able to handle adding,
removing, editing rows of data, sorting them, and so on.
So is the search functionality actually used? And if so where?
I hope I'm not just being obtuse.. I'm just trying to make sure I don't
dismiss that section of the interface and then later find out it's used
internally by something else, and I've hosed things by not implementing it..
Thanks in advance (again)
William Rowland Tag: Default dates Tag: 67133
How do I format datagrid column to short time?
In my VB.Net program I am populatating a datagrid column using an MS Access
database field that that contains data formated in "Short Time" (17:34).
Right now, the datagrid displays the entire date and time. I need only the
short time. How can I do this simply?
I dont't use ASP.
Thanks,
Gigi Tag: Default dates Tag: 67128
Looking for dialog to browse available connections
Can anyone point me to a dialog box which will allow me to browse for an
ADO.NET (or ODBC) connection?
The user would select the provider (ODBC data source), the server, database
etc. Dialog would construct the connection string.
Thanks Tag: Default dates Tag: 67127
Binding a SQL Image Column to an Image Control - Can I Avoid Saving The ByteStream/FileStream To Disk ?
This is a multi-part message in MIME format.
------=_NextPart_000_001E_01C385E1.A67BC190
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I have a C#/ASP.NET/ADO.NET/SQL Server app that stores and retrieves =
images.=20
The problem is the ASP Image control binds to a file. When retrieving an =
image from SQL, I would really like to avoid saving the =
ByteStream/FileStream to disk, just to then read it back into the Image =
control.=20
Is there a way to avoid this?
// This is my current implentation. It works, but requires lots of disk =
i/o.
// Write the ByteArray to a FileStream and then the FileStream to disk.
iLen =3D mySqlDataReader.GetBytes(0, 0, myByteArray, 0, lBufSize);
FileStream myFS =3D new FileStream(@"c:\image.jpg", FileMode.Create, =
FileAccess.Write);
myFS.Write(myByteArray, 0, iLen);
ASPImage1.ImageUrl =3D @"c:\image.jpg";
Thanks in advance,
Ben
------=_NextPart_000_001E_01C385E1.A67BC190
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.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff background=3D"">
<DIV><FONT face=3D"Courier New" size=3D2>I have a C#/ASP.NET/ADO.NET/SQL =
Server app=20
that stores and retrieves images. </FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>The problem is the ASP Image =
control binds=20
to a file. When retrieving an image from SQL, I would really like =
to avoid=20
saving the ByteStream/FileStream to disk, just to then read it back =
into=20
the Image control. <BR></FONT><FONT><FONT face=3D"Courier New" =
size=3D2>Is there a=20
way to avoid this?</FONT></DIV></FONT>
<DIV><FONT size=3D2><FONT face=3D"Courier New"><FONT><FONT=20
face=3DArial></FONT> </DIV></FONT></FONT></FONT>
<DIV><FONT face=3D"Courier New" size=3D2>// This is my current=20
implentation. It works, but requires lots of disk=20
i/o.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>// Write the ByteArray to a =
FileStream and=20
then the FileStream to disk.<BR>iLen =3D mySqlDataReader.GetBytes(0, 0,=20
myByteArray, 0, lBufSize);<BR>FileStream myFS =3D new =
FileStream(@"c:\image.jpg",=20
FileMode.Create, FileAccess.Write);<BR>myFS.Write(myByteArray, 0,=20
iLen);</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>ASPImage1.ImageUrl =3D=20
@"c:\image.jpg";</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Thanks in advance,</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2>Ben</FONT></DIV>
<DIV><FONT face=3D"Courier New" =
size=3D2></FONT> </DIV></BODY></HTML>
------=_NextPart_000_001E_01C385E1.A67BC190-- Tag: Default dates Tag: 67122
Cursors and ExecuteNonQuery
All,
Is it possible for ExecuteNonQuery to open a cursor? The
reason I ask is that I'm debugging a client's C# service
which is leaking cursors and eventually Oracle throws a
MAX CURSOR EXCEEDED exception and the service dies a
horrible death. It appears that the method that is the
main culprit calls several stored procedures which do
something like:
INSERT INTO FOO ( ... ) RETURNING FOO_ID;
The FOO_ID is setup in the C# as an output parameter and
additionally, the client never calls the Dispose method on
the Command object.
Additionally, the Command objects are used like so:
cmd = new OleDbCommand("sp1", cnx);
...
cmd.ExecuteNonQuery();
...
cmd = new OleDbCommand("sp2", cnx);
...
cmd.ExecuteNonQuery();
...
Would this technique cause cursor leaks? Thanks...
Matt Tag: Default dates Tag: 67121
Not getting SqlException using DataReader
I'm using a SqlDataReader to call a proc that reads data. My call to
raiseerror has no effect. Below is the code. if i comment out the select
statement, my code behaves as expected.
CREATE PROCEDURE [dbo].[usp_Table1Read]
@id int
AS
--select * from table1 where IId = @Id
if (@@rowcount = 0)
begin
raiserror('an error occured', 10, 1)
return 50000
end
return 0
GO
using System;
using System.Data;
using System.Data.SqlClient;
namespace ReaderTest
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
Class1 c = new Class1();
c.DoWork();
}
public void DoWork()
{
try
{
//connection
SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Persist Security Info=False;Integrated
Security=SSPI;database=MyDataBase;server=(local)";
connection.Open();
connection.InfoMessage += new
SqlInfoMessageEventHandler(this.MySqlInfoHandler);
//command
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "usp_Table1Read";
command.Connection = connection;
//create return parameter
SqlParameter p = new SqlParameter();
p.ParameterName = "@RetVal";
p.Direction = ParameterDirection.ReturnValue;
p.SqlDbType = SqlDbType.Int;
command.Parameters.Add(p);
p = new SqlParameter();
p.ParameterName = "@Id";
p.Direction = ParameterDirection.Input;
p.SqlDbType = SqlDbType.Int;
p.Value = "3";
command.Parameters.Add(p);
//get reader
SqlDataReader reader = command.ExecuteReader();
//look at return parameter (1)
Console.Write("Check 1: ");
Console.WriteLine(command.Parameters["@RetVal"].Value);
while(reader.Read()){}
reader.Close();
connection.Close();
//look at return parameter (2)
Console.Write("Check 2: ");
Console.WriteLine(command.Parameters["@RetVal"].Value);
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
public void MySqlInfoHandler(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine("************************");
Console.WriteLine(e.Message);
Console.WriteLine("************************");
}
}
} Tag: Default dates Tag: 67120
Referencing datarow/datareader values by property name vs index - How big a hit?
I've read several times that there is a performance hit when referencing
column values in ADO.Net by property name vs index number. Could someone
clue me in to how serious a hit this is, and maybe point me towards an
explanation as to the extra steps involved in referencing by name that are
causing the slowdown?
i.e.
variable=datareader(3)
is faster than
variable=datareader("LastName")
I hate giving up readability in my code (my code documentation skills are
bad enough already), and I've also got a paranoia of the order of columns
somehow getting shifted around. (I know when I bind a table to a datagrid,
the column order doesn't always match the order in the table..)
Any comments?
Thanks,
W.G. Rowland Tag: Default dates Tag: 67116