ADO.NET Data Providers Forum

OledbTransaction blocking insert in Oracle

Sat, 21 Nov 2009 06:14:44 Z

Dear All,
I have a order processing module written in .NET . In it, I open an OledbTransaction to the Oracle 10g database and keep the transaction open until all items are added and processed and then once order is complete, I commit the transaction. Eg:

 dim saConn as oledbconnection = New OleDb.OleDbConnection("Provider=MSDAORA.1;Data Source=test;User Id=test;Password=test")
Try
            saConn.Open()
            saTrans = saConn.BeginTransaction(IsolationLevel.Unspecified)
        Catch ex As Exception
            MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical)
            Me.Close()
            Exit Sub
 End Try

....

Then I do the insert for the first time in the table. For eg:

      dim  sql as string = "insert into orders " & _
                          "(sn, item_code, creation_date,customer_name)  " & _
                          "       values " & _
                          "  (95, 'X1342', to_date('21/11/2009','dd/mm/yyyy'), 'Mr. A.N.Y. Body') " 

        res = ExecuteDMLWithTransaction(sql, saConn, saTrans)

Now, if I start another instance of the application, repeat the steps then I am unable to do the SQL insert from the other instance until I either commit or rollback Transaction from the first one. Please help me to resolve this problem as the transaction seems to lock the whole table.

Thanks,
Noorul

Bug ? in .NET 3.5 SP1

Thu, 12 Nov 2009 22:06:58 Z

Hi,
I think I've found a bug in ADO.NET using .NET Framework 3.5. My system is WinXP x64, and I'm running SQL Server 2008 Std. The bug presents itself as a dropped connection from my web application (that uses connection pooling). I can cause the problem to happen by performing an update using a DataAdapter with a DataSet that has one row changed (only one table in the DataSet). The table in my database has several columns, some of which are of type 'datetime'. When I change the value in the dataset's table's row to DateTime.MinValue and then call adapter.Update(dataset), the connection to SQL Server closes. There is a corresponding pair of errors logged in SQL Server:
--------------------------------
Date  12/11/2009 2:26:37 PM
Log  SQL Server (Current - 12/11/2009 4:59:00 PM)
Source  spid51
Message
The client was unable to reuse a session with SPID 51, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
------------------
and:
------------------
Date  12/11/2009 2:26:37 PM
Log  SQL Server (Current - 12/11/2009 4:59:00 PM)
Source  spid51
Message
Error: 18056, Severity: 20, State: 29.
------------------

