SQL server questions: 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">

<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">

<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.

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 Smile [:)]

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... Confused [*-)]

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.

Monday, February 13, 2012

a little trouble understanding about primary keys

No comments:

Post a Comment

SQL server questions

Blog Archive

About Me