A tricky Sql statement - Database Discussion Boards

LukeV10-Dec-02 3:44

10-Dec-02 3:44

Here's my SQL query (I'm using this with ASP):

UPDATE Customers SET Customers.[FirstName] = 'Luc',
Customers.[LastName] = 'Vandal',
Customers.[Adr1] = '1234 de London',
Customers.[Adr2] = 'ddd',
Customers.[City] = 'Sherbrooke',
Customers.[IDState] = 1,
Customers.[IDCountry] = 1,
Customers.[Zip] = 'J1J3J2',
Customers.[Tel] = '8198889003',
Customers.[Fax] = '888',
Customers.[Email] = 'lvandal@edovia_com'
WHERE Customers.[IDCust] = 1;

Only IDCust, IDState and IDCountry are numbers, all the other fields are text. I get this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Here's some code:

dbPath = "PROVIDER=MSDASQL;" & _
             "DRIVER={Microsoft Access Driver (*.mdb)};" & _
             "DBQ=" + GetDriveLetter + ":" + dbStore + ";" & _
             "UID=admin;"	
  myDSN = dbPath
		
	Email = Replace( CStr(Request("Email")), ".", "_")
	
	mySQL = "UPDATE Customers SET " & _
					"Customers.[FirstName] = '" & Replace( Request("FirstName"), "'", "''" ) & "', " & _
					"Customers.[LastName] = '" & Replace( Request("LastName"), "'", "''" ) & "', " & _
					"Customers.[Adr1] = '" & Replace( Request("Adr1"), "'", "''" ) & "', " & _
					"Customers.[Adr2] = '" & Replace( Request("Adr2"), "'", "''" ) & "', " & _
					"Customers.[City] = '" & Replace( Request("City"), "'", "''" ) & "', " & _
					"Customers.[IDState] = " & CStr( Request("IDState") ) & ", " & _
					"Customers.[IDCountry] = " & CStr( Request("IDCountry") ) & ", " & _
					"Customers.[Zip] = '" & Replace( Request("Zip"), "'", "''" ) & "', " & _
					"Customers.[Tel] = '" & CStr(Replace( Request("Tel"), "'", "''" )) & "', " & _
					"Customers.[Fax] = '" & CStr(Replace( Request("Fax"), "'", "''" )) & "', " & _
					"Customers.[Email] = '" & Email & "' WHERE Customers.[IDCust] = " & CStr(Request("IDCust"))
	

	set conn = server.createobject("adodb.connection")
 	conn.open myDSN
 	set rs = conn.execute(mySQL)

But the record is actually updated... By the way, if I copy and paste the query in Access and run it, it works... What is going on?! Confused | :confused:

---------------
Tired of Spam? Introducing InboxShield® for Microsoft® Outlook®
http://www.edovia.com

Re: I'm going crazy!!!!!!!!

Rob Graham12-Dec-02 4:25

Rob Graham

12-Dec-02 4:25

LukeV wrote:
set rs = conn.execute(mySQL)

Asks ADO to return a recorset, but your query will not produce one.
Try using:

conn.execute(mySQL,,adExecuteNoRecords)

instead (no rs=).

Also, performance would improve somewhat if you used the Jet provider directly, instead of the odbc provider:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + GetDriveLetter + ":" + dbStore +";User ID = admin; Password =;"

Smile | :)

Re: I'm going crazy!!!!!!!!

LukeV12-Dec-02 7:53

LukeV

12-Dec-02 7:53

OldRob wrote:
Also, performance would improve somewhat if you used the Jet provider directly, instead of the odbc provider:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + GetDriveLetter + ":" + dbStore +";User ID = admin; Password =;"

When I use that string, I get this error:

Error Type:
Microsoft JET Database Engine (0x80040E4D)
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

What is the workaround for this?

Thanks!

---------------
Tired of Spam? Introducing InboxShield® for Microsoft® Outlook®
http://www.edovia.com

Re: I'm going crazy!!!!!!!!

Rob Graham12-Dec-02 11:46

Rob Graham

12-Dec-02 11:46

I'm a bit surprised you didn't get the same with the odbc driver, but this this [^] might clarify the issues for you.

Re: I'm going crazy!!!!!!!!

Murat YILDIZ26-Dec-02 5:19

Murat YILDIZ

26-Dec-02 5:19

"Jet OLEDB:System database=yoursystemdatabase.mdw" add this parameter in your connection string.

Re: Get number of records

Daniel Turini10-Dec-02 1:32

Daniel Turini

10-Dec-02 1:32

