|
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
|
|
|
|
|
Thanks Colin
By selvaraj.K
|
|
|
|
|
Hi guys:
What is the best book for learning and practice sql queries (advances queries) ?
Thanks in advance
|
|
|
|
|
SQL For Smarties[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Excellent book.
|
|
|
|
|
I am doing some research on data-warehousing and I was wondering if anybody has a good resource on the subject. I tried finding information using google, but that doesn't give me much that I can use.
WM.
What about weapons of mass-construction?
|
|
|
|
|
I have an awkward question, and i was wondering if any of you have ever seen behaviour like this before:
We have an ASP.net application that is currently in testing and has been for the past several weeks. During the last week, first one of the test sites and then another, started to throw SqlDateTime exceptions, citing a SqlDateTime overflow - The date must be between... etc...
The sql statement that seems to be causing this exception has not changed and is a fairly simple
SELECT X
FROM TABLE
WHERE TABLE_DATE = @MaxDate
where @MaxDate is a constant parameter that we set (this has also not changed, date is somewhat arbitrarily set to 31 Dec 2099 (in that format, so it's shouldn't be in any way ambiguous). The constant is a .net datetime variable.
Unfortunately, this date time problem seems to be occurring throughout the application. What i can't really understand is why this has suddenly changed, first on one web server and then on another.
We checked the localisation settings on the web servers and the database server and found that they were different - however making them the same hasn't fixed the problem and doesn't explain why they were working fine for the past few weeks...
Any help on this would be very much appreciated, i'm stumped and when i get in on Monday, our testers are going to be at a standstill... Unhappy people in our office...
|
|
|
|
|
how can i store picture in binary field and select it as binary to
picturebox by c#
MD_NADA
|
|
|
|
|
Store it in an image column (so ensure the column in the database your are using is of type image, or varbinary(max) in SQL Server 2005) and then you can persist it to the database you would any other column type (I am, of course, assuming that you do the sensible and secure thing and use parameters to pass changable data into your queries). The data must exist in a byte array first.
When you retrieve it from the database you'll get a byte array back. What you do with that byte array is off topic for this forum.
-- modified at 14:07 Saturday 19th August, 2006
|
|
|
|
|
FYI--The image data type is being deprecated in future versions of SQL Server.
When was the last time you poured some wine for you and your sweetie and went out on the front porch to watch the geometry frolic on the lake?--Rebecca M. Riordan, Designing Effective Database Systems
|
|
|
|
|
We know it is quite easy to fetch data from sql server to datatable with dataadaptor of ado.net. However, i want to store my query result with over 2000 records to sql server now. Is there any good approach that could store this datatable immediately to sql server?
Any idea is appreciated! Thank u in advance! ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
sorry to add that the format of the query result is not determined at first. waiting for ur reply!
|
|
|
|
|
I am trying to build a simple script but by Newbie scripting is showing...
The Database creates Ok but the tables are created in the master Database not in the Newly created Db as it should...
Do I need to switch to the new Db after it is created???
I fried to append the New Db name to the table but this gave me an error??? (ShowroomRegDB.ISSUESTb)
Thoughts...
CREATE DATABASE ShowroomRegDB
drop table ISSUESTb;
drop table eMailTb;
drop table ConfigTb;
drop table SuggestionTb;
CREATE TABLE ISSUESTb
(
Issueid INT identity(1,1) not null,
IssuedDate VARCHAR(50),
);
CREATE TABLE eMailTb
(
eMailid INT identity(1,1) not null,
Lastname VARCHAR(50),
Firstname VARCHAR(50),
eMailAddress VARCHAR(100),
);
When people make you see red, be thankful your not colour blind.
|
|
|
|
|
japel wrote: Do I need to switch to the new Db after it is created???
Yes.
After the create, add : "Use database ShowroomRegDB"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
