MS SQL Server :: Duplicate Last Record When Using SqlDataAdapter.Update For Insert Command

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View Replies !

Duplicate Records Are Being Inserted With One Insert Command.
This is like the bug from hell. It is kind of hard to explain, soplease bear with me.Background Info: SQL Server 7.0, on an NT box, Active Server pageswith Javascript, using ADO objects.I'm inserting simple records into a table. But one insert command isplacing 2 or 3 records into the table. The 'extra' records, have thesame data as the previous insert incident, (except for the timestamp).Here is an example. Follow the values of the 'Search String' field:I inserted one record at a time, in the following order (And only oneinsert per item):airplanejetdogcatmousetigerAfter this, I should have had 6 records in the table. But, I endedup with 11!Here is what was recorded in the database:Vid DateTime Type ProductName SearchString NumResultscgcgGeorgeWeb3 Fri Sep 26 09:48:26 PDT 2003 i null airplane 112cgcgGeorgeWeb3 Fri Sep 26 09:49:37 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null dog 49cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null cat 75cgcgGeorgeWeb3 Fri Sep 26 09:52:53 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null tiger 14cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64Look at the timestamps, and notice which ones are the same.I did one insert for 'dog' , but notice how 2 'jet' records wereinsertedat the same time. Then, when I inserted the 'cat' record, another'dog' record was inserted. I waited awhile, and inserted mouse, andonly the mouse was inserted. But soon after, I inserted 'tiger', and 2more mouse records were inserted.If I wait awhile between inserts, then no extra records are inserted.( Notice 'airplane', and the first 'mouse' entries. ) But if I insertrecords right after one another, then the second record insertion alsoinserts a record with data from the 1st insertion.Here is the complete function, in Javascript (The main code ofinterestmay start at the Query = "INSERT ... statement):----------------------------------------------------------------------//Write SearchTrack Record ------------------------------------Search.prototype.writeSearchTrackRec = function(){Response.Write ("<br>Calling function writeSearchTrack "); // fordebugvar Query;var vid;var type = "i"; // Type is imagevar Q = "', '";var datetime = "GETDATE()";//Get the Vid// First - try to get from the outVid var of Cookieinctry{vid = outVid;}catch(e){vid = Request.Cookies("CGIVid"); // Gets cookie id valuevid = ""+vid;if (vid == 'undefined' || vid == ""){vid = "ImageSearchNoVid";}}try{Query = "INSERT SearchTrack (Vid, Type, SearchString, DateTime,NumResults) ";Query += "VALUES ('"+vid+Q+type+Q+this.searchString+"',"+datetime+","+this.numResults+ ")";this.cmd.CommandText = Query;this.cmd.Execute();}catch(e){writeGenericErrLog("Insert SearchTrack failed", "Vid: "+vid+"- SearchString:: "+this.searchString+" - NumResults: "+this.numResults, e.description);}}//end-----------------------------------------------------------------I also wrote a non-object oriented function, and created the commandobject inside the function. But I had the same results.I know that the function is not getting called multiple timesbecause I print out a message each time it is called.This really stumps me. I'll really appreciate any help you canoffer.Thanks,George

View Replies !

Query To Update 1 Record In A Duplicate Set Of Records
How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.

View Replies !

Duplicate A Record Using Update Trigger Question
I am new to SQL and these forums, so please bear with me :)

My basic question is if I can create a update trigger that will pull info from another record in the same table if data in certain fields match the existing record.

An example:

The user creates a new record. If said user enters data in specified fields that matches data in the same fields in another record in the same table, can a update trigger be used to fill out the rest of this record with the data from the record that matches?

If you need more Info on my problem, ask and I will try to explain better. There may be a better way of doing this than using a trigger, but I am not sure. The fields that I would use to match the data would not be the primary key fields.

Thanks!

View Replies !

Lookup && Update Record && Insert Record
Hi All,

I am trying to create package something like that..

1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table

I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)

I will be appreciated if you can help me...

View Replies !

Duplicate Tables Insert/Update In Another Table? Triggers?
I want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?

Thanks for help.

View Replies !

Update Record After Insert
Using trigger
want to update a field being inserted from another record in the same table.