When I use an alternate method for performing the same update ( opening a new connection, setting the command text as an SQL statement with parameters, adding parameters and setting their values, then calling command.ExecuteNonQuery(), I get a real error message that states the datetime value is out of range.

When using the DataAdapter (the first example above), shouldn't the call just return an error instead of generating SQL Server errors that cause the connection to be dropped?

Thanks

Strange connection behaviour. (memory leak)(need help)

Thu, 05 Nov 2009 08:56:18 Z

I have written a polling component that connects to a sybase database every 5 seconds and then executes a stored procedure.

This polling component is a BizTalk Adapter but that doesn't matter for the problem that I encounter.

To connect to the Sybase Database I use two DLL's that come with sybase, they are :

Sybase.Data.AseClient.dll
sybdrvado115.dll

The problem.

De polling component works fine and there are absolutely no signs of a memory leak. After running for several days memroy consumtion is still low.

Then all of a sudden there is something wrong with the Unix Server and it is no longer possible to create a connection to the Sybase Server hosted on the Unix machine.

The error I get is the following :

Event Type: Warning
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5740
Date:  3-11-2009
Time:  14:45:35
User:  N/A
Computer:
Description:XXXXXXXX
The adapter "SybaseStoredProcedure" raised an error message. Details "Client unable to establish a connection".

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

From that moment on there is a memory leak on each poll. So I went to my Development server and tried to enter an invalid Machine name to get the same error message.

Event Type: Warning
Event Source: BizTalk Server 2006
Event Category: BizTalk Server 2006
Event ID: 5740
Date:  11/5/2009
Time:  9:13:32 AM
User:  N/A
Computer: YYYYYYYY
Description:
The adapter "SybaseStoredProcedure" raised an error message. Details "Client unable to establish a connection".

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

So I have exactly the same error message on my developement machine but this time there is NO memory leak at all.

So the real question is....

Does anybody have a clou why a memory leak can exist in this situation.

I have been thinking about several options

- The sybase components do some caching and once a connection has been made, it will return that same connection every time.

( if the connection is broken later on the sybase components don't pick it up ? )

- The polling component is unable to connect to the Sybase adapter unless it's host process (host process of polling component = BizTalk) is restarted.....

The code that gets executed on every poll is the following :

        public void SubmitBatch()
        {
            int nrOfMessages = 0;
            try
            {
                this.receivingData = true;

                //  used to block the Terminate from BizTalk 
                if (!this.control.Enter())
                {
                    return;
                }

                SyncReceiveSubmitBatch batch = null;

                while (nrOfMessages < properties.ReceiveBatchSize && !control.TerminateCalled)
                {
                    MemoryStream newMsgStream = null;

                    // Now try to recieve data 
                    using (AseConnection conn = currentHelper.BuildConnection(connectionParameters))
                    {
                        conn.Open();
                        if (connectionParameters.TransactionLevel != System.Data.IsolationLevel.Unspecified)
                        {
                            AseTransaction trans = conn.BeginTransaction(connectionParameters.TransactionLevel);
                        }
                        using (AseCommand cmd = currentHelper.BuildCommandObject(connectionParameters, conn))
                        {
                            using (DataTable resultParameters = currentHelper.GenerateParameterDatatable(connectionParameters))
                            {
                                DataSet ds = new DataSet("PRC_" + connectionParameters.ProcedureName);
                                AseDataAdapter da = new AseDataAdapter(cmd);
                                da.Fill(ds, "PRC_ROWS_" + connectionParameters.ProcedureName);
                                currentHelper.ResultParametersToTable(ds, resultParameters, cmd);
                                if (ds.Tables["PRC_ROWS_" + connectionParameters.ProcedureName].Rows.Count > 0)
                                {
                                    // Data was found write the data to the stream...
                                    newMsgStream = new MemoryStream();
                                    ds.WriteXml(newMsgStream);
                                    newMsgStream.Position = 0;
                                }
                                if (connectionParameters.TransactionLevel != System.Data.IsolationLevel.Unspecified)
                                {
                                    conn.Transaction.Commit();
                                }
                                da.Dispose();
                                ds.Dispose();
                            }
                        }
                        if (conn.State != ConnectionState.Closed)
                        {
                            conn.Close();
                        }
                    }
                    if (newMsgStream == null)
                    {
                        break; // We might not have reached the batchsize yet but we don't want to wait for that.
                    }
                    else
                    {
                        newMsgStream.Flush();
                        newMsgStream.Seek(0, SeekOrigin.Begin);
                        IBaseMessage btsMsg = CreateMessage(newMsgStream);
                        if (batch == null)
                        {
                            //Create a new batch when this is the first message
                            batch = new SyncReceiveSubmitBatch(this.transportProxy, this.control, 1);
                        }
                        if (properties.IsTwoWay)
                        {
                            // Put your reponse handler code here, if needed.

                        }
                        else
                        {
                            // batch.SubmitMessage(btsMsg, new StreamAndUserData(btsMsg.BodyPart.Data, newMsgStream));
                            batch.SubmitMessage(btsMsg);
                        }
                        nrOfMessages++;
                    }

                }//end while
                if (nrOfMessages != 0)
                {
                    batch.Done();
                }
            }
            catch (Exception e)
            {
                this.transportProxy.SetErrorInfo(e);
            }
            finally
            {
                this.control.Leave();
            }
            receivingData = false;
        }

Well0549

Memory leak on connection due to faulty host file

Thu, 12 Nov 2009 08:18:37 Z

I have a memory leak problem with a connection.
I use the Sybase ado providers but maybe someone experienced this problem as well.

If I enter a faulty server name in the connection string i get an exception like 'Client unable to establish a connection'

If i enter a correct server name but mes sup my host file Pointing to 1.1.1.1 as the ip adress of my server I get a memory leak.

Anybody got any experience with this ?

Well0549

How export dataset/datatable to MDB

Tue, 16 Dec 2008 07:54:01 Z

Hi Pro's,

kindly help me with the issue: Export dataset/datatable to MDB using C#

Using Paramters with Jet Oledb

Tue, 17 Nov 2009 22:37:59 Z

I am having problems using DateTime parameters in the Jet Oledb I have the following code

I would expect the first row to throw the exception but it doesn't. Surely I should get a date back and not a string???

Any help would be greatly appreciated.

Colin

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Test.accdb;Persist Security Info=True");

OleDbCommand command = new OleDbCommand("SELECT ID, @DateParam FROM Test", connection);
OleDbParameter param = new OleDbParameter("@DateParam", OleDbType.DBTimeStamp);

param.Value =

DateTime.Now;

OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataTable table = new DataTable();

command.Parameters.Add(param);
adapter.Fill(table);

Console.WriteLine(table.Rows[0].Field<string>(1)); // THIS LINE IS FINE
Console.WriteLine(table.Rows[0].Field<DateTime>(1)); // THROWS InvalidCastException

Problem reading XML Schema?

Mon, 12 Feb 2007 12:25:10 Z

Hi,

I have the XSD below and the corresponding XML. If I try to read it usind ReadXml with XmlReadMode.ReadSchema it returns no data. However with XmlReadMode.InferSchema it works.

What is missing here?

Thanks

-------------
CODE

DataSet ds = new DataSet();
            ds.ReadXml(@"C:\Documents and Settings\db2admin\My Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1\XMLFile1.xml",
          XmlReadMode.ReadSchema);

XSD

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="XMLSchema1" targetNamespace="http://tempuri.org/XMLSchema1.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/XMLSchema1.xsd" xmlns:mstns="http://tempuri.org/XMLSchema1.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Projects">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Project" minOccurs="0" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="id_project" type="xs:short" minOccurs="0" maxOccurs="unbounded" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
</xs:element>
</xs:schema>

XML

<?xml version="1.0" encoding="utf-8" ?>
<Projects xmlns="http://tempuri.org/XMLSchema1.xsd">
<Project>
    <id_project> 1 </id_project>
</Project>
<Project>
    <id_project> 2 </id_project>
</Project>
</Projects>

IntelliTrace support in 3rd party providers

Wed, 25 Nov 2009 09:31:49 Z

Hi *,

I really like the new IntelliTrace support in i.e. SqlClient, you can see when and what command was executed pretty easily. And I would like to also add support for this into provider for Firebird. Is there some description what a provider writer needs to implement (and call), to make this work?

Jiri {x2} Cincura

ORA-01453: SET TRANSACTION must be first statement of transaction

Thu, 13 Sep 2007 07:10:50 Z

Hi All,

I am getting an error ORA-01453: SET TRANSACTION must be first statement of transaction. rarely

My code is :

Try

' get a connection object with the connection string set

conn = Configurations.get_DB_Connection_Write(txtKey)

' open the connection

conn.Open()

' begin transaction

ts = conn.BeginTransaction(IsolationLevel.ReadCommitted)

' create a command object

cmd = New OracleCommand(SQLText, conn, ts)

' Add the parameter objects to the command

If (Not params Is Nothing) Then

For i = 0 To UBound(params)

cmd.Parameters.Add(params(i))

Next (i)

End If

' Execute the command and return the nbr of rows affected

NbrRowsAffected = cmd.ExecuteNonQuery()

' commit transaction

ts.Commit()

' return the nbr of rows affected

Return NbrRowsAffected

Catch ex As Exception

' Rollback the transaction

If (Not ts Is Nothing) Then ts.Rollback()

' throw all exceptions

' throwing exception with class name, method name and exception message

Throw ex

Finally

' close the connection

If (Not conn Is Nothing) Then conn.Close()

End Try

I dont why is this getting .....and also this error is not consistent...... my db is oracle 9i...

advd thanks for your help

thanks

Antoops

Retrieving database properties from a Jet database

Tue, 24 Nov 2009 16:38:25 Z

Is it possible to retrieve user defined Database properties from a Jet database using ADO.Net?

i.e. using DAO from VBA in an Access Database I can retrieve database properties using the following code;

z = CurrentDB.Properties("MyProperty")

Is there anyway to get at these properties using ADO.NET?

Thanks.

SQL SP to DataGridView

Tue, 24 Nov 2009 17:19:39 Z

The code below takes an SQL query and loads the results into a datagridview. How do I modify this such that I can load the result set returned from a stored procedure?

try
            {
                System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(@myConnection);
                SqlCon.Open();
                using (SqlCommand cmd = new SqlCommand(@myYCommand, SqlCon))
                {
                    using (SqlDataAdapter adap = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        adap.Fill(dt);
                        MyDataGrid.DataSource = dt;
                    }
                }

                SqlCommand cmd1 = new SqlCommand(@myZCommand, SqlCon);
                myZResult = (int)cmd1.ExecuteScalar();

                myResultBox.Text = myState + " returned " + myZResult.ToString() + " rows.";



                SqlCon.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

Wed, 20 Dec 2006 10:56:43 Z

My application is working with a DataBase server which is connected through VPN.

Some time i get an exception like...

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
This may have occurred because all pooled connections were in use and max pool size was reached. Inner Exception:>>"

Can you please let me know if you have come across any such scenario in your application & any solution for it?

Unable to open ODBC connection from Windows Service in .Net

Mon, 16 Nov 2009 14:51:00 Z

Hi All,

I worte a simple windows application to connect to a third party database (pervasive / timberline) through ODBC. It is working fine but when I moved the same code to a windows service, it is unable to open the connection. I get the following error.

System.Data.Odbc.OdbcException
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
   at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
   at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()

I installed it as a network service. The Data files for the database is kept as a shared location on the same machine and the folder has the permission for access by Network Services Account.

I checked the eventlog and found a couple of entries whenever there is a crash. I have listed below

1. Faulting application MyService.exe, version 1.0.0.0, faulting module tsSimba.dll, version 9.6.25.10, fault address 0x0001f74c.

2. The system has called a custom component and that component has failed and generated an exception. This indicates a problem with the custom component. Notify the developer of this component that a failure has occurred and provide them with the information below.
Component Prog ID: 0[ODBC][Env 33012a0]
Method Name: IDispenserDriver::CreateResource
Process Name: MyService.exe
Exception: C0000005
Address: 0x628AF74C
Call Stack:
tssimba!x_dll_version + 0x1D31F
tssimba!x_dll_version + 0x1DFF3
tssimba!x_dll_version + 0x514B
tssimba!rcCnvConvert + 0x44D

I have tried installing the service as Local System, Local Service, User. But I kept getting the same error.

Please help.

Thanks in Advance.

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

Fri, 04 Nov 2005 22:33:26 Z

I just installed Visual Studio 2005 today and I'm already getting an error. This code worked fine on 2003. I am running Windows XP 64 so I definitely have the latest MDAC. Anyone know why I would get this error?

Dim strConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & GetWorkingPath() & "db.mdb;Persist Security Info=False;"

conn = New OleDbConnection(strConnect)

conn.Open()

insert data to access database with QueriesTableAdapter

Tue, 24 Nov 2009 20:16:25 Z

i have code dataset1.xsd

INSERT INTO TBLURUNADI
(URUNADI, TARIH)
VALUES (?, ?)

in c# code

DataSet1TableAdapters.QueriesTableAdapter q = new DataSet1TableAdapters.QueriesTableAdapter();

q.InsertQuery("test1", "test2");

if i show on datagridview i can see my data(q.InsertQuery("test1", "test2");) but not recording to the table. i cant see on table how to insert to table. this code working for sql server database but i am working with access database.

Reading a shapefile (DBF), name too long.

Thu, 19 Nov 2009 20:43:58 Z

I am connecting to a GIS shapefile DBF directly with ADO.NET using the following connection string, "Driver={Microsoft dBASE Driver (*.dbf)};driverid=277;dbq=M:\GISdata" I can create a dataset from a dbase datatable without problem as long is the shapefile name is shorter than 13 characters in length. Otherwise, when I attempt to open a database named (for example) filenametoolong.shp (basename is 15 characters in length), I receive the following error message "You might not have permission to perform this operation, or the object filenametoolong might no longer exist in the database." If I have created a dataset from the the filenametoolong.dbf, I cannot use that dataset for the creation of datagrids, etc. If I shorten the name to (for example) filenametoo.shp (base name is 11 characters in length) the file opens just file and I can see all the data. Is there a workaround other than shortening the names of all of our many, many shapefiles?

Thanks!
Craig

Salt Lake

Using AddToBatch(), ExecuteBatch() and ClearBatch() methods from the DbDataAdpater class

Mon, 06 Jul 2009 20:12:40 Z

Hi guys!
Do you know something about using the AddToBatch(), ExecuteBatch() and ClearBatch() methods from the DbDataAdpater class?
These are protected virtual methods and I can't acess them just instantiating this object.

Do you how can I achieve this?

Thanks for help,
Anderson

INSERT INTO Error - Newbie Needs Help

Mon, 23 Nov 2009 19:01:09 Z

Hi all, I am new to both C# and Databases, and am trying to use a MS ACCESS Database to store plant event data. I have my program filling a dataset from the database, and can navigate through the database, but if I add a record, I get an error that states there is a Syntax Error in the INSERT INTO statement. I am almost sure that the parameter statements in my BuildCommands() method have errors in them, but am having trouble figuring out what they are. I am including the DB data fields and types along with the code here for any advice I can get. I am using the Jet.4.0 data provider, and have st the build properties to x86 since I am using Vista 64bit OS.

Thanks in advance for all who help.

Ed.

Data Base Info:

Table - EventData

Fields:

ID - AutoNumber - Long Integer
EventType - Text - 255 field size
UnitNo - Integer
Start - Date/Time
End - Date/Time
Dearte - Single
Trip - Yes/No - Set to True/False
Comments - Memo

The Program is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Data;
using System.Data.OleDb;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

namespace PlantTrackerWPF
{
    /// <summary>
    /// Interaction logic for EventWindow.xaml
    /// </summary>
    public partial class EventWindow : Window
    {
        // +++ ADO.NET Objects +++
        //
        // MS Access connection String.
        public static string connectionString = @"Provider=Microsoft.JET.OLEDB.4.0; data source = C:\Plant Tracker\PlantTracking.mdb";

        private OleDbDataAdapter dataAdapter;
        private DataSet dataSet;
        private DataTable dataTable;
        private OleDbConnection conn;

        // *** END ADO.NET Objects ***
    
        // +++ Database Objects +++
        private static string id;
        private static string eventType;
        private static int unitNo;
        private static DateTime startTime;
        private static DateTime endTime;
        private static decimal derate;
        private static Boolean trip;
        private static string comments;
        private int indexNumber = 0;

        // *** END Database Objects



        public EventWindow()
        {
            InitializeComponent();



            eventTypeCmboBx.Items.Add("SO");
            eventTypeCmboBx.Items.Add("FO");
            eventTypeCmboBx.Items.Add("DR");


            // Default select command on the EventData Table.
            string commandString = "select * from EventData";

            // The link between sql command and the database connection
            dataAdapter = new OleDbDataAdapter(commandString, connectionString);

            // define insert, update and delete sql commands to use.
            BuildCommands();

            // Declare and fill the in-memory dataset from the database.
            dataSet = new DataSet();
            dataSet.CaseSensitive = true;
            dataAdapter.Fill(dataSet, "EventData");

            // Fill form with first record at loading.
            FillForm(indexNumber);

        } // *** END EventWindow ***

        private void FillForm(int rowID)
        {
            dataTable = dataSet.Tables[0];
            DataRow dataRow = dataTable.Rows[rowID];

            eventIDTxtBx.Text = dataRow["ID"].ToString().Trim();
            eventTypeCmboBx.Text = dataRow["EventType"].ToString().Trim();
            unitNoTxtBx.Text = dataRow["UnitNo"].ToString().Trim();
            startTxtBx.Text = dataRow["Start"].ToString().Trim();
            endTxtBx.Text = dataRow["End"].ToString().Trim();
            derateTxtBx.Text = dataRow["Derate"].ToString().Trim();
            commentsTxtBox.Text = dataRow["Comments"].ToString().Trim();
            tripChkBx.IsChecked = (Boolean)dataRow["Trip"];
            
            
        } // *** END FillForm ***

        private void BuildCommands()
        {
            //Use the select comand's connection again
            OleDbConnection connection = (OleDbConnection)dataAdapter.SelectCommand.Connection;

            //Declare a reusable insert command with parameters
            dataAdapter.InsertCommand = connection.CreateCommand();
            dataAdapter.InsertCommand.CommandText =
                "insert into EventData " +
                "(ID, EventType, UnitNo, Start, End, Derate, Trip, Comments) " +
                "values " +
                "(?,?,?,?,?,?,?,?)";
            dataAdapter.InsertCommand.Parameters.Add("ID", OleDbType.BigInt, 0, "ID");
            dataAdapter.InsertCommand.Parameters.Add("EventType", OleDbType.Char, 0, "EventType");
            dataAdapter.InsertCommand.Parameters.Add("UnitNo", OleDbType.Integer, 0, "UnitNo");
            dataAdapter.InsertCommand.Parameters.Add("Start", OleDbType.Date, 0, "Start");
            dataAdapter.InsertCommand.Parameters.Add("End", OleDbType.Date, 0, "End");
            dataAdapter.InsertCommand.Parameters.Add("Derate", OleDbType.Decimal, 0, "Derate");
            dataAdapter.InsertCommand.Parameters.Add("Trip", OleDbType.Boolean, 0, "Trip");
            dataAdapter.InsertCommand.Parameters.Add("Comments", OleDbType.Char, 0, "Comments");

            //Declare a reusable update command with parameters.
            dataAdapter.UpdateCommand = connection.CreateCommand();
            dataAdapter.UpdateCommand.CommandText = 
                "update EventData " +
                "set EventType = ?, UnitNo = ?, Start = ?, End = ?, Derate = ?, Trip = ?, Comments = ? " +
                "where ID = ?";
            dataAdapter.UpdateCommand.Parameters.Add("EventType", OleDbType.Char, 0, "EventType");
            dataAdapter.UpdateCommand.Parameters.Add("UnitNo", OleDbType.Integer, 0, "UnitNo");
            dataAdapter.UpdateCommand.Parameters.Add("Start", OleDbType.Date, 0, "Start");
            dataAdapter.UpdateCommand.Parameters.Add("End", OleDbType.Date, 0, "End");
            dataAdapter.UpdateCommand.Parameters.Add("Derate", OleDbType.Decimal, 0, "Derate");
            dataAdapter.UpdateCommand.Parameters.Add("Trip", OleDbType.Boolean, 0, "Trip");
            dataAdapter.UpdateCommand.Parameters.Add("Comments", OleDbType.Char, 0, "Comments");
            dataAdapter.UpdateCommand.Parameters.Add("ID", OleDbType.BigInt, 0, "ID");

            // Declare a reusable delete command with parameters
            dataAdapter.DeleteCommand = connection.CreateCommand();
            dataAdapter.DeleteCommand.CommandText = "delete from EventData where ID = ?";
            dataAdapter.DeleteCommand.Parameters.Add("ID", OleDbType.BigInt, 0, "ID");

        }  // *** END BuildComands()

        private void exitMenuItem_Click(object sender, RoutedEventArgs e)
        {
            this.Close();
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            //TO DO!!
        }

        private void firstRecord_Click(object sender, RoutedEventArgs e)
        {
            FillForm(0);
        }

        private void Image_ImageFailed(object sender, ExceptionRoutedEventArgs e)
        {

        }

        private void previousRecord_Click(object sender, RoutedEventArgs e)
        {
            if (indexNumber >= 0)
            {
                indexNumber -= 1;
                FillForm(indexNumber);
            }
            else
            {
                MessageBox.Show("At the beginning of the database.");
            }
        }

        private void nextRecord_Click(object sender, RoutedEventArgs e)
        {
            if (indexNumber < dataTable.Rows.Count)
            {
                indexNumber += 1;
                FillForm(indexNumber);
            }
            else
            {
                MessageBox.Show("At the end of the database.");
            }
        }

        private void lastRecord_Click(object sender, RoutedEventArgs e)
        {
            FillForm(dataTable.Rows.Count-1);
        }

        private void newRecord_Click(object sender, RoutedEventArgs e)
        {
            //Get number of records and last ID number
            int numberOfRows = dataTable.Rows.Count;
            DataRow dataRow = dataTable.Rows[numberOfRows-1];
            int newIdNo = (int)dataRow["ID"] + 1;

            eventIDTxtBx.Text = newIdNo.ToString();
            eventTypeCmboBx.Text = "";
            unitNoTxtBx.Text = "";
            startTxtBx.Text = "";
            endTxtBx.Text = "";
            derateTxtBx.Text = "";
            tripChkBx.IsChecked = false;
            commentsTxtBox.Text = "";

        }

        private void newRecordInsert()
        {
            
            

            // Create a new row and populate it.
            DataRow newRow = dataTable.NewRow();

            newRow["ID"] = Convert.ToInt64(eventIDTxtBx.Text);
            newRow["EventType"] = eventTypeCmboBx.Text;
            newRow["UnitNo"] = Convert.ToInt32(unitNoTxtBx.Text);
            newRow["Start"] = Convert.ToDateTime(startTxtBx.Text);
            newRow["End"] = Convert.ToDateTime(endTxtBx.Text);
            newRow["Derate"] = Convert.ToDecimal(derateTxtBx.Text);
            newRow["Trip"] = tripChkBx.IsChecked;
            newRow["Comments"] = commentsTxtBox.Text;

            //Update the database
            try
            {
                dataSet.Tables["EventData"].Rows.Add(newRow);
                dataAdapter.Update(dataSet, "EventData");
                dataSet.AcceptChanges();

                // inform the user.
                MessageBox.Show("Update Successful");
            }
            catch(OleDbException ex)
            {
                dataSet.RejectChanges();
                MessageBox.Show(ex.Message);
                MessageBox.Show(ex.ErrorCode.ToString());

            }


        }// *** END nerRecordInsert ***

        private void saveRecord_Click(object sender, RoutedEventArgs e)
        {
            newRecordInsert();
        }  // *** END saveRecord_Click ***


    }
}

get the generated sql using dataadapter

Fri, 20 Nov 2009 09:05:43 Z

hi, i'm working with ado.net and dbdataadapeter /dbcommandbuilder.
there's a way to see/log/trace the generated sql (maybe with also the sql arguments values) ?

i cannot access the databas so i cannot use native db monitoring

Best new book ?

Wed, 18 Nov 2009 00:56:58 Z

Years ago I bought David Sceppa's book on ADO.NET. I really enjoyed his style and the content in general. Now after several years of being pushed into a bit of java and websphere, I'm finally back to .NET.

What's the latest really good book (along the same style/content as David's) on ADO.NET ? I looked around all the usual sites at reviews - I even bought one. I did notice I didn't see an 'updated' Sceppa book.

Not interested too much, at least at this moment in time, in Entity Framework, LINQ etc. I tried "Visual C# 2005 Recipes". While I didn't waste my money at all (fun book actually, the way it's laid out and written), it's just not exactly what I'm looking for.

Suggestions ?

Jim In FL

how import data from a datatable to a database table

Tue, 17 Nov 2009 14:47:39 Z

hi, i'm using 2 firebird database with the firebird-specific implementation of System.Data objects.
the 2 db are on 2 different machine

i have the same table on both the db (the difference is only in the table name and that in a case the primary-key colum is int and in the other bigint).

i need to take all the record from a table and add them into the other, except for the id (primary-key column), the id of souce-column is ignored and when the row is added to the other table a new id is generated.

To do that i'm puttting the data of the source table in a datatable (with a dataadapter), then i will serialize/send over the net/deserialize it.

my problem is that i don't know how to add the record of the datatable to the destination table (the id is generated with a trigger before every row insertion so is not a problem).

i know that i can simply iterate every datarow and explicitly set the data but maybe there's an easier way to do that (for example samething similar to dataadapter.update(datatable) ).

NOTE: maybe a solution is to use dataTableOfTargetDataBase.Merge(tableWithRowsToAdd) but i think that to do that when i get the dataTableOfTargetDataBase i'm loading all the already existing rows that will kill myApplication (the destination table is very big), i'm right or there's samething as lazy loading of rows?

The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception.

Tue, 07 Jun 2005 06:58:10 Z

Test it on a virtual PC running SQL 2000 on a Win 2003 PKLATFORM.
Throws the above error on win 2000 machine with a networked SQL 2000 server
Is this an AD thing???

Trying to discover correct syntax for Connection String

Sun, 15 Nov 2009 23:30:38 Z

There's a 10 year old VB6 app that I'm trying to get working on my local network. The problem is that it won't connect to my local copy of SQL Server even though it has no problem doing so at a client's site and on the home network of a colleague. To see if there was some sort of a strange problem connecting from my computer, I installed a copy of SQL Server Management Studio Express. Into this utility app I entered the following data:

Server name:    Server\SQLExpress
Authentication:   SQL Server Authentication
Login:               Abc_Admin
Password:         abc123

Please note that I've altered the Login & Password in this posting for security reasons but I assure they're correct in practice.

Then when I press Connect ... it connects properly to SQL Server as it should! So this told me that there was no problem, in principle, with connecting to SQL Server from my test computer.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

So then took the key VB6 code in question and created a little procedure to try it out. Here's the code:

Private Sub TestConnection()

Dim conTest As New ADODB.Connection
Dim msg As String

On Error GoTo ConnectError

' --------------- Connection using SQLOLEDB ----------------
With conTest
    .Provider = "SQLOLEDB"
    .ConnectionString = "User ID=" & "Abc_Admin" & _
      ";Password=" & "abc123" & _
      ";Data Source=" & "Server\SQLExpress" & _
      ";Initial Catalog=" & "AbcAppDatabase"
    .Open
End With

MsgBox "Connection Succeeded!", vbOKOnly + vbInformation, "Connection Tester"
conTest.Close

Exit Sub

ConnectError:

msg = "Status: ERROR" & vbCrLf & vbCrLf
msg = msg & "Error code: " & Err.Number & vbCrLf
msg = msg & "Error description: " & Err.Description

MsgBox msg, vbOKOnly + vbCritical, "Connection Tester"

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

The code always failed to connect, offering an obscure error code: -2147467259   with this description: [DBMSSOCN] General network error

I have no idea what this means nor how it helps resolve the problem.

Any ideas?

Robert W.

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

Thu, 13 Apr 2006 17:06:51 Z

Any help would be very very much appreciated as I am about 15 hours into this :(

Background is a development system with ASP.NET 2.0 and SQL express 2005. The server is SQL 2005 standard edition. Any ASPX pages that connect to a database results in errors.

I have 2 identical servers with Windows server 2003, one has SQL Express and the other has SQL Server 2005 standard. that is the only difference between these systems. The scripts that work seamlessly when uploaded to the SQL Express server dont work on the SQL Server 2005

My connection string is

two interesting tidbits

1.) No matter what the initial catalog is pointing to, I get the same error. EVEN if the database doesnt exist

Cannot open database "C:\INETPUB\WWWROOT\test\app_data\aspnwet.MDF" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

2.) second interesting thing is this, if I change the datasource to "MSSQLSERVER" which is the instance name of SQL 2005. the error changes to

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have reinstalled SQL server 2005

