LIKE operator in Access - Database Discussion Boards

Re: stored procedures - avoiding recompilations

Chris Meech5-Jan-04 4:57

5-Jan-04 4:57

I know Oracle has a default date format that is set as part of the database configuration. I would suspect the same is true for SQL Server, but I'm not up on it. Either I try to avoid depending upon defaults when writing a stored procedure. In the long run, you will have less headaches if within your procedure, you translate between the strings and date types using explicit formats. For example;

convert a date to a string
    to_char(some_date_field,'dmy')

convert a string to a date
    to_date(some_char_field,'dmy')

Chris Meech

It's much easier to get rich telling people what they want to hear. Christopher Duncan

I can't help getting older, but I refuse to grow up. Roger Wright

I've been meaning to change my sig. Thanks! Alvaro Mendez

We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton

Re: stored procedures - avoiding recompilations [modified]

Marek Grzenkowicz5-Jan-04 5:49

Marek Grzenkowicz

5-Jan-04 5:49

Chris Meech wrote:
In the long run, you will have less headaches if within your procedure, you translate between the strings and date types using explicit formats

I thought about it. However, the problem is that my task is to optimize a system that uses many stored procedures written by different people. I would have to go through all the procedures (I haven't written any of them!) and find places where using converting functions is necessary.

Sounds like a nightmare to me. Smile | :)

modified on Monday, August 30, 2010 6:32 AM

Re: stored procedures - avoiding recompilations

Mike Dimmick5-Jan-04 5:36

Mike Dimmick

5-Jan-04 5:36

I'd recommend using invariant date formats throughout: yyyymmdd.

You could also set the default language database option - see this article in Books Online[^] for more details.

Re: stored procedures - avoiding recompilations [modified]

Marek Grzenkowicz5-Jan-04 6:21

Marek Grzenkowicz

5-Jan-04 6:21

Mike Dimmick wrote:
You could also set the default language database option

Great idea!

I did not know it sets not only the language but also the date format.

Thanks.

modified on Monday, August 30, 2010 6:33 AM

Data structure to describe HTML table

Anonymous26-Dec-03 20:26

Anonymous

26-Dec-03 20:26

Can somebody recommend a SQL data structure that would "describe" a simple HTML table as shown below?

Section 1
Section 2	Section 3	Section 4

Re: Data structure to describe HTML table

Colin Angus Mackay27-Dec-03 6:48

Colin Angus Mackay

27-Dec-03 6:48

Think about what the entities are and their relationship. Also think about what attributes each entity has.

From the example given:
Table, Tr and Td are the entities.
Table is the parent of Tr
Tr is the parent of Td
Td has the attribute colspan
Td has the attribute InnerText (i.e. "Section 1", "Section 2" etc.)

--Colin Mackay--

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

Enumerators in .NET: See how to customise foreach loops with C#

mysql and mysqldump commandline tools

lady_fly26-Dec-03 10:22

lady_fly

26-Dec-03 10:22

Hello

I have a problem, im running mysql and mysqldump command-line exes from my application. i need: to know if the operation succeded from my application, is there a way to know the last error ocurred?

other quertion: i used the --html option, but the output is still plain text, any ideas?

here is one of the commands i execute:

mysql --host=laura --user=avs_db_user --password=faceshape --verbose --html --database=avs_db -e " source c:\backup\aaa.sql" > output.htm

thanks

LIKE operator in Access

Mazdak25-Dec-03 21:02

Mazdak

25-Dec-03 21:02

In SQLServer when I want column that start between range of a to b I use this syntax:

column LIKE '[a-b]%'

But it seems that it does not worl correctly in Access. How should I get that query in Access?

Mazy

No sig. available now.

Re: LIKE operator in Access

Hesham Amin31-Dec-03 11:16

Hesham Amin

31-Dec-03 11:16

try:
column LIKE '[a-b]*'

Re: LIKE operator in Access