the record being inserted I want to pull the bkjrcode from another record where the account = 1040 that also has the same ord# and inv# as the record being inserted.

Here is what I've tried with no luck.

create trigger [updategbkmut] on [dbo].[gbkmut]
after insert
as

update g1
set g1.bkjrcode = g2.bkjrcode
from gbkmut g1
inner join inserted i
on i.ord_no = g1.ord_no and i.inv_no = g1.inv_no
inner join gbkmut g2
on i.ord_no = g2.ord_no and i.inv_no = g2.inv_no
where i.freefield3 = 'Rebate' and g1.account = '1040'

View Replies !

Update Requires Valid Insert Command
I have created a dataset in code with a select command being a stored procedure. I have used commandbuilder so as to create update, insert statements. The update of the dataset receives error "update requires valid insert command".

From reading, it seems the problem is that the select statement is a stored procedure so data adapter cannot created the insert, update commands.

Can I create an update and insert command using update and insert stored procedures and use those to update the dataset (with multiple records of course) or do I have to create my select command using a select statement rather than the stored procedure?

Thanks for any help on this

View Replies !

Trigger Insert Record On Update
I have a parent table with 27 Columns and Child Table with 37 colums - when even there is an update in any of the columns on Parent or Child table, I require new record inserted into Audit_Parent and Audit_child table. Please help with
SQL Code on Create Trigger and insert records into Audit_parent and Audit_child when an Update occurs on any of the columns.
Insert into AuditParent and AuditChild should occur whenever there is an update on either Parent or child table.

Thanks

:confused:

View Replies !

Record Exists Insert Or Update

I had implemented as in the link to insert or update
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

What i want to know is... how can i assume there are no duplicate records.
I used Distinct keyword and queried it showed me all are distint but some where i find some duplicates just don't know why i am having when i look at the data both are exactly same...

Please let me know how can i fix it.
Urgent..

Thanks

View Replies !

CAN I Command (INSERT, DELETE, UPDATE) 2 Tables At The Same Time? POSSIBLE? HOW?
i've read the transact-sql command,
i known that the select command use to retrieve many fields from many tables with one command
select * from table1,table2
yes,
but i ' ve not seen the way to add,delete or update those fields from those tables with one command...

Is it possible? why?
I don't have any idea , can u help me
I want to know the sql commands , if it's possible

thanks for reply,
mochi

View Replies !

