|
Is there a way to call a column by index rather than by its name. I'd like to crawl to a specific column, as in:
SELECT {column + 2} FROM Table
Similar to how you would refer to an element in a C++ array using pointer arithmetic.
|
|
|
|
|
Simple answer: no.
Complicated answer: if the column was called Coln (e.g. Col2) you could probably use sp_executesql (preferred) or EXEC to construct some dynamic SQL.
SQL is very good in the vertical direction, finding rows which match particular criteria; it's very weak in the horizontal.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
OK, I see what you mean. It's basically the way SQL works. I guess I'll just have to use a bunch of IF statements and choose the column based on the index I give it. It's not a big deal, I was just in a more dynamic frame of thought.
|
|
|
|
|
hi
in my app i have a recordset associated with paradox 5 table created by
Borland's Database Desktop 7. (not DAO just crecordset)
and i have a strange situation here:
if (!pDoc->m_rentedFMDBSet.IsOpen())
return;
pDoc->m_rentedFMDBSet.Requery();
int z = pDoc->m_rentedFMDBSet.m_FilmNr;
// in recordset data members is ok data
z = pDoc->m_rentedFMDBSet.CanUpdate(); z > 0
z = pDoc->m_rentedFMDBSet.IsBOF(); z == 0
z = pDoc->m_rentedFMDBSet.IsEOF(); z == 0
z = pDoc->m_rentedFMDBSet.IsDeleted(); z == 0
TRY
{
pDoc->m_test2Set.Delete();
}
CATCH(CDBException, e)
{
AfxMessageBox(e->m_strError);
return;
}
END_CATCH
and i have always exception. TRACE says sth like this:
"update or delete operation had no effect on any row"
i must say that sometimes (when i create new table and add some values
it works, but from my point of view it's random behavior for now)
help
|
|
|
|
|
To simplify date conversion i was adviced to use to integer converted dates i.s.o. the datetime type.
Now i have the integer 37954 which resembles 01-12-2003 (dd-mm-yyyy)
select cast(37954 as datetime) as startdate
But VB6 thinks it is 29-11-2003
?Format(37954, "dd-mm-yyyy")
How is this possible? What am i doing wrong?
Grtz,
Guus
|
|
|
|
|
Doing this will give you a major headache. Use the datetime data type. If you need to use date literals, specify them in the ISO format: 'yyyymmdd' (e.g. today would be '20040401').
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks for the good advice but it doesn't answer my question. How is it possible that an integer converted to a date results in two different dates on VB vs SQL Server?
|
|
|
|
|
VB6's built-in dates use OLE's DATE format - an 8-byte floating point number based on 30 December 1899 = 0.0, with the time represented as a fraction.
SQL Server's datetime data type is made up of two four-byte signed integers, based on 1 January 1900. The documentation reads:
"Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight."
When converting to and from integers, both VB6 and SQL Server simply use their own internal representations, which is why they produce different values.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
That's the answer i was looking for. Thx!
Grtz,
Guus
|
|
|
|
|
Have you tried storing dates as strings?
Easy to display in the format you want and easy to convert correctly to date/time format for any calculations that you need to perform on them.
Jon
|
|
|
|
|
Hi, jonathan15
I hope you can help me... I store an elapsed time value as a string (mm:ss) in my SQL database. When I query the data from Excel, how can I calculate an average from this data? Would it help if I made the SQL datatype some kind of time value?
Thanks in advance for any advice you may provide.
'til next we type...
HAVE FUN!! -- Jesse
|
|
|
|
|
Jesse,
Depends on what you want to do and how you want to get the information into excel really. If you are doing a query to get the raw data into excel and average the results from there you could use something like the following in your Query
"select table1.duration, (cast(left(table1.duration,2) as int) *60)+cast((right(table1.duration,2))as int) as seconds from table1"
assuming the duration is MM:SS, this will add a field called 'Seconds' which would be a numeric value to your query. it would then be simple to average the seconds using an excel function.
Jonathan
|
|
|
|
|
Hie... I have faced this problem when I set my data grid - AllowCustomPaging to True.
Please advice as soon as possible?
Server Error in '/DNSS' Application.
------------------------------------------------------------------------------
AllowCustomPaging must be true and VirtualItemCount must be set for a DataGrid with ID Tel when AllowPaging is set to true and the selected datasource does not implement ICollection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.HttpException: AllowCustomPaging must be true and VirtualItemCount must be set for a DataGrid with ID Tel when AllowPaging is set to true and the selected datasource does not implement ICollection.
Source Error:
Line 14: DbReader = dbcomm.ExecuteReader()
Line 15: Tel.DataSource = DbReader
Line 16: Tel.DataBind()
Line 17: DbReader.Close()
Line 18: DbConnection.Close()
Source File: d:\inetpub\wwwroot\DNSS\AccessDataCon\OlelDataReader.aspx Line: 16
Stack Trace:
[HttpException (0x80004005): AllowCustomPaging must be true and VirtualItemCount must be set for a DataGrid with ID Tel when AllowPaging is set to true and the selected datasource does not implement ICollection.]
System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) +2068
System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +49
System.Web.UI.WebControls.BaseDataList.DataBind() +23
ASP.OlelDataReader_aspx.Page_Load() in d:\inetpub\wwwroot\DNSS\AccessDataCon\OlelDataReader.aspx:16
System.Web.Util.ArglessEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +10
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
|
|
|
|
|
Hi,
I using http://support.microsoft.com/?kbid=320301 as a guide to building a
webservice with a collection of methods for requesting and updating data.
The client will request some data, change it/add more rows, then send it
back to be updated on the server. When the client adds a new row I would
like the identity of the row to be negative, start at -1 and move down.
However when I'm populating the dataset I have to use
FillSchema(ds,SchemaType.Mapped,"Details") if I want the Merge method to
work properly. This prevents my id's from starting at -1, infact it causes
conflicts. Because if the last id in the database is 60, then new rows
added by the client will start at 59. Is there anyway to resolve this? Or
should I omit the FillSchema method and just remove rows with negative id
when the merge is complete? As the client devices are handles, I would like
to keep any extra steps to a minimum.
Any help would be greatly appreciated,
Mark
|
|
|
|
|
Hi friends
My problem is that I want to minus the result of two queries in ms-access. But I wonder if there is any equivalent for minus in access. I should mention that here I can not use NOT IN statement as an equivalent(or I don't know how to use it here!):
I have one query which returns these fields: A, B, C from TableA
and the second query returns some other records with the same fields from TableB. Now how can I subtract the two query results?
Don't forget, that's Persian Gulf not Arabian gulf!
Murphy:
Click Here![^]
I'm thirsty like sun, more landless than wind...
|
|
|
|
|
Hi maysam. Would it work for you to join the two queries with a Left Outer Join on all three fields? then filter for where the returned field from the second query is null.
Something like this...
Select x.A, x.B, x.C
From QueryA x
Left Outer Join
QueryB y on x.A = y.A and x.B = y.B and x.C = y.C
Where y.A is nullThat should show you all records that are returned in QueryA that don't have a matching record in QueryB, effectively subtracting out the matching records.
|
|
|
|
|
I am going to use it...
Thanks!
Don't forget, that's Persian Gulf not Arabian gulf!
Murphy:
Click Here![^]
I'm thirsty like sun, more landless than wind...
|
|
|
|
|
I have a table that it's PK Field Type is autonumber
I want to enter a record between another records.so the other records after it should increase once.how can i do it with query?
|
|
|
|
|
UPDATE table SET PK =PK+1 WHERE PK>=X
INSERT INTO table(PK) VALUES(x)
But your DB may not allow a direct access to the PK field, depending on the implementation. In that case, simply forget it.
dirk
|
|
|
|
|
i want to use the date part of the function getdate in my sql query.
the getdate function return the current date and time. i want only the date part.
how can i do it.
|
|
|
|
|
Goodway,
First of all I assume you use SQL Server (7.0/2000). If not quit reading.
Then, SQL Servers date/time datatype always consists of a date AND a time part. A date without a time component does not exsist is SQL Server.
If you insert a date value without a time, for example jan 1, 2004, SQL Server inserts the date as 2004/01/01 00:00:00.000 (for smalldatetime skip the milliseconds).
So if you need a date without a time you need to convert a datetime value to a string. You can use the CONVERT-function in SQL (see "CAST and CONVERT" in SQL Books online).
Example:
SELECT CONVERT (varchar(24), GetDate() , 103);
Returns:
01/01/2004 -- Note: the result is a string. Not a datetime value.
Good luck
|
|
|
|
|
Using Dataadapter.Fill, a DS.tables("TBL") was populated with, say, n records. Is there a built-in method / procedure through which one can easily find out the row- number in a datacol of the "TBL" contains a given value.
Say, for example, given datacol("FNAMES") and another string fn_str, is there a readily-available procedure such as :-
dim kt as integer, fn_str as string = "John"
kt = ReadilyAvailable(datacol("FNAMES"), fn_Str)
Here, kt will be a value - either -1 or {0 .. n-1}. -1 indicates that the given value does not exist.
Ofcourse, alternatively, a for-next loop will have to be written, but i wanted to avoid it
|
|
|
|
|
C# syntax:
myDataSet.Tables["mytabl"].Rows.Find() method.
Mazy
"Man is different from animals in that he speculates, a high risk activity." - Edward Hoagland
|
|
|
|
|
thanks, Mazy.
Regards,
Sanjay.
|
|
|
|
|
Whenever you insert a new record, then decide you want to manipulate that record immediately after, is there any easier way to find a reference to it than querying for the last record?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
