|
Hi,
when we create a non clustered index for a table, an index is maintained in the database, but when we create the same on a view , does it get saved anywhere !! , i am also aware that views are not persisted in the DB, so i am a bit confused in this.
|
|
|
|
|
When selecting from a view, you will normally use the indexes on the base tables. In SQL Server (2000/2005) you can create indexes on a view (views are really virtual tables), but there are several caveats on how they are created. See Books Online for more information.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
StringBuilder szMyQuery = new StringBuilder();
szMyQuery.Append("UPDATE [Player Data] SET [Player Data].[Father's Email] = \'");
szMyQuery.Append(txtDadEmail.Text);
szMyQuery.Append("\' WHERE [Player Data].[Player Number] = &");
szMyQuery.Append(int.Parse(lblOutput.ToolTip.ToString()));
szMyQuery.Append("&");
See what is wrong?
The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.
|
|
|
|
|
ExpertComing wrote: See what is wrong?
You're code is susceptable to a SQL Injection attack.
However, if this is not the problem you are having (and if it isn't now, it will be when the code is in production) you will have to give us more information. For example: What do you mean by "wrong"? Is it a compiler error? Is there a runtime error? What is the error message? What is the intended behaviour? What is the actual behaviour?
|
|
|
|
|
Colin Angus Mackay wrote: code is susceptable to a SQL Injection attack
It is amazing how many code pieces come through these forums that are susceptable to injection attacks...
I wish CPHog could have a keyboard shortcut to your article 
If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa
|
|
|
|
|
For a start, you don't have to escape apostrophes in your query. I agree with Colin that you are wide open and vulnerable to Injection attacks with this type of query (you'd be much better using a stored procedure). Plus, why have you formatted your query like this? Why not use AppendFormat. Why parse an item to an integer? SQL doesn't care what your initial datatype was (or even know it). Are you really using the ToolTip to store the value (is it not in the Tag)?
You could rewrite this as:
szMyQuery.AppendFormat("UPDATE [Player Data] SET [Father's Email] = '{0}'", txtDadEmail.Text);
szMyQuery.AppendFormat(" WHERE [Player Number] = {0}", lblOutput.Tag.ToString());
I wouldn't recommend this code (see Sql Injection Attacks[^] for a much better way to do this).
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks everyone. Works great and I'll read that article.
The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.
|
|
|
|
|
|
Richard Hartness wrote: If I want to create a DB through the .NET SQL Server APIs by executing a 'CREATE DATABASE' script file against the local SQL Server Express, how can I determine it's install directory?
Have you tried reading the SQL Server documentation for CREATE DATABASE[^]?
|
|
|
|
|
VB 2005
I am trying to get my program to delete datarows out of a resourced database. No matter how i put the code it wont delete the rows. I cant make the program delete all or any of the datarows.
|
|
|
|
|
Karma31251 wrote: I am trying to get my program to delete datarows out of a resourced database
What exactly is a "resourced database"?
|
|
|
|
|
Colin Angus Mackay wrote: What exactly is a "resourced database"?
I'd like to know too. Google didn't really show anything about it 
If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa
|
|
|
|
|
In a Stored Procedure
sp
{
...
..
Query1
Query2
Query3
....
..
}
Here in the above storedprocedure the Query2 should be exceuted whenever the Query1 fails and Query3 should be executed invariable the above condition.
|
|
|
|
|
its been a long time since i programmed in C# but you could do this
try
{
query1
catch
try
{
query2
catch
try
{
query3
catch
messageBox(error)
}
}
}
Something to that effect.
|
|
|
|
|
He wanted to do all that in a stored procedure.
|
|
|
|
|
Inside your stored procedure you could do the following:
INSERT INTO ... (Query 1)
IF @@Error <> 0
BEGIN
INSERT INTO .... (Query 2)
END
UPDATE ... (Query 3)
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Yes i wanted to do it all in Stored procedure,
Thanks a lot Pete
|
|
|
|
|
Hi,
2 tables. User table and Request Table. Request table has a foriegn key in the Request table called AssignedToID.
I would like to store a Request record without specifying the AssignedToID. I am receiving the following error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tblRequest_tblUser1'. The conflict occurred in database 'PMSystem', table 'tblUser', column 'inUserID'.
The input for AssignedToID is a Dropdown List. Here's how it is bound:
lst2.Text = "None"<br />
lst2.Value = 0<br />
lst2.Selected = True<br />
<br />
sQueryDDL = "SELECT tblUser.inUserID, tblUser.vchSurname + ', ' + tblUser.vchForename AS AssignedToName FROM tblUser ORDER BY tblUser.vchSurname"<br />
sTableDDL = "tblUser"<br />
dsDDL = clsCommon.getData(sQueryDDL, sTableDDL)<br />
dsDDL.Tables(0).DefaultView.Sort = "AssignedToName"<br />
Me.ddlAssignedToID.DataSource = dsDDL.Tables(0).DefaultView<br />
Me.ddlAssignedToID.DataTextField = "AssignedToName"<br />
Me.ddlAssignedToID.DataValueField = "inUserID"<br />
Me.ddlAssignedToID.DataBind()<br />
Me.ddlAssignedToID.Items.Insert(0, "lst2")
The AssignedToID field is not mandatory and if a user does not select anything, the default value to be stored is 0. The compiler is not liking this. What could be wrong?
Thank you in advance.
|
|
|
|
|
It looks like you are inserting a value into tblUser, but the Column inUserID needs to have the same value as what is going into tblUser.
|
|
|
|
|
To be honest, I just want to leave the "AssignedTo" value as null, but it won't let me 
If anyone has any suggestions, please help. Thank you for your reply mate.
|
|
|
|
|
Columns with constraints or keys can not have null values. You will need to either eliminate the constraint on the AssignedTo column or will have to provide the same value from the insert in both places. In the db if you run sp_help [TableName] you will see the constraints for both of your tables and I am fairly sure that you will see a constraint on the AssignedTo Column.
|
|
|
|
|
I found out what the problem was. I was leaving the AssignedTo field unassigned, but the parameter was automatically being populated with a value of 0. And ofcourse there is no User in the User table with an ID of 0, therefore it was throwing that error.
So all I had to do was put an IF statement in place. If the Value was 0, make the parameter = (variable = System.DBNull.Value)
Problem sorted. Thank you both for your replies! 
|
|
|
|
|
Hi I am having some trouble trying to figure out this sql query or should I be trying to do this with c# and not sql, I have a database table with 20 columns but I only want to return the columns that have values in at least one of their rows, so if a column consists entirely of null values I do not want to return it. I’m just wondering if anyone has any help or advice with the sql or whether or not its possible.
|
|
|
|
|
tadhg88 wrote: Hi I am having some trouble trying to figure out this sql query or should I be trying to do this with c# and not sql, I have a database table with 20 columns but I only want to return the columns that have values in at least one of their rows, so if a column consists entirely of null values I do not want to return it. I’m just wondering if anyone has any help or advice with the sql or whether or not its possible.
Sure it is possible, but you will need to check each column for a "not null" value.
|
|
|
|
|
this is the sql i am using at the moment so how wud use the not null
Select tl.date, IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',
BodyWeight, FourSiteSkinfold_mm,
FourSiteSkinfold_pct,
SquatJump,
CounterMovementJumpWithoutArms,
CounterMovementJumpWithArms,
TenMAcceleration,
TwentyMAcceleration,
ThirtyMSpeed,
EighteenM321Aerobic_TimeInRedZone,
Fifteen_35m321Anaerobic_TimeInRedZone,
BWSquatPower,
MaxHeartRate,
PremierFitness,
DistanceCovered,
HSR,
Sprint,
PF_16_30,
PF_8_120,
Agility,
HighSpeedRunOut,
SprintOut,
[BodyFat(12site)],
[Agility(R)],
[Agility(L)],
HydrationScore,
From TrainingLog tl
Join Person p on p.ID = tl.PersonID
Join TrainingFitnessTesting tft on tft.TraininglogId = tl.ID
Left Join OmegaWave ow on ow.Date = tl.Date
Where SessionTypeID = 1
And tl.Date = '01/16/2006'
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
