C# ADO.net Adapter Update Question. - C# Discussion Boards

Use this:

SQL

WHERE (REPLACE(c.c_name,' ','')=REPLACE(@name,' ',''))

REPLACE(c.c_name,' ','') this method is replace space of the field name(c.c_name) to select not replace resutl
can you give me any ideas?

I cannot understand what you are trying to say. Can you be more clear ?

ok, this is my original question:
I want to validate category name before insert all information about category into database.
The following condition is the way that i want to compare:
Ex: ------------------------------------------------------------------------
| name in TblCategory | name in Text box | comparative result |
------------------------------------------------------------------------
| white bag-----------| whitebag---------|---existing---------|
------------------------------------------------------------------------
| whitebag----------- | white bag------- |----existing--------|
------------------------------------------------------------------------
| White Bag-----------|--white bag ------|---- existing ------|
------------------------------------------------------------------------

I use this following function in sql database:
BEGIN
IF EXISTS(SELECT TOP 1 *
FROM TblCategory c
WHERE (c.c_name=@name OR c.c_name=REPLACE(@name,' ',''))
AND c.c_id <> @id
)
SET @result=1
ELSE
SET @result =0
END

this give me bad result if TblCategory (white bag) and TextBox to compare has word(whitbag)...this can't compare to me

then you you tell me to do this :

WHERE (REPLACE(c.c_name,' ','')=REPLACE(@name,' ',''))

but this replace is only replace my field name(c.c_name) but not give me validation to insert category infomation

So, you want to match 'white bag' and whitbag' ? Use SOUNDEX function in T-SQL.

SQL

WHERE (SOUNDEX(REPLACE(c.c_name,' ',''))=SOUNDEX(REPLACE(@name,' ','')))

Thanks for your help, sounddex give me good result in my project. but if the name to compare is 'Cat' and 'Catty' or
'book' and 'book2' or 'book3' soundex give the bad result (duplicate name in sound).
Now i can solve this problem by create function bellow:

1/funtion name_compare

