I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.
View 1 RepliesI would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)
View 6 Replies View RelatedHi All,
I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar]
(2)-ReceivedTime [datetime]
(3)-Status [char].
Now here how to remove duplicate entry of same mobile number to MobileNumber field?
Regards
Shaji
How can i keep only one record from duplicate entry.
Example
COLUMN1COLUMN2COLUMN3COLUMN4
AAA121-12
AAA121-13
AAA121-14
Here i want to keep only the top record and want to delete other 2 rows.
Hello,
I have the following query,
SELECT GroupInfo.GroupID, GroupInfo.GroupName
FROM GroupInfo INNER Join DeviceGroup ON(DeviceGroup.GroupID=Groupinfo.GroupID)
INNER Join Deviceinfo ON (Deviceinfo.SerialNumber=DeviceGroup.SerialNumber )
It's out put is as follow:
Group ID GroupName
0 Abc
1 Beta
0 Abc
0 Abc
0 Abc
1 Beta
2 Alpha
Now, I want to make such query which will give me result as a Group ID and Group Name but not in repeating manner, Like,
Group ID GroupName
0 Abc
1 Beta
2 Alpha
Hope I explained what I need to see in result pane.
Thanks,
Junior
Hi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
thanks
Hi,
I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows:
Duplicate data might arrive at your database via an interface to another system
Data is loaded into table from other sources because during data loads, the integrity constraints are disabled
Merging data from disparate systems
Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me???
Thanks,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sectionexpenses]
(@sectionname varchar(30),
@ExpensesName varchar(max),
[code]....
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..
View 10 Replies View RelatedWe have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?
View 4 Replies View RelatedI'm trying to modify a specific row in a table.
$paper_id = 2A
$query = "UPDATE book SET paper_id='$paper_id', title='$title'";
I'm getting this error <<query failed: Duplicate entry '2A' for key 'PRIMARY'>>
Structure:
Column: paper_id
Type: Char
Length: 20
Deafult: None
How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ?
Thanks in advance !
stephen
I am wanting to create a query so that I can combine each of the found duplicates into one entry.
An example of this is:
Name |ID |Tag |Address |carNum
-------------------------------------------------------
Bob Barker |2054| 52377 |235 Some road |9874
Bill Gates |5630| 69471 |014 Washington Rd. |3700
Bob Barker |2054| 97011 |235 Some road |9874
Bob Barker |2054| 40019 |235 Some road |9874
Steve Jobs |8501| 73051 |100 Infinity St. |4901
John Doe |7149| 86740 |7105 Bull Rd. |9282
Bill Gates |5630| 55970 |014 Washington Rd. |3700
Tim Boons |6370| 60701 |852 Mnt. Creek Rd. |7059
In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:
Bob Barker |2054|52377/97011/40019|235 Some road |9874
Bill Gates |5630|69471/55970 |014 Washington Rd.|3700
Steve Jobs |8501|73051 |100 Infinity St. |4901
John Doe |7149|86740 |7105 Bull Rd. |9282
Tim Boons |6370|60701 |852 Mnt. Creek Rd. |7059
Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
,DischDate
,AdmDiagCode
,ID
,AdmDiagCodeDesc
,AdmittingDiagnosis
,AnyDx
,DischDx
,ECode
,IntOOS
,PrinDxAnesDesc
,PrinDxAnesInd
,PrinDxCode
,PrinDxCondDesc
,PrinDxCondInd
,PrinDxDesc
,PrinDxEqualsAdmDxYnu
,SecDx10AnesDesc
,SecDx10AnesInd
,SecDx10Code
,SecDx10CondDesc
,SecDx10CondInd
,SecDx10Description
,SecDx11AnesDesc
,SecDx11AnesInd
,SecDx11Code
,SecDx11CondDesc
,SecDx11CondInd
,SecDx11Description
,SecDx12AnesDesc
,SecDx12AnesInd
,SecDx12Code
,SecDx12CondDesc
,SecDx12CondInd
,SecDx12Description
,SecDx13AnesDesc
,SecDx13AnesInd
,SecDx13Code
,SecDx13CondDesc
,SecDx13CondInd
,SecDx13Description
,SecDx14AnesDesc
,SecDx14AnesInd
,SecDx14Code
,SecDx14CondDesc
,SecDx14CondInd
,SecDx14Description
,SecDx15Code
,SecDx15Description
,SecDx1AnesDesc
,SecDx1AnesInd
,SecDx1Code
,SecDx1CondDesc
,SecDx1CondInd
,SecDx1Description
,SecDx2AnesDesc
,SecDx2AnesInd
,SecDx2Code
,SecDx2CondDesc
,SecDx2CondInd
,SecDx2Description
,SecDx3AnesDesc
,SecDx3AnesInd
,SecDx3Code
,SecDx3CondDesc
,SecDx3CondInd
,SecDx3Description
,SecDx4AnesDesc
,SecDx4AnesInd
,SecDx4Code
,SecDx4CondDesc
,SecDx4CondInd
,SecDx4Description
,SecDx5AnesDesc
,SecDx5AnesInd
,SecDx5Code
,SecDx5CondDesc
,SecDx5CondInd
,SecDx5Description
,SecDx6AnesDesc
,SecDx6AnesInd
,SecDx6Code
,SecDx6CondDesc
,SecDx6CondInd
,SecDx6Description
,SecDx7AnesDesc
,SecDx7AnesInd
,SecDx7Code
,SecDx7CondDesc
,SecDx7CondInd
,SecDx7Description
,SecDx8AnesDesc
,SecDx8AnesInd
,SecDx8Code
,SecDx8CondDesc
,SecDx8CondInd
,SecDx8Description
,SecDx9AnesDesc
,SecDx9AnesInd
,SecDx9Code
,SecDx9CondDesc
,SecDx9CondInd
,SecDx9Description
,VisitTypeCode
,accountnumber
,DischVisitTypeCode
FROM ampfm.rpt_AdtVisitDiag
)
--Select * From CTE
Delete From CTE Where ROWID > 1
Hi there, newbie here.
I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server.
I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them.
Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter.
Here's what I want to do:
I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created.
Here's where I'm up to:
I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control.
Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along?
Cheers!
Hi all,
Below are my tables:
Rowid
Name
1
John
2
Peter
3
Jack Table
Rowid
Rowid1
1
1
1
2
1
3
2
1
2
2
3
2
3
3 Table1
Rowid1
Country
1
USA
2
UK
3
JAPAN Table2
I tried to get the Country for all the people in the first table.
My SQL statement is: SELECT Table.Name, Table2.Country FROM Table Left Join Table1 ON Table.Rowid = Table1.Rowid Left Join Table2 ON Table1.Rowid1 = Table2.Rowid1
My final result is shown on Table2. But is it possible if I can generate the results without the duplicate Names (as shown below)?
Name
Country
John
USA
UK
JAPAN
Peter
USA
UK
Jack
UK
JAPAN
Any advice will much appreciated.
Hello All,
I am using the Web Site Administration Tool with the ASP.NET login controls to create/manage user logins & roles for my site.
I have exported the ASPNETDB.MDF into my SQL Database ready to use with my app. The problem that I'm having is that whenever I add a user, using either the Web Site Administration Tool or the Classes available in the code-behind. The entry is doubled up in the database.
This is not a problem when logging on....however, when it comes to deleting a user it will only take one of the entries out and leave the duplicate in there. So if the administrator has deleted a user from the app and then tried to create another one with the same UserName (Which whomever is well within his/her rights to be able to do), they cannot because the entry which has "stay behind" in the database conflicts with the entry.
Any help on this matter would be greatly apprieciated. If any more info on this matter is required please ask (It was quite hard to try and explain this :-)
I have formview and want to get uniq id from SQL Server 2005 after it inserted.
I think i should put some code in the event of oniteminserted, anybody have sample code
Thanks
Hu
Hello,I am trying to determine the best way to do the following. For simplicity we have two tables Master and Awards. These share a common pk UFID. Master contains columns (UFID, AccountName...) Awards contains columns (ID, UFID, AwardingAgency, Amount) When a user submits a new award in Formview I would like to populate the UFID automatically so that the user does not have to enter this each time. I am currently using the logged in username to select records for that user only. The value of this username matches Master.AccountName. Since I am hoping to use this same logic across many tables, I would appreciate any suggestions as to how best handle this.Thanks,Ken
View 1 Replies View Relatedrewrite the below two queries (so that i can avoid duplicates) i need to send email to everyone not the dup[right][/right]licated ones)?
Create table #MyPhoneList
(
AccountID int,
EmailWork varchar(50),
EmailHome varchar(50),
EmailOther varchar(50),
[Code] ....
--> In this table AccountID is uniquee
--> email values could be null or repetetive for work / home / Other (same email can be used more than one columns for accountid)
-- a new column will be created with name as Sourceflag( the value could be work, Home, Other depend on email coming from) then removes duplicates
SELECT AccountID , Email, SourceFlag, ROW_NUMBER() OVER(PARTITION BY AccountID, Email ORDER BY Sourceflag desc) AS ROW
INTO #List
from (
SELECTAccountID
, EmailWorkAS EMAIL
, 'Work'AS SourceFlag
FROM#MyPhoneList (NoLock) eml
WHEREIsOffersToWorkEmail= 1
[code]....
Hi All,
Is it possible in SQL to Restrict value in one table checking a value on anather tables.
Scenerio-1.
I have two table let say,
Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value.
Scenerio -2.[/B]
I have table called [B]SIC.
This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns.
Please Advise.
Vijay
I m trying to INSERT a record to my Consultants table using sqldatasource and formview:
here is my sqldatasource code:
<asp:SqlDataSource ID="sqlDS1" runat="server" ConnectionString="<%$ ConnectionStrings:myDB %>"InsertCommand="INSERT INTO [Consultants] ([firstName],[lastName],[skillCategoryID],[resourceManagerID],[AMgroupID],[skillSet],[statusID],[location],[comments],[profile],[isAvailable],[dateModified],[focusID])
VALUES (@FNAME, @LASTNAME, 1, @RESOURCEMANAGERID, @AMGROUPID, @SKILLSET, @STATUSID, @LOCATION,@COMMENTS,@PROFILE,@ISAVAILABLE,getdate(),1)">
<InsertParameters> <asp:FormParameter FormField="txtFName" Name="FNAME" /> <%--other parameters--%></InsertParameters></asp:SqlDataSource>
now in my formview i have this:<asp:FormView DefaultMode="Insert" ID="FormView1" runat="server" DataSourceID="sqlDS1" DataKeyNames="id">
<table border="0" cellspacing="5" cellpadding="0"><tr> <td class="blacktextbold">First Name:</td> <td class="blacktext"><asp:TextBox ID="txtFName" CssClass="txtfield" Width="200" runat="server" Text="<% #Bind('firstName')%>" /> <asp:RequiredFieldValidator ControlToValidate="txtFName" runat="server" ValidationGroup="gpInsert" ErrorMessage="Required Field" Display="Dynamic" CssClass="redtextsmallbold" /> </td></tr> </table>
THE PROBLEM:no matter what i put the txtFName field its ALWAYS null and i get INSERT error as column firstName cant accept NULLany ideas? ur help would be appreciated
Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional.
In my Favorites table, the 3 fields are.....favoriteId (set as primary key)userIdcarId
I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives.
1) Check proactively by doing a.....SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table.
2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique.
Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice?
TIA for your help.
hello friends
i m stuck up with a problem...actually i dont have much experience in database line....i m new to this line....i have recently joined the job & this problem is like a test of me....if i will be able to give the solution then everything is fine otherwise i will be fired & im not in a condition to leave this job as this is my first job in software development....i have got this chance with lots of difficulty....so please help me if u can...
the problem is....>> i m using a procedure to check the duplicatye records by using string comparison against address of persons..allover the country....
i m using SQL server 7.0
i have a single table(name of table is DATA) which contains 350000 records( i mean address entries) there are about 35 columns but i have to check duplicate records only against address field...for that first of all i remove special characters from the address field.....then i compare first 20 characters for duplicate entries...
for this i m generating another table(name of another table is RESULT)...
how the logic works...initially the data table contains the records but the result table is totally blank....first of all i pick first entry of address from DATA table then...check it with the entry in RESULT table if the entry exists... it compares the address if the record is same then it generates a refference of this address and make an entry....means a refference of that entry....(as far as very first record is concerned there will be no entry in the RESULT table so it will enter the address over there...then it picks up the second record...checks it in the RESULT table...now this record will be compared with the one & only entry in the RESULT table....if the entry is same then the refference will be entered... otherwise it will be entered as second record in the RESULT table....)
now where lies the problem.....initially the procedure is very fast.... but it gradually slows down .....because(when it checks the 10th record for duplication it compares the entry in RESULT table for 9 times only
*** similarly when it checks the 100th record it compares it for 99 times
*** similarly when it checks the 10000th record it compares it for 9999 times
so here lies the problem....
when it checks the 100000th record it gets dammm slow...
what i have get till now is that i have checked.....>>>>>
5000 records in 4 mins....
25000 records in 22 mins....
and
100000 records in 20 hours....(means initially its faster but it gradually slows down)
************************************************** ************************
here i m giving the code for the procedure......
************************************************** *************************
CREATE PROCEDURE pro1 as
SET NOCOUNT ON
Declare @IvgId as numeric(15)
Declare @Address as nvarchar(250)
Declare @AddressClean as nvarchar(250)
Declare @MaxLen as INT
Declare @Add as nvarchar(250)
Declare @Ic as int
Declare @FoundIvgId as numeric(15)
Declare @NewIvgId as numeric(15)
/* here 'N' is for keeping track for some system failures etc */
Declare CurData CURSOR forward_only FOR Select IvgId, Address From Data Where ProcessClean = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address
WHILE @@FETCH_STATUS = 0
Begin
/*here i m doing string cleaning by removing special characcters */
Select @MaxLen = len(LTRIM(RTRIM(@Address)))
Select @Address = LOWER(@Address)
Select @Ic = 1
Select @AddressClean = ' '
While @Ic <= @MaxLen
/* here @MaxLen is the maximum length of the address field but i have to compare only first 20 characters */
Begin
Select @Add = Substring(@Address, @Ic, 1)
If ascii(@Add) > 47 AND ascii(@Add) <= 64 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End
If ascii(@Add) > 90 AND @Add <> ' '
Begin
Select @AddressClean = @AddressClean + @Add
End
Select @Ic = @Ic + 1
End
/* now we have removed special characters , for failure checking i m using this 'Y' */
Update Data Set AddressClean = @AddressClean, ProcessClean = 'Y'
Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address
End
PRINT 'Cleaning Done.............................'
Close CurData
Deallocate CurData
/* till now procedure doesnt take too much time & cleans all the 3 lack records in abt 40 mins but next part is giving trouble */
Declare CurData CURSOR FOR Select IvgId, Address, AddressClean From Data Where ProcessDup = 'N'
OPEN CurData
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
Select @NewIvgId = 100
WHILE @@FETCH_STATUS = 0
Begin
If EXISTS (Select IvgId From Result Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20))
Begin
Update Result Set DupIvgId = @IvgId Where SubString(RTRIM(LTRIM(AdressClean)),1,20) = SubString(RTRIM(LTRIM(@AddressClean)),1,20)
End
ELSE
Begin
Insert Into Result Values (@NewIvgId, @Address, @AddressClean,0)
Select @NewIvgId = @NewIvgId + 1
End
Update Data set ProcessDup = 'Y' Where IvgId = @IvgId
FETCH NEXT FROM CurData INTO @IvgId, @Address, @AddressClean
End
Close CurData
Deallocate CurData
SET NOCOUNT OFF
Print 'Done................................'
************************************************** **************************
now the procedure is over....now i m writing the SQL script of DATA & RESULT table
************************************************** ************************
CREATE TABLE [dbo].[DATA] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Title] [varchar] (10) NULL ,
[FirstName] [varchar] (50) NULL ,
[MiddleName] [varchar] (10) NULL ,
[LastName] [varchar] (30) NULL ,
[Add1] [varchar] (150) NULL ,
[Add2] [varchar] (50) NULL ,
[Add3] [varchar] (50) NULL ,
[City] [varchar] (30) NULL ,
[State] [varchar] (30) NULL ,
[Country] [varchar] (20) NULL ,
[Pincode] [varchar] (10) NULL ,
[OffPhone] [varchar] (20) NULL ,
[OffFax] [varchar] (20) NULL ,
[ResPhone] [varchar] (20) NULL ,
[ResFax] [varchar] (20) NULL ,
[EMail] [varchar] (50) NULL ,
[Source] [varchar] (20) NULL ,
[MODEL] [varchar] (20) NULL ,
[PNCD] [varchar] (6) NULL ,
[DupKey] [decimal](18, 0) NULL ,
[Duplicate] [int] NULL ,
[HouseHoldID] [varchar] (50) NULL ,
[YearSlab] [varchar] (10) NULL ,
[CleanStatus] [int] NULL ,
[AddStatus] [int] NULL ,
[BatchNo] [varchar] (20) NULL ,
[ModelStatus] [int] NULL ,
[Month] [int] NULL ,
[Year] [int] NULL ,
[SapStatus] [int] NULL ,
[ErrCase] [int] NULL ,
[cmpCity] [varchar] (50) NULL ,
[Product] [varchar] (1) NULL ,
[cmpPinCode] [varchar] (6) NULL ,
[Address] [nvarchar] (250) NULL ,
[AddressClean] [nvarchar] (250) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DATA_TEST] Script Date: 15/06/2001 8:36:21 PM ******/
CREATE TABLE [dbo].[DATA_TEST] (
[IVGID] [numeric](18, 0) NOT NULL ,
[Address] [nvarchar] (50) NULL ,
[AddressClean] [nvarchar] (50) NULL ,
[DupIvgId] [numeric](18, 0) NULL ,
[ProcessClean] [nvarchar] (1) NULL ,
[ProcessDup] [nvarchar] (1) NULL
) ON [PRIMARY]
GO
so now i have given the whole description of my problem....i m eagerly waiting for reply......
if anybody can help....i will be very thankful.....
bye for now
Bhupinder singh
I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
View 3 Replies View RelatedI setup the databse and Visual Web Developer to use
stored procedures when the insert command is used. The
database also uses the field UserName that I pass using a
SessionParameter within the InserParameter block from the
Membership.GetUser.Username from the aspnet_ tables.
My difficulty is when using the "Formview" as the body to
with which to insert (I wanted the design versatility of
FormView) I get an error: "system.formatExpression: Input
string was not in a correct format" when I leave the
textboxes empty and invoke the insert command. I first
assumed that the bound textboxes are not being converted
correctly when left blank, so I used the
ConvertEmptyStringToNull=True, with no success. I then
coupled that with the Type=[correct format] still with
the same error. Alas, my final attempt was to set
defaults in the "ALTER PROCEDURE" within the stored
procedure itself.
Any help would be most appreciated. Thanks.
.
Dear All,
i have a SqlDataSource with a simple select command(e.g. "select a,b,c from foo"). The insert command is a stored procedure and takes less parameters than there are columns in select statement (e.g. "insertFoo(a char(10))").
When used in combination with form view, i get "Procedure or function insertFoo has too many arguments specified" error.
It seems that form view always posts all columns as parameter collection (breakpoint in formview_inserting event shows this) to insert command.
Am I doing something wrong or is this by design? Is the only solution to manualy tweak parameters in formview_inserting event?
TIA
Jernej
I'm trying to get to grips with the formview control and have a problem regarding where an exception is raised if SQL server raises an error when an insert is taking place. I am new to both asp.net & c#
I have put the following code in the SqlDataSource2_Inserted & Formview1_Inserted events as a way of determining where the exception is raised:
if (e.ExceptionHandled){ string exceptionmessage = e.Exception.Message; lblStatus.Text = exceptionmessage;}
These two events are the only one's that have the ExceptionHandled and Exception properties.
I run the code with data that will raise a duplicate key error from SQL Server but the error is not being caught by either of these two handlers. All I get is a page that starts with
Server Error in '/SourceCode' Application.
Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated....
Is there somewhere else I should be looking to handle this sort of thing? I thought using the databound controls would make life easier but I'm actually finding it more of a pain than coding everything myself.
i have data like...
userid startdate in_out
1 2011-11-20 9.30 1
1 2011-11-20 10.30 0
1 2011-11-20 12.30 0
1 2011-11-20 12.45 1
1 2011-11-20 6.30 0
See 1-Entry 0-exit for 2011-11-20 12.30 is exit but entry time of that exit is missing....
Can i insert same exit value for entry?
HiI'm using a FormView to allow my administrator to add/edit/remove questions/answers to the FAQ of my helpdesk.Now I figured out how to use the FormView to adjust the data, the only thing the FormView does NOT do, is actually update the database, it does not give any errors, it just doesn't do anything.This is the code: <asp:SqlDataSource ID="sdsAdminFaqDetails" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>"
DeleteCommand="DELETE FROM [Faq] WHERE [QuestionID] = @QuestionID"
InsertCommand="INSERT INTO [Faq] ([Question], [Answer]) VALUES (@Question, @Answer)"
SelectCommand="SELECT * FROM [Faq]"
UpdateCommand="UPDATE [Faq] SET [Question] = @Question, [Answer] = @Answer WHERE [QuestionID] = @QuestionID">
<DeleteParameters>
<asp:Parameter Name="QuestionID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Question" />
<asp:Parameter Name="Answer" />
<asp:Parameter Name="QuestionID" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="Question" />
<asp:Parameter Name="Answer" />
</InsertParameters>
</asp:SqlDataSource>
<asp:FormView ID="FormView1" runat="server" DataSourceID="sdsAdminFaqDetails">
<ItemTemplate>
<table border="0">
<tr>
<td style="width: 189px"><asp:Label ID="lblQuestionLabel" runat="server" Text="Vraag:"></asp:Label></td>
</tr>
<tr>
<td style="width: 189px"><asp:TextBox ReadOnly="True" ID="txtQuestion" runat="server" Text='<%# Eval("Question") %>' Width="309px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 189px"><asp:Label ID="lblAnswerLabel" runat="server" Text="Antwoord:"></asp:Label></td>
</tr>
<tr>
<td style="width: 189px; height: 40px"><asp:TextBox ReadOnly="True" ID="txtAnswer" runat="server" Text='<%# Eval("Answer") %>' Height="160px" TextMode="MultiLine" Width="457px"></asp:TextBox></td>
</tr>
<tr>
<td><asp:Button ID="btnEdit" runat="server" Text="Wijzig" CommandName="Edit" /> <asp:Button ID="btnInsert" runat="server" Text="Nieuw" CommandName="New" /></td>
</tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table border="0">
<tr>
<td style="width: 189px"><asp:Label ID="lblQuestionLabel" runat="server" Text="Vraag:"></asp:Label></td>
</tr>
<tr>
<td style="width: 189px"><asp:TextBox ID="txtQuestion" runat="server" Text='<%# Bind("Question") %>' Width="309px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 189px"><asp:Label ID="lblAnswerLabel" runat="server" Text="Antwoord:"></asp:Label></td>
</tr>
<tr>
<td style="width: 189px; height: 40px"><asp:TextBox ID="txtAnswer" runat="server" Text='<%# Bind("Answer") %>' Height="160px" TextMode="MultiLine" Width="457px"></asp:TextBox></td>
</tr>
<tr>
<td><asp:Button ID="btnUpdate" runat="server" Text="Bevestig" CommandName="Update" /> <asp:Button ID="btnDelete" runat="server" Text="Verwijder" CommandName="Delete" /></td>
</tr>
</table>
</EditItemTemplate>
<InsertItemTemplate>
<table border="0">
<tr>
<td style="width: 189px"><asp:Label ID="lblQuestionLabel" runat="server" Text="Vraag:"></asp:Label></td>
</tr>
<tr>
<td style="width: 189px"><asp:TextBox ID="txtQuestion" runat="server" Width="309px"></asp:TextBox></td>
</tr>
<tr>
<td style="width: 189px"><asp:Label ID="lblAnswerLabel" runat="server" Text="Antwoord:"></asp:Label></td>
</tr>
<tr>
<td style="width: 189px; height: 40px"><asp:TextBox ID="txtAnswer" runat="server" Height="160px" TextMode="MultiLine" Width="457px"></asp:TextBox></td>
</tr>
<tr>
<td><asp:Button ID="btnInsert" runat="server" Text="Voeg toe" CommandName="Insert" /> <asp:Button ID="btnCancel" runat="server" Text="Annuleer" CommandName="Cancel" /></td>
</tr>
</table>
</InsertItemTemplate>
<EmptyDataTemplate>
U hebt niet geselecteerd welke vraag u wilt wijzigen.
</EmptyDataTemplate>
</asp:FormView>
hope one of you sees where i made a mistake, or forgot about something...thx for your help
Hi
I am new to asp.net world. I have a page with two formviews bound to two sqldatsources. One datasource connects to sql database and other to access. The information from both the databases is quite similar. The default mode for formview1 is edittemplate and for formview2 it is itemtemplate. I want to give the user an option to update formview1 data based on information retrevied in formview2 on a click of a button.
I want to do this in code behind .cs file. I am not sure how can I access the values from formview templates e.g formview1.itemtemplate... etc?
Can anyone please suggest a way to acheive this?
Thanks
Guys, here is my scenario:
I have 3 tables
1) Vendor
2) Service
3) Service Product
Each vendor has the ability to have any number of services, all with any number of service products.
How should I setup my page so I can edit/insert vendors, and tie a list of service products and services to them?
My plan was to have a Formview with either a checkboxlist, or multilist (to select multiple items - but they don't work that way) and then store selected items in another table (each item selected with its own record) to hold onto the vendor id and tie it to a service id and a product id, but I was unsure how to do this.
Any help would be greatly appreciated.
FYI I am writing in VB.
I am trying to return the ID of the last record entered into my database so the user will have his Record ID. I'm trying to do this in a from view.
text='<%#eval("ID")%>'
SelectCommand="SELECT MAX(ID) FROM [Webenhancetest]">
If it is done in this manner, it says DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'MAX'.
and if I just use SELECT IDFROM [Webenhancetest it works but only returns the first record which is 1.
How do I grab the last record, or Max(ID)???