Can Insert Trigger Update Record Being Inserted
I think a trigger is the way to go on this but let me try to explain the confusion. I have a table with an id field. Based upon this ID field I need to have a character stripped out of the ID and placed in another column on the same table/same record(Sorry, because of the development env I can't use a computer column). The question is how to do this with an insert trigger. My understanding is the trigger will fire on the insert, but the record isn't there yet. Is there a way to handle this? My code below does not error but also does not update?

create TRIGGER test_Ins_tr
ON table1
FOR update
AS
DECLARE @oid char(1)
declare @actid char(10)
SELECT @oid = SUBSTRING(right(col1,2),1,1), @actid = col1
FROM inserted
update table1
set col2 = @oid where table1.col1 = @actid

View Replies !

Checking To See If A Record Exists And If So Update Else Insert
I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

Phil

View Replies !

Update Table If Record Exists Else Insert ?
Is there a way to structure a query to update an existing table record if it already exists, otherwise insert a new record into that table?

View Replies !

Package For Update/insert And Check For New Record
hi,

i'm total newbee on SSIS packages and therefore need guidance.

I want to make a ssis package that (in order):

- check in table (tbl_orders) if there is any new order made
- if new order is made, update column (time_last_change)
- if this order has geography ID (ID_geography) inserted, insert name of geography.

Thank you in advance,

View Replies !

SqlDataAdapter Update
Hi all,
I have datatable having around 50 rows and 3 columns ID, Name and ExpVal, which is an expression columns,where the values can be any SQL functions Like REPLICATE(), SOUNDEX ( 'value' ) Or REVERSE ( 'value' ).....
i want to insert each row in that datatable like
INSERT INTO TAB1 ( ID, Name, ExpVal) VALUES (1, 'some name', SOUNDEX ( 'some name' ) )
so that the ExpVal will have value of the function ie inserted row look like
ID Name ExpVal1 some name S500 <--- Result of SOUNDEX ( 'some name' )
I'm using sqldatadapter to insert these values to the database
string sql = "INSERT INTO TAB1 (ID, Name, ExpVal)VALUES (@ID, @Name, @ExpVal) ";SqlDataAdapter sqlAdptr = new SqlDataAdapter();SqlCommand sqlCmd = new SqlCommand(sql, con);sqlCmd.parameters.Add("@ID", SqlDbType.Int, 0, "ID");sqlCmd.parameters.Add("@Name", SqlDbType.NVarChar, 200, "Name");sqlCmd.parameters.Add("@ExpVal", SqlDbType.VarChar, 100, "ExpVal");sqlAdptr.InsertCommand = sqlCmd;sqlAdptr.Update(dataTable);
This works fine, but the problem is, now the TAB1 contains
ID Name ExpVal1 some name 'SOUNDEX ( 'some name' )' instead of S500
Thatis the sql funtion is passed by SqlDataAdapter to database as a string and it is not executing while row is inserted to the table.Please provide what changes i have to make if i want SOUNDEX ( 'some name' ) to executed while data insertion take place
Thanks in advance

View Replies !

SqlDataAdapter.Update()
I have a project C# . I used ADO.NET 2.0 to write Dataset(this Dataset have one table and it is from Oracle ) into a table in SQL.Tables(in Oracle and SQL ) have same struct. But my project doesn't work . I don't understand what problem . I hope anyone will help me . Thank you very muchthe following is my code :da = new SqlDataAdapter(); da.InsertCommand = new SqlCommand("INSERT INTO IMP_EXCHANGE VALUES (@brcd, @ccycd,@noticeday,@quotecnt,@ttbuyrt,@ttsellrt,@cshbuyrt,@cshsellrt,@quotetm,@rgstdt,@ccycdseq);", sqlConn); //da.InsertCommand = new SqlCommand("INSERT INTO IMP_EXCHANGE (brcd) VALUES (@brcd);", sqlConn); da.InsertCommand.Parameters.Add("@brcd", SqlDbType.Char, 4, "brcd"); da.InsertCommand.Parameters.Add("@ccycd", SqlDbType.VarChar, 4, "ccycd"); da.InsertCommand.Parameters.Add("@noticeday", SqlDbType.Char, 8, "noticeday"); da.InsertCommand.Parameters.Add("@quotecnt", SqlDbType.Int, 4, "quotecnt"); da.InsertCommand.Parameters.Add("@ttbuyrt", SqlDbType.Float, 9, "ttbuyrt"); da.InsertCommand.Parameters.Add("@ttsellrt", SqlDbType.Float, 9, "ttsellrt"); da.InsertCommand.Parameters.Add("@cshbuyrt", SqlDbType.Float, 9, "cshbuyrt"); da.InsertCommand.Parameters.Add("@cshsellrt", SqlDbType.Float, 9, "cshsellrt"); da.InsertCommand.Parameters.Add("@quotetm", SqlDbType.DateTime, 8, "quotetm"); da.InsertCommand.Parameters.Add("@rgstdt", SqlDbType.DateTime, 8, "rgstdt"); da.InsertCommand.Parameters.Add("@ccycdseq", SqlDbType.Char, 1, "ccycdseq"); da.InsertCommand.UpdatedRowSource = UpdateRowSource.None; da.UpdateBatchSize =1000; int k = da.Update(ds1, "IMP_EXCHANGE"); but if i copy this Dataset to other Dataset , it work fine public void insertData(DataSet ds, string tableDes) { DataSet ds1 = new DataSet(); DataTable tb = new DataTable("IMP_EXCHANGE"); DataColumn col; for (int i = 0; i<ds.Tables[0].Columns.Count; i++) { col = new DataColumn(ds.Tables[0].Columns[i].ColumnName, ds.Tables[0].Columns[i].DataType); tb.Columns.Add(col); } tb.Merge(ds.Tables[0], false); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataRow row = tb.NewRow(); for (int j = 0; j < tb.Columns.Count; j++) { row[j] = ds.Tables[0].Rows[i][j].ToString(); } tb.Rows.Add(row); } ds1.Tables.Add(tb); sqlConn.Open(); da = new SqlDataAdapter(); da.InsertCommand = new SqlCommand("INSERT INTO IMP_EXCHANGE VALUES (@brcd, @ccycd,@noticeday,@quotecnt,@ttbuyrt,@ttsellrt,@cshbuyrt,@cshsellrt,@quotetm,@rgstdt,@ccycdseq);", sqlConn); //da.InsertCommand = new SqlCommand("INSERT INTO IMP_EXCHANGE (brcd) VALUES (@brcd);", sqlConn); da.InsertCommand.Parameters.Add("@brcd", SqlDbType.Char, 4, "brcd"); da.InsertCommand.Parameters.Add("@ccycd", SqlDbType.VarChar, 4, "ccycd"); da.InsertCommand.Parameters.Add("@noticeday", SqlDbType.Char, 8, "noticeday"); da.InsertCommand.Parameters.Add("@quotecnt", SqlDbType.Int, 4, "quotecnt"); da.InsertCommand.Parameters.Add("@ttbuyrt", SqlDbType.Float, 9, "ttbuyrt"); da.InsertCommand.Parameters.Add("@ttsellrt", SqlDbType.Float, 9, "ttsellrt"); da.InsertCommand.Parameters.Add("@cshbuyrt", SqlDbType.Float, 9, "cshbuyrt"); da.InsertCommand.Parameters.Add("@cshsellrt", SqlDbType.Float, 9, "cshsellrt"); da.InsertCommand.Parameters.Add("@quotetm", SqlDbType.DateTime, 8, "quotetm"); da.InsertCommand.Parameters.Add("@rgstdt", SqlDbType.DateTime, 8, "rgstdt"); da.InsertCommand.Parameters.Add("@ccycdseq", SqlDbType.Char, 1, "ccycdseq"); da.InsertCommand.UpdatedRowSource = UpdateRowSource.None; da.UpdateBatchSize =tb.Rows.Count; int k = da.Update(ds1, "IMP_EXCHANGE");

View Replies !

SqlDataAdapter.Update
I am using SqlDataAdapter.Update(DataSet) to insert records into multiple tables in one call, but for some reason only first table was inserted.

Dim _cnn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim _sql As String = "Select a,b FROM Table1;Select d, c From Table2;"
Dim _da As New SqlDataAdapter(_sql, _cnn)
Dim _sqlCmdBdr As New SqlCommandBuilder(_da)
Dim _ds As New DataSet
_da.Fill(_ds)
Dim _newrow1 As DataRow = _ds.Tables(0).NewRow
_newrow1("a") = "NewA"
_newrow1("b") = "NewB"
_ds.Tables(0).Rows.Add(_newrow1)

Dim _newrow2 As DataRow = _ds.Tables(1).NewRow
_newrow2("d") = "NewD"
_newrow2("c") = "NewC"
_ds.Tables(1).Rows.Add(_newrow2)

_da.Update(_ds)

new record inserted only into Table1, no new rows in Table2

Please advise!
Thanks

View Replies !

Update Database Using The SqlDataAdapter
Hi, I was looking at how to update a database using SqlDataAdapter and I stumbled upon this code snippet from (http://www.java2s.com/Code/CSharp/Database-ADO.net/UpdatedatabaseusingtheSqlDataAdapter.htm):using System;
using System.Data;
using System.Data.SqlClient;

class Class1{
static void Main(string[] args){
SqlConnection thisConnection = new SqlConnection("server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT ID, FirstName FROM Employee", thisConnection);

SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);

DataSet thisDataSet = new DataSet();

thisAdapter.Fill(thisDataSet, "Employee");

Console.WriteLine("name before change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);

thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";

thisAdapter.Update(thisDataSet, "Employee");

Console.WriteLine("name after change: {0}", thisDataSet.Tables["Employee"].Rows[9]["FirstName"]);

}
}I was just wondering, without iteration, how did the line "thisDataSet.Tables["Employee"].Rows[1]["FirstName"] = "Inc";" managed to know which row it is to update? I tried it on my own application and it worked fine.Any help to assist me in understanding this would be appreciated. Thanks

View Replies !

Help With SqlDataAdapter.Update URGENT
MyCommand.Parameters.Add(new SqlParameter("@ConsultantName",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Calls",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@PPC",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Mth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@DaysInMonth",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Coach",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@Center",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ProductValue",SqlDbType.VarChar));
MyCommand.Parameters.Add(new SqlParameter("@ObjectiveValue",SqlDbType.VarChar));
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.CommandTimeout = 360;
try
{
SqlDataAdapter saveCenterCoaches = new SqlDataAdapter(MyCommand);
saveCenterCoaches.InsertCommand = MyCommand;

DataSet updateSet = finalSet.GetChanges(DataRowState.Added);
saveCenterCoaches.Update(updateSet.Tables[0]);
}
catch(Exception ex)
{
throw ex;
}
Iam getting "Procedure expects parameter @ConsultantName, which was not supplied."
I have consultantname and other parameters built in my datatable.
Is it the correct way of doing?
Can someone help.It is urgent.

View Replies !

SqlDataAdapter.Update Question?
Hi,
I have 10 rows in my database table. My page pulls these out with a DataView and I can display them on DataGrid ok.
I want to update these 10 rows in the database with SqlDataAdapter.Update so...
I modify my data by and then convert the new modified rows to a DataSet (ds)
I then pass this AND the SqlDataAdapter (da) that contains the unmodified rows to the following function:
protected void UpdateChechDigitPassed(string storedProcName, DataSet ds, SqlDataAdapter da){   Connection.Open();
   SqlCommand cmdUpdate = new SqlCommand();   cmdUpdate.Connection =Connection;   cmdUpdate.CommandType = CommandType.StoredProcedure;   cmdUpdate.CommandText = storedProcName;      cmdUpdate.Parameters.Add("@ID", SqlDbType.Int,4, "ID");   cmdUpdate.Parameters["@ID"].SourceVersion = DataRowVersion.Original;      cmdUpdate.Parameters.Add("@CheckDigitPassed", SqlDbType.Char,10, "CheckDigitPassed");
   da.AcceptChangesDuringFill = true;   da.Fill(ds,"Results");            da.UpdateCommand = cmdUpdate;      da.Update(ds, "Results");   Connection.Close();   }
When I run this I get the following :
"Update requires a valid InsertCommand when passed DataRow collection with new rows. "
Can anyone see what I'm doing wrong?
Thanks for your help

View Replies !

SQLDataAdapter Update Hanging
We've come across a strange problem using an SQLDataAdapter to update a large amount of data. We've had no problem using the same code for a smaller amount of data, and we only come across the problem once the amount of data is past some threshold. With Profiler running, we can watch the insert statements executing until some random time, when they will simply stop executing and it will hang until the DataAdapter CommandTimeout is exceeded. This is on a test Database which is not in use by any other users. We've tried changing the UpdateBatchSize, with no real differences. Any ideas?

View Replies !

Primary Key Getting Problem With SqlDataAdapter.Update
Hi,
I am using SqlDataAdapter.Update method to insert new row in database using commandbuilder(i.e, generating insert,update commands on the fly). Now I want the new primary key generated in this row to insert it into another table. I dont want to fire another query with database.
Is there something I m doing wrong or missing some attributes to set.
Thx

View Replies !

Update A Table With SqlDataAdapter...does It Work With Sql Text DataType ?
I tryed to update tables part of my MSDE database, using the SqlDataAdapter.Update() method. It worked fine untill I tryed to update a table that has a Column with the Text SQL DataType. It didn't work. The error was :

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

Is there a way to do it ?
Thanks,
Jeff

View Replies !

Duplicate Record
Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] I’d like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

I’m not sure how to write this query, can someone help? Thanks in advance!

View Replies !

Duplicate Record
Hi guys how do you hide duplicate records, how would I do a select statement for that

In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )

