|
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.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
