|
Try this
select *
from syscolumns
where id = ( select id from sysobjects where name = 'Table1' )
and colid in ( select distinct keyno from sysindexkeys where id = (select id from sysobjects where name = 'Table1' ) )
Where Table1 is the table name for which you are trying to find Primary Keys
Bhaskara
|
|
|
|
|
Or
If you are using VB.NET or some app, you can use SQLDMO components provided by MS SQL Server and manipulate DB objects, iterate through databases and tables and find out info about DB objects
Bhaskara
|
|
|
|
|
You should never read data straight from the sys... tables. The next service pack you install could completely change their schema/usage.
Instead, try these two queries. Once you see the data, you should be able to figure out how to combine them to meet your needs:
<br />
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS<br />
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE<br />
Grim (aka Toby) MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL
GO
(0 row(s) affected)
|
|
|
|
|
Hi there!
I'd like to create a batch file (*.bat) into which I can place osql calls that will run a couple of stored procedures (in order to create a database or populate some tables, etc.). I have a couple of probs with it:
1. I'd like to only redirect errors into a file. How can this be done? If I use the /o param the complete inputfile gets written out! And how can I suppress those >1 >2 >3... (what do they represent anyway?)
2. Is there a way to do variables in a batch file, which later can be used? It like to create for example a variable holding the username at the top of the *.bat file which I later can reuse when running osql commands.
3. Any samples, better ideas, hints?
Thanks for reading,
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
how to create DSN at run time of our database.
Actualy i want that when i deliver the product to user
and he execute the application ..my application
create the DSN of its database ownself..
that is user not build it by using administrative tools in control panel.
i want to do it by CODE .. that i can acces my data base.
any code ,, sample ???
thanx
|
|
|
|
|
DSN is configured in the registry - simple reg key adding & writing fns work ok - look in HKEY_LOCAL_MACHINE\Software\ODBC and you should be able to figure it out
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I cant Get ut point ...
plz send any sample / code line etc.
i wanna make user DSN ...
thanx
|
|
|
|
|
OK - to make a DSN, you need to make some keys in the registry. This can be acheived using the WIN32API function RegCreateKeyEx. (There is probably a simpler method in .NET).
If you open regedit, and navigate to HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.ini, then you will see several keys.
The simplest way to find out what you need to add, is to use the UI to create the DSN that you need, and then look at what it adds to this key. (since different sources need different information). Once you know what needs adding, you can write some code to add the keys.....
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I got it ..but cannot build code.
can u send me some code plz
plz at zahid_ash@yahoo.com
thanx
|
|
|
|
|
I need to create a new table in MS Access which has an auto number field as primary key.
Thanks
|
|
|
|
|
in database window :
Select tables
Click new
Design view -> OK
set Fieldname = (your Filed name) and datatype = AutoNumber
click primary key from toolbar.
Save table.
|
|
|
|
|
well actually i needed to do this through a SQL query.
|
|
|
|
|
FASTian wrote:
well actually i needed to do this through a SQL query.
you should tell me 
Create table ABC(MyID COUNTER primary key )
|
|
|
|
|
sorry forgot to mention that .....really appreciate your help
thanks
|
|
|
|
|
Good day.
I am a beginner in VB.NET. I have a problem that I used (TheTable.Rows.Count + 1) to generate the no. for the customer ID. If I deleted some of the records and then add a new records, the customer ID will not correct. This is because I am using TheTable.Rows.Count + 1, what I can do to solve this problem?
How do check the last customerID in the Customer table in SQL server? can this solve my problem? I am using ADO.net.
The code i am writing is :
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
SqlDataAdapter1.Fill(Me.DataSet11, "Customer")
Dim TheTable As DataTable = Me.DataSet11.Tables(0)
Dim aRow As DataRow = TheTable.NewRow()
txtCustID.Text = TheTable.Rows.Count + 1
ClearForm()
txtCustName.Focus()
end sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
Dim TheTable As DataTable = Me.DataSet11.Tables(0)
Dim aRow As DataRow = TheTable.NewRow()
aRow("CustID") = TheTable.Rows.Count + 1
aRow("CustName") = txtCustName.Text
aRow("CustAdd") = txtAdd.Text
aRow("Postcode") = txtPostcode.Text
aRow("City") = txtCity.Text
TheTable.Rows.Add(aRow)
SqlDataAdapter1.Update(Me.DataSet11, "Customer")
txtCustID.Text = aRow("CustID")
MessageBox.Show("Customer Was Successfully Added ", "VIP", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtCustID.Text = TheTable.Rows.Count + 1
ClearForm()
txtCustName.Focus()
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
If anyone know how to solve this problem, please post.
Thanks in advance!
viv
|
|
|
|
|
Write a query to get Max(custId).
Select max(custID) from tblCustomer
What this will do is get the maximum value in the cust id column from the table and return it to you, just add 1 to it and show thas as your next custID.
Tarakeshwar
CCIE Q(Routing and Switching), MCSE Security
|
|
|
|
|
Wrong!!!
You cannot rely on the identity to always 'grow by one'
If you want to know the latest Identiit value of a table use:
IDENT_CURRENT('table_name')
If you want the Identity of the record you just inserted use:
SCOPE_IDENTITY( )
Wout Louwers
|
|
|
|
|
I guess, MAX thing will surely work in anycase.
And also, I heard, SQL Server looses the current identity value when server is restarted, I think it is in 6.0/or 6.5 version. After restarting, you need to run dbcc consistency check on the database to restore the identity values.
Bhaskara
|
|
|
|
|
I have sum DataSets and DataAdapters on my MainForm.
I configure and fill the datasets through the DataAdapters on the MainForm.
I want to use this configurated DataAdapters and DataSets in all my other form, without reConfiguration or refilling.
please guide to me.
thank you.
Hadi : hd_ali@yahoo.com
|
|
|
|
|
Write a common class. In that define your DataSet and the Adapter.
You could write a function called executeQuery in this class. So from the main form you can call this function and fill the dataset. You can call this function by creating a object of that class in any of your forms having the same namespace.
Tarakeshwar
CCIE Q(Routing and Switching), MCSE Security
|
|
|
|
|
Or
If you want these datasets to be available only for these two forms, write a constructor in the second form, which will accept dataset as a parameter. When calling the second form, send the dataset from the MainForm to Second form.
Bhaskara
|
|
|
|
|
I m using VC++ and Access as database
ti insert a Date it take following format...
db->ExecuteSQL ("INSERT INTO Tab values ('20-JUNE-1991') ");
But to delete a date it take #mon/dd/yyyy# format
db->ExecuteSQL ("DELETE from Tab WHERE [Tab].[date] = #7/21/1991# ");
i not under stand y the fornat differnce in delete there is a hash # ..
Now i want to fetch records between two dates
i m using following query
but it yields only one record .. but i have many record in database
if(!rs->IsOpen())
{
rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * from Tab WHERE [Tab].[date] BETWEEN #6/25/1992# AND #6/21/1994# ");
int n =rs->GetRecordCount ();
if(n==0)
{
rs->Close ();
db->Close();
AfxMessageBox (" NO record found");
}
else
{
db->Close();
CString ss;
ss.Format ("%d" , n);
AfxMessageBox (" found no of records... " +ss);
}
}
plz rely me thanx
how can i get all records between two given dates .
|
|
|
|
|
The reason you don't need # for the insert is because the date is in DD-MMMM-YYYY format.
When you insert a date, it is preferable to use ISO format, which is YYYY-MM-DD or YYYY/MM/DD (I know, ISO is supposed to be YYYYMMDD, but it doesn't work with Access without the / or - ).
Here's an example on inserting date:
INSERT INTO tblTest(dtmDate) VALUES (#2004-06-17)
When you query data using daterange, also use the ISO format otherwise the DD part may be assumed as MM or vice versa.
Here's an example on specifying date range condition:
SELECT * FROM tblTest WHERE tblTest.dtmDate BETWEEN #2001-01-01# AND #2002-01-15#
Hope it helps. 
|
|
|
|
|
I tried it but .. again it is fetching only ONE record.
is there any problme of datatype. BCOZ in ACCESS
datatype is Date/Time .. is Time plays a pert in it
when u using only date ..
problm is still there ..
|
|
|
|
|
If you only have Date in your Date/Time column you don't need to specify the Time part.
I have tested the date range condition and it is supposed to return all date in between the specified range.
You might want to check whether the Day and Month of the date in the database is stored correctly, i.e. 01/06/2004 is 1 June 2004 not 6 January 2004.
Do some test with the data with date in between the range, e.g. with SELECT * WHERE dtmDate = #2004-06-01#
Hope it helps.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
