TableAdapter.Update (Data Not Being Writen To The Database)

May 30, 2006

Problem

I downloaded the VB Lesson 9 Visual Basic Project from Microsoft Learning Resources. With out making any changes to the project I compiled and ran it, making changes to the data and saving the changes. Every thing look great until you run it a second time and see that the changes to the data are all lost.

 

Observations:

1. Changes made to the data from within Visual Studio (Show Table Data) do take effect.

 

2. The in-memory data (DataSet) is changed as you move from row to row.

 

3. The TableAdapter.Update call returns 1, ostensibly indicating the number of rows updated.

 

4. The data in the database is not changed by the TableAdapter.Update call.

 

Operating System: Windows XP Pro Service Pack 2

Other Software installed on this system

20SQL Server 2005 Express

Visual Web Developer 2005 Express

Visual Studio 6.0 Enterprise

Visual Studio .net Enterprise

Microsoft .NET Framework SDK

Microsoft Office 2003

 

 

If anyone can help me understand the cause of this problem I would greatly appreciate it.

 

Thanks

Bob

View 1 Replies


ADVERTISEMENT

~TableAdapter.Update Not Updating The Database

Apr 28, 2006

I am designing my first database (Visual Basic & SQL Server Express 2005) and it appears as if the database isn't updating. I am running it by hitting F5, closing by hitting the "X" and then hitting F5 again to check if the changes have stuck. They don't. These are the instructions given in the tutorial.

Any ideas? Thanks.

In particular I have tried two step-by-step tutorials distributed by Microsoft: 1. Absolute Beginner's Video Series, Lesson09; and 2. the Help tutorial: Managing Your Records (ms-help://MS.VSExpressCC.v80/MS.NETFramework.v20.en/dv_vbcnexpress/html/1ccdb8f1-4162-4a54-af17-231007eb529b.htm)

The code for the form is:

Public Class Form1


Private Sub AddressesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddressesBindingNavigatorSaveItem.Click


Me.Validate()

Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

End Sub

Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing


Me.AddressesBindingSource.EndEdit()

Me.AddressesTableAdapter.Update(Me.FirstDatabaseDataSet.Addresses)

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


'TODO: This line of code loads data into the 'FirstDatabaseDataSet.Addresses' table. You can move, or remove it, as needed.

Me.AddressesTableAdapter.Fill(Me.FirstDatabaseDataSet.Addresses)

End Sub
End Class

View 3 Replies View Related

Tableadapter.update Problem

Feb 26, 2008

If this is the wrong forum, please let me know.

Using .NET Compact Framework, VB.NET, SQL Server CE 3.0

I use tableadapter.update in many places throughout my application. The user can save a record by click on a menu item called Save.

I call this code:

Private Sub Save

bindingsource.endedit
tableadapter.update(me.dataset.datatable)

End Sub

In most places the code works fine. It saves updates to the table. However, in two tables it is not working. It does not throw any exceptions, but does not save the record.

The subs that work and the ones that dont are coded exactly the same.

Thanks for any help

Ryan

View 1 Replies View Related

SQL Server Is Writen In Which Programming Language

Dec 7, 2006

Any one know the SQLServer is written in which programming language C++ or C?

I know the rival DB Oracle is written in C programming language.

Thanks.

RKK

View 5 Replies View Related

Database Create ODBC Connections To Access Database Directly And Update Data?

Sep 10, 2012

We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?

View 1 Replies View Related

SQL Security :: Making Data Change In Read Only Database Without Letting Other Users Update Data

Aug 6, 2015

I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.

For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database
single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?

Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.

The code;

use master
alter database xxx set read_write
with rollback immediate
alter database xxx set single_user
with rollback immediate

use xxx
update  tablexxx set columnxxx=yyy
use master
alter database xxx set read_only
with rollback immediate
alter database xxx set multi_user
with rollback immediate

View 5 Replies View Related

Weekly Update Part Of Database With Data From Original Database

Mar 26, 2008

Hi!

