Database Discussion Boards

new_phoenix10-Oct-07 3:40

10-Oct-07 3:40

I appreciate the assistance that all of you have provided so far. However, I have encountered a slight glitch with the CreateQueryDef() method. The problem is that it seems to take a while for the QueryDef to be actually created and available for use in the following code.

After I create a QueryDef, I need to use it in the very next subroutine, a point in which it might not actually become available in MS Access. Is there a way to pause execution of the subroutine until the QueryDef is actually created and available for use?

Re: CreateQueryDef() pause needed

DQNOK10-Oct-07 5:32

DQNOK

10-Oct-07 5:32

I've not tried this...

Perhaps something like:

CreateQueryDef("mynewquery",...)

while( notexists( "mynewquery" ) )
   doevents
loop

qdf = QueryDefs("mynewquery")

Those are not the right names, but maybe the right idea.

Let us know if you get a solution. I may want to use it myself.

David

There has got to be a more elegant way to do this...

new_phoenix10-Oct-07 10:13

new_phoenix

10-Oct-07 10:13

Here is what I did, and interestingly enough...it works.

I created code that essentially does nothing but execute a loop and pass a counter to a control. Then I added a control to the form, left it visible, but essentially hid it from the form by making the textbox's background color, border color, and fore color the exact same as the underlying form color. This provided something for the processor to do while the QueryDef was being created. I essentially needed to count to 10000.

Here is the code:

'Provides time to create QueryDef qryFinalResult
Dim intCount As Integer
intCount = 0
For intCount = 1 To 10000
    txtCounter.SetFocus
    txtCounter.Text = "Count: " & intCount & "."
    intCount = intCount + 1
Next

Does anybody have a more elegant way to do this? This code prevents that error message from coming up indicating that the QueryDef that I had just created does not exist. When I step through the code myself, the processor has enough time to create the QueryDef object, but when I run it as an application, it does not have time to create it in time to use it.

How to query all of the tab_name in the same database

nibabug10-Oct-07 1:16

nibabug

10-Oct-07 1:16

when my program run, i want to creat a new table, but i do not know if the name is exist.
so, i want to check that if the name can be used or no, but i don't know how to query all the table names

give me a help, please
i am just starting lerning SQL and VC++
thanks a lot

wuhuaiji

Re: How to query all of the tab_name in the same database

Krish - KP10-Oct-07 1:21

Krish - KP

10-Oct-07 1:21

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'table_name') CREATE TABLE table_name ( ....

"sysobjects" is the table which contains all objects created

Regards
KP

Re: How to query all of the tab_name in the same database

Virendrak10-Oct-07 2:25

Virendrak

10-Oct-07 2:25

IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='Student')
SELECT 'Student exists.'
ELSE
SELECT 'Student does not exist.'

-- or

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='Student'

-- or

SELECT * FROM sysobjects WHERE name='Student'

--OR
IF OBJECT_ID('Student') IS NOT NULL
PRINT 'Student exists.'
ELSE
PRINT 'Student does not exist.'

Never Think That You Have Failed Instead Always Think That u hav Better Chance Next Time...

Re: How to query all of the tab_name in the same database

GuyThiebaut10-Oct-07 5:21

GuyThiebaut

10-Oct-07 5:21

Hi,

I created a function to check if a table exists, as I need to check for this sort of thing frequently.

Here is the function code:

<code>
CREATE function dbo.DoesObjectExist(@Name varchar(200), @Type char(1))
returns varchar(7)

begin
declare @Result varchar(7)

if exists (select * from sysobjects
where name = @Name
and xtype = @Type)
	select @Result = 'present'
else	
	select @Result = ''

return @Result
end

This is how you check if the OffVansQtyOrdered table exists using this function:

<code>
if dbo.DoesObjectExist('OffVansQtyOrdered','u') = 'present'

I hope this is of help...

You always pass failure on the way to success.

Re: How to query all of the tab_name in the same database

nibabug10-Oct-07 14:11