I have about had it with this database I have been asked to make a report out of

View Replies !

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different && Insert The New Record In The Table
Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View Replies !

Duplicate Inserted Record
Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT CompanyName: <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>" insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)" selectcommand="SELECT * FROM [items]"> <insertparameters> <asp:controlparameter controlid="txtCompanyName" name="CompanyName" /> <asp:controlparameter controlid="txtPhone" name="Phone" /> </insertparameters></asp:sqldatasource> VBPartial Class Default2 Inherits System.Web.UI.Page Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items

View Replies !

Duplicate Record Trigger
This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.

            -- duplicate record check
            SET @step = 'Duplicate record'
            IF EXISTS (
                        SELECT     i.myID, i.Type
                        FROM         INSERTED i INNER JOIN
                                              myDB.dbo.myTable c ON i.myID = c.myID
                        GROUP BY i.myID, i.Type
                        HAVING      (COUNT(*) > 1) AND (i.Type = 'In')
            )
            BEGIN
                        ROLLBACK transaction
                        RAISERROR('Error: step: %s. rollback is done.', 16, 1, @step)
                        Return
            END

What is problem?

View Replies !

Duplicate Record Problem
I am working on a web application that utilizes a sql server database. One of the tables is a large text file that is imported through a DTS package from a Unix server. For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table. I need to get rid of these duplicates, but can't seem to get a workable solution. the query that is needed to get the records is:SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName, tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone, tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2, tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTimeFROM tblAppointments CROSS JOIN tblPTDEMO2WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)AND tblAppointments.Insurance1 = 'MED'AND tblAppointments.ApptTypeID <> 'MTG'AND tblAppointments.ApptTypeID <> 'PNV'AND DateDiff("dd", ApptDate, GetDate()) = 0Order By tblAppointments.ApptDateMy first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.

