a query to find primary key

I have a query that can tell me the columns in a table. In this case "Contact". What I would like to add to my query is if the column is part of the primary key. Can that be done? how so?

Code Snippet

SELECT

c.name AS column_name,

c.column_id,

SCHEMA_NAME(t.schema_id) AS type_schema,

t.name AS type_name,

t.is_user_defined,

t.is_assembly_type,

c.max_length,

c.precision,

c.scale

FROM

sys.columns AS c

JOIN sys.types AS t ON

c.user_type_id=t.user_type_id

WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;

Thnx

Matt

You should be able to extend this query by including the table name. Look at this post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1973994&SiteID=1
|||I don't understand how to join the INFORMATION_SCHEMA tables to my existing query....|||

What about:

Code Snippet

SELECT

TABLE_NAME,

c.name AS column_name,

c.column_id,

SCHEMA_NAME(t.schema_id) AS type_schema,

t.name AS type_name,

t.is_user_defined,

t.is_assembly_type,

c.max_length,

c.precision,

c.scale

FROM

sys.columns AS c

JOIN sys.types AS t ON

c.user_type_id=t.user_type_id

JOIN INFORMATION_SCHEMA.[KEY_COLUMN_USAGE] CCU

ON OBJECT_ID(CCU.TABLE_SCHEMA + '.' + CCU.TABLE_NAME) = object_id

WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;

Jens K. Suessmeyer

http://www.sqlserver2005.de

a query to find primary key

I have a query that can tell me the columns in a table. In this case "Contact". What I would like to add to my query is if the column is part of the primary key. Can that be done? how so?

Code Snippet

SELECT

c.name AS column_name,

c.column_id,

SCHEMA_NAME(t.schema_id) AS type_schema,

t.name AS type_name,

t.is_user_defined,

t.is_assembly_type,

c.max_length,

c.precision,

c.scale

FROM

sys.columns AS c

JOIN sys.types AS t ON

c.user_type_id=t.user_type_id

WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;

Thnx

Matt

You should be able to extend this query by including the table name. Look at this post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1973994&SiteID=1
|||I don't understand how to join the INFORMATION_SCHEMA tables to my existing query....|||

What about:

Code Snippet

SELECT

TABLE_NAME,

c.name AS column_name,

c.column_id,

SCHEMA_NAME(t.schema_id) AS type_schema,

t.name AS type_name,

t.is_user_defined,

t.is_assembly_type,

c.max_length,

c.precision,

c.scale

FROM

sys.columns AS c

JOIN sys.types AS t ON

c.user_type_id=t.user_type_id

JOIN INFORMATION_SCHEMA.[KEY_COLUMN_USAGE] CCU

ON OBJECT_ID(CCU.TABLE_SCHEMA + '.' + CCU.TABLE_NAME) = object_id

WHERE c.object_id = OBJECT_ID('Contact') ORDER BY c.column_id;

Jens K. Suessmeyer

http://www.sqlserver2005.de

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
--
TIA,
ChrisR> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go

a little trouble understanding about primary keys

Hi,

I have a couple of questions regarding primary keys, and whether I really need one or not.

Right now, I am using a GridView control to display all the data in my Access database, but am using a SqlDataSource control to do it. Everything works fine, and I am also using the GridView to Edit/Delete records, and I am using a DetailsView control to insert new records into the database.

The questions I have are these:

1) What I have right now in the database is a value called ID, which is just an autonumber, which has the order of the database, but I would like to change it so that the database sorts by the date awarded, which is a field in the database called "mdate", and make it so that when an admin enters a new date, it sorts automatically by date. Because of that, I am not really sure if I need to have the ID value at all.

I dont understand if it will be of any use, if I want all the values to show up by date, starting from 2006 back. If anyone can explain, or tell me if I am thinking correctly?

2) Also, right now in the database, the clients who started the database inserted the values in the "mdate" field as "Awarded mm/dd/year" instead of just "mm/dd/year".

How could I write a function to go through each record in the "mdate" column, delete the word "Awarded " and then convert it into a datetime object, so I could sort it by date? Is it possible, or would I have to do it manually?