I have verified that under SQL Server 2005 Surface Area Configuration that remote connections has Both TCP and Named Pipes enabled

I have verified that under the Network Configuration that the protocols for TCP/IP and Named pipes are enabled.

I have tried atleast 20 different variations of Connectionstrings

I have ran aspnet_regsql against the database

I have verified that the SQL Server Browser is started

I have verified that the TCP/IP is set in Network Configuration to default port 1433

I have gave all authentication rights to Network Service and ASPNET accounts for testing against both the MASTER and ASPNET databases using Management Studio and attaching the Database

I have created a custom SQL account with access to the database in question and added the username password syntax to the connectionstring and get the same login failed message.

If anyone can give any insight that would be MUCH appreciated!! thanks in advance.

Why would recordset.source return blank on certain setups but not on others?

Fri, 20 Nov 2009 15:18:19 Z

In essence the problem boils down to this, on one environment in live using COM+, this returns blank:

Dim strSQL As String = mrsIntranetChanges.Source

Whereas running the same webpage on a local install returns the SQL that was initially being set.

The actual recordset has been confirmed to have contents in it so the source has obviously worked:
mrsIntranetChanges.RecordCount
returns records in both environments.

The initial SQL used to populate it is this:

mrsIntranetChanges = objPageAccess.GetIntranetRecordSetFromSQL(

"select *,FieldDescription from IntranetChanges,Fields where FieldSQLName =* IntranetChangesFieldName and IntranetChangesPageName='NewEmployeeWizard.aspx' and IntranetChangesNewRecordID = " & (mintRecordID).ToString)

