How to put NULL into database column
Hi,
I have a stored procedures that takes few parameters( lets say 2).
Parameters are value for particular columns.
A column of type "binary' can contain "null".
I want to update a row, so that first column will have some value and second
should contain null. How can I do it?
MyParam.Value = null;
cmd.Parameters.Add(MyParam); // obviously doesn't work
Thanks,
Lubomir Tag: Selecting active query for data adapter Tag: 143916
Error connecting to Oracle
Using some .Net code to connect to Oracle the follow error message is
now being displayed
File or assembly name Oracle.DataAccess, or one of its dependencies,
was not found.
Exception Details: System.IO.FileNotFoundException: File or assembly
name Oracle.DataAccess, or one of its dependencies, was not found.
This section of code has not been changed and the a test ODBC
connection can be successfully made from the web server. In the
Windows path is the folder containing the dll as the first folder
location.
Does anybody know why this error is occurring and how to resolve it? Tag: Selecting active query for data adapter Tag: 143914
Getting data as returned from SQL Server
is there anyway to get the data as it is returend from the sql server? I
have a command object that returns 100,000 records which I want to see as
they come back... kinda like how SQL Managment studio does when you execute
a query it shows the data as it comes in... any way to do that in ADO.NET? I
am using .NET 3.5 on VS 2008 thanks! Tag: Selecting active query for data adapter Tag: 143907
Timeout in .NET but not in Management Studio
I have a SQL Server 2005 database which I use from ASP.NET 2.0.
I invoke stored procedures using SqlConnection, SqlCommand and SqlDataReader
from System.Data.SqlClient.
When I invoke a SP from SQL ServerManagement Studio it executes in 3 seconds
and returns 4000 rows.
When the same procedure is invoked from SqlCommand.ExecuteReader it takes
more than 30 seconds an sometimes renders a timeout in .NET.
In Profiler the invocations of the SP:s look exactly the same.
If I trace Scans they behave differently though. The time difference lies
within the Scans but also the patterns of Scan:Started - Scan:Stopped differ.
In my connectionStrings the providerName is System.Data.SqlClient.
What can make SQL Server execute the query in two different ways? They seem
to be getting different execution plans.
How can I make .NET use the faster way?
Regards Ove Tag: Selecting active query for data adapter Tag: 143897
Incredibly slow writing by CSV driver
Since my application uses the Excel driver to read some files, I used the
same Excel driver to write some CSV files. I also tried using the Text
driver to write CSV files. The results are correct but they take enormous
amounts of CPU time.
Of course I know how to write a CSV file using plain old Shift-JIS character
strings and quotation marks and commas, and probably I'll do that in order
to speed up this operation, but the question still remains.
Why is this so slow? The sample data table had 213 rows (header plus 212
data rows) and 25 columns, all strings (some of them empty strings). The
total file size on disk is 34KB. The computations and database operations
in memory take a few milliseconds, not even noticeable when running under a
debugger. But the call to
dataAdapter.Update(dataTable);
takes 55 SECONDS OF CPU TIME on a Pentium 4 running at 3 GHz. It occupies
100% of one CPU core for 55 seconds.
Actual time to write the file might be a few hundred milliseconds since 34KB
occupies several NTFS structures. Anyway, this thing isn't disk bound, and
it's not CPU bound in my code, it's CPU bound in the Update method.
What is going on here?
#undef UseOdbc // Use OleDb
string fileName = @"C:\test.csv"; // (not really)
string Headers[] = new string[25] { "1", "2", "3", /* ... */ "25" };
int columnCount = 25; // (not really)
FileInfo fileInfo = new System.IO.FileInfo(fileName);
string dirName = fileInfo.DirectoryName;
string tableName = fileInfo.Name;
#if UseOdbc
OdbcConnection connection = new OdbcConnection(
"Provider=MSDASQL;" +
"DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=" + dirName +
";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
"HDR=Yes;FMT=Delimited'");
connection.Open();
OdbcDataAdapter dataAdapter = new OdbcDataAdapter(
"SELECT * FROM [" + tableName + "]", connection);
OdbcCommand insertCmd = new OdbcCommand();
OdbcType varcharType = OdbcType.VarChar;
#else // OleDb
OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dirName +
";Extended Properties='Text;Extensions=asc,csv,tab,txt;" +
"HDR=Yes;FMT=Delimited'");
connection.Open();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(
"SELECT * FROM [" + tableName + "]", connection);
OleDbCommand insertCmd = new OleDbCommand();
OleDbType varcharType = OleDbType.VarChar;
#endif // OleDb
DataTable dataTable = new DataTable(tableName);
DataColumnCollection dataColumns = dataTable.Columns;
int columnNum;
string dataColumnName;
DataColumn dataColumn;
StringBuilder insertCmdHead = new StringBuilder(
"INSERT INTO [" + tableName + "] (");
StringBuilder insertCmdTail = new StringBuilder("VALUES (");
StringBuilder fileHeader = new StringBuilder();
for (columnNum = 0; columnNum < columnCount - 1; columnNum++)
{
dataColumnName = Headers[columnNum];
dataColumnName = dataColumnName.Replace('\n', ' ');
dataColumn = dataColumns.Add(dataColumnName);
dataColumn.DataType = typeof(string);
dataColumn.DefaultValue = "";
insertCmdHead.Append("[" + dataColumnName + "], ");
insertCmdTail.Append("?, ");
insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
dataColumnName);
fileHeader.Append("\"" + dataColumnName + "\",");
}
dataColumnName = Headers[columnCount - 1];
dataColumn = dataColumns.Add(dataColumnName);
dataColumn.DataType = typeof(string);
dataColumn.DefaultValue = "";
insertCmdHead.Append("[" + dataColumnName + "]) ");
insertCmdTail.Append("?)");
insertCmd.Parameters.Add(dataColumnName, varcharType, 255,
dataColumnName);
insertCmd.CommandText = insertCmdHead.ToString() +
insertCmdTail.ToString();
insertCmd.Connection = connection;
dataAdapter.InsertCommand = insertCmd;
fileHeader.Append("\"" + dataColumnName + "\"");
for (int rowNum = 0; rowNum < 212; rowNum++) // (not really)
{
DataRow dataRow = dataTable.NewRow();
dataRow[2] = "hi i'm 2"; // (not really)
dataRow[18] = "18"; // (not really)
dataRow[19] = "19"; // (not really)
// (around half the cells default to empty strings)
dataTable.Rows.Add(dataRow);
}
StreamWriter fileWriter = new StreamWriter(fileInfo.Create(),
Encoding.Default);
fileWriter.WriteLine(fileHeader.ToString());
fileWriter.Close();
fileWriter.Dispose();
// UP TO THIS POINT TAKES A FEW MILLISECONDS, OK
//
// 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
dataAdapter.Update(dataTable); // 55 SECONDS TO WRITE 34 KILOBYTES
// 55 SECONDS OF CPU TIME (Pentium 4 3 GHz) TO WRITE 34 KILOBYTES
//
// OK AFTER THIS
dataAdapter.Dispose();
dataTable.Dispose();
connection.Close(); Tag: Selecting active query for data adapter Tag: 143894
RAD apps for db development
Hi
Are there any RAD apps to speed up one-many db application development that
are worth considering?
Thanks
Regards Tag: Selecting active query for data adapter Tag: 143889
Reformed access user needs advise for future
Hi
Many a times I have been told off here for asking question on a backend
other than sql sever. So I have finally seen the light and have decided to
take up my next project with sql server backend (vb.net front). So here is
my question;
What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.
So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).
Thanks
Regards Tag: Selecting active query for data adapter Tag: 143885
Getting Casting error when passing paramarray with Microsoft.Pract
I am using Microsoft.Practices.EnterpriseLibrary.Data as DataLayer in my
project.
I am facing problem to pass objSqlParam array to methods provided by
Microsoft.Practices.EnterpriseLibrary.Data as DataLayer itseld(i.e.
db.ExecuteNonQuery(storedProcedureName, parameterValues) and as on same with
others)
I am doing as below:
Dim objSqlParam As SqlParameter() = Nothing
objSqlParam = New SqlParameter(1) {}
objSqlParam(0) = New SqlParameter("@CustId", SqlDbType.Int)
objSqlParam(0).Direction = ParameterDirection.OutPut
objSqlParam(0).Value = 1
objSqlParam(0) = New SqlParameter("@CustName", SqlDbType.Varchar)
objSqlParam(0).Direction = ParameterDirection.Input
objSqlClient.ExecuteNonQuery(SP_CustSave,
objSqlParam(0).Value,objSqlParam(1).Value))
This last should be as below
objSqlClient.ExecuteNonQuery(SP_CustSave, objSqlParam)
when i do this i found error for could not cast <datatype> to <datatype>
Weather i passed the objSqlParam array with values it works fine.
Help me out on this.
Regards,
JaiPrakash Sharma Tag: Selecting active query for data adapter Tag: 143882
OutPut Perameter with Microsoft.Practices.EnterpriseLibrary.Data
Hi,
I am using Microsoft.Practices.EnterpriseLibrary.Data as DataLayer in my
project.
I am facing problem to get OutPut Parameter in my Method which i am getting
from my StoreProcedure.
I am doing as below:
Dim objSqlParam As SqlParameter() = Nothing
objSqlParam = New SqlParameter(1) {}
objSqlParam(0) = New SqlParameter("@CustId", SqlDbType.Int)
objSqlParam(0).Direction = ParameterDirection.OutPut
objSqlParam(0).Value = 1
objSqlParam(0) = New SqlParameter("@CustName", SqlDbType.Varchar)
objSqlParam(0).Direction = ParameterDirection.Input
objPIDataSet = objSqlClient.ExecuteDataSet(SP_CustSave,
objSqlParam(1).Value)
return Convert.ToInt(objSqlParam(0).Value.ToString())
Here i am not getting the Output Value from SP in Parameter, even though the
SP is returning as i execute SP in Query Analyzer.
Help me out on this.
Regards,
JaiPrakash Sharma Tag: Selecting active query for data adapter Tag: 143881
Which protocol is used if not specified in connection string?
Say you have a SQL Server connection string in the following format,
where MySqlServer is a server connected a local network
"data source=MySqlServer; Initial Catalog=MyDatabase; Integrated
Security=FALSE; User ID=MyUserName; Password=MyPassword; Pooling=True;
Connect Timeout=50"
I am wondering what protocol (tcp, named pipes, etc.) would be used
with a connection string in this format.
If MySqlServer supports both named pipes and tcp, is the protocol
indeterminant? Or is there a default protocol?
BOL doesn't seem very clear about this. Tag: Selecting active query for data adapter Tag: 143880
OleDbDataAdapter Fill - strange behavior
All,
If I do 2 OleDbDataAdapter Fills in a row, it does not work. I get a
message saying that it cannot find the table I am looking for.
Actually it is a Range in an Excel spreadsheet. I have tried closing
the Connection and reopening it, but that makes no difference. Now
here is the strange part, if I put a breakpoint (I am using C#) on the
second Fill command, and then continue, it works!! I tried a 5 second
delay between Fill commands, but that did not help either. Any
thoughts?
TIA,
BIll Tag: Selecting active query for data adapter Tag: 143879
Problem trying to update dbf table using VFPOLEDB.1 Provider
Hi,
I am new to using dbf files with .net. I have created a small
program using visual web developer 2008. I am able to read a table
using the oledb provider but if I try an update I get the famous
cryptic "One or more errors occurred during processing of command"
error.By the way I'm running vista home premium. The field (subtotal)
I'm trying to update is type "N".
It's been a while since I've used dbf tables so I apologize if any
of my assumptions are stupid. I tried making "77.0" just "77" and a
few other things. I downloaded Microsoft OLE DB Provider for Visual
FoxPro 9.0 by the way.
Here is the code; the error occurs on te executenonquery:
Try
Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Users\Public;")
cn1.Open()
Dim cmd1 As New OleDbCommand( _
"Update OE Set SUBTOTAL = 77.0", cn1)
cmd1.ExecuteNonQuery()
Catch e As Exception
MsgBox(e.ToString())
End Try
Any help would be appreciated.
Thanks,
fig000 Tag: Selecting active query for data adapter Tag: 143874
Code for ADO.NET: The Complete Reference
Hello All. I am trying to use ADO.NET: The Complete reference by Michael and
Denielle Otey . I wanted to download the code from the publisher's (Osborne)
website BUT when I go to the Osborne website and enter this title into the
"Search For" space, it says it cannot find that book. I suppose it is
because it is several years old. I am trying to use the "Finding a Record in
the DataTable" on page 366.
Would anyone have the Code to this Book and be willing to email it to me?
jimrichards@satx.rr.com If you do, it will be very helpful to me because I
make too many mistakes when I copy code from a book. Thanks in
advance for any help on this. Jim Tag: Selecting active query for data adapter Tag: 143860
Code for ADO.NET: The Complete Reference
Hell All. I am trying to use ADO.NET: The Complete reference by Michael and
Denielle Otey . I wanted to download the code from the publisher's (Osborne)
website BUT when I go to the Osborne website and enter this title into the
"Search For" space, it says it cannot find that book. I suppose it is
because it is several years old. I am trying to use the "Finding a Record in
the DataTable" on page 366.
Would anyone have the Code to this Book and be willing to email it to me?
jimrichards@satx.rr.com If you do, it will be very helpful to me. Thanks in
advance for any help on this. Jim Tag: Selecting active query for data adapter Tag: 143854
syntax problem
All,
I have a C# app that is accessing an Excel spreadsheet using the
following:
OleDbDataAdapter metricDataAdapter = new OleDbDataAdapter("SELECT
* FROM [ReqReviewsData]", Connection);
This works, and I can read the ReqReviewsData range into my dataset.
Now I am trying to write the dataset (or anything for that matter)
back to the Excel spreadsheet. I do the following (taken from an
example supplied by David Hayden):
dbCmd = Connection.CreateCommand();
dbCmd.CommandText = "Update [ReqReviewsData] Set Space = \"xx\"
WHERE MetricName = \"Verified\"";
dbCmd.ExecuteNonQuery();
This generates the following:
Syntax error in UPDATE statement.
The connection string looks like:
string ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=D:\\LETB\\Documentation\\Metrics\
\LETBMetrics.xlsm;" +
"Extended Properties=" + (char)34 + "Excel
12.0;HDR=YES;" + (char)34;
And yes indeed the Excel range that I am selecting has a column with
"Space" and "MetricName" which really should not have anything to do
with syntax anyway. I have diddled with dbCmd.CommandText in several
ways, one of which is the following:
dbCmd.CommandText = "Update [ReqReviewsData] Set Space = " +
(char)34 + "xx" + (char)34 + " WHERE MetricName = " + (char)34
+ "Verified" + (char)34;
The produces the same error. What oh what is wrong with my UPDATE
statement?
TIA,
Bill Tag: Selecting active query for data adapter Tag: 143851
varchar(max) - SqlParameter
I'm wondering how should I set up SqlParameter
in order to pass varchar(max) type values.
http://msdn2.microsoft.com/en-us/library/bb399384.aspx
suggests using "-1" as value for SqlParameter's size.
so:
command.CommandText = "select len(@p)";
command.CommandType = CommandType.Text;
SqlParameter p = New SqlParameter("@p", SqlDbType.VarChar, -1);
p.Value = new String('a',10);
command.Parameters.Add(p)
After this code is executed several times (with
"@p" parameter holding string of different lengths),
it appears that this query is present in the
sys.dm_exec_cached_plans view multiple times
(each cache item is stored for concrete @p parameter
length).
It seems that:
- when using 0, -1 or simply not providind Size for SqlParameter,
it's size is calculated from parameter value.
- if it is above limits (4000/8000), the parameter
gets VarChar(MAX) type
- otherwise, parameter gets concrete VarChar(size) type.
- in order to make query optimizer reuse cached query plan,
datatypes must match (including size...).
So - back then - how to setup SqlParameter in order to
pass VarChar(MAX) values correctly?
--
Marcin Tag: Selecting active query for data adapter Tag: 143848
What is the fastest way to update all rows of a table in a dataset
Hello and thanks for your help :)
I have uploaded an xml into a dataset so now it contains a single table
filled with the xml values. I need to update one of the fields in all the
rows with a constant value.
What is the fastest way of doing it
Btw: I don not have a database to which I can save the table and then
execute an update command.
Any ideas? Tag: Selecting active query for data adapter Tag: 143846
Any way to make OleDb read an Excel file?
With Visual Studio 2005 SP1 and DotNet Framework 2 SP1, Excel 2003 can read
a file that was saved by Excel 2003, but the OleDb driver can't.
Example: Excel row 2 maps to row 1 in OleDb, and Excel columns 1 to 24 (A
to X) map to columns 0 to 23 in OleDb. Counting these in OleDb, row 1
columns 0 to 4 are OK, columns 5 and 6 get corrupted to DbNull, columns 7 to
12 are OK, columns 13 and 14 get corrupted to DbNull, etc.
If I do Interop to Excel 2003 instead of using OleDb, it works. Counting
these in Excel interop, row 2 columns 1 to 24 are entirely OK.
Back to the OleDb problem. One worksheet has 40 columns instead of 24. The
worksheet with 40 columns works. Several of the additional columns read as
doubles instead of text but ToString() works on them. But all of the
24-column worksheets get corrupted by OleDb, and the corrupted column
numbers vary.
Does anyone know how to get OleDb to work?
OleDbConnection connection = new OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties='Excel 8.0;HDR=NO'");
connection.Open();
DataTable schemaTable = connection.GetSchema("Tables");
DataRow[] schemaRows = schemaTable.Select();
foreach (DataRow schemaRow in schemaRows)
{
string tableName = schemaRow["TABLE_NAME"].ToString();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(
"SELECT * FROM [" + tableName + "]", connection);
int headerRowNum = 1;
DataTable dataTable = new DataTable(tableName);
dataAdapter.FillSchema(dataTable, SchemaType.Source);
DataColumnCollection dataColumns = dataTable.Columns;
int columnCount = dataColumns.Count;
foreach (DataColumn dataColumn in dataColumns)
{
dataColumn.DataType = typeof(string);
}
dataAdapter.Fill(dataTable);
int rowCount = dataTable.Rows.Count;
if (!((rowCount > headerRowNum) &&
((columnCount == 24) || (columnCount == 40))))
{
dataTable.Dispose();
dataAdapter.Dispose();
continue;
}
DataRow headerRow = dataTable.Rows[headerRowNum];
// ... examine tableName in the debugger; it is correct including $ ...
// ... examine headerRow in the debugger; some elements are null ...
string x = (string)headerRow[5]; // crashes
Using Odbc, besides the obvious changes to the DotNet class names and the
connection string, one more change is this:
int headerRowNum = 0;
instead of
int headerRowNum = 1;
The reason of course is that HDR=NO in Odbc means HDR=YES.
So with those changes, I read the Excel file in Odbc instead of OleDb. The
corruption in Odbc is identical to the corruption in OleDb.
Does anyone know how to get either OleDb or Odbc to read an Excel file?
I think I have more news though. Although Excel 2003 saved the .xls file,
now I guess that Excel 97 or 2000 or XP first created the .xls file. Here's
the reason: In all of the cells which I'm trying to interpret, either the
cell value was alphabetic, or the cell value had an apostrophe followed by
digits, so the cell values were already text. For some other cells, Excel
2003 displays them with a little triangle in one corner, and a tooltip
explains that the cell is text due to one of those reasons. However, for
the cells which I'm trying to interpret, Excel 2003 doesn't display that
triangle ... at first. However, if I put the mouse cursor in one of those
cells and double-click, but do not type anything, and then move the mouse
cursor to another cell and click, then suddenly Excel 2003 displays that
little triangle in one corner of the cell that I double-clicked, and a
tooltip explains that the cell is text due to one of those reasons.
Furthermore, if I hit Ctrl+S to save the file, then suddenly OleDb starts
correctly reading the cell that I double-clicked.
So, was there a bug in Excel 97 or 2000 or XP, which corrupted the format
when saving some cells which were text due to having apostrophe plus digits,
but which saved some other cells correctly? And Excel 2003 contains a hack
to read corrupted .xls files correctly? But OleDb is missing that hack so
it gets all confused when reading files which were saved by Excel 97 or 2000
or XP (or saved by Excel 2003 without having double-clicked a corrupted
cell)?
Is there some way to get the same hack copied into OleDb so it will start
working? Tag: Selecting active query for data adapter Tag: 143843
Problem connecting to SQL
Hello,
I have several ASP.NET 2.0 sites That connect to a SQL 7 database. Up
untill a few days ago they had been functioning just fine . For some reason
all my SQL connections are returning the following error sporaticly:
----------------------------------------------------------
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)
----------------------------------------------------------
This is not happening all the time but there have been an unusual amount
of these errors as of late. I have searched the we web but so far I have
been unable to find any documentation about this error that relates to SQL
7.
Any help would be appreciated.
-Scott Tag: Selecting active query for data adapter Tag: 143837
Locating record on form by key
Hi
I have a winform db app that I have created by dragging fields on the form
i.e. code has been generated by vs2008. How can I locate a record on the
form by the record id?
Many Thanks
Regards Tag: Selecting active query for data adapter Tag: 143836
DefaultValues
I currently have some trouble with DataSets and DefaultValues.
I have a DB on an MSSQL2000 Server with a table that contains several
Columns with default values - e.g. defined like
CREATE TABLE tbl_A (
[AGUID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_tbl_A_AGUID] DEFAULT (newid()),
[hidden] [bit] NOT NULL CONSTRAINT [DF_tbl_A_Hidden] DEFAULT (0)
)
I did use the assistent to create a new data source for my project based on
that DB.
Unluckely the defaultvalues are not fetched from the DB. I could understand,
that newid() was not fetched since it's a function - but at least the 0
should be set.
Can anybody help me on how to get the DefaultValues from the DB into the
DataSet? Tag: Selecting active query for data adapter Tag: 143832
Faster .Compute()
I have a class library that does calculations (usually AVERAGE) on
(sometimes) large sets of data. The nature of the application is that it
normally does several calculations (5-50 or more depending on user requests)
on the same set of data, so the basic implementation is that I grab all the
data out into a DataTable and then the library takes arguments and uses the
.Compute() method of the DataTable to return results. In most cases, I'm
doing an average of a column on a subset of rows. Subsequent calls do
averages on different subsets of rows.
This all works fine until the DataTable grows to 10k+ rows. Once I pass
that threshold (or thereabouts), the performance goes into the toilet. It
can literally take minutes to pull reports that would take < 10 seconds on a
dataset half the size.
The problem is that I don't want a single request to result in 50+ queries
to my database, yet I must do 50+ calculations. I can add application
servers pretty easily, but I only have the 1 database server. That's my
scarce resource so I want to minimize his work (even though he's better at
doing aggregation that my app server) and put my app servers to work.
I am well aware of the limitations of the DataTable and I am also well aware
that if I run these same queries directly against the database, I get much
better performance (orders of magnitude better). I'm just not sure how to
overcome this.
So my question is how can I pull out possibly large sets of data and do
calculations on the application servers so as not to overload my database
server but avoid this huge performance penalty that I get when working with
large DataTables? I'm willing to give up some performance for smaller sets
of data if necessary (the tradeoff would be worth it, I'm sure, if I can
figure out how to optimize the top end).
I have a small benchmarking application that I'm working with and I have yet
to find any method that out-performs DataTable.Compute(). I'm working under
the assumption that I need to start w/ a DataTable because I will need to
access the data several times (maybe I'm hosed right there - that's why I'm
asking this question).
Here is what I've tried thus far:
1) Manually call DataTable.Select() and get a DataRow[] object - brute force
iterate and do the average
2) Use LINQ against an EnumerableRowCollection<DataRow> pulled from the
DataTable and call .Average()
3) Brute force iterate over the DataTable myself and do the average
4) Call DataTable.CreateDataReader() and brute force iterate on the
DataReader to do the average
Of all of these methods, #1 results in the closest performance numbers to
.Compute() (and it's pretty darn close) and #2 results in the worst (almost
twice as bad) with #3 & #4 falling solidly in the middle of those two.
Does anyone out there have an idea on how to solve this problem?
Many thanks -
Paul Prewett Tag: Selecting active query for data adapter Tag: 143831
Renaming Excel Worksheets via ADO.NET
I've written a program to extract information from Excel Worksheets
over ado.net and most of it's working fine.
Is there a way to change the sheetname via ado.net? Tag: Selecting active query for data adapter Tag: 143829
Updating data adapters
Hi
I have several data adapters on my form. My question is, would a statement
like
Me.TableAdapterManager.UpdateAll(Me.MyDataSet)
update all adapters or do I need to update each adapter individually?
Thanks
Regards Tag: Selecting active query for data adapter Tag: 143823
Accessing data adapter in a dataset
Hi
I am trying to access the data adapter for a table in a dataset as below;
MyDataSet.MyTable.Clear()
MyDataSet.MyTableTableAdapter.Fill(MyDataSet.MyTable)
But I am getting a 'MyDataSet.MyTableTableAdapter' is not a member of
'MyDataSet'. What is the problem and how can I access the underlying data
adapter for the table MyTable?
Thanks
Regards Tag: Selecting active query for data adapter Tag: 143821
Which is more expensive?
Which is more expensive? Trapping an Exception to prevent duplicate entries
in a database, or using IF EXISTS in T-SQL?
Also, how would I go about testing which is more expensive myself?
Thanks Tag: Selecting active query for data adapter Tag: 143817
Performance implication of Parameters
Hi.
I was recently talking to a colleague who uses SQL Server as a backend to
his applications. He mentioned that adding parameters causes SQL Server to
determine the datatype and can cause performance issues, unless you
explicitly specify the datatype.
Does this also apply to Oracle, and if so, how can I specify it? Right now I
am doing the following:
int a = 4;
String b = "test";
DateTime today = System.DateTime.Today;
String sql = "insert into something (a, b, c) values (:a, :b, :c)";
OracleCommand aCommand = ... using above sql ...
aCommand.Parameters.Add("a", a);
aCommand.Parameters.Add("b", b);
aCommand.Parameters.Add("c", c);
Is there another way I could be doing this that would speed things up? Tag: Selecting active query for data adapter Tag: 143805
How can I update a an entire table in a dataset?
Hello, any help will be appreciated :)
I have a dataset filled with data from a database table. Now I want to do an
update command that will effect all rows in a specified table in this dataset
(something like: "UPDATE TableName SET RowName = RowName + 10")
How can I do it without executing it individually for each row?
Thanks. Tag: Selecting active query for data adapter Tag: 143800
TableAdapter and IN Statements
Hi,
I'm trying to configure a TableAdapter with an SQL statement fo the form:
Select * from SomeTable where ID IN "1,2,3,4";
Can I do this with a tableadapter?
Thanks in advance for your help!
Terry Tag: Selecting active query for data adapter Tag: 143788
insert many rows from tableadapter (vb2005)
I have to insert a lot of rows from a tableadapter to a sql table.
The operation is slow because table adptert send a single insert command for
each row.
Is there any way to have it with only one batch command? Or a faster
solution?
Thank you
Alessandro Tag: Selecting active query for data adapter Tag: 143783
Refilling dataset without changing context
Hi
I have a winform db app that is "bound" to a dataset...via data adpater,
binding source and all that. As this is a multi-user app I need to refill
dataset from time to time so the newest data is available for user to view.
My problem is that I
a) do not wish the app to become unresponsive while re-fill is being
performed so need to do it as background task if possible.
b) do not want the record context to change for user so user does not notice
any disruption.
My question is, are the above possible? If so, how? If not what is an
alternate strategy?
Thanks
Regards Tag: Selecting active query for data adapter Tag: 143769
Adding New Row, Set Value
Hi,
I am using the BindingNavigator control on my WindowsForm.
When I click the '+' button, it appears that the program automatically
adds another row to the datatable in the underlying typed dataset -
fine.
I need to set the value for one of the columns
programmatically. How do I reference this newly created row? This
must be done before I call Update to send it to the database.
Private Sub bnEmploymentAddNewItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
bnEmploymentAddNewItem.Click
??? = iPersonID
End Sub
Can someone help me with the syntax for referencing this newly
created row so that I can set the value for the column?
J Tag: Selecting active query for data adapter Tag: 143745
Access to vb.net converter
Hi
Has any one checked this access to vb.net converter?
http://www.microtools.us/anetvb7.aspx
Any good?
Thanks
Regards Tag: Selecting active query for data adapter Tag: 143729
System.Threading.ThreadAbortException: Thread was being aborted
Hi,
I am importing a flat file into a remote database using ADO.NET.
First, I use a dataadapter to load the contents of the flat file into
a datatable. Then, I use SqlCommandBuilder to update the data into a
database table. I am doing this in development so the file and the
script are local, while the database is remote.
The flat file is only 16,236 rows, which I didn't think would be any
trouble. But on the first run it thew this error:
System.Threading.ThreadAbortException: Thread was being aborted. Each
time I run it now, it imports fewer and fewer rows, until the last
time, it only imported about 800 before it bombed out threw this
error.
I've updated the time outs in the .config files and in IIS. No
change. If I limit the original select to 100 rows, it works fine.
It seems like a timeout, yet it does not give a time out error.
Any ideas?
Thanks,
Casey Tag: Selecting active query for data adapter Tag: 143728
Getting connection information from vs 2008 generated code
Hi
I have developed a winform db app by dragging fields onto a form in vs 2008
i.e. I have not written code to open db connection myself and instead have
let vs 2008 generate that code under the hood automatically. My question is,
is there a way to get a handle to the db connection created by vs 2008?
Thanks
Regards Tag: Selecting active query for data adapter Tag: 143727
Opening Access db via DAO in vb.net
Hi
In my vb.net app I am trying to open a db via DAO as below;
Imports dao
Dim dbSynch As dao.Database
dbSynch = DBEngine(0).OpenDatabase("C:\MYDB.MDB")
The problem is that on the last line I am getting the error 'DBEngine' is a
type and cannot be used as an expression. What am I doing wrong?
Many Thanks
Regards Tag: Selecting active query for data adapter Tag: 143726
Parameterised query with an ms access table
Hi
When using sql server, I can use a sql like below in my data adapter to send
the sql a parameter value;
SELECT <field list>
FROM <table>
WHERE Fieldx = @Para1
My question is, can I do something similar with an access db ie can I send a
parameter to a data adaptor sql which is connected to an ms access table? If
not is there another way to do this?
Thanks
Regards Tag: Selecting active query for data adapter Tag: 143717
T-SQL Return row affected count in CLR proc?
Is there a way to say to the caller just like an Insert/update/delete
command would do from a T-SQL CLR Stored Procedure how many rows my
procedure affected? I know I can push text down the SqlContext's Pipe as
messages but how do you say here is my rows affected count? thanks! Tag: Selecting active query for data adapter Tag: 143706
Problems on adding a new dataset using MySQL via ODBC
Hi all!
I am using Visual Studio 2008 and I am having problems on creating a
dataset. I have a MySQL database accessing through ODBC. On adding a new data
source, when I try to select a table on the database it gives the following
error:
<`database`..`table`>
Could not get column information for database object named
'`database`..`table`'
Does anyone knows what is happening?
Thanks in advance,
Vanessa Tag: Selecting active query for data adapter Tag: 143700
Total newbie, getting error "Reference to a non-shared member requires
I'm trying to jump start with VB.Net/Ado.Net to get something done at
work.
I'm using the book "Programming Microsoft ADO.Net 2.0". One problem
with this book is that his sample code does not spell out the imports.
Usually somewhere in the paragraphs or pages before, it says "This
assumes you're importing blah.blah.blah."
Well, I'm not finding the reference.
I've used the Data Source Configuration wizard to create BisDataSet.
It's blowing up on line 9, on "BisDataSet" saying "Reference to a
non-shared member requires an object reference."
Trying to look that error message up on the Microsoft gives me a generic
definition and little to go on.
Here's my code:
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Data
1 Module Module1
2
3 Sub Main()
4
5 End Sub
6 Sub write_Scorecard_RRTP()
7
8 Dim RRTPRowset As BisDataSet.RRTP_ScorecardRow()
9 RRTPRowset = BisDataSet.RRTP_Scorecard(0)
10
11 End Sub
12 End Module Tag: Selecting active query for data adapter Tag: 143683
Data table export to CSV?
Is it possible to export a single (not related to anything else) data
table's contents to a CSV file? I have XML export working fine, but they
want the files in CSV format.. thanks! Tag: Selecting active query for data adapter Tag: 143673
How do I stop VS2005 from backward-quoting identifiers on update
I'm using the VS2005 wizard/design tool for generating typed
datatables and adapters for a desktop application. At design-time I
point the development environment at an MSJet database, but I've been
careful to make all of the queries that I add to the adapters generic
SQL which should work against most environments (or at least MS SQL
Server, which I also want to target)
BUT, the update/insert/delete statements that are being handily
generated by the wizard are cloaking all of the identifiers with
backward quotes which work OK with MSJet but not when I shift the
connection to MS SQL Server at run time. Is there any way of stopping
that backward-quoting behaviour at design time? I cannot see it... Tag: Selecting active query for data adapter Tag: 143667
Oracle connection Fails
Hi All,
I have built a simple .Net console application. In which i am trying
to connect to the oracle database by using Oracle data provider
for .Net from ORACLE site and it works fine on my machine. I have
installed the same on my main server but when i run this application
from Our main server i am getting an exception. The exception is as
follows.
'Oracle.DataAccess.Client.OracleException at
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32
rrCode, OracleConnection conn, IntPtr opsErrCt
x, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32
errCode, Oracle
Connection conn, IntPtr opsErrCtx, Object src)
at Oracle.DataAccess.Client.OracleConnection.Open()
at TestODACWithWindows2000.Program.Main(String[] args)'
I am not able to trace out the exact cause why it is failing from that
server. While the oracle server is pingable from that server.
My application is as follows
using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data.SqlClient;
using System.Xml;
using System.Data;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.IO;
namespace TestODACWithWindows2000
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Opening........");
string ConnectionString = "Data
Source=(DESCRIPTION="
+
"(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Host)(PORT=1521)))"
+
"(CONNECT_DATA=(SERVER=DEDICATED)(SID = SID)));"
+ "User
Id=username;Password=password;";
OracleConnection ora = new
OracleConnection(ConnectionString);
try
{
ora.Open();
}
catch (Exception ee)
{
Console.WriteLine("{0} Exception caught.", ee);
Console.ReadLine();
}
}
}
}
Please help me out.. Its urgent. Tag: Selecting active query for data adapter Tag: 143666
Programmatically create Access 2007 database
Greetings...
From this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317881
article I've knew how to create access 2000 databases. But what with
2007?
Best regards... Tag: Selecting active query for data adapter Tag: 143662
Trouble Using DataAdapter.Update()
Hi, and thanks in advance for any help :-)
From a high level, heres what I'm trying to do. I have a flat comma
delimited file that I need to import into a database, but after some of the
data is massaged and mingled with data in the destination table. So, I first
import the flat file data into a dataset using one dataadapter and one
dataset. Then, I use another dataadapter and dataset to import the current
data from the eventual destination table. Then, I do the logic and place the
resulting data into the *second* dataset.
So far so good. Now, all I have left to do is update the database table
with the new data. Easy, right?
But I can't make it happen. I know it's a lot of code, but here's what I've
got:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' First, import the flat file data
Dim TextConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" &
"C:\Inetpub\wwwroot\GrandCentralCoupons\comissionjunction\" & ";" & _
"Extended Properties=""Text;HDR=Yes;FMT=Delimited;"""
Dim TextConn As New System.Data.OleDb.OleDbConnection(TextConnectionString)
TextConn.Open()
Dim dad1 As New System.Data.OleDb.OleDbDataAdapter("Select top 5 * from
cj.txt", TextConn)
Dim dsSource As DataSet = New DataSet
dad1.Fill(dsSource)
TextConn.Close()
dad1.Dispose()
' Verify that the data is corect by binding to a gridview for visual display
GridView1.DataSource = dsSource
GridView1.DataBind()
' Now, get the currentdata from the destination database table
Dim dbConn As New
SqlConnection(WebConfigurationManager.ConnectionStrings("GCC_Remote").ConnectionString)
Dim dad2 As New SqlDataAdapter()
dad2.SelectCommand = New SqlCommand("SELECT * FROM import_temp_cj2", dbConn)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(dad2)
dbConn.Open()
Dim dsDestination As DataSet = New DataSet
dad2.Fill(dsDestination, "temp_import_cj")
' verify the data is correct by binding to a second gridview for display
GridView2.DataSource = dsDestination
GridView2.DataBind()
' mix. mingle, manage the data form the two datasets
'/* lots of code snipped for easy of viewing */
' verify the new data is correct by binding to a third gridview for display
GridView3.DataSource = dsDestination
GridView3.DataBind()
' update the database table
dad2.Update(dsDestination, "temp_import_cj") '<--- NOT WORKING!!!
dbConn.Close()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All three gridviews display exactly what they should be displaying. No
errors at compile or runtime. But, the new data just never makes it into the
db table.
I have a hunch I am missing something really simple... but I can't pick it
out. This is the article I am using as a guide:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclasstopic.asp
Thanks! Tag: Selecting active query for data adapter Tag: 143659
Vengadeshwaraa Astrologers
Vision
To be the leader in the field of Astrology, to be the best
Astrological service providers and to be ranked as the pioneer
Astrologer in the world. To deliver various aspects and knowledge
bases of Sri Lanka to the whole world and to be the Astrological force
that binds all human beings together, wherever they be.
Mission
To become an Astrologically meaningful resource and be a vital part of
lives of our members and users. Providing efficient & trusted service
24 hrs a day, 7 days a week. Our endeavor has become fruitful in
making solutions for users to shop on line with direct delivery
facilities while offering you the opportunity to know yourself, to
grow and to improve your life.
Shree Vengadeshwaraa Astrologers founded on 08th April 1972 has since
gained numerous audience due to the untiring efforts by us and we
believe that no other Astrology service can provide you with the value
that Vengadeshwaraa can. So much so, our service is backed by an
unconditional guarantee.We have predicted thousands and thousands of
horoscopes and have never failed.We want this science to be taken to
all mankind irrespective of religion. This is based on mathematical
calculations and in no way concerned to the religion.
We provide a pleasant, satisfying and superior Astrology experience to
our customers while protecting their privacy and security.
Shree Vengadeshwaraa Astrologers consist of Sri Lankan and Indian
Astrologers and count over 35 years of dedicated and friendly service
out of which 20 years has been spent on research alone.We have a very
eminent team of Astrologers who are well educated and traditionally
inherited this divine/legendary art of science.Our Astrologers are not
only educated but also traditionally qualified. Our Astrologers are
supposed to be best in this profession.
Shree Vengadeshwaraa Astrologers has 03 branches worldwide namely, Sri
Lanka, India and Germany.
Head of Sri Lanka branch Brahmashree V.S.Sharma is a very popular
Astrologer in Jaffna, Sri Lanka
Head of India branch Padmanaapa Saastrikal
Head of Germany branch Brahmashree V.Mahadeva Sharma
Our consultant service to International customers are absolutely free
of charge. We have geared ourselves to launch our new branches in
London, Canada, Singapore, Malaysia and Australia next year.
Shree Vengadeshwaraa also offers services in the field of Matrimonial,
Hindu Religion Development and Hindu Brahmins social network. We take
pride in our endeavour for the introduction of "TOP 100 ASTROLOGERS
RATING PROGRAM" for the first time.
Visit vengadeshwaraa.com
Please take some time to get to know us better through the links that
we have provided on this page and enjoy very competitive product
price, special promotions and of course 100% satisfaction on accurate
calculation of Horoscopes.
Our Astrological and sincere thanks for visit Now:http://
www.vengadeshwaraa.com Tag: Selecting active query for data adapter Tag: 143658
Insert c# DateTime object to Oracle Table using OleDbDataAdapter
I have a table defined as:
create table product_price
(
productId int,
price number(20,10),
price_date date,
primary key (productId)
);
I am reading through a price file in c# and I would like to insert a row
into the table for every price+date+productId combination. I am using
the DataAdapter insertCommand and Table.Rows.Add(newRow) method but it
fails when setting the date of the new row.
// Setting the insert command
priceDA.InsertCommand = priceDA.SelectCommand.Connection.CreateCommand
();
priceDA.InsertCommand.CommandText = "insert into product_price
(productId, price, price_date)" +
"values (@productId, @price, TO_DATE('@priceDate', 'YYYYMMDD'))";
// Adding the row
private void addPriceRow (int prodId, decimal price, DateTime priceDate,
DataTable priceTable)
// priceDate and other params defined elsewhere
DataRow newPriceRow = priceTable.NewRow();
newPriceRow["productId"] = prodId;
newPriceRow["price"] = prodPrice;
newPriceRow["price_date"] = priceDate;
priceDA.Update(priceDS, "product_price"); // Dies here with an oracle
error ORA-00936 missing expression
priceDS.AcceptChanges();
I think this is some sort of date format error, so I tried: newPriceRow
["price_date"] = priceDate.ToString("yyyyMMdd"); but that line fails at
runtime with: "String was not recognised as a valid DateTime"
Any ideas how I can save this date to the table?
Many thanks in advance.
--
JohnH Tag: Selecting active query for data adapter Tag: 143657
Inserting records from one database into identical table in another datrabase
Hi!
I'm transferring the records in a table from one database to another.
I create a data adapter using a connection from the first database to fill a
dataset.
I add an InsertCommand with a different connection to the second database,
and add the necessary parameters.
I hoped that when I called the update method, the records in the dataset
added from the first database would be inserted into the identical table in
the second database.
This didn't work, but no exception was thrown. Just no records were added.
My code is like this modified code from msdn - should this work?
Private Sub AdapterUpdate(ByVal connectionStringA As String, ByVal
connectionStringB As String)
dim connectionA As SqlConnection = New SqlConnection( _
connectionStringA)
dim connectionB As SqlConnection = New SqlConnection( _
connectionStringB)
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", _
connectionA)
adapter.InsertCommand = New SqlCommand( _
"INSERT Categories (CategoryName) values (@CategoryName),
connectionB)
adapter.UpdateCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")
Dim categoryTable As New DataTable
adapter.Fill(categoryTable)
adapter.Update(categoryTable)
End Sub Tag: Selecting active query for data adapter Tag: 143654
SQL CLR Sproc isn't running right
I have a stored procedure for SQL Server 2005 which is done with the CLR...
when its executed as a admin it works. when its executed as a non-admin it
doesn't work... any ideas? the user has permission to execute the procedure
on the server... seems to be something with the .NET SPROC's that is going
wierd Tag: Selecting active query for data adapter Tag: 143652
list box primary key vb .net
Does anyone know how to populate a list box using SqlDataReader. I am
using VB .NET 2005. Along with the list box value that is visible I
need it's primary key value that is not visible. Tag: Selecting active query for data adapter Tag: 143646
Hi
In vs 2008 dataset designer allows multiple sqls for a table's data adapter.
How does one tell the data adapter which sql to use?