|
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.
|
|
|
|
|
i have a query... i'm to make a distributed database kind of thing using C# at front end and sql server at back end:
such that there are almost 4 clients,each running the same application(database), and one server. Any new entries made to the database at each client, prompts the server about changes. As a result of which the server should consolidate all the changes on the server itself in a database. the clients are independent entities and only communicate to the server.How could this consolidation be done? I am thinking of using FTP, such as cute ftp or VssConnectServer/ client etc to transfer files between client and server.. Is there a better way to deal with this scenario?
|
|
|
|
|
shum23 wrote: I am thinking of using FTP, such as cute ftp or VssConnectServer/ client etc to transfer files between client and server.. Is there a better way to deal with this scenario?
Or you could have one database on the server and allow the applications to connect to the one central database. It is, after all, what most databases are designed to do.
|
|
|
|
|
Dear All,
I have parent child relation in my single table. now i want to retrieve all the data in tree structure with joining of other table. Can any one help me to generate the query regarding this?
The Table is As below.
ParentID ChildId Name.
0 1 A
1 2 B
1 3 C
0 4 d
Another Table has the data like
ID Value
2 X
3 Y
?????How to write query to get values in hierircal struture as below?
A
B = X
C = d
Thanks.
TC.
|
|
|
|
|
Hierarchical queries I guess is supported by Oracle and MS SQL 2005, I am not sure if MS SQL 2000 supports it. You could just google for hierarchical queries and you have lot of articles explaining how to use it.
Tarakeshwar Reddy
MCP, CCIE Q(R&S)
Experience is like a comb that life gives you when you are bald - Navjot Singh Sidhu
|
|
|
|
|
Hi,
I have a DataGrid which display a DataTable from MySQL Table source.
in the DataGrid, there are these columns (from table ItemList):
ItemID, PeriodID, Debits, Credits
column PeriodID is a smallint foreign key which connect to table PeriodList.
these are PeriodList's column:
PeriodID, PeriodName
my question is simple, in DataGrid, how can I display PeriodName instead of PeriodID ? so my Datagrid columns will be like this:
ItemID, PeriodName, Debits, Credits
Thx.
|
|
|
|
|
You can simply not select the PeriodID when you are filling your grid by using a SELECT statement like this:
SELECT ItemID, PeriodName, Debits, Credits From ItemList, PeriodList WHERE ItemList.PeriodID = PeriodList.PeriodID
But since you may need to reference the PeriodID from the column, you may want to keep selecting it but hide it. To do this you need to set the visiblity of the column to false. If you are in a PC project you can just do it like this:
dgMyDataGrid.Columns("PeriodID").Visible = False
If you are in a web project you would probably want put code like this in the grid's RowDataBound event:
e.Row.Cells(1).Visible = False
(Where 1 is the index of the PeriodID column)
Hope this helps.
|
|
|
|
|
I can't use your solution. Because it means I have to query MySQL Database. My program has been designed to only query MySQL once every 15 minutes. And every process (select, insert, update, delete) will be involving DataTable in DataSet and minimum query to MySQL.
But, if there's a way to query DataTable instead of MySQL maybe it works.
thanks for ur help.
|
|
|
|
|
You should be able to simply add Bound Columns to your DataGrid control, specify which field in the DataTable each column is bound to, and then hide the other columns by setting the visibility to false.
|
|
|
|
|
|
I have 1 more question but not related.
In my program I use DataGrid to show the content of DataTable
and it shows the data nicely. But, once I tried to delete one row from MySQLBrowser while my program runs. What happen was, the deleted row still exist in my DataTable even though I pressed Refresh to fill DataTable again with new Data from MySQL.
that row is gone when I start over my program.
But, if I add new row from MySQLBrowser, and I pressed Refresh in my program, that new row is added to my DataTable ?
thx for the help
|
|
|
|
|
That does seem a little odd. Is this a Windows PC Project that you are writing or a Web Project? My first thought is to make sure that you are clearing the DataTable before you re-fill it again with data. And if that isn't the problem you can try to debug what happens when you press the Refresh button to make sure it is doing what it is supposed to. (If you have Visual Studio 2005 you can highlight your DataTable variable when in debug mode, hover the mouse cursor over it, then click on the magnifying glass icon that appears and a popup window will show you exactly what is in the DataTable. It's a very handy debug feature in VS2005)
Hope this helps.
|
|
|
|
|
This is a Windows PC Project.
If I fill DataTable with same data while DataTable still consist data, the deleted row still exists in DataTable, and the new row (also added from MySQLBrowser) is added to my DataTable.
If I clear DataSet first, then fill it again, everything works fine!. I clear DataSet because if I clear DataTable, there's and constrain error.
But if you read the procedure Fill() description, it says that Fill() can be used to refresh DataTable too, so it means, no need to clear DataTable right?
-----
I decided to set DataSet constrain to false then clear DataTable then set DataSet constrain to true then Fill DataTable again.
is this effective? or are the another more effective way?
-- modified at 22:46 Wednesday 20th December, 2006
|
|
|
|
|
With the projects I do, it is easier to just use DataTables. I don't use DataSets very much. But if what you are doing works, then it is effective.
About your problem with the added records refreshing but the deleted records not...maybe it has something to do with how you are deleting the record. If you post some of your code it would help. (And you might want to post it as a new question on the forum so it will get more attention)
|
|
|
|
|
Clearing DataTable really works. But, what if there are thousands rows?
Anywayay, here's the code:
I have a Static Class which holds the only DataSet in my program. All DataTables created are stored in that DataSet.
/*******************************************************************/
public static DataSet dsObj = new DataSet(); //the one and only DataSet in my program
public static MySqlConnection connObj = new MySqlConnection(connStr);
//method to add Table or Tables to DataSet
public static void addTblsToDsObj(String tblName)
{
String[] tblArr = tblName.Split(',');
dsObj.EnforceConstraints = false; //I remove constrain before clearing DataTable, because of ForeignKey problem
for (int i = 0; i < tblArr.Length; i++ )
{
if(dsObj.Tables.Contains(tblArr[i])){ //If DataSet has same Table, I clear that table before fill it again
dsObj.Tables[tblArr[i]].Clear();
}
}
dsObj.EnforceConstraints = true; //after needed tables are cleared, I set it to true because I want to load the primary key too.
for (int i = 0; i < tblArr.Length; i++)
{
MySqlDataAdapter daObj = new MySqlDataAdapter("SELECT * FROM " + tblArr[i], connObj);
MySqlCommandBuilder cbObj = new MySqlCommandBuilder(daObj);
daObj.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daObj.Fill(dsObj, tblArr[i]);
}
}
/*******************************************************************/
Now, everytime my Form load a Table from MySQL, I call method addTblsToDsObj(String tblName).
Note that code above works fine with deleted data outside my Program environtment (because if Clear method).
I repeat my question above, what if I have thousand of rows? it means I will clear a lot of unnecessary rows right? so I guess it's not an optimal performance.
Thx for any help. I'll try to post this as new message too.
|
|
|
|
|
hi
well I wanted to write a query that deletes a specific no of rows from a table wwhich are specified by the user
is there a way that u can do that..
plz help
thanks in advance
Rocky
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