View Replies !

Duplicate Record Question
In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.

The problem is what to actually test for. When the code throws the exception it is a big long string . .

"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"

I just thought that there has to be something simplar to test for than comparing the exception to the above string.

Can anyone tell me of a better way of doing this ?

(by the way I am only using Web Matrix and MSDE in case it matters)

Mark

View Replies !

Deleting Duplicate Record
hi to all,

How to delete duplicate record in the recordset?

Thanks...

View Replies !

Extracting Duplicate Record On The Same Id
Hi everybody i need help on on a query on how i can extract this... with the following table below..

id pub
1 a
1 b
2 c
2 c

I need to extract only the id and pub where pub has more than one with the same id... in the case of the above the result would be

id pub

2 c
2 c

thanks

View Replies !

Remove Duplicate Record
i'm a newbie to sql , anyone can give me suggestions on how to
remove duplicate records in a table, a table also has primary key,
thanks

View Replies !

Duplicate Record Problem
So I'm working on updating and normalizing an old database, and I have some duplicate records that I can't seem to get rid of. Every column is identical, right down to what is supposed to be the key. I can't right a delete query to just isolate one row, and I can't delete (or even udpate) any row in management studio. Any thoughts on how to remove the extra rows?

There is a field that's supposed to be unique, so I can write a simple query to get all of the problem rows. The only thing is that they come back in pairs.