Here is the code I have now:

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Default.aspx.cs"Inherits="_Default" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"DataSourceID="myDataSource1"

Height="50px"Width="300px"OnPageIndexChanging="DetailsView1_PageIndexChanging"Font-Names="Arial"Font-Size="Smaller">

<Fields>

<asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"ShowInsertButton="True"/>

</Fields>

</asp:DetailsView>

<br/>

</div>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="false"Font-Names="Verdana"AutoGenerateEditButton="True"AutoGenerateDeleteButton="true"DataSourceID="myDataSource1"DataKeyNames="ID">

<Columns>

<asp:BoundFieldHeaderText="ID"DataField="ID"ReadOnly="true"Visible="false"/>

<asp:BoundFieldHeaderText="Name"DataField="name"/>

<asp:BoundFieldHeaderText="Department Retired From"DataField="dept"/>

<asp:BoundFieldHeaderText="Current State Of Residence"DataField="state"/>

<asp:BoundFieldHeaderText="Purpose Of Award"DataField="award"/>

<asp:BoundFieldHeaderText="Date Awarded"DataField="mdate"/>

</Columns>

<RowStyleFont-Size="Smaller"Height="50px"HorizontalAlign="Center"/>

</asp:GridView>

<asp:SqlDataSourceID="myDataSource1"runat="server"SelectCommand="SELECT * from [finawards]"ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|finawards_new.mdb"ProviderName="System.Data.OleDb"UpdateCommand="UPDATE [finawards] SET [name] = @.name, [dept] = @.dept, [state] = @.state, [award] = @.award, [mdate] = @.mdate WHERE [ID] = @.ID"DeleteCommand="DELETE FROM finawards WHERE [ID] = @.ID"InsertCommand="INSERT INTO finawards (name, dept, state, award, mdate) VALUES (@.name, @.dept, @.state, @.award, @.mdate)"></asp:SqlDataSource>

</form>

</body>

</html>

Thanks,

sls29 wrote:

1) What I have right now in the database is a value called ID, which is just an autonumber, which has the order of the database, but I would like to change it so that the database sorts by the date awarded, which is a field in the database called "mdate", and make it so that when an admin enters a new date, it sorts automatically by date. Because of that, I am not really sure if I need to have the ID value at all.

Modify your SELECT statement to add an ORDER BY with ASC or DESC clause at the end.

SelectCommand="SELECT * from [finawards] ORDER BY mdate"

Yes. It is a good idea to have a Primary Key. You may not be using it now but every table must have a PK.

sls29 wrote:

How could I write a function to go through each record in the "mdate" column, delete the word "Awarded " and then convert it into a datetime object, so I could sort it by date? Is it possible, or would I have to do it manually?

You could use a REPLACE function to remove all the "Awarded" values. something like this:

SELECT REPLACE(Field1, "Awarded", "") FROM Table1;

After you do that you can manually change the datatype of the column

|||

Close, you want:

UPDATE Table1 SET Field1=REPLACE(Field1,"Awarded ","")

that'll get rid of the pesky text.

Yes, every table should have a primary key. If you want to pull back by mdate, fine, put an index on it too. But without a primary key column like the id field, what if two awards are given out to the same person/people/thing on the same day? How would/could you distinguish between the records if all the columns are exactly the same? That's what the ID field is doing for you. In addition, if you have records that refer back to the award (by ID), then if at a later time you decide to change the mdate or some other field in the table, then your other tables will still be able to find the corresponding record because the ID never changes (for that record).

|||

Thank you for the help.

So, basically, I can sort the GridView display anyway I want, but will still need the ID value there, just for unique identification purposes? I do not have to have the ID effect the ordering of how the GridView will display...

About how to get rid of the "Awarded" text. So, basically, I should be able to run a command:

UPDATE finawards SET mdate=REPLACE(mdate, "Awarded ", "");

Right in Access, and then change the column type to a date/time object?

Thanks again, I really appreciate it. I am trying to learn and get practice with ASP.NET 2.0, and it is nice to have a place to ask questions, and get some advice!

|||

1). Personally,I try my best not to use date as primary key as it can be duplicated. If the reason just want to get the data sorted in the database, I believe you can always sort the data in your query. So, I'd use the ID for PK.