One thing that I thought might have been relevant is that the DLL that actually gets the information from the database had the 'persist security info' property not set in it's connection string. However changing this didn't seem to have any effect.

This is happening on Server 2003 with IIS6 and Server 2008 with IIS7 with IE8 as the browser. It doesn't happen on a local install of 2003 server with the business logic installed locally rather than as a COM+ app (using the same browser) so this would initially point to it being something to do with the connection string?

However it may be some other setting on those servers as they are different machines.

The main reason for the question is to see if anyone's aware of known causes of this .source returning blank as I can't seem to find any out there.

compact access 2007 database

Thu, 25 Jan 2007 12:26:22 Z

Does anyone knows how to compact an access 2007 database with .net?

The previous code with JRO.JetEngine is not working for 2007 format. We used that code for previouse db but not working for the latest one.

Advance Database application sample

Sun, 22 Nov 2009 11:15:55 Z

Can anyone provide me an advance database application sample that includes generic data access using ado.net and creating a framework for such application.

I actually wanna learn how to program in c#.net for multiple database platform without changing a code and

wanna learn how to create so called framework for building windows form for database applications that manages frequently used sql statements like inserting,updating and accessing multiple recordsets, validating controls, data and everything.

I tried to search in google and everywhere but i couldn't find any suitable samples to download.