nibabug

10-Oct-07 14:11

firstly thanks again,
by the way,i am so sorry, that i do not really understand what you write above, and i do not know in VC++ , how to create a table, just like the question i write "a problem about m_pConnection->Execute", have you a good way to resolve it, or can can you tell me which problem exist in my program

wuhuaiji

Re: How to query all of the tab_name in the same database

GuyThiebaut10-Oct-07 21:16

GuyThiebaut

10-Oct-07 21:16

Hi,

The first example is what is called a "user defined function".
This is a means for SQL to process data based on parameters passed to the function and then return a value(or a table).
In this case the function takes an object and object type as input.
The object type, in the bottom piece of code, is "u" which means that the function only looks for tables.

The advantage of a function is that you can create this code once and reuse it many times by just calling it with dbo.DoesObjectExist('tablename','u').

In terms of creating a SQL table in C++ I'm sorry to say that I cannot help you with that yet as I am learning C# at the moment.
What I can recommend is google and searches with strings such as "create SQL table with C++" and "SQL create table".
As a tool google is invaluable and has proved to be just this in my work - as there is always going to be an example somewhere out there of what you are trying to do.

Good luck.

You always pass failure on the way to success.

problem in page indexing in Grid View

ahmed_fci200610-Oct-07 0:44

ahmed_fci2006

10-Oct-07 0:44

hi every body
i have my project that i have pages that contains grid views then with updating sume page the underlining under page number became hidden and some pages still underline under page number visible what happend and how i can make the pages page underlines visible thx in advance. Smile | :)

let's work together

Last value in a Table

BINOVAR10-Oct-07 0:18

BINOVAR

10-Oct-07 0:18

how to find the last value in a Table .

My table was designed by another person ..it doesnt have any Identity column ..

It has only EMPname,Empaddress,EmpContactno.

can any one pls tell me how to find the last value in the table

Thanks in advance

Regards
Bino varghese

Re: Last value in a Table

Aavesh Agarwal10-Oct-07 0:32

Aavesh Agarwal

10-Oct-07 0:32

you mean to say u want a last record of the Table...is that so...

Re: Last value in a Table

Aavesh Agarwal10-Oct-07 0:36

Aavesh Agarwal

10-Oct-07 0:36

if i m getting you correct so let me tell you there is no such way by which u can get the last row of the tabe u shuld be more specific abut ur query..but there is a way by which u can achieve ur task.

select top 1 * from table
order by somefield

Hope it will work for you.

Thanks and regards
Aavesh Agaerwal

Re: Last value in a Table

Paddy Boyd10-Oct-07 0:56

Paddy Boyd

10-Oct-07 0:56

Last value by what criteria?

Re: Last value in a Table

Paddy Boyd10-Oct-07 1:11

Paddy Boyd

10-Oct-07 1:11

Not sure why you need this bug:

SELECT TOP 1 X
FROM TABLE
ORDER BY X DESC

Re: Last value in a Table

BINOVAR10-Oct-07 1:09

BINOVAR

10-Oct-07 1:09

hi all

Thanks for ur reply..

If I order by I vl not get the last value in the table..

I want the last record in the table .

Re: Last value in a Table

pmarfleet10-Oct-07 2:12

pmarfleet

10-Oct-07 2:12

One of the fundamental principles of a relational database is that the physical order of rows and columns in the tuples (tables) is not important.

http://en.wikipedia.org/wiki/Relational_database[^]

If you need to extract data in a particular order, the data itself should provide the means of doing this. For instance, if you had an numeric EmployeeID field which was incremented every time a new employee joined the company then you could find the last value in your table by selecting the row of data with the highest EmployeeID value. You don't have any data items that you can use for this purpose in your table so you can't achieve this.

Paul Marfleet

Query regarding Trigger

Aavesh Agarwal9-Oct-07 23:37

Aavesh Agarwal

9-Oct-07 23:37

Suppose if i have 4 After triggers and i want to give the order for them in which they fired i can do that with the help of sp_setordertrigger but with this i can only specify which one can be fired first and last one but what abut the rest of triggers how can i order them..please help me..in this i m new for trigger...

Big Grin | :-D

Sucess need one master Stroke Big Grin | :-D

Thanks and Regards
Aavesh Agarwal

Swap the columns values [modified]

Aavesh Agarwal9-Oct-07 22:55

Aavesh Agarwal

9-Oct-07 22:55

How can we swap the values of the Two columns..using query

is it possible or not if it is then how?

Please let me know the query.......

-- modified at 5:26 Wednesday 10th October, 2007

Re: Swap the columns values

Joe9-Oct-07 23:26

Joe

9-Oct-07 23:26

Aavesh Agarwal wrote:
swap the values of the Two columns

Assuming that both the columns are of the same data type
update yourtable set columnA = columnB, columnB = columnA

Re: Swap the columns values

Aavesh Agarwal9-Oct-07 23:28

Aavesh Agarwal

9-Oct-07 23:28

thanks for ur reply..its working but logically how it happens in sqlserver..by using a=b or b=a ..it will not give the same values in the both the columns...

problem in a tipical query(plzzz help me)

Arihant rathi9-Oct-07 20:57

Arihant rathi

9-Oct-07 20:57

hi this is ajay rathi,
i am facing a very big problem,

select month(Start_date) as start_Month, year(Start_date) as start_Year,
sum(case when Status = 'Active' then 1
else 0 end) as Active,
sum(case when Status = 'Inactive' then 1
else 0 end) as Billed
from tbl_Post_Job where Company_Id_Fk = 60 and Start_date between Start_date and Exp_date group by year(Start_date), month(Start_date)order by year(Start_date), month(Start_date)

in this query i want many things,first of all i want to say this is not right query i want query from you..

(1)month(Start_date) as start_Month, year(Start_date) as start_Year
from this line i want month and year from Start_date to Exp_date

(2)in active condition,if any company's status is active from january to april so in this condition the entry of active should be come in january to april in every month

(3)group by year(Start_date), month(Start_date)
in group by section same problem,i want data from Start_date to exp_date

actually the main problem i m facing is this that when i need month and year together and active postion and billed position saperatlly..
actually i need this data in following form...

Date | Active | Billed
------------------------------------
january,2007 | 4 | 2
feburary,2007 | 7 | 5
march,2007 | 4 | 2
april,2007 | 6 | 4

------------------------------------
january,2007 is the start_date and april,2007 is the exp_date

actualy above data january,2007 is coming in form of 1,2007 but i need this data in form of january,2007 so plz tell me how can i convert this data...

plz if any one needs any clarification then plz ask me about this because i need this query immediatly..
plz try to help me as soon as posible

Ajay Rathi
software engineer
NOIDA(UP),INDIA

Re: problem in a tipical query(plzzz help me)

joemonvarghese9-Oct-07 22:32

joemonvarghese

9-Oct-07 22:32

hi Ajay

Actually i didn't undestand your Qn...

but using the below qry u will get the name of the month..
select datename(month,month(start_date)) from <tablename>

Regards

Joe

Re: problem in a tipical query(plzzz help me)

Arihant rathi10-Oct-07 0:28

Arihant rathi

10-Oct-07 0:28

you gave me solution of one problem "datename(month,month(start_date))"
but this is giving me again and again only "january" nothing else..
so plz give me the other solution..

and wht you could not under stand in my first problem plz tell me....
i need the solution...
plzzzzzzzz

Ajay Rathi
software engineer
NOIDA(UP),INDIA

Re: problem in a tipical query(plzzz help me)

pmarfleet10-Oct-07 1:55

pmarfleet

10-Oct-07 1:55

Arihant rathi wrote:
plzzzzzzzz

Enough with the textspeak please. It's really annoying.

If you want to get the year/month description, concatenate the result of datename(month,month(start_date)) and year(start_date).

Paul Marfleet

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