Using XML to read data from SQL Server
Does anyone have a good, complete example code that would show the
necessary steps for using XML to read data from a SQL Server database?
I'm trying to switch from DataReaders and I have very little knowledge
in the XML area. I would be very grateful and I suspect it would help
many others to see such a posting in this news group. Tag: Get Rich Tag: 124292
Unable to insert BLOB in Oracle
Hello,
I am unable to insert BLOB objects into Oracle 9i database from .NET. I
get the following exception:
"ORA-01460: unimplemented or unreasonable conversion requested".
I have a table in the database that accepts a number and blob object.
The blob object i am trying to insert is a compressed dataset.
Every thing seems to work fine if i insert small amount of data (compressing
around 500 records) but i get an exception
if i try to store data that has more than 500 records compressed.
Any ideas as to why this may happen?
Thanks
Bala Tag: Get Rich Tag: 124288
ADO.NET 2.0, WebMethods and datatable strange behavior
Hi,
I am using VS2005 and .NET 2.0
I have a WebMethod that accept a typed data table. I realized that whatever
rows that client sends to the SOAP server through the datatable, the
webmethod received an empty datatable!!
After spending several hours, I found that the only kind of data table that
I can pass it to a webmethod is detached datatable. If data table is
attached and part of a dataset, then the webmethod receives an empty
datatable!
Is that true? Or I am missing something?
Thank you,
Alan Tag: Get Rich Tag: 124282
Saving contents of datatable to Access table without writing SQL
Hello,
In ADO.NET is there anyway to save the contents of an datatable
directly to an Access table without having to loop through and generate
a SQL insert query string? I figure there's probably a way to do it as
one chunk.
I'm using VB.NET 1.1.
Thanks,
Eric Tag: Get Rich Tag: 124275
DataSet Question
Hi,
I have a dataset that is filled with data from the db. The user has
modified that data in one of the tables where some of fields should now be
null. I have played around with delete the row and adding a new row but
have problems with the update. Do you think I should just reset the row by
looping through the row and setting all the values to null before I update
changes to the row?
Thanks Tag: Get Rich Tag: 124274
DataColumn size
Hi everybody!
Do someone know the easiest way to obtain the datacolumn size in code (I
mean the size that
you define creating a text field in a table)? In VB6 you could get it by
means of Recordset.Field.
DefinedSize property. I have been searchin for something similar in ADO.NET
but without luck.
Thanks beforehand Tag: Get Rich Tag: 124256
AD.NET 2.0: BindingSource and Parent Child relationship
Hi,
I have two BindingSource(s) in my form, one for parent and one for child
area.
The unique situation in my form is that the child area chooses its parent by
a ComboBox.
The problem is when I use AndNew method in the child BindingSource, I cannot
choose the parent record through the combobox and parentDataSource.Position.
The parent BindingSource complains about [Not Null] columns and throws
exception.
This doesn't make any sense to me. I shouldn't have to use
DataSet.EnforceConstraints because I do not have any NULL value at that
specific parent record and position!
I am sure that I am missing an important points, what is it?
Any help would be appreciated,
Alan Tag: Get Rich Tag: 124248
How to write generic code access in ADO.NET
M. Chand is a .NET consultant, author and the admin and founder of C#
Corner. He has been working with .NET technology since pre beta
releases.
http://www.dotnetwire.com/frame_redirect.asp?newsid=3171
using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.Data.Odbc;
namespace GenericDataAccessApp
{
public class GenericAdoNetComp
{
private IDbConnection idbConn = null;
private IDbDataAdapter idbAdapter = null;
private DbDataAdapter dbAdapter = null;
private IDataReader iReader = null;
public GenericAdoNetComp()
{
}
// GetConnection returns IDbConnection
public IDbConnection GetConnection(int connType,
string connString)
{
switch (connType)
{
case 1: // OleDb Data Provider
idbConn = new OleDbConnection(connString);
break;
case 2: // Sql Data Provider
idbConn = new SqlConnection(connString);
break;
case 3: // ODBC Data Provider
idbConn = new OdbcConnection(connString);
break;
// case 3: // Add your custom data provider
default:
break;
}
return idbConn;
}
// GetDataAdapter returns IDbDataAdapter
public IDbDataAdapter GetDataAdapter(int connType,
string connString, string sql)
{
switch (connType)
{
case 1: // OleDb Data Provider
idbAdapter = new OleDbDataAdapter(sql, connString);
break;
case 2: // Sql Data Provider
idbAdapter = new SqlDataAdapter(sql, connString);
break;
case 3: // ODBC Data Provider
idbAdapter = new OdbcDataAdapter(sql, connString);
break;
// case 3: // Add your custom data provider
default:
break;
}
return idbAdapter;
}
}
}
public class Client
{
private void ConnectBtn_Click(object sender, System.EventArgs e)
{
GenericAdoNetComp genDP = new GenericAdoNetComp();
sql = "SELECT * FROM Employees";
if (radioButton1.Checked)
{
connString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\\Northwind.mdb";
conn = genDP.GetConnection(1, connString);
adapter = genDP.GetDataAdapter(1, connString, sql);
}
else if (radioButton2.Checked)
{
connString =
"Data Source=MCB;Initial Catalog=Northwind;user
id=sa;password=;";
conn = genDP.GetConnection(2, connString);
adapter = genDP.GetDataAdapter(2, connString, sql);
}
else if (radioButton3.Checked)
{
// Construct your connection string here
conn = genDP.GetConnection(3, connString);
adapter = genDP.GetDataAdapter(3, connString, sql);
}
try
{
conn.Open();
// Fill a DataSet
DataSet ds = new DataSet();
adapter.Fill(ds);
dataGrid1.DataSource = ds.Tables[0].DefaultView;
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
finally
{
conn.Close();
}
}
} Tag: Get Rich Tag: 124247
SQLconnection problem
I'm pretty new to asp.net and I'm having trouble just trying to set up
a basic SQLconnection within visual studio, I'm sure the problem must
be something basic but I can't seem to find it.
The problem is that the connection string ("Integrated
Security=yes;Initial Catalog=Northwind;Data Source=(local)") is
underlined within the code editor and provides the error text 'Too many
arguements for 'Public Sub New()' when you hover over the text.
a snippet of the code is below:
Imports System.Data
Imports System.Data.SqlClient
' ----- within the page_load function ------
Dim sqlConnection As SQLConnection
sqlConnection = New SqlConnection("Integrated Security=yes;Initial
Catalog=Northwind;Data Source=(local)") Tag: Get Rich Tag: 124246
Binary serializing of dataset in framework 2.0
I was eager to try the new binary serialization of dataset in version 2.0 of
the framework. The new serialization seems to be very fast comparede to the
"binary" serialization in framework 1.1. However I have a few problems
consering columns of the type Date:
Columns set to the value of Date.MinValue and Date.MaxValue get corruptede
depending on the what time zone my computer is set to:
1. When time zone is set to Greenwich Mean Time (GMT) everything works just
fine.
2. When time zone is set to something east of GMT for example GMT +1
(Denmark) coulms with the value Date.MinValue are corrupted during the
serialization and deserialization.
3. When time zone is set to something west of GMT for example GMT -6
(Central Time US) coulms with the value Date.MaxValue are corrupted during
the serialization and deserialization.
I also expirience problems with null valus:
Columns with null values will be set to Date.MinVaule after deserialization
when time time zone is east of GMT.
Is this a bug or am i doing something worg?
I use the code below to demostrate this behaviour:
'Create a dataset for test purpose
'--------------------------------------
Dim drNew As DataRow
Me.DatasetOriginal = New DataSet
Me.DatasetOriginal.Tables.Add("TEST")
Me.DatasetOriginal.Tables("TEST").Columns.Add("Id", GetType(Integer))
Me.DatasetOriginal.Tables("TEST").Columns.Add("Name", GetType(String))
Me.DatasetOriginal.Tables("TEST").Columns.Add("Date", GetType(Date))
Me.DatasetOriginal.Tables("TEST").Columns.Add("Number",
GetType(Integer))
Me.DatasetOriginal.Tables("TEST").Columns.Add("Bool",
GetType(Boolean))
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 1
drNew.Item("Name") = "My name"
drNew.Item("Date") = Date.MaxValue
drNew.Item("Number") = 13
drNew.Item("Bool") = True
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 2
drNew.Item("Date") = Date.MinValue
drNew.Item("Number") = 13
drNew.Item("Bool") = True
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 3
drNew.Item("Name") = "My name"
drNew.Item("Number") = 13
drNew.Item("Bool") = True
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 4
drNew.Item("Name") = "My name"
drNew.Item("Date") = Date.Now
drNew.Item("Bool") = True
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
drNew = Me.DatasetOriginal.Tables("TEST").NewRow()
drNew.Item("Id") = 5
drNew.Item("Name") = "My name"
drNew.Item("Date") = Date.Now
drNew.Item("Number") = 13
Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew)
'Serialize dataset
'-------------------
Dim bin As New
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
Dim Writer2 As New System.IO.StreamWriter("C:\DatasetSerialized.dat")
Try
DatasetOriginal.WriteXml("C:\Serialized.xml",
XmlWriteMode.WriteSchema)
DatasetOriginal.RemotingFormat = SerializationFormat.Binary
bin.Serialize(Writer2.BaseStream, DatasetOriginal)
Writer2.Close()
Writer2 = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'DeSerializer dataset
'-----------------------
Dim bin As New
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
Dim Reader As New System.IO.StreamReader("C:\DatasetSerialized.dat")
Try
DatasetSerialized = New DataSet
DatasetSerialized.RemotingFormat = SerializationFormat.Binary
DatasetSerialized = bin.Deserialize(Reader.BaseStream)
Reader.Close()
Reader = Nothing
DatasetSerialized.WriteXml("C:\DeSerialized.xml",
XmlWriteMode.WriteSchema)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try Tag: Get Rich Tag: 124244
DataColumn.Exression and string formatting
Hi,
I am truing to add a calculated column to my data table. The column must
show something like: "Date: 2005-01-23"
I underestand that we can use Convert to convert a DateTime value to string.
How can I convert it in specific date format?
Thank you,
Alan Tag: Get Rich Tag: 124229
excel datasource and gridview sorting
Hi!
I am somewhat befuddled.
I have a gridview that I have filled using oledb datareader with
data from an excel sheet.(code at bottom)
My problem is that it does not sort and I can't figure out why.
If I import to access and create a datasource using the table in
access I imported from all is well.
But when I fill it with the reader sorting has no effect. the
code below works for gridview1 which uses a access datasource but the first
one is uneffected.
I have set allowsorting = true on both.
But only the second one is sorting both by the button and by
clicking on the hyperlink in the header.
Can anyone tell me what I need to get the excel to sort?
Thanks for your interest!
Len
Protected Sub btnSort_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSort.Click
Dim direction As SortDirection
direction = SortDirection.Ascending
ExcelOut.Sort("Attending", direction)
AccessGridView1.Sort("Attending", direction)
========PageLoad=====================================================
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Dim DBConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Me.MapPath("Upload/DailyInfo.xls") & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes""")
DBConnection.Open()
Dim strConnString As String = Me.MapPath("Upload/DailyInfo.xls")
Dim SQLString As String = "SELECT Hospital,[Room],[Patient
Name],DOB,Symptom,Notes,Attending,Consulting ,[Initial Date Seen],FU FROM
[INPATIENT PATIENT SVCS$]"
Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
Dim DBReader As OleDbDataReader = DBCommand.ExecuteReader()
ExcelOut.DataSource = DBReader
ExcelOut.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
Html:================================
<asp:GridView ID="ExcelOut" runat="server" Width="800px"
CellPadding="4" ForeColor="#333333" GridLines="None" AllowSorting="True"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="HOSPITAL" HeaderText="HOSPITAL"
SortExpression="HOSPITAL" />
<asp:BoundField DataField="Room" HeaderText="Room"
SortExpression="Room" />
<asp:BoundField DataField="Patient Name" HeaderText="Patient
Name" SortExpression="Patient Name" />
<asp:BoundField DataField="DOB" HeaderText="DOB"
SortExpression="DOB" />
<asp:BoundField DataField="Symptom" HeaderText="Symptom"
SortExpression="Symptom" />
<asp:BoundField DataField="Notes" HeaderText="Notes"
SortExpression="Notes" />
<asp:BoundField DataField="Attending" HeaderText="Attending"
SortExpression="Attending" />
<asp:BoundField DataField="Consulting" HeaderText="Consulting"
SortExpression="Consulting" />
<asp:BoundField DataField="Initial Date Seen"
HeaderText="Initial Date Seen" SortExpression="Initial Date Seen" />
<asp:BoundField DataField="FU" HeaderText="FU"
SortExpression="FU" />
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True"
ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True"
ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333"
HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True"
ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
a..
b..
c.. Report abuse
-------------------------------------------------------------------------------- Tag: Get Rich Tag: 124214
Accessing a field in untyped DS
Hello,
I have an copied data set. I'm trying to access a field.
daOrder.Fill(dsOrderSheet);
DataSet dsCopy = new DataSet();
dsCopy = dsOrderSheet.Copy();
// How do you do this part?
output = dsCopy.Tables["orders"].Rows[0].ItemArray["orderDate"];
//or
output = dsCopy.Tables[0].Rows[0].ItemArray[1];
//neither work.
How is this done?
Thanks kindly for any advice on this
Ant Tag: Get Rich Tag: 124205
Adding a Datatable to a dataset
Hi,
I'm currently getting up to speed with ADO.NET & have a few questions on it.
This question relates to why a data table is added to a data set such:
DataTable dtEmp = ds.Tables.Add["employees"]; // From book
i would have thought it would be more like:
DataTable dtEmp = New DataTable("employees");
ds.Tables.Add(dtEmp); // The way I think it should be. Works ok
So what is going on in the first line? (from the book)
Is dtEmp being referenced to the "employees" table that is being added to
the dataset? (As opposed to dtEmp actually being added?). It doesn't seem
intuitutive to me...
Thanks for any comments
Ant Tag: Get Rich Tag: 124203
How do I assign a typed DataTable to its typed DataSet?
Hi,
The following code:
TypedDataSet typedDS = new TypedDataSet();
TypedDataSet.TypedDataTable typedDT;
//
// Code to fill typedDT
//
typedDS.TypedDataTable = typedDT; // Colmpiler error!
gives me a compiler error:
Property or indexer cannot be assigned to -- it is read only
What is the proper way to assign a typed DataTable to its typed DataSet?
Thank you,
Alan Tag: Get Rich Tag: 124201
Datagrid calculated column not updating
I have a datagrid (C# Windows Forms App) bound to a dataset. One column
in the dataset is a calculated column based on an expression that
simply subtracts the value in one column from the value in another
column. I created the dataset using a dataset control and the
properties dialog boxes. The dataset is not populated from a database -
the data is entered into the grid by the user.
By adding the following line of code in the form load event handler:
dtChequeNos.Columns["line_count"].Expression = "to_seq_no -
from_seq_no";
I was able to get the column to perform the calculation but it only
displays the result when the user moves to a new row in the grid. I
need the calculated column to display the result before the user moves
to a new row. Is this possible? Tag: Get Rich Tag: 124199
Q: Updating the correct keys
Hi
I'm hoping somebody can help me with the following problem that has occurred
to me.
Suppose I have two tables in an SQL Server database. Let's call these tables
A and B. Assume that A has two fields: a primary key and another holding a
string. In table B there are three fields: a primary key, a foreign key
(which links to the primary key in A) and other field holding a string.
Suppose I load these tables into a DataSet. I populate table A with some
rows. For each of these rows I create some rows in B which are linked by the
foreign key to A i.e. there is a one to many relationship between A and B.
Once this is done, I update the DataSet via a DataAdaptor such that these
tables are transferred to the SQL database.
My question is this. As far as I can see, the values in the primary key of A
and the corresponding keys in B will not necessarily be the same in the
DataSet and the SQL database! Do you see what I mean? I can't see how the
relationship specified is still valid after the Update.
Can anybody explain this to me?
Thanks in advance
Geoff Tag: Get Rich Tag: 124198
ODBCDataReader NO_DATA error when reading from CSV file
This is a strange one. In ASP.Net v1.1 using VB, I'm using an
ODBCDataReader to read from a CSV file.
Most of the time it works, but if the "description" field contains more
than 2046 chars, I get a NO_DATA error (see below for full error) when
trying to read that row. If this field contains 2046 chars or less,
then everything works fine.
There are about 6800 rows or lines in the CSV file. However, if I
reduce the amount of lines in the CSV to, say 25, then the description
field can contain more than 2046 chars without causing the error.
Here's a sample from my code:
'Save uploaded file to disk.
filCSV.PostedFile.SaveAs(server.MapPath("import/properties.csv"))
'Import data from CSV into MSSQL.
dim strTxtConString as string = "Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=" & Server.MapPath("import/") & ";"
dim conTxt as ODBCConnection = New ODBCConnection(strTxtConString)
dim cmdTxt as ODBCCommand
dim drTxt as ODBCDataReader
conTxt.Open()
cmdTxt = conTxt.CreateCommand()
cmdTxt.CommandText = "SELECT * FROM [properties.csv]"
cmdTxt.CommandType = CommandType.Text
drTxt = cmdTxt.ExecuteReader()
Do While drTxt.Read()
try
response.write("<br>" & drTxt("Number of characters").ToString() & "
" & drTxt("Description").ToString())
catch
response.write("<br>Error at " & drTxt("Number of
characters").ToString() & " characters")
exit do
finally
end try
Loop
drTxt.Close()
conTxt.Close()
Here's some code to generate the CSV file that causes the error:
dim i as integer
dim strDescription as string
for i = 1 to 2000
strDescription = strDescription & "a"
next
response.write(vbcrlf & """Number of characters"", ""Description""" &
vbcrlf)
for i = 1 to 1000
strDescription = strDescription & "a"
response.write(len(strDescription) & ", """ & strDescription & """" &
vbcrlf)
next
And here's the error message that occurs when the number of characters
in the description field of the CSV reaches 2047 chars:
NO_DATA - no error information available
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.Data.Odbc.OdbcException: NO_DATA - no error
information available
Source Error:
Line 53: Do While drTxt.Read()
Line 54: 'try
Line 55: response.write("<br>" & drTxt("Number of
characters").ToString() & " " & drTxt("Description").ToString())
Line 56: 'catch
Line 57: response.write("<br>Error at " & drTxt("Number of
characters").ToString() & " characters")
Thanks very much to anyone who can shed any light on this.
Simon Tag: Get Rich Tag: 124197
"EOF" in Data table
Hi,
I'm just coming from classic ADO (& still thinking that way) & I'm wondering
how you can determine when you get to the EOF in a data table. This is what
I've done below. It works, but I don't think it's the most elegant way of
doing it. How is this normally done? Am I thinking the totally wrong way for
ADO.NET?
Thanks for any thoughts.
Ant
int i = 0;
private void button1_Click(object sender, System.EventArgs e)
{
daEmployees.Fill(dsEmployees);
try
{
string empName = dsEmployees.Employees[i++].FirstName;
txtEmpName.Text = empName;
}
catch
{
// do nothing here
}
} Tag: Get Rich Tag: 124193
Adding commands at runtime to generated adapters
So Iâ??ve got this dataset that was built with the designer. All is well except
that the Select command needs to be generated at runtime for app-specific
reasons.
I thought I would do something clever, which was add a version of Fill() to
the dataset class that takes the SQL string as a parameter. But much to my
dismay, my designer-built dataset class is frequently rebuilt, presumably to
keep it in synch with changes to the table structure.
Thatâ??s actually quite a neat little feature but now Iâ??m in a bind. The first
line in the generated TableAdapterâ??s Fill() does this:
this->Adapter->SelectCommand = this->CommandCollection[0];
Both CommandCollection and the underlying array _commandCollection are
private, as is the underlying SqlAdapter of my TableAdapter. Since I canâ??t
say CommandCollection[0]=this or Adapter->SelectCommand=that Iâ??m at a bit of
a loss as to how Iâ??m supposed to provide a Select statement at runtime.
Deriving yet another class from the generated TableAdapter whose sole purpose
is to override Fill() seems a little silly.
What am I missing here?
--
Scott Bruno
Shiny Entertainment Tag: Get Rich Tag: 124188
Database with diverse data
Can anyone recommend a good whitepaper, strategy, or book on this...
I am going to implement a project where I will be getting several very
different types of data from third-party vendors; the data will often be in
a format only understood by the third-party (encoded). I would like to be
able to store the data in whatever form I get it in, plus a few key fields
for retrieval (e.g. date). I'm curious about different architectures to use
when extracting the data...and at which points to allow algorithms (aka
plug-ins) to unencode the data for either presentation or transport to the
third-party via web services. Loose coupling of the unencode algorithms
from the data is highly desireable since I may have to support lots of
unique data sets.
Amil Tag: Get Rich Tag: 124182
Where to use connection, dataset, datareader ?
Hi,
I have some difficulties in understanding how to use the connection,
dataset, datareader in a form.
Let's suppose I want to create a datareader and when I push a button to
bring another row from the dataset.
Supposedly I create under MyForm_Load event the connection, then a command
and the reader.
SqlConnection conn = new SqlConnection(...);
conn.open();
SqlCommand cmd = conn.CreateCommnad();
cmd.CommandType = ...
cmdCommnadText = ....
SqlReader dr = cmd.ExecuteReader();
If I try to use dr.Read() in side a ButtonRead_Click event, dr is not
recognized.
So, what is the approach here, where I create and use the components to make
them work?
Thanks,
Doru Tag: Get Rich Tag: 124178
EndCurrentEdit does not push changes into the dataset
I have a Windows Form written in C# with a non-edit DataGrid showing a
list of records. When the user selects a row in the grid, the full
details of the record display in a panel which contains textboxes and
comboboxes. The underlying datatable that the detail panel is bound to
only ever has one record at a given time.
When the user edits the detail record and then issues a save command by
clicking the save button, all works well. This is because the focus
moves from the current control and current record to the button.
However if the user issues a Save by pressing the F2 key (caught by the
form's ProcessCmdKey), the focus remains in the current control and
current record. Under these circumstances, the EndCurrentEdit will not
push the changes into the dataset.
I tried calling EndCurrentEdit() on each control (in the control leave
event) in the following ways:
control.BindingContext[dataset.Tables[datatablename]].EndCurrentEdit();
if (control.DataBindings.Count > 0)
{
control.DataBindings[0].BindingManagerBase.EndCurrentEdit();
}
I tried calling EndEdit() on the datatable row
dataset.Tables[datatablename].Rows[rownumber].EndEdit()
I then implemented a BindingManagerBase as follows:
bindMgrBase = this.BindingContext [mydataset, datatablename];
and on save called
bindMgrBase.EndCurrentEdit()
I tried
1) moving programmatically to the next textbox control
2) moving programmatically back to the datagrid
3) moving programmatically to a button
I suspect that the EndCurrentEdit() requires the user to move off the
current record. That is difficult to test as the record detail panel
only ever has one record at a given time.
Is there any way to make this work? Anyone have any ideas for me?
...Susan Tag: Get Rich Tag: 124177
Middle tier Data access object in .net assembly or COM?
Hi,
I am new in VS.net. We try to migrate our VB6 application to .net. In our
VB6 application, we have a middle tier data access object to handle the
connection and stored procedures' calls. Do we still do middle tier DO in
COM? or in just Assembly? why?
Thanks in advance,
DW Tag: Get Rich Tag: 124167
Manipulating data on input w/ TableAdapter & Dataset.
I know this is easier to do with a datareader and a listview, but can
it be done with a tableadapter and a dataset?
I want to manipulate the data prior to it reaching the dataset, For
instance, data is stored as Firstname and Lastname and sometimes there
will be no firstname, so appending a comma in this instance looks
stupid. So I need to check if firstname exists then return lastname &
", " & firstname or if not exist, just return lastname.
I was trying to do this with an IIF statement in the SQL code but it
wouldn't run saying it was an unrecognized command although it is
described in the BOL documentation for SQL Server 2000.
I also tried to do it with partial class of the dataset. First on
RowChanging, e.row = "processed data", error = cannot change proposed
value. Next on RowChanged, also met with error.
Any ideas, or is this not meant to be? Tag: Get Rich Tag: 124166
[.NET 2.0]Problem with DataTable.Load (dt,LoadOptions.Upsert)
Hi,
I would like to update an Sql Server database with Excel data. For this, I
use2 identical typed datatables: dtSql, and dtXls.
Here's my code simplified:
TypedDataTable dtXls = new TypedDataTable;
TypedDataTable dtSql = new TypedDataTable;
dataAdapterXls.Fill (dtXls);
dataAdapterSql.Fill (dtSql);
DataTableReader drXls = dtXls.CreateDataReader();
dtSql.Load(drXls, LoadOption.Upsert);
Some rows in Excel have a primary key (PK1) value that is already present on
the Sql database and therefore in dtSql, and I get this error message on
those rows:
"Column 'PK1'' is constrained to be unique. Value 'xxx'' is already
present."
The documentation states that when using the option "Upsert" of the Load
member : "The incoming values for this row will be written to the current
version of each column. The original version of each column's data will not
be changed."
So to me, those rows should have a current value taken from Excel and a
rowstate equal to "Modified"...
I don't really understand...
Cheers Tag: Get Rich Tag: 124165
TransactionManagerCommunicationException using TransactionScope
I'm writing a Windows service that uses the new TableAdapter in .NET 2.0. I
have several adapters that must be updated as an atomic operation, so I have
wrapped them using the TransactionScope.
When I run my code I get a TransactionManagerCommunicationException with the
following message "Network access for Distributed Transaction Manager (MSDTC)
has been disabled. Please enable DTC for network access in the security
configuration for MSDTC using the Component Services Administrative tool."
I looked at the server and found a security configuration dialog for MSDTC.
When looking at the settings, "Network DTC Access' was enabled. I even tried
"No Authentication Required" without any luck.
I'm running SQL2005 on 2003 Server. Tag: Get Rich Tag: 124162
SQL Table to DataSet to Dataview, to DataSet to XML!
Hi everybody,
i've a question for this group: i've developed a windows service that
every x minutes count records present in a sql table and if those
records are bigger than a fixed threshold start with clean operations.
Workflow is this:
1) Load records in a DataSet:
DataSet ds = new DataSet();
cmd = "SELECT * FROM " + TableName;
........
adapter.Fill( ds );
2)Generate a DataView form table inside dataset and order it (i must
order. I tryed to order before load table in DataSet, but table is very
big and often i had timeout)
DataView dv = new DataView(ds.Tables[0]);
string order = GetTimeFieldName ( Table ) + " ASC";
dv.Sort = order;
3) Now i need convert data to XML, but not only one XML file, N XML
file of 10000 records. To do this i generate N DataTable from DataView,
add each DataTable to a New DataSet and call
WriteXML method N time.
The workflow working fine, but i think is not optimal solution (load
dataset, copy to dataview, order dataview, copy again ....... ) ,
someone have a better idea, or some suggestion to gave me? Tag: Get Rich Tag: 124159
DataSet to Dataview to Dataset to XML!
Hi everybody,
i've a question for this group: i've developed a windows service that
every x minutes count records present in a sql table and if those
records are bigger than a fixed threshold start with clean operations.
Workflow is this:
1) Load records in a DataSet:
DataSet ds = new DataSet();
cmd = "SELECT * FROM " + TableName;
........
adapter.Fill( ds );
2)Generate a DataView form table inside dataset and order it (i must
order. I tryed to order before load table in DataSet, but table is very
big and often i had timeout)
DataView dv = new DataView(ds.Tables[0]);
string order = GetTimeFieldName ( Table ) + " ASC";
dv.Sort = order;
3) Now i need convert data to XML, but not only one XML file, N XML
file of 10000 records. To do this i generate N DataTable from DataView,
add each DataTable to a New DataSet and call
WriteXML method N time.
The workflow working fine, but i think is not optimal solution (load
dataset, copy to dataview, order dataview, copy again ....... ) ,
someone have a better idea, or some suggestion to gave me? Tag: Get Rich Tag: 124158
DataAdapter error
Hi,
I've created a relational group of tables using the designer for the Data
Adapter. It decided to cross join all the tables, so I manually did it using
Inner joins, nothing special just PK to FK.
When I click preview data, then try to fill a dataset created by the
Generate Dataset button of the DataAdapter, I get the following error:
"The data adapter could not return the data from the data source."
"Syntax error converting the varchar value 'myStringValue' to a column of
data type int".
When I look at the schema of the Dataset, all the columns are varchar.
Why is it doing this?
Any suggestions/past experiences very welcome & thanks in advance.
Ant Tag: Get Rich Tag: 124156
Fitting TableAdapter into Remoting Infrastructure
Short question: how to fit TableAdapters into (true) n-tier development.
More Info:
In VS2003 in terms of *physical* n-tier development, I had a fairly nice
pattern going. My DAL exists on a separate server and accessed via remoting.
In order for the client machine to "know" about the classes and methods on
the server (rather than going the lazy route and installing the server dll's
on the client) I have a very lightweight dll that contains "Types and
interfaces" that is installed on both the server and client.
This DLL acts as a WSDL file of sorts.... it contains datasets, classes, and
most importantly *interfaces* for the data access methods on the server (all
methods on the server must implement a particular Interface). This is not
hard to do and it's great because I can write a new server from the ground
up that contains the same methods but totally different implementation
without ever touching the client (at all!).
But, now in VS2005 / ADO.NET 2.0 I have a problem. I really really really
like the TableAdapter classes and the IDE designer for it. But am purplexed
that I can't PHYSICALLY separate it from the Dataset. My old pattern called
for the Datasets to exist in the "Types and Interfaces" DLL (because the
client uses them too) and the DataAdapters to exist on the remoted Server
DLL.
Anyone have any ideas on how I can adapt TableAdapters to this paradigm? I
absolutely hate the idea of putting my data access code on the client...
even if it's only going to get executed on the server. It violates the whole
notion of N-TIER development (logical layers is not the same as N-Tier...
though everyone seems to have forgotten this... including Microsoft).
P.S. I know I can continue to use DataAdapters. But, TableAdapters are way
easier to configure. Tag: Get Rich Tag: 124155
Table name in Dataset
Hi,
When I create a typed dataset containing multiple relational tables using
Generate dataset, it creates a single set of records based on what was chosen
when setting up the data adapter. If I have an orders table, an orderItems
table, a customer table etc. set up in the DataAdapter, the dataset chooses
one of these tables names as the name that intellisense exposes as the set of
records. This can be changed manually when viewing the schema.
My question is, how does it choose which table name to use for this combined
set of columns?
Thanks for your thoughts on this
Ant Tag: Get Rich Tag: 124150
General network error
Hi
I am supporting a client that gets 5-10 of the following errors each day:
General network error. Check your network documentation.
-----------------------------
Stack trace:
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString
options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
They are running SQLServer 2000 on a Windows 2003 Server domain.
The applications always use the following connection string:
"Server=MySqlServer;Integrated Security=SSPI;Pooling=true;Initial
Catalog=MyDatabase"
Someone has verified that:
- every SqlConnection.Open() has a corresponding SqlConnection.Close()
- every SqlReader.ExecuteReader() has a corresponding SqlReader.Close()
All code is enclosed in try/catch blocks - there are no other exceptions
occurring
One blog I found suggested turning off pooling, another suggested setting
Max Pool Count to some very high number like 5000.
I would very much like to understand the cause of the problem before I start
trying things at random.
Can anyone help?
Thanks in advance.
Valerie Hough Tag: Get Rich Tag: 124149
GetChanges question
Can someone please tell me how the following is possible? When I do this:
System.Data.DataSet addedAndModified = myDataset.GetChanges(
DataRowState.Added | DataRowState.Modified );
some of the rows in the resulting dataset have a RowState of
DataRowState.Deleted. Tag: Get Rich Tag: 124145
DBAccess in WinForms
Hi EveryOne,
What is the best approach for data access while developing applications
using Windows Forms ? Is it opening, working with and closing a new
SQLconnection and SQLcommand object everytime a DB access is needed , or
maintaining a single open connection/command object at the application level
( one for each user ) and then just reusing it again and again. The
connection string that the connection will use can be assumed to be static.
--
Vedanshu Mandvikar Tag: Get Rich Tag: 124144
Error saving typed dataset (.NET 2.0) - Object not set to referenc
I have an ASP.NET project with a typed dataset (with table adapters). The
table adapters' connection string property was set to get the connection
strings out of the web.config file. I was able to add new table adapters,
and all worked well for a while.
Now I need to add another table adapter and cannot do it. When I try to add
a new one, the connection strings from the web.config file no longer show up
in the wizard's connection string dropdown. Also, all of the existing table
adapters' connection string properties say "Unable to find connection
'dbname' for object 'web.config'. The connection string could not be found
in application settings, or the data provider associated with the connection
string could not be loaded. Oddly enough, if I run the project, the existing
table adapters have no problem connecting to the database.
Since I couldn't add a new table adapter, I figured I'd try it the
old-fashioned way and just add a DataTable and update it in the code (I've
had to work around many bugs in VS2005, so I'm used to it). However, when I
try to add a DataTable and click "Save", I get an "Object not set to a
reference of an object." messagebox.
When I click on "View Code" for the dataset, I get a couple more "Object not
set..." message boxes, then the schema loads. Then I notice there are over
100 warnings aobut attributes not being declared.
I can delete the entire dataset and rebuild it from scratch, and it will
work fine for a little while. After a few days though, it gets back to this
error. Any ideas? Tag: Get Rich Tag: 124137
SqlTransaction problems
Hi Everyone,
I'm currently working on a form that requires 3 stored procs to run (one of
them several times), but these stored procs are in different functions (see
below). The problem is that after the commit, the database doesn't reflect
any of the changes. I open a new connection before calling a proc, and then
pass the same connection to each function and within the function, I set the
transaction to the Command object. Here is the code shortened:
Private Sub cmdSave_Click( ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click
Dim cnSQL as SqlConnection
Dim Success as Boolean = True
try
cnSQL = OpenConnection()
Success = SaveAdmissionNursingAssessment1(cnSQL)
if Success = True then
Success = SaveFamilyHistory(cnSQL)
if not Success then
SaveTrans.Dispose
else
Success = SaveAdmissionNursingAssessment2(cnSQL)
if not Success then
SaveTrans.Dispose
End If
End If
else
cnSQL.Dispose
end if
cmdAdd.Enabled = True
cmdSave.Enabled = False
cmdCancel.Enabled = False
Catch ex As Exception
msgbox(ex.Message )
End Try
End Sub
Private Function SaveAdmissionNursingAssessment1(cnSQL As SqlConnection) as
Boolean
Dim success as Boolean = true
Dim cmSQL As SqlCommand
Try
SaveTrans = cnSQL.BeginTransaction
if isnew then
cmSQL = New SqlCommand("intake_AddAdminNurseAssess1", cnSQL)
cmsql.Transaction = SaveTrans
else
cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment1", cnSQL)
cmsql.Parameters.Add("KeyID", sqldbtype.Int ).Value = fAssessId
cmsql.Transaction = SaveTrans
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value =
fPatientId
cmsql.Parameters.Add("PreparedDate", sqldbtype.SmallDateTime ).Value =
Now()
cmSQL.ExecuteNonQuery()
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
' Close and Clean up objects
cmSQL.Dispose()
SaveAdmissionNursingAssessment1 = success
End Try
End Function
Private Function SaveAdmissionNursingAssessment2(cnSQL As SqlConnection) as
Boolean
Dim success as Boolean = True
Dim cmSQL As SqlCommand
Try
if isnew then
cmSQL = New SqlCommand("intake_AddAdminNurseAssessment2", cnSQL)
cmsql.Transaction = SaveTrans
isnew = False
else
cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment2", cnSQL)
cmsql.Parameters.Add("KeyID", sqldbtype.Int ).Value = fAssessId
cmsql.Transaction = SaveTrans
isDirty = False
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value =
fPatientId
cmSQL.ExecuteNonQuery()
SaveTrans.Commit
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
' Close and Clean up objects
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
SaveTrans.Dispose
SaveAdmissionNursingAssessment2 = success
End Try
End Function
Private Function SaveFamilyHistory(cnSQL As SqlConnection) as Boolean
'Dim cnSQL As SqlConnection
Dim success as Boolean = true
Dim cmSQL As SqlCommand
Dim i as Integer
Try
For i = 2 To flxFamilyHistory1.Rows.Count - 1
if len(trim(flxFamilyHistory1(i, 1))) > 0 or
len(trim(flxFamilyHistory1(i, 3))) > 0 then
if not isnumeric(flxFamilyHistory1(i, 6)) then
cmSQL = New SqlCommand("intake_AddANFamilyHistory", cnSQL)
cmsql.Transaction = SaveTrans
else
cmSQL = New SqlCommand("intake_UpdANFamilyHistory", cnSQL)
cmsql.Parameters.Add("@HistId", sqldbtype.int).Value =
flxFamilyHistory1(i, 0)
cmsql.Transaction = SaveTrans
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("@AssessId", sqldbtype.Int ).Value = fAssessId
cmsql.Parameters.Add("@PatientID", sqldbtype.Varchar, 20 ).Value =
fPatientId
cmsql.Parameters.Add("@Age", sqldbtype.Varchar, 5 ).Value =
flxFamilyHistory1(i, 1)
cmsql.Parameters.Add("@Health", sqldbtype.Varchar, 25 ).Value =
flxFamilyHistory1(i, 2)
cmsql.Parameters.Add("@AgeOfDeath", sqldbtype.Varchar, 50 ).Value =
flxFamilyHistory1(i, 3)
cmsql.Parameters.Add("@CauseOfDeath", sqldbtype.Varchar, 50 ).Value
= flxFamilyHistory1(i, 4)
cmsql.Parameters.Add("@Who", sqldbtype.Varchar, 50 ).Value =
flxFamilyHistory1(i, 5)
cmsql.Parameters.Add("@SortOrder", sqldbtype.Varchar, 50 ).Value = i
cmSQL.ExecuteNonQuery()
End If
Next
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
cmSQL.Dispose()
SaveFamilyHistory = success
End Try
end function
Thanks for any info. Is there a better way to handle this when crossing
function with our code. Thanks so much for any info.
Michael Tag: Get Rich Tag: 124136
cannot connect to an oracle
Hi
I have problems when I try to connect to my Oracle db from VS 2003. I get
this error: ORA-12154: TNS:could not resolve the connect identifier specified
My tnsnames.ora-file I have this text:
MALL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
Z9100774.ne.ad.ner.capgemini.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mall)
)
)
What can be the problem?
Thanks
Julia Tag: Get Rich Tag: 124132
DataReader Limits data returned!
I have a stored procedure that returns xml data (using For XML), it returns
about 9000 records. I use a datareader to write the data to a XML file.
For some reason, the datareader doesn't write all data, it seems like the
data returned has some size limit.
here is my code, my code is based on a msdn example for using getBytes were
it writes data to the stream by chunks based on the buffer size:
'----some code here to fill in the stored procedure name and
parameters.
Dim mydatareader As SqlDataReader
connectionObj.Open()
mydatareader = CommandObj.ExecuteReader
Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 100
Dim outChar(bufferSize - 1) As Char
Dim retval As Long
Dim startindex As Long = 0
If mydatareader.Read Then
fs = New FileStream("c:\test.xml", FileMode.OpenOrCreate,
FileAccess.Write)
bw = New BinaryWriter(fs)
retval = mydatareader.GetChars(0, startindex, outChar, 0, 100)
' Continue reading and writing while there are bytes beyond the
size of the buffer.
Do While retval = bufferSize
'bw.Write(outByte)
bw.Write(outChar)
bw.Flush()
' Reposition the start index to the end of the last buffer
and fill the buffer.
startindex += bufferSize
retval = mydatareader.GetChars(0, startindex, outChar, 0,
bufferSize)
Loop
'Write the remaining buffer.
bw.Write(outChar, 0, CType(retval - 1, Integer))
bw.Flush()
' Close the output file.
bw.Close()
fs.Close()
End If
connectionObj.Close()
connectionObj.Dispose()
CommandObj.Dispose() Tag: Get Rich Tag: 124120
click event handler executed twice
Hi everyone:
I got simple aspx with a user control in it that posts some entered
data to a database. The problem I'm seeing is that when the submit
button is clicked the code is executed twice and I get duplicate rows
in the database.
The including aspx page is this:
<%@ Page language="c#" Codebehind="test.aspx.cs"
AutoEventWireup="false" Inherits="DirectedInsight.test" %>
<%@ Register TagPrefix="uc1" TagName="comments"
Src="includes/comments.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html>
<head>
<title>test</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET
7.1">
<meta name="CODE_LANGUAGE" content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body ms_positioning="GridLayout">
<form id="Form1" method="post" runat="server">
<uc1:comments id="Comments1" runat="server"></uc1:comments>
</form>
</body>
</html>
The user control looks like this:
<%@ Control Language="c#" AutoEventWireup="false"
Codebehind="comments.ascx.cs"
Inherits="DirectedInsight.includes.comments"
TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%>
<table class="normalText">
<tr>
<td>Your name:
</td>
<td><asp:textbox id="submitName"
runat="server"></asp:textbox></td>
<td>Your email:
</td>
<td><asp:textbox id="submitEmail"
runat="server"></asp:textbox></td>
</tr>
<tr>
<td>Comment:
</td>
<td colspan="3"><asp:textbox id="comment" runat="server"
textmode="MultiLine" rows="6" columns="50"></asp:textbox></td>
</tr>
<tr>
<td align="center" colspan="4">
<asp:button id="Button1" onclick="submitComment"
runat="server" cssclass="commonButton" text="Submit
Comment"></asp:button>
</td>
</tr>
</table>
And the event handler is this:
public void submitComment(object sender, System.EventArgs e) {
string connStr =
"SERVER=SQLServer;UID=USER;PWD=Password;DATABASE=Database;";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insertComment";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param =
cmd.Parameters.Add("@articleID",SqlDbType.Int, 4);
param.Direction = ParameterDirection.Input;
param.Value = cArticleID;
param = cmd.Parameters.Add("@submitName",SqlDbType.VarChar,
30);
param.Direction = ParameterDirection.Input;
param.Value = submitName.Text;
param =
cmd.Parameters.Add("@submitEmail",SqlDbType.VarChar, 100);
param.Direction = ParameterDirection.Input;
param.Value = submitEmail.Text;
param = cmd.Parameters.Add("@comment",SqlDbType.Text,
1000);
param.Direction = ParameterDirection.Input;
param.Value = comment.Text;
cmd.ExecuteNonQuery();
}
The stored procedure is single insert statement.
Everytime I click the submit button I end up with two identical rows in
the database. I've googled and can find nothing similar on it. Any help
would be greatly appreciated!!
thanks in advance
Dave Tag: Get Rich Tag: 124119
Document locking concept ?
Hi !
I have problem with locking concept in my application (ADO.NET , C#).
Application has several documents which looks like Order.My question is what
and how should I lock those documents.
1.Lock document as a whole (Order + OrderLines) so another user can't work
on this document (pessimistic lock)?
1.1. How to implement this on SQL server ?
2.Should I use optimistic locking on Order and OrderLines ?
I3. If I use optimistic locking on OrderLines should I each time update
timestamp of Order and by each insert, update and read of OrderLine check
whether somebody has changed this Order and/or OrderLine which is both
possible ?
Any thoughts are appreciated! Tag: Get Rich Tag: 124113
copy selected row from a datagridview to another
I'm trying to copy seleced rows from
dgv1 datagridview, bound to dsOrders1 dataset
to
dgb2 datagridview, bound to dsOrders2 dataset
both dsOrders1 and dsOrders2 are instance of dsOrders dataset schema.
Can you help me?
Thanks. Tag: Get Rich Tag: 124112
question on sql
Dear all
1.)How can i save the stored procedure in sql 2005 server, into server and
NOT in file location
2.)How can create folder in stored procedure to separate categories?
3.)How can view the created date and modify date of stored pro Tag: Get Rich Tag: 124111
TransactionScope and DataSets
Hi,
I'm wondering what the official Microsoft recommendation is for handling
TransactionScope transactions when using TableAdapters within a DataSet.
I have a dataset that various areas of my business logic uses, and at a
higher level I'd like to wrap several business functions in a
TransactionScope transaction.
The problem is, each TableAdapter has an InitConnection method generated
that creates a _new_ Connection to the database. If a second connection gets
used within the TransactionScope, the transaction will needlessly be promoted
to a distributed transaction.
My current workaround is to alter the Dataset.Designer.cs generated file
manually to change how Connections are created, but that's somewhat painful
sin ce the file gets regenerated automatically.
Thanks for any advice,
Kirk Tag: Get Rich Tag: 124103
What causes SqlCommandBuilder to not build commands?
Language is C++ and my code is pretty much right out of the documentation
example, yet I'm not ending up with any generated commands. In the following,
assume the relevant objects are members of a form class and this is happening
on the form's load event:
m_SqlConnection = gcnew SqlConnection( strConnection );
m_SqlAdapter = gcnew SqlDataAdapter( strQuery, m_SqlConnection );
m_SqlCommandBuilder = gcnew SqlCommandBuilder( m_SqlAdapter );
m_DataSet = gcnew DataSet();
m_SqlAdapter->Fill( m_DataSet, "SomeTable" );
My dataset is correctly populated; I can see the data both in the debugger
and in the controls I bind to the dataset. My adapter, however, has
"undefined value" for every command except the SelectCommand, which I
provided myself in the constructor with strQuery.
Does anyone know why this might be the case? No exceptions are thrown from
the above code so there's no indication why the thing failed. Tag: Get Rich Tag: 124100
OLEDB Exception: The search key was not found in any record
Greetings!
I've written a console application to perform some trivial database
routines. When I run the application, it catches the an OleDbException
that states "The search key was not found in any record." My C# code
follows:
=============== snip ==========================
OleDbCommand cmd = new OleDbCommand(sqlstmt, new
OleDbConnection(cnct));
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
Console.Read(); // used to pause application long enough to read
message
}
cmd.Dispose();
=============== snip ==========================
sqlstmt = "DELETE FROM Accounts WHERE AccountID > 3"
cnct = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA
SOURCE=data_source;Persist Security Info=False;Jet OLEDB:Database
Password=password"
where data_source is the fully qualified local path of the database and
password is, well, obvious (I hope).
I've checked the database and it contains the Accounts table and
AccountID column. The AccountID column does have numbers greater than
three.
I have run this application before in the past with success. It is an
enigma to me why it is no longer functioning. I did recently install
the .NET Framework 2.0 update, but even after uninstalling it, I still
received the exception.
Any assistance that could be provided would be greatly appreciated.
Thanks.
Tim Tag: Get Rich Tag: 124095
JOIN on 2 datasets?
Can I code a JOIN on 2 datasets the same as 2 tables in a database or
do I have to retrieve the table by index or something of the table and
JOIN those? There's only 1 table in each ds... Tag: Get Rich Tag: 124089