|
I am writing a C#/.NET program for SQL Server which performs a bulk move of data from one database to another. This is intended to be a utility end users of our product can use to perform routine maintenance on the database and will alao have options to perform summarization on the data in the alternate database after it has been moved. The program dynamically builds an INSERT and a DELETE command and issues them to the database using the 'ExecuteNonQuery()' command. If I use the Query Analyzer to test the INSERT/DELETE commands, they complete without error.
The problem: The 'ExecuteNonQuery()' of the ODBC classes only returns an 'int' representing the number of rows effected. However, the number of records copied is often too large to fit within an integer. This, in turn, causes a '-1' to be returned instead. A careful examination of the data reveals that the INSERT actually did copy the data (most of the time), but the '-1' causes the program to not issue the DELETE command, because it uses the return value of 'ExecuteNonQuery()' as another indicator of an error condition.
This is unreliable and inconsistent (unfortunately the only consistency Microsoft follows is that they are consistently inconsistent) and can lead to duplicate information. Is there a way around this??? Is there a way to determine the TRUE number of records effected by the call??? Is there another, more reliable, way of determining whether the command completed successfully???
NOTE: my exception handler is not getting triggered in this case, nor does it look like the connection status is changing (I was assuming possibly a timeout was happening) since my connection status handler is not getting called either.
|
|
|
|
|
How about a sproc (Stored PROCedure) with an output parameter for the number of rows affected?
your sproc definition would look something like:
CREATE PROCEDURE spInsertData(@rows int output) as<br />
whatever your insert code is<br />
set @rows = @@rowcount<br />
Then create a SqlCommand object in your c#. Actually, if you drag and drop from the ServerExplorer, VS should do all the work for you.
Just ExecuteNonQuery, close the connection and look at the [1]'th element of the parameters collection
Bill
|
|
|
|
|
OOPS...hang on...if you're overflowing int, you're inserting more than 2 billion rows?
|
|
|
|
|
I think the "int" being returned is an int16 and not an int32. Here is why. I just ran several more tests and found one, consistently repeatable situation where it is copying over 450000 records, but the routine's return value was just over 2000!!!
I confirm this by using query analyzer to delete the contents of the destination table. Then I run the procedure. After nearly a minute, it returns and the number reported by the routine is just over 2600 rows effected. However, if I do a "SELECT count(*)" on the table in query analyzer, it reports over 461000. The 461000+ figure is correct!!!
It is acting exactly as if it is overflowing an int16 number.
Oh yeah. On the parameter thing. I think only parameters are set/returned by stored procedures. If I add a parameter, it's value is unchanged upon return from the command call, to spite the fact I set the parameter to contain the command returnvalue.
|
|
|
|
|
The first (stored procedure) won't work, because the record selection criteria is too flexible. When I said I "dynamically" built the command strings, I literally allow the user to choose one of several fields in the table and present them with allowed values. Those fields not selected by the user are not added to the "Where" clause. Then, I reuse the exact same "Where" clause on the DELETE command. This alone would require the creation of several stored procedures to make it as flexible as I need it to be.
The fewer the additional fields selected, the more records will be included in the process. The only non-optional selection criteria is record date.
The second part of your suggestion, about trying to pass in parameters the value... I am researching this. It could work, but I am a bit new to the passing of parameters in a db exec command call, so that will take a bit of experimentation to see if it will accomplish the task.
|
|
|
|
|
well...I am not sure what to tell you. All the doc's I've seen claim that ExecuteNonQuery returns int32. Perhaps someone else has some insight?
If you want, sent me the source code... I may be able to figure out what's going on from that.
Best,
Bill
|
|
|
|
|
I just tried one other thing. I ran the program in debug mode, and just after building the INSERT string, but just before calling the ExecuteNonQuery() call, I copied the string to SQL Query analyzer and ran it directly from there. It too copied the correct 461000+ records, but when I had the program continue and run the Execute command, it reported the usual 2600+ records. So, the INSERT command is correct as far as I am concerned.
I also tried to call the ExecuteReader() command and tried to extract the RowsEffected from the returned datareader. I saw it too is an 'int', and it too only reported 2600+ rows effected, even though 461000+ records were copied.
|
|
|
|
|
Yep. I just tried adding the following code to a stand alone program...
long fredl = 461000;
short freds = (short)fredl;
fredl = (long)freds;
Out.Text = fredl.ToString();
and got 2200+ as the result in 'Out', which is the kind of result I am getting from my ExecuteNonQuery() call. I think that when the procedure gets the return from the command, it is funneling it through a short int at some point and then returning it as an Int32. That explains why the return is so unstable.
|
|
|
|
|
I may have found a solution that seems to be working. I added the following to the end of the INSERT... and the DELETE... strings.
SELECT ROWCOUNT_BIG() AS CountOfRows
and then executed this using the datareader execution command. This returns the correct value in a datareader object where I simply retrieved the value as if it was table information. More testing to follow but this seems to have solved the problem.
|
|
|
|
|
Hi,
How to replace a string while retrieving data from an access database in ASP. NET.
I wrote the query as
Select replace(empname,"tt","mm") from employeetable;
but its giving the error as undefined function
pls do reply for this
regards
mijo thomas
|
|
|
|
|
Select IIF([empname]='tt','mm',[empname)) as empname
From employeetable;
This will give you mm if the value of empname is 'tt'; otherwise it will give you empname.
Hope that is what you are looking for 
Barbara
|
|
|
|
|
I made a program using CDaoRecordSet with ACCESS97 database.
And I want to query table data by array long[] that I have.
long IDID[5]={7, 12, 14, 27, 33};
and I want to Do like this.
CString ralp;
//ID2 is a feild name of the table is named move in xx.mdb
ralp.Format("ID2 LIKE %ld OR %ld OR %ld OR %ld OR %ld",IDID[0],IDID[1],IDID[2],IDID[3],IDID[4],IDID[5]");
m_pSet->m_strFilter=ralp;
m_pSet->Requery();
But it goes wrong, it has just a one record though it must have 5.
I want to query by my known numbers.
Please help me. thanks.
|
|
|
|
|
Personally, I'd use
"ID2 IN (%ld,%ld,%ld,%ld)"
but then, I'm picky. The requery is likely to fail anyway, since (1) LIKE is applied to a string field, NOT a numeric type, and (2) the syntax should be
ID2 LIKE 'x' OR ID2 LIKE 'y' OR ID2 LIKE 'z'
Be aware that there is an upper limit (although I forget what) as to how many elements you can put in an IN clause.
Using IN is more convenient if there isn't a fixed number of elements, since you can append ",value" data easily.
Steve S
|
|
|
|
|
Does anyone know of a good article or can give an idea on how to generate multiple headers (2 rows in my case) using a datagrid?
Regards,
Tiruvan
|
|
|
|
|
Are you wanting 2 rows in the grid as well?
Anyway, the only way I can think of off of top of my head is to create a template column and add a 2nd control to the header portion of the template.
In the property builder, create at least one template column. Afterwards right click on the grid => Edit Item Template => select a template column. Drag what ever controls you would like from the toolbar and set the binding up for those controls.
Michael
|
|
|
|
|
Hi Tiruvan,
I've just uploaded my first article, which addresses adding separator rows to a Datagrid. You could use the same technique to add an extra header row. Take a look at this article and let me know if you have any questions:
http://www.codeproject.com/aspnet/DatagridSeparator.asp
Datagrid Girl
|
|
|
|
|
I work on an office PC and we have our SQL Server installed on our Development Server.
I am working on an ASP.NET application which requires me to use DataGrids. I've created a connection to one of the databases on the SQL Server and set a particular timeout.
Why is it that when I run my application it sometimes runs but other times it gives the error: "SQL Server not found or does not exist".
Regards,
Tiruvan
|
|
|
|
|
check that MSSQLServer service is running 
|
|
|
|
|
I clearly am missing something (hopefull obvious to most) as how the adapter update function works. I have .net 1.1 and to test I just select the table name from a combobox and then refresh the data grid. The display works fine in showing the data and in showing changes but I fail when I want to submit the changes back.
I am using two functions 1) to get the data from the database
private void TableComboBox_SelectedIndexChanged(object sender, System.EventArgs e)
{
m_tableName = TableComboBox.SelectedItem.ToString();
if(m_tableName.Length<1) return;
m_dataGrid1.SetDataBinding(m_dsTables, "");
try
{
string sql = "Select * FROM " + m_tableName;
if(m_dataAdapter!=null) m_dataAdapter.Dispose();
m_dataAdapter = new OdbcDataAdapter(sql, m_Connection);
m_dsTables.Clear();
m_dataAdapter.Fill(m_dsTables, m_tableName);
m_dataGrid1.DataSource = m_dsTables.Tables[m_tableName].DefaultView;
}
catch(Exception ex)
{
string myval = ex.ToString();
}
}
and then press a button to "commit" the chagnes.
private void CommitButton_Click(object sender, System.EventArgs e)
{
string myval;
try
{
m_dsTables.AcceptChanges();
m_dataAdapter.Update(m_dsTables,m_tableName);
}
catch(Exception ex)
{
myval = ex.ToString();
}
}
I am new to this so any pointers to read are fine or a sample code segment.
Thanks.
|
|
|
|
|
private void CommitButton_Click(object sender, System.EventArgs e)
{
string myerrormes;
DataSet cdataSet=null;
int cnt=0;
try
{
cdataSet = m_dataSet.GetChanges();
cdataSet.WriteXml("C:\\gridC.xml");
m_dataSet.WriteXml("C:\\gridM.xml");
m_dataSet.AcceptChanges();
cnt = m_dataAdapter.Update(m_dataSet,m_tbname);
}
catch (Exception ex)
{
myerrormes = ex.ToString();
}
}
Well not the progress I had hoped for. When I write out the changed and complete data sets to an XML file they do have the correct updates. So I am missing something with the AcceptChanges and Update methods. The database does not get the updates. I have the same effect with Oracle, SQL Server and Jet.
|
|
|
|
|
Ok...Silly questions firt:
How did you generate the DataAdapter? Did you code it by hand or drag-n-drop from the ServerExplorer?
If you drag-n-dropped, you need to be sure that the table has a primary key defined if you want the update and delete commands to be generated correctly by VS.NET.
Another silly question: is that a windows app or a web app?
Hope this helps...
Bill
|
|
|
|
|
Bill Dean wrote:
How did you generate the DataAdapter?
Hand coded it. Just did a drag and drop with no differences. I am in both cases entering the connection string via code. The table I am experimenting with does have a primary key defined.
Bill Dean wrote:
Another silly question: is that a windows app or a web app?
My sample is a little windows app just to display a single table. The goal is to learn the correct code and then it will go into a Web Service.
I am sure my problem is something simple, just am overlooking something in the samples provided vs what I am doing.
I may try a Oracle or SqlServer Client vs the ODBC and see what differences I find.
Thanks for the help.
"Don't be so anti-american, would you?
KaЯl (to Paul Watson on Baseball Bats) 26 Nov '03 "
|
|
|
|
|
Hi Michael,
ok...so you dragged a dataadapter from the toolbox to the form? Or did you drag the table onto the form from the Server Explorer? I am guessing from your previous response that you dragged the blank dataadapter from the toolbox...yes?
Forgive me if I am being too basic here....
A dataadapter has four Command objects associated with it: select, insert, update and delete commands. If you construct the dataadapter in code by specifying the sql command in a string along with the connectionstring it ONLY generates the SELECT command.
I've never used the OdbcDataAdapter, but for the OLEDB and SQL dataadapters, there is a CommandBuilder class that will generate the other 3 commands for you (provided the table has a primary key).
Before you do the update, in your CommitButton_Click method:
OdbcCommandBuilder CB = new OdbcCommandBuilder(m_dataAdapter);
Creating CB should create the other three commands on m_dataAdapter, so the update should work.
Good luck,
Bill
ps: see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataodbcodbccommandbuilderclasstopic.asp
|
|
|
|
|
Bill Dean wrote:
If you construct the dataadapter in code by specifying the sql command in a string along with the connectionstring it ONLY generates the SELECT command.
Before you do the update, in your CommitButton_Click method:
OdbcCommandBuilder CB = new OdbcCommandBuilder(m_dataAdapter);
For the record; this is exactly what I was missing.
Bill Again Thanks.
"Don't be so anti-american, would you?
KaЯl (to Paul Watson on Baseball Bats) 26 Nov '03 "
|
|
|
|
|
Hi,
the problem is in the order of these 2 statements:
> m_dsTables.AcceptChanges();
> m_dataAdapter.Update(m_dsTables,m_tableName);
You have to update before accepting the changes in the DataSet.
AcceptChanges means: dataset, forget the information you have collected about the modification of the data since you have received it from the DataAdapters or the last call of AcceptChanges.
So if you call this method before the Update of the DataAdapter, it appears to the adapter that there were do modifications at all and nothing has to be written back in the database.
Concerning this topic also have a look at the EndCurrentEdit-method of the BindingManager and the GetChanges-method of the dataset.
Hope I could help
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