Mazdak31-Dec-03 19:03

Mazdak

31-Dec-03 19:03

Nope,I found it some days before, MS ACCESS does not support range for LIKE. I should use it seprately:"[a]% AND [b]%" And I donna why '%' works but '*' doesn't work cause as you said '*' is correct. Any way, thanks for your reply.

Mazy

No sig. available now.

Re: LIKE operator in Access

Hesham Amin31-Dec-03 22:56

Hesham Amin

31-Dec-03 22:56

Mazdak wrote:
MS ACCESS does not support range for LIKE.

how comes ?
open the sample database northwind (comes with access) and try this query :

SELECT lastname
FROM Employees
where lastname like '[a-d]*'

results :
Last Name:
Davolio
Buchanan
Callahan
Dodsworth

Re: LIKE operator in Access

Mazdak1-Jan-04 0:22

Mazdak

1-Jan-04 0:22

Yes,its srtange for me too,but that really happend,maybe because my database contain Farsi charachter and windows always have problem with Farsi charachters.

Mazy

No sig. available now.

SQL server - Permission and ownership

CillyMe25-Dec-03 20:41

CillyMe

25-Dec-03 20:41

1. I've created a a view based on table "Orders".

2. I've been assigned role="dlladmin", let's say my login="Tom"

3. I granted jane (public) access privilege by:
grant select on vwOrders to jane

4. server admin "sa" transfered the view to dbo

sp_changeobjectowner 'Tom.vwOrders', 'dbo'

5. I checked Jane's access privilege to vwOrders by:
5a. From Jane's account:
select * from vwOrders

result: NO problem, jane can access the view. OKAY.

5b. Enterprise Manager: Choose the table, select properties>Permission
result: Jane was not assigned SELECT permission??
MY QUESTION: WHY? Is it a bug in Enterprise Manager? That privileges assigned before the view was transfered is not reflected in choose table>Properties>Permission?

Thanks in advance.

A string to compare with the rest of the records

codeajay24-Dec-03 18:59

codeajay

24-Dec-03 18:59

Hello fellow friends,
I am working on a project that needs to compare a users input string with the rest of the records in the table (MS-SQL).
E.g : User enters a string "I love .net"
Other entries in the database :
1. "I love ADO.net"
2. "I love ADO"
3. "No love"

The user entered string is to be compared with the rest of the records and displayed in % (no. of words found matching, taking users string as 100%) for each record.
For above 3 records : 1. 100%, 2. 67% 3. 34% (approximately)

The project is in C#.
What i need is a function/s. or a sql procedure or...any code thats can solve my problem.

"Merry Christmas to All"

A-La

connecting to Exces database(file)

Bharat Gadhia23-Dec-03 10:14

Bharat Gadhia

23-Dec-03 10:14

I am trying to retriev table in excel file using following code, but I get connection error like : Microsoft.Jet.OleDB 4.0 not registered on local machine or installbale ISAM not found. I am using Windows 2000 Adevance server and Visual Studio.Net 2003 and FrameWork 1.1 on my computer. Can any one please help?:
===========
CODE SNIPPET
============
string FileNam = "C:\\ExcelToXml\\ExcelInfo.xml";
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB 4.0;Data Source=EXCEL.C:\\ExcelToXml\\foodgroup.xls;Extended Properties=Excel 8.0;";
OleDbConnection Conn = new OleDbConnection(strConn);