You can't. The DataReader is (like) a forward-only cursor, so you don't have the count beforehand.
The way to go if you need is to do a SELECT count(*) FROM Orders or use a DataSet

I see dumb people

Re: Get number of records

Steve McLenithan10-Dec-02 1:55

Steve McLenithan

10-Dec-02 1:55

Use a Dateset to select the same records as the Reader does, but without getting any unnessesary data. use the myDataset.Tables["filledTableName"].Rows.Count property. then use the reader to extract your data.

I'm not sure how efficient this is, but i think it's the easiest.

Anybody else have a different way besides using SQL's Count(*)?

********************
* SteveMcLenithan
* steve@steve-mac.com
* Google News!
********************

A tricky Sql statement

DamienL9-Dec-02 23:51

DamienL

9-Dec-02 23:51

Hi all,
I need your help

This Sql statement calulates journey details after you enter your start station, end station and a time to travel after. It calculates your journey when the two stations you enter are not on the same 'route'.

I use 3 tables out of my database for this query:
*=Primary Key & -> are the relationships

StopsAt: Follows: Route:
JourneyNumber-> <-JourneyNumber <-*RouteNumber
StationName *RouteNumber->
Time
*AutoNumber

An example is:
Input- Crofton Rd to Loft Rd, after 12:25

Output:
Crofton Rd 12:30 Abbey Rd 12:46 Route:46A
& connect to>
Essex Rd 12:49 Loft Rd 12:52 Route:123A
....
Crofton Rd 12:30 Clare Rd 12:46 Route:46A
& connect to>
Sorrow Rd 12:49 Loft Rd 12:52 Route:123A
...and every other possible route there is

The problem: the query outputs all the possible connecting routes. I want it to pick one journey from Crofton Rd to X and from X to Loft Rd. So not list every possible route from Crofton road to Loft Rd.

I think I have to design another clause in the FROM part to account for Third.StationName and Fourth.StionName and join these up to the 'Follows' and 'Route' tables. I tried many things and cannot get it.
There are 4 copies of the StopsAt table made & I guess I have to make another copy of the 'Follows' and 'Route' table and Inner Join these.

I wrote the Sql statement like this so it's easier to read.

Please help me.

Sql= "
SELECT DISTINCT
First.StationName AS Begin,
First.Time AS StartTime,
Second.StationName AS SecondStop,
Second.Time AS ArriveTime,
Route.RouteNumber AS RouteNumber1,

Third.StationName AS ThirdStop,
Third.Time AS StartTime2,
Fourth.StationName AS Destination,
Fourth.Time AS DestinationTime,
Route2.RouteNumber AS RouteNumber2

FROM
Route AS Route2,
StopsAt AS Third,
StopsAt AS Fourth,

StopsAt AS Second
INNER JOIN ((
StopsAt AS First INNER JOIN Follows
ON First.JourneyNumber = Follows.JourneyNumber)
INNER JOIN
Route
ON Follows.RouteNumber = Route.RouteNumber)
ON Second.JourneyNumber = Follows.JourneyNumber

WHERE
First.StationName = [user input]
AND Fourth.StationName = [user input]
AND Third.Time > Second.Time
AND Second.Time > First.Time
AND Fourth.Time > Third.Time
AND First.JourneyNumber = Second.JourneyNumber
AND Third.JourneyNumber = Fourth.JourneyNumber
AND First.Time > [user input]

Damien

How can I add a string that has a single quote character in it?

Bart-Man9-Dec-02 11:28

Bart-Man

9-Dec-02 11:28

Greetings,

I was wondering how can I add a string with character beginning with a single quote using sql?

For example:
------------
The year 1985 becomes: '85
I want to add the '85 to a character field.

Or even a "~".
1985 is added to the database as ~85.

I know strings can be added with 'somevalue', but what if the string starts, or has a weird character in it?
Thanks in advance for any help! Smile | :)

Re: How can I add a string that has a single quote character in it?

David Salter9-Dec-02 11:56

David Salter

9-Dec-02 11:56

Replace each occurence of a single quote with 2 single quotes.

e.g. insert into data (x) values ('the year is ''85')

Dave.

Re: How can I add a string that has a single quote character in it?

Bart-Man10-Dec-02 3:36

Bart-Man

10-Dec-02 3:36

Hello,

Thanks for the response, I did not know about the double replace of the "'" character.
That is good to know!! Smile | :)

Re: How can I add a string that has a single quote character in it?

Paul Watson10-Dec-02 0:03

Paul Watson

10-Dec-02 0:03

Bart-Man wrote:
I was wondering how can I add a string with character beginning with a single quote using sql?

