|
I have a table tab with a field of type Time and a couple of records in that table.
If i call select * from tab from mysql console, the result is ok, displaying time in normal format.
When i do the same using ADO, the result will contain variants of type VT_DATE with fields having the current date added to the time.
For example, 10:10:10 in mysql console becomes 2002-12-12 10:10:10 AM with ADO.
Is there a reason for such behaviour? And how could i make ADO return Time in the correct format?
rechi
|
|
|
|
|
I've created a stored procedure using SQL server query analyzer. The procedure takes single argument which is an output variable and return certain integer value. The procedure is something like this:
CREATE PROCEDURE DBO.MUMAdds
@addtype int <code>output</code>
AS
SET @addtype=100
return 20
GO
Now i want to execute this procedure using query analyzer, in order to get two values i.e the value this function return and the output value that is passed as a parameter to this procedure.
Can anyone tell me that how can i execute this procedure i.e supplying paremeter variable and getting returned value in a variable ????
|
|
|
|
|
Shamoon wrote:
Now i want to execute this procedure using query analyzer, in order to get two values i.e the value this function return and the output value that is passed as a parameter to this procedure.
DECLARE @addtype int
execute mumadds @addtype OUTPUT
print @addtype
But I am not sure how you get the "return 20" back, probably best to make it an output param.
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)
|
|
|
|
|
<br />
DECLARE @res int<br />
DECLARE @addtype int<br />
EXEC @res = dbo.MUMAdds @addtype OUTPUT<br />
But it's better to use an output parameter instead...
|
|
|
|
|
Alex Kay wrote:
DECLARE @res int
DECLARE @addtype int
EXEC @res = dbo.MUMAdds @addtype OUTPUT
Thanks Alex.
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)
|
|
|
|
|
How to Connect to ODBC (SQL SERVER 2000)
through ADO
which provider should be used
|
|
|
|
|
If you want to connect to an ODBC datasource, use "Microsoft OLE DB Provider for ODBC Drivers".
If you are going straight ADO and not using ODBC, yuo should use "Microsoft OLE DB Provider for SQL Server".
Dave
|
|
|
|
|
How to connect to SQL Server 2000(personal) not only from local network but also from other networks in the group using ADO
What should be
Provider
Data Source
Initial Catalog
ConnectionPtr->Open(_bstr_t(L"Provider=;Data Source=;
Initial Catalog = "),
_bstr_t(L""),
_bstr_t(L""),
adModeUnknown);
it is the first time I am in ADO , and do not know what they are (Provider,..)
and what the other arguments of the Open() function are.
and how to change the paswword to the SQL Server from Windows authentication
to SQL Server authentication
|
|
|
|
|
All the configuration options should be the same irrespective of where you are connecting from.
I'm not sure if the personal edition of SQL Server can accept connections from remote machines though.
Dave.
|
|
|
|
|
I have been using SQL for a while but I still cant get a definitive answer to this.
Given a table with a dword column which of these is correct:
INSERT INTO table (dvalue) VALUES ('0')
or
INSERT INTO table (dvalue) VALUES (0)
I seem to have some examples for each and the DB engine I'm using seems to only allow the first.
|
|
|
|
|
It depends on the datatype of the column. Assuming you a re using SQL Server, if the datatype of the column is float or int, then you would use the second example. For datatypes such as varchar, char, nvarchar and nchar, you would use your first example.
Jeremy
Jeremy Oldham
|
|
|
|
|
Thanks.
Thats what I thought.
Shame the implementation im using is busted then.
|
|
|
|
|
Bangerman wrote:
Shame the implementation im using is busted then.
What DB are you using?
|
|
|
|
|
EDB V4.3
Its a real pain because im porting my app to use ODBC and I think I'm ging to end up having to have two versions of every SQL in my program.
|
|
|
|
|
No you're not. Implement a function like this (in pseudo code here)
string DecideWhetherMyDBImplementationIsBrokenAndWrapANumberProperlyForIt ( DWORD n)
{
if (broken db)
return "'" + string(n) + "'";
else
return string(n);
}
and use that a lot.
#include <beer.h>
|
|
|
|
|
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?!
---------------
Tired of Spam? Introducing InboxShield® for Microsoft® Outlook®
http://www.edovia.com
|
|
|
|
|
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 =;"
|
|
|
|
|
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
|
|
|
|
|
I'm a bit surprised you didn't get the same with the odbc driver, but this this [^] might clarify the issues for you.
|
|
|
|
|
"Jet OLEDB:System database=yoursystemdatabase.mdw" add this parameter in your connection string.
|
|
|
|
|
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
|
|
|
|
|
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!
********************
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
Replace each occurence of a single quote with 2 single quotes.
e.g. insert into data (x) values ('the year is ''85')
Dave.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