Thanks in advance

FireBird Data Provider

Wed, 18 Nov 2009 01:38:25 Z

Challenge: I have VS2010 Beta2 installed. I want to connect to a datasource which is Firebird.

Has anyone had success doing this.
I need some more detailed intsructions on how to do it (the ddex readme files are a bit vague)

from what I can tell the process involves
Installing Firbird server
gacutil - to import dll's into the gac
registry mods
editing of machine.config

I have got so far as to actually see firbird as an option in my "add new data source" Dialog in VS2010 Hooray!!
I even get a success message on the "Test"
but it does not connect... bombs out or I get a error
"Failed to find or load the registered .Net Framework Data Provider"

GetSchema is too slow!

Fri, 20 Nov 2009 22:07:49 Z

myDataTable = myConn.GetSchema("Columns");

In my program I need to populate a list of tables in a database that belong to my application, and filter out the rest. GetSchema("Columns") returns both table and column names, so I thought I could use that data to do my filtering. However, this command takes forever to return results even on a database with a small number of tables (this is probably due to the volume of data returned - 18 columns in the datatable).

All I am interested in are the "TABLE_NAME" and "COLUMN_NAME" columns returned by GetSchema, but I cannot figure out how to turn off the other columns. Can I use the restrictions to turn off these columns and speed up the response time?