View Replies !

Eliminate Duplicate Record(s)?
Hey Again,

I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL:

SELECT
r.[requestID]
,r.[requserID]
,r.[departmentID]
,CONVERT(CHAR(8),r.[submitDate],10)AS submitDate
,CONVERT(CHAR(8),r.[dueDate],10)AS dueDate
,CONVERT(CHAR(8),r.[revisedDueDate],10) AS revisedDueDate
,r.[reqStatus]
,r.[completedDate]
,d.[departmentName]
,s.[statusName]
,u.lastName + ', ' + u.firstName AS submittedBy
,ra.userID

FROMtblUserDepartment ud
INNER JOIN tblRequest rON ud.departmentID = r.departmentID
INNER JOIN tblDepartment dON r.departmentID = d.departmentID
INNER JOIN tblStatus sON r.reqStatus = s.statusID
INNER JOIN tblUser uON r.requserID = u.userID
LEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestID
WHEREud.userID= @userID

This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening).

Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.

View Replies !

Delete Duplicate Record
Hi ,

How can i delete the duplicate record from a table

use Northwind
create table Emp (Ecode char(2), Ename char(10))
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A1','A')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A2','B')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A3','C')
Insert into Emp(Ecode, Ename) values('A4','D')
Insert into Emp(Ecode, Ename) values('A4','D')