USE [MagicStore]
GO
/****** Object: UserDefinedFunction [dbo].[fn_name_compare] Script Date: 12/01/2009 09:06:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_name_compare]
(
@str1 NVARCHAR(MAX),
@str2 NVARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
DECLARE @result BIT
IF ( REPLACE(@str1,' ','')=REPLACE(@str2,' ',''))
SET @result =1
ELSE
SET @result=0

RETURN @result
END

2/Call function name compare to validate existing name:

USE [MagicStore]
GO
/****** Object: UserDefinedFunction [dbo].[fn_exist_name] Script Date: 12/01/2009 09:08:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_exist_name]
(
@option INT,
@id BIGINT,
@name NVARCHAR(MAX)
)

RETURNS BIT
BEGIN
DECLARE @result BIT

IF(@option=1)
BEGIN
IF EXISTS(SELECT TOP 1 *
FROM TblCategory c
WHERE 1=dbo.fn_name_compare(c.c_name,@name)
AND c.c_id<>@id
)
SET @result=1
ELSE
SET @result =0
END

else IF(@option=2)
BEGIN
IF EXISTS(SELECT TOP 1 *
FROM TblSubCategory sc
WHERE 1=dbo.fn_name_compare(sc.sc_name,@name)
AND sc.sc_id<>@id
)
SET @result=1
ELSE
SET @result =0
END

ELSE IF(@option=3)
BEGIN
IF EXISTS(SELECT TOP 1 *
FROM TblItem i
WHERE 1=dbo.fn_name_compare(i.i_name_eng,@name)
AND i.i_Id<>@id
)
SET @result =1
ELSE
SET @result=0
END

ELSE IF (@option=4)
BEGIN
IF EXISTS(SELECT TOP 1 *
FROM TblItem i
WHERE 1=dbo.fn_name_compare(i.i_name_kh,@name)
AND i.i_Id<>@id
)
SET @result=1
ELSE
SET @result=0
END

RETURN @result
END

this give me good result with all case of name validate.

I'm new to C# and am trying to access and update a Access Database. I've managed to create the database and can see the results in Access. I'm also able to open the Database and load the tables, but when I try to do an update it does not post back my changes. Below is the code with comments where things seem to fail. I do not get an exception, I just don't see the changes in the database file.

// This method seems to work fine, it loads the data from the database into the six tables.
public void openDatabase(DataSet ds, string filename)
{
// I have 6 tables in the dataset/database.
string[] names = { "Project", "Portfolios", "Equations", "Scenarios", "Reports", "Rules" };
string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename;

for (int i = 0; i < names.Length; i++)
{
string strAccessSelect = "SELECT * FROM " + names[i];

try
{
myAccessConn = new OleDbConnection(strAccessConn);
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
return;
}

try
{
OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn);
myDataAdapter = new OleDbDataAdapter(myAccessCommand);

myAccessConn.Open();
myDataAdapter.Fill(ds, names[i]);
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return;
}
finally
{
myAccessConn.Close();
}

}
}

// This is the method I'm having trouble with, I sent the Dataset off to another routine which changes some
// of the datarows, I can see the changes there in the dataset but update does not write them to disk.
public void updateDatabase(DataSet ds)
{
try
{
dw = new DebugWindow(); // This opens up a window to display the dataset
dw.Show();
dw.dumpDataSet(ds); // This dumps the dataset to the window, the dataset looks correct with my changes

myAccessConn.Open(); // I don't think I need to do an open as the update should do this but I'm trying all kinds of things
ds.AcceptChanges(); // Don't think I need this

// This seems to be the line that does not work. I've also tried (ds, "Rules") as well. Ideally, I would
// just pass in the dataset and all the tables would be updated.
myDataAdapter.Update(ds.Tables["Rules"]);
myAccessConn.Close();
}
catch (System.Exception ex)
{
dw.displayMsg("Update Failed" + ex.Message);
}
}

DataAdapters are very simple; they can only do very simple things. They are really only good for writing quick-and-dirty demoes at Microsoft launch events. Any non-trivial application will quickly become complex enough that a DataAdapter will be of no use -- especially Update.

I don't use DataAdapters and neither should you (or anyone else for that matter); they cause more trouble than they're worth. You would be much better off in the long run, learning how to access databases at a lower level.

ExecuteScalar, ExecuteNonQuery, DataAdapter.Fill, and DataAdapter.Update all use ExecuteReader (and therefore DataReaders); learn to use DataReaders.

(I think part of your problem above may be that myDataAdapter gets changed between calls to openDatabase and updateDatabase. But the whole thing should be rewritten. Dead | X|

)

Thank you for your insight. I'll look into using a DataReader. When I first implemented this I actually read the .mdb in binary format and converted it line by line. It seems most books point to the DataAdapters, but as you say these seem only good for the quick and dirty. Thanks.

Oi Adam
Listen to what Piebald said, quick and dirty or DO NOT USE THEM. Learn about a Data Access Layer (DAL) and implement one of the many existing ones available here in the articles.

Most accomplished developers build there own, I see a lot of new devs using the Enterprise library for their DAL these days.

You can listen to the other peoples' advice to use ExecuteNonQuery(), etc. However, I am going to just talk about your piece of code.

The adapter maintains four different query commands internally. One of them is the update query you implicitly called when you call myDataAdapter.Update(...). However, in order for it to work, the adapter has to know how to correctly construct the update query internally. What you need to make this program to work is to add the following line to tell the adapter how to construct the query internally:

OleDbCommandBuilder builder=new OleDbCommandBuilder(myDataAdapter);

Add the above line right after the line:

myDataAdapter = new OleDbDataAdapter(myAccessCommand);

After that, your Update(...) call should work.

Thanks dxlee, I gave this a try but update still does not seem to write to the disk. I tried to cut things down to one table as I think flipping between them is causing problems. I'll look more into the OleDBCommandBuilder. Thanks.

We need to use MSMQ in one of my project. Is there any Library(Similar to enter prise library DAAB) available that will make the task easy and manageable.

Also it would be great if you can point me to some MSMQ starter kits or real life sample applications that uses MSMQ

Thanks in advance

.Net has functionality built in for this -> MessageQueue[^]

Badger | [badger,badger,badger,badger...]

Spring.NET might help, it has some MSMQ support although I have never used it. Spring JMS is very easy to use, so if the .NET MSMQ equivalent is similar it is probably worth looking into.

I published my C# application using Visual Studio 2005 to a server. I then ran the setup to install the app on my computer ... no problem, app runs fine. Then, I had another analyst in the office do the same to their computer ... again, no problem and app runs fine. The manager tried it to his computer and although the install seemd to go fine, there is a problem, app does not appear.

When he starts the application, nothing appears on the screen. The app appears in the task manager and is running but it is no where to be found on the screen nor when Shift-Tab are pressed.

Am I missing something? This reminds me of imaging a computer and then applying it to other computers during a deployment. The computer environment has to be similar (exact) or there will be problems. Does something similar happen when trying to deploy a published app to various machines (enviroments).

BTW, the app works fine on the manager's computer if the source is run from within Visual Studio using Start Without Debugging.

This is perplexing and any advise will be much appreciated.

Hi all.
Is it possible to change type of calendar in DateTimePicker ?
e.g : I'm gonna replace Persian Calendar with the default calendar in DateTimePicker !
Thanks

No.. It wont be. You need to use any Javascript calendar.. Smile | :)

Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->
Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript

Dear All,

Im making a windows service in vs 2008. it has to perform a task once a day at 12:00 AM.
What is the best way to accomplish this? using Timers? or other ways?

Thanks in advance.

Does it really have to be a windows service if you only need to run it once every day at a certain time? Maybe you could schedule windows to run your program att 12AM instead?

Hi Calla,
Thanks for response.
It has to be a win service that automatically runs at that time and not throug windows

Priya Prk wrote:
It has to be a win service

Why? What will the task do?

It's probably not the best technique. If it's a homework assignment, then it's OK. But if it's a work assignment then you should take our suggestions and comments to your boss and ask if he agrees with us. A smart boss will see our point, but there could be other concerns you don't know about and haven't presented. For instance, this may be just the first phase of a larger scheduling project.

Priya Prk wrote:
not throug windows

It's going to be "through Windows" either way.

P.S.

Priya Prk wrote:
that automatically runs at that time

Do you understand that a Windows Service is always running? (Unless you stop it of course.)

I would use a Windows Scheduled Task; not a Windows Service.

I only use a Windows Service for things that need to happen much more frequently (perhaps up to every ten minutes). Even things that are to happen every hour are better done with a task, in my opinion.

What will the process do?

I worked with events (cause your service should be able to stop on request) and used a loop like this.

timeToWait = CalcMillisecondsTillMaintainceFromNow();
while(true)
{
     if(WaitForEvents(timeToWait) == event fired)
         timeToWait = CalcMillisecondsTillMaintainceFromNow();
     else
         InvokeMaintanceRoutine();
}

Greetings
Covean