I can always use a datareader and query the info myself, but GetSchema seems to be quite useless if I have to go that route.

how to change ms access database password in c#?

Thu, 01 Feb 2007 01:55:27 Z

hi,

how to change the ms access database password in c#? i search through the web and found one in VBA but not in C#. http://support.microsoft.com/kb/170961

Please give some hints. Thank you.

regrads,
chunket

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

Tue, 21 Feb 2006 23:20:41 Z

It is my understanding that when using a TableAdapter and selecting the proper options, Insert, Delete and Update statements are generated. The Update statement should work just using the following:
myTableAdapter.Update(changes);
Where changes is the DataSet containing the modified data.

This logic works great for one of the tables in the DB but not for another.

Learning this new stuff is Confusing at best - Frustrating at worst.

Help appreciated,

Michael

SessionState 2.0 Problem connecting to SQL Server

Thu, 23 Mar 2006 13:55:36 Z

We are attempting to upgrade from 1.1 to 2.0. We are running SQL Server 2003, and it has SessionState 1.1 installed successfully. We installed 2.0 on the same server, but under a different database name. The userid and password that is used to connect to 1.1 was given dbowner permissions on the new database. When we attempt to use the application, we get the following error: Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.

I know it's installed. I just don't know how to determine why .net doesn't think it's installed. What error detecting can I do?

This is the connection string we're using:
<sessionState mode="SQLServer" allowCustomSqlDatabase="true" sqlConnectionString="data source=SQL1;database=aspnetdb;user id=uuuuuu;password=pppppp" cookieless="false" timeout="120"/>

This exact same connection string (without the custom database stuff) works for 1.1. I haven't found anything saying there's a problem using SQL Server 2003, or you can't run both at once. Could someone please give me some ideas on what I can do to debug the session State and find out where the problem exists?

Help calling native code after calling OleDbConnection.Open on excel 2007 file

Tue, 17 Nov 2009 21:27:09 Z

I think this is the right place to put this.

Whenever I call a OleDbConnection.Open on an excel 2007 file, any following calls to a native function that returns a string will crash the program. This is very specific to excel 2007 files and native functions that return a string. Also, if I call that function before Open and then again after, it will work fine.

I think this is best explained with code, and please tell me if I am doing anything wrong.