The other reply is one way, but another is to start using stored procedures and parametres. They may be slog in setting up, but once you start using parametres things get a lot better.

Just my 2 cents Smile | :)

Paul Watson
Bluegrass
Cape Town, South Africa

Christopher Duncan wrote:
Which explains why when Santa asked, "And what do you want for Christmas, little boy?" I said, "A life." (Accesories sold separately)

Re: How can I add a string that has a single quote character in it?

Bart-Man10-Dec-02 3:37

Bart-Man

10-Dec-02 3:37

Hello,

Thanks for the tip! I will have to try using the stored procdedures Smile | :)

Re: How can I add a string that has a single quote character in it?

Daniel Turini10-Dec-02 1:40

Daniel Turini

10-Dec-02 1:40

You need to execute the query with prepared statements. Depending on the language you are using look for something named Command or SQLCommand and a collection named Parameter or SQLParameters.

Forget about double-quotes. More details in my article[^]

I see dumb people

Re: How can I add a string that has a single quote character in it?

Bart-Man10-Dec-02 3:39

Bart-Man

10-Dec-02 3:39

Hello,

Thanks for the link to your article, it was very helpful!! Smile | :)

Re: How can I add a string that has a single quote character in it?

David Salter11-Dec-02 10:37

David Salter

11-Dec-02 10:37

Daniel Turini wrote:
Forget about double-quotes. More details in my article[^]

Does that work in ODBC also?

Dave

Re: How can I add a string that has a single quote character in it?

Daniel Turini11-Dec-02 11:49

Daniel Turini

11-Dec-02 11:49

Davey wrote:
Does that work in ODBC also?
Yes, it works. Almost any modern database access technology will provide functionality simillar to ADO's Command/Parameter.

I see dumb people

Record Number ...

RJS9-Dec-02 7:41

RJS

9-Dec-02 7:41

Hi,

I would like to know if it is possible to do something like these:

1)
select field1,field2 from table_name where line_number_in_table >50 and line_number_in_table < 100
in sql2000?
So I would get only those records between the 50 and the 100..

2) Is there any similar command in sql2000 for rownum in Oracle?

Thanks a million!

Re: Record Number ...

David Salter9-Dec-02 10:04

David Salter

9-Dec-02 10:04

RJS wrote:
2) Is there any similar command in sql2000 for rownum in Oracle?

Select top xxx * from tablename

e.g.

select top 10 * from authors

Re: Record Number ...

Not Active9-Dec-02 14:33

Not Active

9-Dec-02 14:33

That will only get the top 10 rows. You would need to use your index( if you have one) to select the rows.

select * from table
where index > 50 and index < 100

This assumes that the index is numberic and sequential.

Re: Record Number ...

Daniel Turini10-Dec-02 1:43

Daniel Turini

10-Dec-02 1:43

RJS wrote:
1)
select field1,field2 from table_name where line_number_in_table >50 and line_number_in_table < 100
in sql2000?
No. You need a real column. BTW, in SQL, in general, it's a bad practice to assume a physical order of records in a query.

RJS wrote:
Is there any similar command in sql2000 for rownum in Oracle?
No, but you can fake it.

I see dumb people

Partial Search

Sarvesvara (BVKS) Dasa9-Dec-02 1:25

Sarvesvara (BVKS) Dasa

9-Dec-02 1:25

I want to do a partial no search in the database... Like... I want to search the data containing just the characters "age" in it.

Is this possible in MS-Access-2000?? I want to access this through my ASP page!!

I started with nothing,
And I still have lots of it left with me.

Re: Partial Search

Not Active9-Dec-02 2:09

Not Active

9-Dec-02 2:09

I think you need to sit down with a good book on SQL and Database Design before asking any more questions. We will give you help but you need to do some work also.

Re: Partial Search

Sarvesvara (BVKS) Dasa9-Dec-02 2:39

Sarvesvara (BVKS) Dasa

9-Dec-02 2:39

Mark Nischalke wrote:
sit down with a good book on SQL and Database Design

Phew... If at all I have the time, I wont have posted this question to the site. All my responsibility on me is to port from ms-access to sql-server 2000. And finally, some more current jobs like this search too. What shall I do other that hook on my questions to cp.

If u have answers... let me know....

I am posting the question to get idea. I am not asking u do come and do my project as a whole....

Thanks

I started with nothing,
And I still have lots of it left with me.

Re: Partial Search

David Salter9-Dec-02 3:00

David Salter

9-Dec-02 3:00

In its simplest form:

select requiredcolumn from table where column like '%age%'

will do the job.

Dave.

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