SQL server questions: understanding

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.

Hello,
I am building an application for Windows Forms using. I am new to SQL
Server 'Views'. Are the following correct understanding of their use?
1.) I believe a view can be referenced in a stored procedure something
like this;
Select * from View1
Is that true?
2.) I believe I can update to a view even when it includes two tables.
I intend to build a dataAdapter to a view for select and update. I
believe it can keep track of how to perform the update? Do I understand
its use correctly?
3.) Is it necessary to include both the the PK and FK when two tables
are part of the view or does it know the relationship established in
the database in the 'diagrams' area take care of that?
Thank you,
dbuchanan1) Yes
2) As long as you update only one of the member tables. That said, some
views are not updateable.
3) No
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
<dbuchanan52@.hotmail.com> wrote in message
news:1132411200.326003.199900@.g49g2000cwa.googlegroups.com...
> Hello,
> I am building an application for Windows Forms using. I am new to SQL
> Server 'Views'. Are the following correct understanding of their use?
> 1.) I believe a view can be referenced in a stored procedure something
> like this;
> Select * from View1
> Is that true?
> 2.) I believe I can update to a view even when it includes two tables.
> I intend to build a dataAdapter to a view for select and update. I
> believe it can keep track of how to perform the update? Do I understand
> its use correctly?
> 3.) Is it necessary to include both the the PK and FK when two tables
> are part of the view or does it know the relationship established in
> the database in the 'diagrams' area take care of that?
> Thank you,
> dbuchanan
>|||dbuchanan52@.hotmail.com wrote:
> Hello,
> I am building an application for Windows Forms using. I am new to SQL
> Server 'Views'. Are the following correct understanding of their use?
> 1.) I believe a view can be referenced in a stored procedure something
> like this;
> Select * from View1
> Is that true?
Yes

> 2.) I believe I can update to a view even when it includes two tables.
> I intend to build a dataAdapter to a view for select and update. I
> believe it can keep track of how to perform the update? Do I understand
> its use correctly?
Copy&Pasted from SQLServer Documentation:
Updatable Views
You can modify the data of an underlying base table through a view, as
long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must
reference columns from only one base table.
The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in
any other way, such as through the following:
An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a
computation and are also not updatable.
The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
The previous restrictions apply to any subqueries in the FROM clause of
the view, just as they apply to the view itself. Generally, the Database
Engine must be able to unambiguously trace modifications from the view
definition to one base table. For more information, see Modifying Data
Through a View.
If the previous restrictions prevent you from modifying data directly
through a view, consider the following options:
INSTEAD OF Triggers
INSTEAD OF triggers can be created on a view to make a view updatable.
The INSTEAD OF trigger is executed instead of the data modification
statement on which the trigger is defined. This trigger lets the user
specify the set of actions that must happen to process the data
modification statement. Therefore, if an INSTEAD OF trigger exists for a
view on a specific data modification statement (INSERT, UPDATE, or
DELETE), the corresponding view is updatable through that statement. For
more information about INSTEAD OF triggers, see Designing INSTEAD OF
Triggers.
Partitioned Views
If the view is a partitioned view, the view is updatable, subject to
certain restrictions. When it is needed, the Database Engine
distinguishes local partitioned views as the views in which all
participating tables and the view are on the same instance of SQL
Server, and distributed partitioned views as the views in which at least
one of the tables in the view resides on a different or remote server.
For more information about partitioned views, see Creating Partitioned
Views.

> 3.) Is it necessary to include both the the PK and FK when two tables
> are part of the view or does it know the relationship established in
> the database in the 'diagrams' area take care of that?
You define Views in SQL... so you have to define the "join" that you
want to use.
> Thank you,
> dbuchanan
>

Monday, February 13, 2012

a little trouble understanding about primary keys

Thursday, February 9, 2012

A few simple questions about SQL Server 'Views'

SQL server questions

Blog Archive

About Me