2 parts:
Create an empty C++ project (testnative).
Change the project so that it creates a dll.
Create 1 file with the following contents.

__declspec(dllexport) char *test()
{
	return "asdf";
}

2nd part:
Create a C# executable project:
Use the following code for Program.cs:

public static class Test
    {
        [DllImport("testnative.dll", CallingConvention = CallingConvention.Cdecl)]
        public extern static string test();
    }

    static class Program
    {
        [STAThread]
        static void Main()
        {
            string str;

            // If you uncomment these lines, everything will work fine
            //str = Test.test();
            //MessageBox.Show(str);

            OleDbConnection connection = new OleDbConnection(
                "Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source=test.xlsx;" +
                "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");
            connection.Open();
            connection.Close();

            str = Test.test();
            MessageBox.Show(str);
        }
    }

Obviously you need an excel 2007 file called test.xlsx in there. I have tried with a few different xlsx files with no changes.

The call to Test.test() will not return, and the program ends. No debug information, it just stops. Also, I have tried this with both .NET 2.0 and 3.5.

I am running Windows 7 x64 but forcing the compilation to 32 bit.

Any information would be greatly appreciated.

Thanks,
Matt Spinder

Namespace or type specified in the Imports 'MySql.Data.MySqlClient' doesn't contain any public member or cannot be found.

Mon, 14 Nov 2005 15:50:26 Z

Dear All,

I'm trying to connect to a MySQL server via ASP.NET 2.0 using the Visual Web Developer 2005 Express Edition. I've downloaded and installed the MySQL Connector/Net.

I'm using the following code:

<%@ Page Language="VB" debug="true"%>

<%@ Import Namespace = "System.Data" %>

<%@ Import Namespace = "MySql.Data.MySqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

Dim myConnection As MySqlConnection

Dim myDataAdapter As MySqlDataAdapter

Dim myDataSet As DataSet

Dim strSQL As String

Dim iRecordCount As Integer

myConnection = New MySqlConnection("server=localhost; user id=15secs; password=Test12; database=mydatabase; pooling=false;")

strSQL = "SELECT * FROM mytable;"

myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)

myDataSet = New DataSet()

myDataAdapter.Fill(myDataSet, "mytable")

MySQLDataGrid.DataSource = myDataSet

MySQLDataGrid.DataBind()

End Sub

</script>

<title>Simple MySQL Database Query</title>

</head>

<body>

<div>

<asp:DataGrid id="MySQLDataGrid" runat="server" />

</div>

</form>

</body>

</html>

When I try to view the result with CTRL+F5 there is the following error:

Warning 2 Namespace or type specified in the Imports 'MySql.Data.MySqlClient' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases. C:\Documents and Settings\Merlijn\My Documents\Visual Studio 2005\WebSites\WebSite2\MySQL.aspx 8 1 C:\...\WebSite2\

I did some research and found out the MySQL.Data.dll is somewere necesarry to build the page. I've found this dll file but I don't have any idee were I should copy this file to to make it work with Visual Web Developer 2005 Express Edition.

Can somebody help me?

Kind regards,

JB

How to dynamically change connection string in generated dataset class?

Mon, 17 Oct 2005 11:08:36 Z

I have a project with database classes which are generated from database objects. That is I add a dataset to the project and drag and drop a database object onto it. The problem with this is that I have to give a connectionstring which is stored in the code. This is ok while developing the application. But runtime I want to use another connectionstring, both for security reasons and because each user of the application will correspond to a database user (ms sql) and shall have a corresponding connectionstring. Is there an easy way to do this without having to restore to plain all datasets and do everything myself?

Using System.Data.SqlClient to retrieve SQL Servers available

Fri, 20 Nov 2009 09:56:26 Z

Hi,

i want to build a wizard to let a user connect to a SQL server database.
There for I want to show the user a list with SQL Servers found like the Add Connection in Visual Studio.

How can I retrieve information about SQL Servers found in my network?

Thanks.

    Dim Dbf As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient") 
    Dim Con As DbConnection = Dbf.CreateConnection

COMPlusExceptionCode 0xE0434F4E thrown executing stored procedure.

Mon, 16 Jun 2008 18:31:48 Z

I've got some code that intermittantly is throwing an error message stating that an Object reference is not set to an instance of an Object. The inner exception message shows that it contains _COMPlusExceptionCode -532459699 ( OxE0434F4E )

Even though I've been very liberal in my use of try { } / catch { } blocks surrounding all of my calls to ADO.net table adapters, the application reports that it is "An Unhandled Exception."

* * * * *

My code goes out to all 50 of our stores and retrieves each store's sales transaction log files as a text file. The code then issues a BCP command against each of the 50 files to import all of this data into a single SQL Server table. Once the BCP is completed, the program then uses a query table adapter to execute a stored procedure (which does not return any parameters.) This stored procedure simply sets a "DAYID" field in all records. (There are approximately 1.3 million records.)

During a typical run where this might fail, the application seems to hang for about 2 minutes (my timeout value in the connection string is set to 120 seconds) and then throws the error message. If I wait for 5-10 minutes, I can simply restart the application (without making any other changes to anything) and it usually completes successfully.

The following code is a snippet from Main( ) in my code:

/****************************************/

/* run the routine to BCP the files */

/* into SQL Server TLOG table */

/****************************************/

try

{

notice.AddLine( "Starting BCP of data into TLog." );

if ( myStore == 0 )

{

tlogBCP.runBCP( tgtDate, notice ); /* BCP the raw files into SQL */

}

else

{

tlogBCP.runBCP( tgtDate, notice, myStore );

}

} /* end try */

catch ( Exception e )

{

notice.AddLine( "Error: Unhandled exception originated from the runBCP block!\n" +

e.Message.ToString( ) + "\n" + e.InnerException.Message.ToString( ) + "\n" );

}

/**********************************/

/* update TLOG table data to set */

/* all dayid values to tgtDate */

/**********************************/

try

{

Console.WriteLine( "Running UPDATE_DAYID stored procedure." );

tlogBCP.UpdateDAYID( tgtDate, notice ); /* sets all DAYID values in TLOG */

Console.WriteLine( "Running SetTLogCount stored procedure." );

tlogBCP.SetTLogCount( tgtDate, notice ); /* routine sets TLOG_records field*/

}

catch ( Exception e )

{

notice.AddLine( "Error: Unhandled exception originated from the UpdateDAYID or SetTLogCount code!\n" +

e.Message.ToString( ) + "\n" + e.InnerException.Message.ToString( ) + "\n" );

}

/****************************************/

/* Now insert data from TLOG table into */

/* the TLOG_ARCHIVE table */

/****************************************/

try

{

AsynchSQL.InsertToArchive( tgtDate, notice, myStatus ); /* insert data into TLOG_ARCHIVE */

}

catch ( Exception e )

{

notice.AddLine( "Error: Unhandled exception originated from the InsertToArchive block!\n" +

e.Message.ToString( ) + "\n" + e.InnerException.Message.ToString( ) + "\n" );

}

/********************************************/

/* Next step clears and inserts data from */

/* TLOG into the day_sales_load table. */

/********************************************/

try

{

AsynchSQL.DaySalesLoad( tgtDate, notice, myStatus ); /* insert into day_sales_load */

}

catch ( Exception e )

{

notice.AddLine( "Error: Unhandled exception originated from the DaySaleLoad block!\n" +

e.Message.ToString( ) + "\n" + e.InnerException.Message.ToString( ) + "\n" );

}

/********************************************/

/* Next step clears and inserts data from */

/* TLOG into the FSM_SALIENT_TLOG table. */

/********************************************/

AsynchSQL.Salient_Load( tgtDate, notice, myStatus ); /* insert int fsm_salient_tlog */

AsynchSQL.RunSQLPlus( tgtDate, notice, myStatus ); /* copies data into Oracle */

} /* end outter-most try */

catch ( Exception e )

{

notice.AddLine( "ERROR: Caught an unhandled exception in Main( )." );

notice.AddLine( "\nException: " + e.Message + "\n" + "Inner exception: " + e.InnerException.Message );

notice.SendMsg( );

}

An unhandled exception ('System.Data.SqlClient.SqlException') in a windows service

Fri, 18 Sep 2009 18:18:13 Z

I have a windows service which is multi threaded using a ThreadPool.QueueUserWorkItem , that does some data capture from several other databases. The service for the production environment runs in a 2003 server which has multiple processors. Every now and then almost once every week (not consistent), I get an error. This error does not get trapped in any of my error handling code. My development box has XP, I have run this service on it for several days but never have encountered this error.

A maximum of 4 threads are spawned during each run. All threads attempt to update/delete/insert two tables, but never the same record.

The service crashes and the only entry is in the Application Event Viewer.

Event Type: Error
Event Source: .NET Runtime 2.0 Error Reporting
Event Category: None
Event ID: 5000
Date:  9/18/2009
Time:  3:22:38 AM
User:  N/A
Computer: DP01ABRE
Description:
EventType clr20r3, P1 salesincentivedatacapture.exe, P2 1.0.0.0, P3 4a9beed3, P4 system.data, P5 2.0.0.0, P6 4889deaf, P7 2490, P8 2c, P9 system.data.sqlclient.sql, P10 NIL.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 63 00 6c 00 72 00 32 00   c.l.r.2.
0008: 30 00 72 00 33 00 2c 00   0.r.3.,.
0010: 20 00 73 00 61 00 6c 00    .s.a.l.
0018: 65 00 73 00 69 00 6e 00   e.s.i.n.
0020: 63 00 65 00 6e 00 74 00   c.e.n.t.
0028: 69 00 76 00 65 00 64 00   i.v.e.d.
0030: 61 00 74 00 61 00 63 00   a.t.a.c.
0038: 61 00 70 00 74 00 75 00   a.p.t.u.
0040: 72 00 65 00 2e 00 65 00   r.e...e.
0048: 78 00 65 00 2c 00 20 00   x.e.,. .
0050: 31 00 2e 00 30 00 2e 00   1...0...
0058: 30 00 2e 00 30 00 2c 00   0...0.,.
0060: 20 00 34 00 61 00 39 00    .4.a.9.
0068: 62 00 65 00 65 00 64 00   b.e.e.d.
0070: 33 00 2c 00 20 00 73 00   3.,. .s.
0078: 79 00 73 00 74 00 65 00   y.s.t.e.
0080: 6d 00 2e 00 64 00 61 00   m...d.a.
0088: 74 00 61 00 2c 00 20 00   t.a.,. .
0090: 32 00 2e 00 30 00 2e 00   2...0...
0098: 30 00 2e 00 30 00 2c 00   0...0.,.
00a0: 20 00 34 00 38 00 38 00    .4.8.8.
00a8: 39 00 64 00 65 00 61 00   9.d.e.a.
00b0: 66 00 2c 00 20 00 32 00   f.,. .2.
00b8: 34 00 39 00 30 00 2c 00   4.9.0.,.
00c0: 20 00 32 00 63 00 2c 00    .2.c.,.
00c8: 20 00 73 00 79 00 73 00    .s.y.s.
00d0: 74 00 65 00 6d 00 2e 00   t.e.m...
00d8: 64 00 61 00 74 00 61 00   d.a.t.a.
00e0: 2e 00 73 00 71 00 6c 00   ..s.q.l.
00e8: 63 00 6c 00 69 00 65 00   c.l.i.e.
00f0: 6e 00 74 00 2e 00 73 00   n.t...s.
00f8: 71 00 6c 00 20 00 4e 00   q.l. .N.
0100: 49 00 4c 00 0d 00 0a 00   I.L.....

How to check if a column exists in a datareader

Tue, 12 Dec 2006 10:45:45 Z

I'm up against this problem for which I'm surprised to find no help.

I want to check if a column exists in the datareader before attempting to read it. If i don't check i get the indexOutofRange exception.

There is similar functionality with datatable where we do dt.Columns.IndexOf("ColName")

But how do we do this with datareader???

SQLBulkCopy and unique identity columns

Tue, 17 Nov 2009 23:22:31 Z

I have a CSV file that looks something like this:
date, title, type
2009-11-01, myTitle, buy

My SQL table looks something like this:
unique identifyer with newid(), date, title, type

SQLBulkInsert is working perfectly if I do NOT have the unique ID column. How can I make SQLBulkCopy work in import while allowing SQL to assign a newid to each row?

Here is my code:

using

(SqlConnection cn = new SqlConnection("myconnection"))

{

cn.Open();

using (SqlBulkCopy copy = new SqlBulkCopy(cn))

{

copy.ColumnMappings.Add(0, 0);

copy.ColumnMappings.Add(1, 1);

copy.ColumnMappings.Add(2, 2);

copy.DestinationTableName =

"table";

copy.WriteToServer(dt);

}

cn.Close();

}