I have an original database that I want to copy once to another database. Then I want to update the data weekly with the data of the original database. I don€™t change any tables or columns in that part of the database. Just some tables more in the €˜new€™ database (than the tables from the original database) with some references to the tables that have to be updated weekly.
How can I do this? (if you know what I mean... it's a sort of a datawarehouse where different sources come together.. that part that represents the original database is just a part of the datawarehouse. That part is exactly the same structure as the original database.)

Thanks,

Sandra

View 1 Replies View Related

How To Update Data From This Database To Orther Database ?

Jun 20, 2007

 
Please help me How to update data from this Database to orther Database ?
Thank

View 5 Replies View Related

Update Page When Data In Database Changes.

Jan 4, 2006

Hi,
Can someone help me.  I need to update a page on my site when the data in a database table is changed (by someone else). 
I would like to poll the database every 5 secs or so to see if it has changed.  I am using asp.net v1.1.
Any help would be appreciated.
Jon

View 1 Replies View Related

How To Update Data In The Database, My Attempt Was Unsuccessful.

Mar 17, 2007

I was hoping I could get this to work, so far I can access all the data from the database, but I also want to update it. I placed a button, updateBttn_Click event to update the database.It's not updating though and I am not gettting any errors. Could you please look at the update query in the update button and see if I have it right. Also, I think it might be because I have the first retrieval of the database tables set to  ExecuteReader(). Do I need to redo this and use ExecuteNonQuery somewhere else?Thanks in advance. using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class userpage : System.Web.UI.Page{    protected void Page_Load(object sender, EventArgs e)    {        if (!IsPostBack)        {        usrnmeLbl.Text = PreviousPage.CurrentUser;        hiddenpassLbl.Text = PreviousPage.CurrentPass;        //Define database connection        SqlConnection conn = new SqlConnection(            "Server=localhost\SqlExpress;Database=MyDB;" + "Integrated Security=True");        //Create command        //SqlCommand comm = new SqlCommand("SELECT UserName, Password, UserID FROM UsersTable WHERE UserName = '" + usrnmeLbl.Text + "'", conn);        //SqlCommand comm = new SqlCommand("SELECT Father FROM FamHistTable WHERE UserID IN (SELECT UserID, UserName, Password FROM UsersTable WHERE UserName = '" + usrnmeLbl.Text + "')", conn);        //SqlCommand comm = new SqlCommand("SELECT FamHistTable.Father FROM FamHistTable INNER JOIN UsersTable ON FamHistTable.UserID = UsersTable.UserID WHERE UsersTable.UserID = '" + usrnmeLbl.Text + "'", conn);        SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = '" + usrnmeLbl.Text + "'", conn);        //Open connection        conn.Open();        //Execute the command        SqlDataReader reader = comm.ExecuteReader();        //Read and display the data            while (reader.Read())            {                string usr = reader["username"].ToString();                usr = usr.TrimEnd();                string pss = reader["password"].ToString();                pss = pss.TrimEnd();                if (usrnmeLbl.Text == usr)                {                    if (hiddenpassLbl.Text == pss)                    {                        readDB_Lbl.Text += reader["username"] + "<br />";                        //readDB_Lbl.Text += "Your User ID is: " + reader["UserID"] + "<br />";                        readDB_Lbl.Text += "Your Father's name is: " + reader["Father"] + "<br />";                        readDB_Lbl.Text += "Your Mother's name is: " + reader["Mother"] + "<br />";                    }                    else                    {                        readDB_Lbl.Text = "User and Password mismatch.";                    }                }            }                        reader.Close();        conn.Close();    }       }    protected void updateBttn_Click(object sender, EventArgs e)    {        SqlCommand comm = new SqlCommand("UPDATE FatherHistTable SET " + "'famUpdteDDL.SelectedItem.Value'" + "  = " + "'addPrsnTxtBx.Text'" + "WHERE UserName = " + "'usrnmeLbl.Text'");    }}

View 8 Replies View Related

Insert,update,select Data From The Database?

Jan 31, 2008

Hi everyone..i m new to this field.. can anyone explain me with simple example onhow to insert,update,select data from the sqldatabase? i m using vwd 2005 express edition along with sql express edition. plz explain the simple example with code (C#) including how to pass connection strings etc.thank you.jack.  

View 6 Replies View Related

Refresh Page When Database Data Is Update

Apr 18, 2006

Hi All,
Is it possible to refresh the web page when certain table data is updated?  And I cannot use the auto-refresh feature.  Thank you very much.
 

View 1 Replies View Related

Update/insert The Xml Data In Database Table

Oct 10, 2007

From: JAGADISH KUMAR GEDELA [jgedela@miraclesoft.com]
Sent: 10/10/2007 4:13:43 PM
To: jgedela@miraclesoft.com [jgedela@miraclesoft.com]
Subject: forum
Hi all,

I need to Insert the XML File data into SQL SERVER 2005 db(table).
For that I created the table with XML Native column (using typed xml)
*********************************create table command************
CREATE TABLE XmlCatalog (
ID INT PRIMARY KEY,
Document XML(CONTENT xyz))
***********************************
In order to Create the table with typed xml ,before that we have to create the xml schema which i
mentioned below
************************************create schema command********
CREATE XML SCHEMA COLLECTION xyz AS
'Place xml schema file ’
************************************
I created the xml schema file by using the xmlspy software.

--------------------------Insert command---------
INSERT into XmlCatalog VALUES
(1,'copy xml file ‘)
-------------------------------
I need to retrieve the xml data from the table
------------select query----------
SELECT Document.query (‘data (/X12//UserId)') AS USERID,
Document.query (‘data (/X12/X12_Q1/header/ISA//ISA_Authorization_Information_Qualifier)')
AS
ISA_Authorization_Information from XmlCatalog.
-----------------


I Need to update/insert/delete the xml data in the table

Can you please suggest the procedure to implement the above requirement(insert/update/delete)

View 5 Replies View Related

Insert, Delete, Update Data In Database

Mar 14, 2006

hi. i'm trying to create a c# application which would insert, update and delete data from a database. could anyone pls point me to the right direction in which i should take? thanks in advance.

View 1 Replies View Related

Mass Insert / Update External Data Into Internal SQL Database

Nov 15, 2006

Hola!I'm currently building a site that uses an external database to store all the product details, and an internal database that will act as a cache so that we don't have to keep hitting the external database to retrieve the products every time a customer requests a list.What I need to do is retrieve all these products from External and insert them into Internal if they don't exist - if they do already exist then I have to update Internal with new prices, number in stock etc.I was wondering if there was a way to insert / update these products en-mass without looping through and building a new insert / update query for every product - there could be thousands at a time!Does anyone have any ideas or could you point me in the right direction?I'm thinking that because I need to check if the products exist in a different data store than the original source, I don't have a choice but to loop through them all.Cheers,G. 

View 2 Replies View Related

Integration Services :: Update Simultaneously Whenever Insert New Data In Both Database

Aug 10, 2015

Am using SSIS to integrate between two database. First one is insert data from SQL to Sybase. its working fine and insert simulatenously. Now need to update table from sybase to SQL with condition(where). How to do this task. Is there any possiblities to execute SSIS without using SQL agent,  update simultaneously whenever insert new data in both database.

View 8 Replies View Related

How To Pick Up Data From SQL Server And Update A Oracle Database Using A SSIS Package

Mar 5, 2007

Hi,
I have a table in SQL Server 2005 which has [Id] and [Name] as its columns.
I also have a Oracle database which has a similar table.

What I want to do is as follows:
In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.

Can someone guide me as to how I can specify a update statement in the destination dataflow.

Thanks.

View 7 Replies View Related

SQL Login With TableAdapter

Jul 31, 2006

Ok, just made a TableAdapter and the SELECT statement works fine and pulls data. But doing an INSERT is something different, it craps out wanting a login.
No neat Wizard (or so I haven't found yet) that lets me give the TableAdapter the login info for the SQL Server, which I have. 
Do I need to write code with the login and password for the SQL connection instead?
-Ed

View 1 Replies View Related

Problem With T-SQL In TableAdapter

Jul 11, 2007

Hi,
I have a TableAdapter created in a Dataset. I'm creating a search function for a table, and here's how my code looks like in the "Add Query" wizard:
Select * from Event where eventname like '%@EventName%'
But when I click on preview data, I do not get the prompt to enter a value for the @EventName parameter, where went wrong?

View 4 Replies View Related

I/O Blob Through TableAdapter

May 26, 2006

What's a good way to work with Blobs and TableAdapters, in terms of declaring compatible column types in SQL Server and DataTable fields?

View 2 Replies View Related

TransactionScope And TableAdapter

Jul 30, 2007

This thread has also been posted under '.NET Data Access and Storage'. However, I have realized that the same code contruction using SQL Server connection- and tableadapter objects work fine so I am trying to get answers here also.

I am attempting to do transactional updates to SQL Compact Edition database using TableAdapter and TransactionScope like this:


using (TransactionScope ts = new TransactionScope())

{


SqlCeConnection sqlConn = new SqlCeConnection(connectionString);

myTableTableAdapter ta = new myTableTableAdapter();

ta.Connection = sqlConn;

ta.Update(dsmyTable.myTable);
ts.Complete();

}


dsmyTable is a strongly typed dataset created through the Dataset Designer and populated with data from the database prior to the code sample above.

This all works fine. However, when removing the call to 'ts.Complete()' to simulate the transaction rolling back, data is still stored into the database.

Am I missing something here or does TransactionScope not support SQL Compact Edition? Any help is appreciated!

Regards.

View 3 Replies View Related

TableAdapter And Variable

Sep 5, 2006

Hello,



I want to do something like this in a TableAdapter's SQL Statement:



SELECT Type.*

FROM Controllers INNER JOIN

@Type AS Type ON Type.ConfigurationId = Controllers.ActiveConfig INNER JOIN

Tank ON Controllers.ControllerId = Tank.ControllerId

WHERE (Tank.TankId = @TankId)



But I get an error because of the @Type variable. Is there any other way to choose a table in a TableAdapter with variables?



Thanks for your help!

View 3 Replies View Related

Passing Parameters In TableAdapter

Jul 28, 2006

A little new to ASP.NET pages, and I'm trying to pass some parameters to a SQL 2000 Server using a TableAdapter, code is as follows:
------
TestTableAdapters.test_ModemsTableAdapter modemsAdapter = new TestTableAdapters.test_ModemsTableAdapter();
// Add a new modem
modemsAdapter.InsertModem(frmRDate, frmModem_ID, frmProvisioning, strDecESN );
--------
And the error I get when loading the ASP.NET page is as follows:
Compiler Error Message: CS1502: The best overloaded method match for 'TestTableAdapters.test_ModemsTableAdapter.InsertModem(System.DateTime?, string, string, string)' has some invalid arguments
---------------
Now I realized the four strings that I am trying to pass to the server refer to ID's on Textboxes on the web page. Not sure if that might be the problem for databinding... ? Or is it my statement for the adapter?
-Ed

View 4 Replies View Related

TableAdapter Class Not Generated

Aug 2, 2006

I created a new DataSet object using the
wizard and had no probs, it's very straightforward.  I created a
GetProducts() method and also added a GetProductCount() method.  I
read somewhere that when the DataSet is saved, it will generate the
TableAdapter classes and store them in the project nested under the
DataSet object.  This isn't happening, the files aren't there and
yes I'm displaying all files.  There *is* however the TableAdapter
class but it's in not in the project, it's a temporary file w/ the
following path:
   
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET
Fileswebsitef38f8b5500014aeSources_App_Codepw_products.xsd.72cecc2a.cs.

I saved the DataSet several times, compiled the entire solution and
even closed the solution yet this temporary file isn't getting saved
where it should be.  Does anyone have any ideas abt this? 
I'm eager to start developing my DAL and def want to use these
TableAdapter classes.

Thx in advance,
-Pete

View 2 Replies View Related

Tableadapter Query Question

Nov 20, 2006

hii am using the nothwind database for a current exercise, i am wanting to user to be able to search for products based on two input methods (product name, category).here is the code i currently have: SELECT    DISTINCT  ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, ProductName,                       ProductID, CategoryIDFROM         ProductsWHERE       (CategoryID = @categoryID) OR                       (ProductName LIKE '%' + @ProductName + '%')i'm wanting to have it set so the user can use both input methods, or either, when one input method is used by the user it works fine, but when both are used it generates results for 2 seporate queriesi'm wanting to have it set so the user can search by product name in certain categories (when both search input methods are used)how would i go about doing this? is there some kind of equivalent to an AND/OR statement?eg.  WHERE       (CategoryID = @categoryID) AND OR
                      (ProductName LIKE '%' + @ProductName + '%') thanks in advance! 

View 2 Replies View Related

Going From Sqlconnection And Sqlreader To Using A Tableadapter And ????????

Apr 13, 2007

Below is my code right now Im using a direct sqlconnection for each request. I would like to use Tableadapters and/or my BLL code like the rest of my sites pages. I cant find how to do this progamatically and using the xmltextwriter. (Also i need to be able to name the xml output nodes like below)Thanks for the help.NeilPrivate Sub GetAllEvents(ByVal SqlString)    Response.Clear()    Response.ContentType = "text/xml"    Dim objX As New XmlTextWriter(Response.OutputStream, Encoding.UTF8)    objX.WriteStartDocument()    objX.WriteStartElement("Events")    Dim objConnection As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|*********.mdf;Integrated Security=True;User Instance=True")    objConnection.Open()    Dim sql As String = SqlString    Dim objCommand As New SqlCommand(sql, objConnection)    Dim objReader As SqlDataReader = objCommand.ExecuteReader()    While objReader.Read()      objX.WriteStartElement("Event")      objX.WriteAttributeString("EventId", objReader.GetInt32(0))      objX.WriteAttributeString("EventName", objReader.GetString(1))      objX.WriteAttributeString("EventDescription", objReader.GetString(2))      objX.WriteAttributeString("EventDate", objReader.GetDateTime(3))      objX.WriteAttributeString("CurrentDate", Date.Now.ToString)      If Not objReader.IsDBNull(12) Then        objX.WriteAttributeString("EventImage", objReader.GetString(12))      End If      objX.WriteEndElement()    End While    objReader.Close()    objConnection.Close()    objX.WriteEndElement()    objX.WriteEndDocument()    objX.Flush()    objX.Close()    Response.End()  End Sub

View 2 Replies View Related

If/then Parameterized Queries Using Tableadapter

Jun 29, 2007

Hey fellas.  Here's my situation.  I have two textboxes where the user enters a "start" date and an "end" date.  I want to search a table to find records who's "expired" column date is between those two dates provided by the user.  The tricky part is, if the user just puts a start date in but no end date, I want it to search from whatever start date the user entered to the future and beyond.  Essentially, I think I'm looking for a SQL statement along the lines of:
  SELECT Request.RequestID, Request.URL, ActionProvider.Name, Request.CurrentStageID, Request.Decision, Request.SubmissionDate,
Request.ExpirationDate
FROM Request INNER JOIN
RequestSpecificActionProvider ON Request.RequestID = RequestSpecificActionProvider.RequestID INNER JOIN
ActionProvider ON RequestSpecificActionProvider.ActionProviderID = ActionProvider.ActionProviderID INNER JOIN
RoleActionProvider ON ActionProvider.ActionProviderID = RoleActionProvider.ActionProviderID INNER JOIN
Role ON RoleActionProvider.RoleID = Role.RoleID
WHERE

CASE WHEN @BeginDate is not null AND @BeginDate <> ''
THEN Request.ExpirationDate > @BeginDate
END

AND

CASE WHEN @EndDate is not null AND @EndDate <> ''
THEN Request.ExpirationDate > @EndDate
END

AND (Role.Description = 'Requestor')

 
I realize my code isn't correct and there's still a floating "AND" out there I would have to put some logic around.  Anyway, how do I do this?  Do I need to build three separate queries in my tableadapter (one for if both dates are provided, one for if start date is provided, one for if end date is provided) and build the logic in my application code or can I tackle it with SQL?  If I can tackle it with SQL, where have I gone astray?  I'm currently getting the error: "Error in WHERE clause near '>'. Unable to parse query text."
 Thanks for the help everyone!

View 3 Replies View Related

TableAdapter And Connection Strings

Sep 19, 2007

Hello,
I'm trying to setup a typed dataset with a table adapter in .NET 2.0, and the problem I am having is that I cannot get the table adapter to use existing connection strings setup in the web.config file.  How can I get it to do so?  It doesn't see the connection strings, and so it wants me to create a new connection, which I don't want to do.

View 2 Replies View Related

Select SCOPE_IDENTITY() From TableAdapter

Oct 8, 2007

I like to run a query from my tableadapter, but if i insert a row i like to know the id of it.So i was looking on asp.net and found a tutorial for it. But it doesnt work!
My inset query:
"INSERT INTO Klanten                      (BedrijfsNaam, Contactpersoon, Adres, Postcode, Woonplaats, Telefoon, Mobiel, [E-mail], Website, Kvk, BTWnr)VALUES     (?,?,?,?,?,?,?,?,?,?,?);
Select SCOPE_IDENTITY()"
As database i user MS SQL Express.Also i tried it with @@Identity, but that works eighter.If i try the insert code with the scope identity above, and i put it in the Query builder from MS Visual Studio, it returns an error.. :
"The following errors were encountered while parsing the contents of the SQL pane:
Unable to parse query text."This error is only there if i add scope_idititiy or @@identity to it.Also if i run it in the SQL part of SQL Studio Express i get the same error.
So should i change this?Or how can i get the id of it ?Of course i can read the database after the insert, but its not save enough i think.
 

View 4 Replies View Related

TableAdapter Configuration Wizard Error

Jan 31, 2007

Hi,I always use the following sql statement to check whether an email address exists in a database. With my latest project I tried to do this within the TableAdapter Configuration Wizard and it gave me an error:  IF NOT EXISTS (SELECT Email FROM tblEmailList WHERE Email=@Email) BEGIN INSERT INTO tblEmailList (Email, DateRegistered) VALUES (@Email, @DateReg) SELECT 1 AS RESULT END ELSE BEGIN SELECT 0 AS RESULT END first it gave an error saying I must declare the variables @Email and @DateReg then when I removed the part of insert and just wanted to check the email address existence it gave me this error:The Compound statement SQL construct or statement is not supportedAny idea anyone? 

View 5 Replies View Related

Passing Integer List To SQL Using TableAdapter?

Apr 15, 2007

I want to select rows that have an integer value IN a parameter list value
I want to pass an integer list to my tableadapter's select 
as in  Select  * from sometable
Where myIntValue IN (@intParamList )
 
I cannot figure out how to get my list into @intParamList
It works fine as a single value  - eg 3, but how do I set it to 1,2,3 ?
Thanks
 Bill

View 2 Replies View Related

TableAdapter.Insert Method At VS .Net 2008.

Mar 28, 2008

Hi there,

i have a problem with my smart device application that i develop.

To simplify:

1) C# smart device appliccation
2)a database file *sdf
3)a typed dataset using the wizard to do so
4)TableAdapter with connection string with no problems(cool)
5)use of Insert method that returns 1 ( also cool) [--> MyTableAdapter.Insert("blabla","blablabla"); <------]
6)no saved records in the DB file(baaaaaad)

Plz any help would be appreciated.

Dimoklis
Software Developer

View 9 Replies View Related

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

May 27, 2008

hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email


i use FUNCTION i get on this forum to use split from multi update

how to loop for evry update send an single eamil to evry employee ID send one email

i update like this


Code Snippet
:

DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3

now
how to send an EMAIL for evry ROW update but "personal email" to the employee



Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'


TNX

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved