|
Hiya I am using ADO and need to be able to detect if a table already exists.
If the table exists, I need to drop the table and create this new table.
This is my code so far:
// create table and don't allow any zero value records
sqlcmd.Format( "CREATE TABLE Pxhsinv (Invoice varchar(30) NOT NULL,Invoice_Record varchar(8000) NOT NULL)" );
// execute the sql string
pConnection->Execute(_bstr_t(sqlcmd), RecordsAffected, 1);
// empty string
sqlcmd.Empty();
How do I detect if the table exists already..
Thanks.
|
|
|
|
|
I think that if the table already exists, the database will throw an error. Could you trap it?
|
|
|
|
|
Run the Query -
Select name from sysobjects where name like 'Pxhsinv' and type = 'U'
If this query gives some record in the resultset, the table is present else not.
Gaurav
|
|
|
|
|
I have a customer with a database problem. On a peer-to-peer network they run a point-of-sale system with the database on one host. This software is written such that each host accessing the database must have a drive mapped to the database home folder. A problem occurs when the data host is booted up after the clients - the share is not available, and so nothing works until they reboot everything. Manually remapping the drive is confusing for users, and I often find 5 drives mapped to the same share on a host by the time they call me.
I'd like to take a crack at rewriting the whole system (it's fairly simple - Inventory and Sales only, no G/L) and want to circumvent this problem. What is the proper way to implement the data access such that, if the database is unavailable at startup, it can reliably continue to check for it each time a user attempts access?
"The Lion shall lie down with the Lamb;
but the Lamb will not get much sleep..."
Lazarus Long
|
|
|
|
|
It depends on how you connect to the database and what kind of database it is.
onwards and upwards...
|
|
|
|
|
Obviously, and the selection of platform will depend on what techniques are most reliable and what supports them. The question remains, what is best?
"The Lion shall lie down with the Lamb;
but the Lamb will not get much sleep..."
Lazarus Long
|
|
|
|
|
In my SQL Db I have a view, which provides for me a well defined interface to obtain this infomation. Alas, this view pulls a set of data which is in format of
TestName | SectionName | QuestionText
---------+-------------+-------------
SAT | Reading | Who is cap. kirk
SAT | Reading | ...
SAT | Reading | Blah
SAT | MATH | 10+12
SAT | MATH | 10+1
SAT | MATH | 8==7
And I load this into a single table in a dataset using the fill command. Then we create the filter object. And he is the issue, How do I databind an object to the section Name column and only show the unique/ distint values?
From previous posts on other sites dataView.RowFilter() is not an option, it only supports 'where clause' like functionality. The only thing that I can not change is the information that is piped to me from the database (that is a well defined and documented interface.) Thus I need to work with the data set information some how (can I convert it xml, would that help any?)
|
|
|
|
|
you can issue a second statement
SELECT DISTINCT SectionName FROM myView
|
|
|
|
|
Right O, but that causes another trip to the database, and I already have it in memory, is there a better way?
|
|
|
|
|
Use a Hashtable, make the SectionName the key.
|
|
|
|
|
I am not a DBA and only know enough SQL to get the job done. Well, most of the time. The rest of the time I wing it and it normally works fine. This time though I am stuck and need your wisdom 
Ok I have three tables, Companies, Mailshots and Emails.
In Emails is the Status column which can contain three possible values; Sent, Failed, In Queue.
I need a view which will show me the number of Sent, Failed and In Queue rows for all Mailshots for all Companies.
So the result should be something like this:
Company | Mailshot | Sent | Failed | In Queue
---------------------------------------------
ACME | ACME101 | 100 | 10 | 150
ACME | ACME102 | 300 | 10 | 250
ACME | ACME103 | 0 | 0 | 350
I have been trying to wrap my head around CUBE and PIVOT in SQL but alas God never intended for my brain to grow so big, so I can't (wrap around that is.)
Any help, however obvious, is greatly appreciated. Thank you. *slinks off back to HTML and CSS*
Paul Watson
Bluegrass
Cape Town, South Africa
Robert Edward Caldecott wrote:
My father-in-law calls yer man bits "weasels"
|
|
|
|
|
select
C.CompanyName,
S.MailShotDesc,
SUM(case E.Status when 'S' then 1 else 0 end) 'Sent',
SUM(case E.Status when 'F' then 1 else 0 end) 'Failed',
SUM(case E.Status when 'Q' then 1 else 0 end) 'In Queue'
from
Company C,
MailShot S,
EMails E
where
S.CompanyKey = S.CompanyKey and
E.MailShotKey = S.MailShotKey
group by
C.CompanyName,
S.MailShotDesc
order by 1, 2
onwards and upwards...
|
|
|
|
|
This format is the simple way to cross-tab detail records with different statuses.
onwards and upwards...
|
|
|
|
|
Thank you very much basementman, much appreciated and it works like a bomb
Paul Watson
Bluegrass
Cape Town, South Africa
Robert Edward Caldecott wrote:
My father-in-law calls yer man bits "weasels"
|
|
|
|
|
Friends i made a trigger in SQL server. The purpose of this trigger is very simple i.e to find out the number of records updated in the table and then print it. For this purpose i am using:
select @count = count(*) from updated
This is working fine in my home computer, but i am getting error in my office computer that:
Invalid object 'updated'
i.e SQL server is unable to find the table 'updated' which is its own internal table.
How can i get rid of this error.
Thanks
Imtiaz
|
|
|
|
|
the tables exposed to triggers are 'inserted' and 'deleted'. I expect that at home, you actually have a user table named 'updated' in your db, which is why it is working.
onwards and upwards...
|
|
|
|
|
zbasementman wrote:
the tables exposed to triggers are 'inserted' and 'deleted'.
Both of these are also not working for me 
|
|
|
|
|
I think if you change your select to this format, it may work:
select @count = count(COLUMN_NAME) from INSERTED
|
|
|
|
|
hello,
i have long string to store in my database.
i am using varchar, but its too small for storing it.
i used text before, but i can't use where clause with it without using LIKE nad IS NULL.
so which datatype should i use for storing storing complete information?
LOOKING FOR GUIDENCE
ASIM NAZIR
Asim
|
|
|
|
|
i think varchar is best option.
sorry for my bad English.
|
|
|
|
|
If you can get away with it, use a varchar(7500). If your data is larger that 7500, you will need to use text.
You can also use PatIndex and CharIndex with text fields.
So, if you want to do an equality join, you can use something like:
where
CharIndex('StringToSeek',TextField) = 1
onwards and upwards...
|
|
|
|
|
Hi All!!
I have binded my text fields to DataColumns of table at runtime.In data navigation mode, all text fields of my application are disabled. When I press the 'Add New' button, all fields are enabled and cleared.
Problem is that when I focus to next control, all the data of current record is again loaded in all textboxes . While I want to remain them clear.
plzzzzzzzzzzzzzzzzz help me.
Thanx in Advance
sorry for my bad English.
|
|
|
|
|
I have a website that needs to access a SQL Server 2000 database hosted on the local machine. I've created an ODBC connection to the database and created users with applicable permissions in the database itself, but when I try and login with the query analyzer or from the website I get the error "SGL doesn't associate this user with a trusted connection." This is the DSN I'm using.
DATABASE_DSN = "Provider=SQLOLEDB.1; Password=abcd; Persist Security Info=True;User ID=abcd;Initial Catalog=extrosite;Data Source=LocalServer"
Please help 
Paul 
I have been afraid always. When you see something insurmountable ahead of you, say to yourself: "All right! I am afraid. Now that I've been properly afraid, let's go forward." That is the whole secret. - Jeanne d'Arc
modified 18-Jul-18 11:59am.
|
|
|
|
|
Don't worry peeps I've solved it 
Paul
I have been afraid always. When you see something insurmountable ahead of you, say to yourself: "All right! I am afraid. Now that I've been properly afraid, let's go forward." That is the whole secret. - Jeanne d'Arc
modified 18-Jul-18 11:59am.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
