|
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'
|
|
|
|
|
how to create the table in sqlserver/msaccess?
how to insert the table in sqlserver/msaccess?
how to update the table in sqlserver/msaccess?
how to delete the table in sqlserver/msaccess?
how to view the table in sqlserver/msaccess?
with regards,
sivasubramanian.k
|
|
|
|
|
How to google sqlserver/msaccess?
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.
|
|
|
|
|
siva.k wrote: how to create the table in sqlserver/msaccess?
how to insert the table in sqlserver/msaccess?
how to update the table in sqlserver/msaccess?
how to delete the table in sqlserver/msaccess?
how to view the table in sqlserver/msaccess?
 Are all your posts like this???
Learn to study books / search the net, and do things for yourself
|
|
|
|
|
hi,
Like stored procedures can we declare variables in views ?
If yes How they are declared and if not what is the alternative?
please guide.
thank you.
yog
hui gfgh kgdgrt njjn hjgkn
|
|
|
|
|
You can't declare a variable because a view is just that - a view of data held in the database. Typically, you would create your view and then use a SP with variables to retrieve information based on criteria. Alternatively, if you are trying to use variables to perform more complex operations on your data, you might be able to achieve similar effects using functions such as COALESCE, etc...
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