select * from emp order by Ecode

Thanks

ASM

View Replies !

Duplicate Record Inserted. Weird.........
1 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
2 Dim sqlStr As String
3 Dim sqlStr2 As String
4 Dim myConnection As MySqlConnection = Nothing
5 Dim myCommand As MySqlCommand = Nothing
6 Dim myConnection2 As MySqlConnection = Nothing
7 Dim myCommand2 As MySqlCommand = Nothing
8 Dim myReader As MySqlDataReader = Nothing
9 Dim IC As String
10
11 IC = txtIC1.Text + "-" + txtIC2.Text + "-" + txtIC3.Text
12
13
14 Try
15 sqlStr2 = "SELECT * FROM User WHERE uLogin='" & txtUserName.Text.Trim() & "'"
16
17 ' Connection
18 myConnection2 = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
19 myConnection2.Open()
20 ' Command
21 myCommand2 = New MySqlCommand(sqlStr2, myConnection2)
22 ' Reader
23 myReader = myCommand2.ExecuteReader()
24 Catch ex As Exception
25 ' Exception Error Here
26 Response.Write(Err.Number & " - " & Err.Description)
27
28 End Try
29 ' Checking
30 If myReader.Read() Then
31
32
33 Label2.Text = "Username already exist. Please choose another username"
34 Label3.Text = "*"
35
36 Else
37
38 Try
39
40
41 sqlStr = "INSERT INTO userapplication(uaName,uaIC,) VALUE (?uName,?uIC )"
42
43
44
45 ' Connection
46 myConnection = New MySqlConnection(ConfigurationManager.ConnectionStrings("dbConnection").ToString())
47 myConnection.Open()
48 'Command
49 myCommand = New MySqlCommand(sqlStr, myConnection)
50
51 myCommand.Parameters.AddWithValue("?uName", txtName.Text)
52 myCommand.Parameters.AddWithValue("?uIC", IC)
53
54
55 myCommand.ExecuteNonQuery()
56 myConnection.Close()
57 Response.Redirect("Register.aspx", False)
58
59 Catch ex As Exception
60 ' Exception Error Here
61 Response.Write(Err.Number & " - " & Err.Description)
62 Finally
63 ' Clean Up
64 If Not IsNothing(myCommand) Then
65 myCommand.Dispose()
66 End If
67 '
68 If Not IsNothing(myConnection) Then
69 If myConnection.State = Data.ConnectionState.Open Then myConnection.Close()
70 myConnection.Dispose()
71 End If
72 End Try
73
74
75 End If
76
77 End Sub
78
79

above is my code for the user registration page.the code that i bold,which with number 55,56 and 57,are where the problem occur.
when it run,it run 55, then 57,then back to 55, then 57 again
means that my db hav duplicate record being insert
anyone know how to solve this problem?

View Replies !

Q:Duplicate A Record Except One Or Two Specific Fields Using SQL
Using Transact-SQL how can I copy all fields except one from one record to another? The field in question being the identity field. Since, this field cannot be duplicated a simple INSERT statement fails. How can I specify an exclusion list of fields?

-Sumit

View Replies !

Simple Duplicate Record Question
I have a table with 2 columns, col1 is unique, col2 is not.
col1 is numeric col2 is varchar.
Here is the problem,
col2 will have duplicate values, I need the largest numeric value from col1 with unique value from col2.
Thanx for any help.

View Replies !

Help Selecting Duplicate Record Details
I have the following query I am using to identify duplicate records in one of my database tables:

Code:

SELECT memberID,
COUNT(memberID) AS NumOccurrences
FROM ChapterMembers
GROUP BY memberID
HAVING ( COUNT(memberID) > 1 )

Executing the above proc returns 4079 records...

Now, I would also like to know the ChapterID for each member with a duplicate record. ChapterID is also stored in the ChapterMembers Table...

I tried running the following procedure:

Code:

