|
Hi,
TO improve the performance of operations on a table I want to partition the table. So can someone let me know how to do this in SQL Server 2000.
Regards,
Uma
|
|
|
|
|
|
Hi
I want to create a partitioned table. can u give me the syntax for it.
Regards,
Uma
|
|
|
|
|
|
I would like to take the field values of a row in a DataTable and copy them to a new row in the same DataTable. Below is the code I used to do this.
Dim newrow As DataRow = Me.MyDataSet.MyDataTable.Rows(0)
Me.MyDataSet.MyDataTable.Rows.Add(newrow)
An exception is thrown saying this row already belongs to this table.
How can I accomplish this?
Thanks
|
|
|
|
|
Why on earth would you want to?
It is already there!
Steve
|
|
|
|
|
I have no idea why you would want to do this, but it can be accomplished with the ImportRow method (among other ways).
Me.MyDataSet.MyDataTable.ImportRow(newrow)
Why do you want to duplicate a row in the same datatable?
|
|
|
|
|
The data table contains patient information. One row, however, contains default values for any new patients. The user can edit any existing patient in the data table. When the user clicks on New the values from the default row are used to populate the new row with default values.
The primary key field cannot be included in the ImportRow. I think and error will occur when updating back to the database. Can this field be excluded?
|
|
|
|
|
Dim defaultRow As DataRow = Me.MyDataSet.MyDataTable.Rows(0)
Dim newRow as Datarow = Me.MyDataSet.MyDataTable.Newrow
'Set newRow values for all necessary columns
newRow.Item("MyColumn") = defaultRow.Item("MyColumn")
Me.MyDataSet.MyDataTable.Rows.Add(newrow)
I haven't tested this, but it should work. I would think, however, that it would be easier to simply specify default values for your columns in the dataset, or even at the database level.
|
|
|
|
|
There are hundreds of fields that are used for default values so your last idea would be to lengthy.
The importrow does not work because the table has a primary key field and that field cannot be imported. I tried unsuccessfully to remove that field from the datarow.
Any other ideas?
|
|
|
|
|
You could try passing the values from the defaultRow to the newRow using the ItemArray method, and then change the value of the primary key with the Item method.
newRow.ItemArray = defaultRow.ItemArray
newRow.Item("PrimaryKey") = NewPrimaryKeyValue
|
|
|
|
|
Being the field is a unique primary key VB might not allow you to assign a value to that field. But I will try this. Thanks.
|
|
|
|
|
I'm not sure what you mean. A primary key field needs to be assigned a value just like any other field.
|
|
|
|
|
The error I get is this:
Column 'MyTableid' is constrained to be unique
No matter what value I assign to the primary key field, even if the value I assign is unique to the table, this error is thrown.
|
|
|
|
|
Why don't you just set the columns in each row to have default values?
When you create your table, you can set the columns to have default values!
Then, if you do not change or provide a value for a particular field, the default value is inserted automatically. No need to copy a default row at all...
Steve
-- modified at 13:12 Tuesday 22nd August, 2006
|
|
|
|
|
That sounds good. But the table has a few hundred fields. Is there any way to set the columns to have default values without alot of coding?
|
|
|
|
|
Dim MyTable As DataTable
MyTable = New DataTable("MyTable")
Dim myDataColumn As DataColumn
'Create and add columns
'An Integer column
myDataColumn = New DataColumn
With myDataColumn
.DataType = System.Type.GetType("System.Int32")
.ColumnName = "Quantity"
.DefaultValue = 1
End With
tempChargesTable.Columns.Add(myDataColumn)
'A String column
myDataColumn = New DataColumn
With myDataColumn
.DataType = System.Type.GetType("System.String")
.ColumnName = "Description"
.DefaultValue = "My default text..."
End With
tempChargesTable.Columns.Add(myDataColumn)
'And so on...
'Add table to your dataset
MyDataSet.Tables.Add(MyTable)
That's how to do it in a table...
You could set the default values in your query but if you have hundreds of fields I suppose you are using a 'SELECT *' statement.
If your query is returning hundreds of fields, it is not very efficient. I assume your datasource is not normalised? If it isn't, your best solution would be to sort that out...
Where is the data coming from?
What are you going to do with the data?
Steve
|
|
|
|
|
The datasource is normalized. There is just alot of fields. The data is coming from an Access database. The data is patient info and is maintained using Add, Delete, and Edit functions I have built in. Because there are so many fields the user needs to fill in when a new patient is added the default values feature becomes very useful.
Because there are so many fields, to implement your code above would require alot of typing. What I think I can do is the following: (but I need to work out the exact syntax)
for ColNumb = 0 to MyTable.columns.count MyTable.Columns(ColNumb).DefaultValue=MyDefaultTable.Rows(0).item(ColNumb)
end for
The for next loop will scan through each column in MyTable and assign the field value from MyDefaultTable to the column default value. Both table have the exact field structure. MyTable contains the patient info and MyDefaultTable contains the default values. Think this will work?
|
|
|
|
|
I have a data entry form that I'm creating inside a class library rather than directly in a Windows Application. (Reason is that I need to be able to import the form into several different projects and I don't want to just cut and paste it into each.)
I have the form set up and linking to the database and all works fine...except that I cannot 'addnew'. When I do Me.BindingContext(DataSetName, "TableName").Addnew nothing happens.
All of the other functions of the BindingContext class are working fine. I can navigate, delete, edit & save records, but I cannot add a new record.
If I put the form in the Windows Application and re-establish the dataset and connections (due to the namespace change) everything works fine.
Any advice would be greatly appreciated.
|
|
|
|
|
I don't think that putting this code in a class library is what is causing the problem. There must be some other reason for it. When you put the code in a windows application are you absolutely sure that all you are doing is changing the namespace? Could there be some other reason that you don't quite see at the moment. Is there something additional in the namespace of the EXE that isn't in the namespace you are using in the class library that could be changing things? Are all the right things referenced in the class library?
|
|
|
|
|
All of the same references are made in both--System, System.Data, System.Drawing, System.Windows.Forms, and System.XML plus one of my own.
When I moved the form into to the Windows Aplication, I dragged the file from the class library into the Application--they were both open. Then I re-created the dataset in the Windows Application so that it would have the same namespace. I used the "Generate Dataset" tool in both.
Also...all of the other functions of the databinding were working properly.
|
|
|
|
|
Problem Solved Colin, and thanks for your insight.
Apparently there was a problem with enforcing constraints and some checkboxes on my form. I found the problem by manually coding the databindings for each text box and found that the Addnew function was working properly until I got to the checkboxes... I'm still not sure what caused that problem, but I know how to work around it. :->
|
|
|
|
|
This may be a longshot, but you could try calling the ResetBindings method on the bindingsource before you call AddNew. Perhaps the problem is that the bindings are not synchronized.
|
|
|
|
|
Hi friends. This is selvaraj.K. My requirement is When i updated data in Master table(Parent) and at the same time i inserted data in detail table(Child table).Some time the data updated in Master table but not inserted in detail table,that time we suffer a lot(So i need when the data not inserted in detail table, the master table data will be deleted automatically(i mean rollback the task).I hope u have catch my point.I have given below my stored procedure.Please find out and give assistance to me As soon as possible.
My stored procedure is
----------------------
CREATE PROCEDURE spCallerUpdation (@strHdrID VARCHAR(30),@strClaimID VARCHAR(50),@strUser VARCHAR(30),@strCode varchar(30),@strComments varchar(2000),@FollowupDate varchar(30))
AS
BEGIN
--IF @strDtlID = ''
BEGIN ----- Generate DETAIL ID
--PRINT @strDtlID
DECLARE @strDtlID VARCHAR(30)
DECLARE @maxid VARCHAR(30)
SET @maxid = CONVERT(BIGINT,(SELECT MAX(SUBSTRING(MCD_DtlID, 4 ,LEN(MCD_DtlID)-3)) FROM MARMS_Claim_DTL WHERE MCD_DtlID LIKE '%C%' AND SUBSTRING(MCD_DtlID,2,2)=RIGHT(DATEPART(YEAR,GETDATE()),2)))
--PRINT @maxid
IF @maxid IS NULL
SET @strDtlID = 'C' + RIGHT(DATEPART(YEAR,GETDATE()),2) + '0000000000001'
ELSE
BEGIN
SET @maxID = @maxID + 1
SET @strDtlID = 'C' + RIGHT(DATEPART(YEAR,GETDATE()),2) + STUFF('0000000000000',13-LEN(@maxid)+1,LEN(@maxid),@maxid)
END
DECLARE @SeqNo INT
SELECT @SeqNo = MAX(MCD_SeqNo) FROM MARMS_Claim_Dtl WHERE MCD_ClaimID = @strClaimID
IF @SeqNo IS NULL
BEGIN
SET @SeqNo = 1
END
ELSE
BEGIN
SET @SeqNo = @SeqNo + 1
END
IF @FollowupDate = ''
BEGIN
SET @FollowupDate = NULL
END
UPDATE MARMS_Claim_Hdr SET MCH_User = @strUser, MCH_LastUpdateDate = GETDATE(), MCH_SeqID = @SeqNo,
MCH_Comments = @strComments,MCH_StatusCodeID = @strCode,MCH_FollowupDate=@FollowupDate
WHERE MCH_ClaimID = @strClaimID
INSERT INTO MARMS_Claim_Dtl(MCD_DtlID, MCD_MCH_HdrID, MCD_ClaimID, MCD_Date,
MCD_User, MCD_SeqNo, MCD_Status_Code, MCD_Comments, MCD_Caller,MCD_FUDATE)
VALUES(@strDtlID, @strHdrID, @strClaimID, GETDATE(), @strUser, @SeqNo, @strCode, @strComments, @strUser,@FollowupDate)
END
END
|
|
|
|
|
Put a BEGIN TRANSACTION at the top of the procedure. Then if you detect an error you ROLLBACK TRANSACTION at the point the error occurs and exit the stored procedure. If you don't detect an error then COMMIT TRANSACTION at the end.
e.g.
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
