Database

13-May-04 19:37

Hello Friends,

I have a problem in SQL 2000. I have a table call Employee in that there r many details of employee such as his name his age and other details.
I want to insert one employee name D'souza mike for that i write a query
insert into employee values ('D'Souza Mike', 21)
Here name is D'Souza so that special character ' create problem while executeing query.

any one know how to insert special characters like ' _ % then please tell me

Thank You

Hemant Mane
Smile | :)

Hesham Amin13-May-04 21:35

Hesham Amin

13-May-04 21:35

insert into employee values ('D''Souza Mike', 21)

sanjucsharp13-May-04 21:39

sanjucsharp

13-May-04 21:39

You can't directly insert any special character.Just decode your special character with any other name e.g. "'" you can give your single quote the name "SQT".Now insert this value in the database.Now when you retrieve the value then decode this "SQT" to "'".That's all..... your job has done.

Anonymous19-May-04 1:29

19-May-04 1:29

if the employee's name itself includes the chars : "SQT" ,
it can't work well .

Re: Embarcadero Rapid SQL

Rob Graham15-May-04 2:54

Rob Graham

15-May-04 2:54

Either escape the ' with another ' ('') or use an SqlCommand object with parameters. You can set the value of the parameter to a string containing 'special characters' and it will insert without difficulty.

Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf

Embarcadero Rapid SQL

Nino_113-May-04 15:57

Nino_1

13-May-04 15:57

Anyone have any hands on with Rapid SQL, we are considering buying an enterprise license.
What are your thoughts on this tool good, bad etc;

Thanks

Tony

mtone18-May-04 7:26

mtone

18-May-04 7:26

Check the price tag

Row Concatenation

Ruchi Gupta13-May-04 13:08

Ruchi Gupta

13-May-04 13:08

I have followign two tables, and want to update TB1 such that IDs col gets updated

PARAMETER tables
TB1
Dt desk IDs
----------- ---------- ----------
1 PT
1 AB
2 PT
2 AB

TB2
Dt desk book IDs
----------- ---------- ---------- -----------
1 PT PT1 100
1 PT PT2 200
1 PT PT3 300
1 AB AB1 400
2 PT PT1 500
2 PT PT2 600
2 PT PT3 700
2 AB AB1 800

Final RESULT
TB1
Dt desk IDs
----------- ---------- ----------
1 PT 100, 200, 300
1 AB 400
2 PT 500, 600, 700
2 AB 800

Any pointers

Thanks
Ruchi

Re: Row Concatenation

Bill Dean18-May-04 5:20

Bill Dean

18-May-04 5:20

Is this on sql server? If so, your best bet might be to write a cursor to build each of your id strings ('100,200,300') and then update. I've been trying to come up with a clever way to do it in one SQL statement, doing self joins of tb2 to itself...but I am not getting anywhere...

Typically, when I need to denormalize data like this, I do the denormalization in the application (typically c#...but that's details). SQL just does not lend itself to this kind of thing nearly as readily as ADO.NET / more procedural programming approaches do.

Hope this helps
Bill

Re: Row Concatenation

Ruchi Gupta18-May-04 5:40

Ruchi Gupta

18-May-04 5:40

Thanks very much for your response.

I myself am doing the same thing you suggested - Cursor & Updates. But it is kind of expensive operation, when temp table is huge (depending on param passed to SP)

I need this result set for C# application. Could you please give me pointers on how you do it in ADO.NET

Thanks
Ruchi

Re: Row Concatenation

Bill Dean18-May-04 6:10

Bill Dean

18-May-04 6:10

Ruchi Gupta wrote:
But it is kind of expensive operation, when temp table is huge

Agreed. Which is why I was trying to be clever...the update...from is often handy for complex updates, but I can't come up with a single query that will generate your desired result strings ('100,200,300','400', etc). Besides, that approach required that you know the max number of strings (in your example, 3) before hand.

You mention passing parameters for the first time above. Are you updating only a few records in TB1? That might make this easier. I was assuming you were updating the whole table.

Anyway in ADO, the idea is to get your data from TB2 in order, loop through the results building the new IDs string for each entry in TB1 and then do the update. C# doesn't suffer from the horrible performance penalties of SQL for looping.

Something like:

-Load TB1 into a DataTable using a dataadapter (if you set the PK, you can use a SqlCommandBuilder to automatically generate your update command)
-Load TB2, sorted by DT,Desk into a second datatable
-create a string variable to accumulate the id string;
-Cycle through every row of this second table.
-if the current row has the same DT and Desk as the previous row, just add the ID to the id string with the comma
-other wise, set the IDs column of the Row in the first DataTable with the matching DT and Desk to the accumulated ID string. Use DataTable.Find() to find the row. Clear the ID string.

-At the end, use the original dataadapter to update the database.

This way, you can do all the itteration in C#, which does it well, rather than in SQL which is terrible at it.

Hope this helps
Bill

database location

Anonymous13-May-04 11:44

13-May-04 11:44

this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\Technomedia.mdb;Mode=ReadWrite|Share Deny None;"

for the source, how do I make it so it accesses the database that is in the same folder as the program? Because when I load the program on other computer it give me this exception error:

"Process id = 0x4f4(1268), Thead id = 0x5dc(1500)"

I think it gives me this because it can't find the database because it is hardcoded into the program as being in a different location. So I want to know how to make it look for the database in the program location. i tried Data Source=technomedia.mdb but it gives me an error.

Thanks

Jeff Varszegi13-May-04 12:01

13-May-04 12:01

Does this work? What exception is thrown, if not?

oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Password=\"\";User ID=Admin;Data Source=" + Environment.CurrentDirectory + "\\Technomedia.mdb;Mode=ReadWrite|Share Deny None;"

Anonymous13-May-04 12:26

13-May-04 12:26

now it gets stuck on my dataadapter, give me this:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

on this line: oleDbDataAdapter3.Fill(dataSet1, "Region");

Jeff Varszegi13-May-04 13:19

13-May-04 13:19

Please post the full stack trace. (You should always do this when you post a question; it'll speed things up a bit.)

Anonymous13-May-04 14:04

13-May-04 14:04

I am just started with c# and do it as a hoby. So I have no idea what a full stack trace is.

Thanks

Jeff Varszegi13-May-04 14:54

13-May-04 14:54

Okay, I didn't notice that it was an unhandled exception. The first thing you need to do is wrap that section of code in a try-catch block so you can catch the exception! When an exception is "unhandled", that means that it's bubbled up to the top, causing your program to crash. You will do something like this:

try {
   // ... your code that's throwing the exception; following is a line that throws one on purpose
   throw new ApplicationException("I'm a little teapot");
} catch (Exception e) {
   Console.WriteLine(e.StackTrace);
}

You should read up on exception handling right away-- you're already past the "Hello, world!" stage, and into database applications, where you really need to be able to clean up your resources in case of a problem. Good luck.

Anonymous13-May-04 15:41

13-May-04 15:41

this is what I get

at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at WindowsApplication5.Form1..ctor() in c:\documents and settings\roman\my documents\visual studio projects\windowsapplication5\form1.cs:line 77

Jeff Varszegi15-May-04 2:43

15-May-04 2:43

Try creating an ODBC data source, and then use the data source name in your connection string.

Anonymous15-May-04 4:17