SELECT memberID,
COUNT(memberID) AS NumOccurrences, chapterID
FROM ChapterMembers
GROUP BY memberID, chapterID
HAVING ( COUNT(memberID) > 1 )

But zero results are returned ...

The ultimate goal here is to identify duplicate records where one of their chapterID's = '81017' and to delete that record from the database.

Anyone have any ideas what I am doing wrong? Also, any suggestions for removing the records would be appreciated.

Thanks,

Jandrews

View Replies !

How To Extract Duplicate Record Using Ssis

ex. from source the file you want to split the record into two, the one with a clean record and the other one with duplicate records

View Replies !

Fetch Returning Duplicate Last Record
Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes

(

@ResidentID int

)

RETURNS varchar(250)

AS

BEGIN

DECLARE @RDLike varchar(50)

DECLARE @RDLikeList varchar(250)

BEGIN

SELECT @RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

WHERE (ResidentID = @ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @RDLike

SELECT @RDLikeList = @RDLike

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @RDLike

SELECT @RDLikeList = @RDLikeList + ', ' + @RDLike

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @RDLikeList

END

View Replies !

Deleting The Duplicate Record From Table
Hi ,
i am using sql server 2005.
i have one table where i need to find records that have same citycode and hospitalcode and doctorcode then delete the record keeping only one record of them
my problem is table structure have idendtity column which is unique.
that is m table structure is something like

recid citycode hospcode doctorcode otherdesp
1 0001 hp001 d0001 ...
2 0002 hp002 d0002 ...
3 0001 hp001 d0001 ...
4 0002 hp002 d0002 ...

please suggest

thank you

View Replies !

Database Updates (using SqlDataAdapter) During Runtime But Database Table Does Not Update
I apologize if this is a simple question, but I am very new to SQLServer. I feel like I am missing something easy here. I an running VB..NET from Visual Studio 2005 and SQL Server Express. The code inquestion is as follows:ChosenProjectName = TextBox2.TextDim NewProject As New DataSetOpenConn()Dim GetProjectList As String = "SELECT * FROM Approved_Projects ORDERBY Project_ID DESC"Dim GetProjectsAdapter As SqlDataAdapter = NewSqlDataAdapter(GetProjectList, MainCon)Dim myDataSet As DataSet = New DataSet()Dim myDataRow As DataRowGetProjectsAdapter.Fill(myDataSet, "Approved_Projects")Dim LastProjectID As Integer =myDataSet.Tables("Approved_Projects").Rows(0)("Project_ID")myDataRow = myDataSet.Tables("Approved_Projects").NewRowmyDataRow("Project_Name") = ChosenProjectNamemyDataRow("Project_Status_ID") = 1myDataRow("Project_Box_State_ID") = 1myDataRow("Project_ID") = LastProjectID + 1myDataSet.Tables("Approved_Projects").Rows.Add(myDataRow)Dim myDataRowsCommandBuilder As SqlCommandBuilder = NewSqlCommandBuilder(GetProjectsAdapter)GetProjectsAdapter.Update(myDataSet, "Approved_Projects")GetProjectsAdapter.Dispose()myDataSet.Dispose()CloseConn()In short, I am attempting to read data from a table of approvedprojects (named Approved_Projects) into a dataset, add a record, returnthat data through the SqlDataAdapter to my database.Here is my question: after this piece of code runs in my program, I canreopen the connection to my database, query the data for Projects andmy new record shows up fine. BUT when I go to the Server Explorer inVisual Studio to look at the new Table Data in "Approved_Projects", thenew data isn't there AND when I rerun the application, the new recordis absent. What am I missing here?Any help will be greatly appreciated.Dirk

View Replies !

Duplicate Record Inserted With Stored Procedure
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String)
Dim SqlText As String
Dim cmd As SqlCommand
Dim strIPAddress As String

'Get the users IP address
strIPAddress = Request.UserHostAddress

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
SqlText = "sp_AddUserLog"
cmd = New SqlCommand(SqlText)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con

cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username
cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress

Try
con.Open()
cmd.ExecuteNonQuery()
Finally
con.Close()
End Try

End Sub

View Replies !

Opinion On Preventing Duplicate Record Insertion
Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..

View Replies !