try
{
Conn.Open();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
//You must use the $ after the object you reference in the spreadsheet
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", Conn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
DataSet myDataSet = new DataSet();
try
{
objAdapter.Fill(myDataSet, "ExcelInfo");
Conn.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
myDataSet.WriteXml(FileName);
}

Bharat Gadhia

Re: connecting to Exces database(file)

Rob Graham25-Dec-03 9:59

Rob Graham

25-Dec-03 9:59

Bharat Gadhia wrote:
strConn = "Provider=Microsoft.Jet.OLEDB 4.0;Data Source=EXCEL.C:\\ExcelToXml\\foodgroup.xls;Extended Properties=Excel 8.0;";

does not look correct.

try:
strConn = "Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:\\ExcelToXml\\foodgroup.xls;Extended Properties=Excel 8.0;";
(no "EXCEL." before path to datasource).

Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard

Re: connecting to Exces database(file)

Bharat Gadhia25-Dec-03 12:16

Bharat Gadhia

25-Dec-03 12:16

Hi Graham,
Thank you so much for your reply. I did try using:

strConn = "Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:\\ExcelToXml\\foodgroup.xls;Extended Properties=Excel 8.0;";

Problem persists. In fact I tried various ways to write connection string and use it, but I get the same error mesages. I have on my machine entire MS Office suit installed.

With best regards

Bharat Gadhia

Help is always welcome

Re: connecting to Exces database(file)

Mike Dimmick5-Jan-04 5:41

Mike Dimmick

5-Jan-04 5:41

Missing . - the name of the provider is 'Microsoft.Jet.OLEDB.4.0'.

Re: connecting to Exces database(file)

Bharat Gadhia5-Jan-04 5:54

Bharat Gadhia

5-Jan-04 5:54

Thanks for your suggestion. I did try that but somehow problem persisits.

ADO versus ADO.NET with Visual C++

François Gasnier22-Dec-03 5:02

François Gasnier

22-Dec-03 5:02

I have already used ADO with Visual C++.
I also used ADO.NET with Visual C#.

My questions are :
-Is it possible to use ADO.NET with Visual C++ in the same one uses ADO (I could not find any sample with the DataSet in MSDN)?
- If yes can you show me an example?

Thanks for any answer!

Connecting to Oracle database with ADO

wbng21-Dec-03 14:44

wbng

21-Dec-03 14:44

I'm a beginner in the field of Oracle. I want to know the way on how to make the connection to Oracle with ADO format.I have no idea about all the store procedures... The only thing i experienced is connecting to MS Access with ADO(ODBC). Is it the similar way to do so with this Oracle????

wb

Looking for good ADO book suggestion

Flack21-Dec-03 14:15

Flack

21-Dec-03 14:15

Hello,

I want to learn ADO.NET. I am an intermediate level programmer and am currently learning C#. Can anyone suggest a good book to purchase to learn ADO?

Thanx for the help.

-Flack

Re: Looking for good ADO book suggestion

Colin Angus Mackay21-Dec-03 14:50

Colin Angus Mackay

21-Dec-03 14:50

Hello again...

Well the book I suggested for ASP.NET also gives a little introduction to ADO.NET as well (in the context of a Web Application). But once your past that, for a more in depth look, go for "Professional ADO.NET programming" published by Wrox Press. Although I ought to warn you that the content is quite dense if you have no experience what so ever.

--Colin Mackay--

Microsoft SQL Server - Bulk Update Lock

CillyMe19-Dec-03 21:36

CillyMe

19-Dec-03 21:36

What's the purpose of Bulk Update Lock? How's it different from Exclusive locks if all it does is to prevent concurrent transaction from accessing the table to which it's loading data? Can SQL server simply assign a Exclusive lock to the table during BCP operations?

Thanks in advance.

What's the best Desktop Database for Windows Forms Apps?

bJoeyLouie19-Dec-03 8:51

bJoeyLouie

19-Dec-03 8:51

Does anyone have a recommendation for a database for a windows forms (c# - ado.net) application. I'd prefer not to use MSDE, MySql, Postgres since the app will only have one user - kind of like Quicken or Money. Also, my channel will be download - and these DB's are rather large.

I've heard bad things about Microsoft Access and JET. I haven't experienced personnally, wouldn't mind hear perspectives on this as well. Other's I've been checking out are SQLite, TurboDB, Advantage and Codebase.

Which one is the best from a value, quality perspective?

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.