2). Try this

// this will set the mdate to the 10 characters of the mdate from the right, I assume the last 10 characters in the mdate field is all date format.

Update [Table_Name] Set mdate = Right(mdate,10);

run the query, and change the column type to datetime manually in the design view.

good luck

|||

Thanks again for all the help. I changed all the values of my "mdate" field to take away the "Awarded " test, and then converted that field to be of type "datetime" and changed the format string to "Short Date" so it would only show the date in the form mm/dd/year.

But, for some reason, when it displays the mdate field in the GridView it shows up as "mm/dd/year 12:00am".

Is there a reason it is showing a time as well, even though there is no time in the database? Is there something I have to add to my code so that it only shows up in short date form?

Right now, I have the same code except for theORDER BY mdate addition to my SelectCommand.

I was not sure where to add the formatting restrictions. When I try to insert data also, it also gives me a type mismatch error. I am assuming that is because I put in only a date, and not a time?

Thanks in advance for any advice.

|||

I was able to get only the date to display in the GridView by adding this to my boundfield for the mdate field.

DataFormatString = "{0:mm/dd/yyyy}" HtmlEncode="False"3

The problem I am having now is that when I try to insert a new record, I get a "Data type mismatch criteria" error.

I was reading that the problem is because even if it is in the right format, Access will not allow a DateTime object to be inserted as a string. How can I convert the value that is entered into the "mdate" textbox created by the DetailsView control to a ShortDate object so that I can insert it into the database?

Thanks again.

|||

I tried a couple of new things to fix my problem, but with no luck.

First, I tried adding the InsertParameters collection to my SqlDataSource and set the Type of my "mdate" as DateTime, but that did not work.

<InsertParameters>
<asp:Parameter Name="ID" Type="String" />
<asp:Parameter Name="name" Type="String" />
<asp:Parameter Name="dept" Type="String" />
<asp:Parameter Name="state" Type="String" />
<asp:Parameter Name="award" Type="String" />
<asp:Parameter Name="mdate" Type="DateTime" />
</InsertParameters>

Then, I tried to modify the ItemInserting function of the DetailsView so that it checks for which value is the "mdate" function, and converts it to a DateTime object, but I am still getting the same error.

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
for (int i = 0; i < e.Values.Count; i++)
{
if (e.Values[i].ToString().Contains("/"))
{
Convert.ToDateTime(e.Values[i].ToString());
}
else
{
e.Values[i] = Server.HtmlEncode(e.Values[i].ToString());
}
}
}

I know the function is a little primitive, but I just figured that if the current value contains a "/", it would have to be the mdate field, since there is no other field that would have that in it.

Is there anything fundamentally wrong? I am totally confused...

|||

Try playing around in the SqlDataSource_Inserting event. There is a lot more control there with what actually gets sent to the database.

Just curious, shouldn't you be using the AccessDataSource control with Access?

|||

Thanks for the tip, I will look at that event also. The reason I am using the SqlDataSource control is because in the quickstarts it had mentioned that you can use the SqlDataSource control with an Access database also, and it would give you the "added functionality" that comes with the SqlDataSource control.

Maybe I can try to change it, and see if that works better.

|||

Well, I tried to change my SqlDataSource_Inserting function to the following:

Convert.ToDateTime(myDataSource1.InsertParameters["mdate"].ToString().Trim());

That did not work, but atleast now I get a different error

Now, when I try to insert a new record, I get the error:

"The string was not recognized as a valid DateTime. There is an unknown word at index 0".

Is the above code that I wrote enough for the Inserting function? I thought since the only problem I was having was with mdate, it would convert that value to a DateTime object, and then perform the insert command, but now I am getting this new error...

I tried adding the Trim() to my Convert statement thinking that was why I was getting my error, but I am still getting it.

Any ideas? Thanks again...

|||

I finally got this resolved! I ended up getting rid of the DetailsView control altogether, and just included a regular form to insert into the database. Then I used an OleDbCommand and a ExecuteNonQuery method to insert the values.

I was able to insert the date properly also using DateTime.Parse(), and everything is working great now.

Thanks to everyone for their help.