DataAdapter Update Problems
Sep 25, 2007
Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows"
I've looked and I can see what the problem is, can anybody help please?
Private Sub FillData()
'SQL connection
Me.sqlconnection = New System.Data.SqlClient.SqlConnection
Me.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")
Dim sql As String
If Request("action") = "gen" Then
sql = "select item from tblPickList where GroupName = 'xxx'AND Category = '" & Category2 & "' ORDER BY item ASC"
ElseIf Request("action") = "fav" Then
sql = "select identifiertext as item from tblfavourites where HRID = " & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC"
ElseIf Request("action") = "spec" Then
sql = "select item from tblPickList where GroupName = '" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC"
End If
ds = New DataSet
da = New SqlClient.SqlDataAdapter(sql, sqlconnection)
Try
sqlconnection.Open()
da.Fill(ds, "tblPickListTemp")
sqlconnection.Close()
Catch ex As Exception
sqlconnection.Close()
End Try
ds.Tables(0).Columns.Add("HRID")
ds.Tables(0).Columns.Add("Flag")
'may possibly add a favourites column here at a later date to ensure no duplication
Dim dr As DataRow
For Each dr In ds.Tables("tblPickListTemp").Rows
dr("HRID") = Session("hrid")
dr("Flag") = 0
Next
DgrdFavourites.DataSource = ds
DgrdFavourites.DataBind()
End Sub
Public Sub CreateTempRecords()
'SQL connection
Me.sqlconnection = New System.Data.SqlClient.SqlConnection
Me.sqlcommand = New System.Data.SqlClient.SqlCommand
Me.da = New SqlClient.SqlDataAdapter
Me.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")
Dim sql As String
sql = "Insert into tblPickListTemp (Item, HRID, Flag) values (@Item, @HRID, @Flag)"
sqlcommand.CommandText = sql
sqlcommand.Connection = sqlconnection
sqlcommand.Parameters.Add("@Item", "Item")
sqlcommand.Parameters.Add("@HRID", "HRID")
sqlcommand.Parameters.Add("@Flag", "Flag")
da.InsertCommand = sqlcommand
da.Update(ds.Tables(0))
End Sub
View 10 Replies
ADVERTISEMENT
Sep 16, 2004
hi
this is probably a piece of pie to fix but i be having some problems......
i have this peice of code to update the sql dataapadapter
Dstest1.Tables("test").Rows(0)("hello") = Me.textboxhello.Text
SqlDataAdapter1.Update(Dstest1, "test")
this does not work, it does work however if i replace the Me.textboxhello.Text
with a value in quotation marks or with another text box not linked to that feild in the table... it does not work with the relevant text box!!!!!!!!
thanks for all your help
cq
View 1 Replies
View Related
May 18, 2007
Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!
View 5 Replies
View Related
Jul 13, 2004
I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?
View 1 Replies
View Related
Feb 5, 2005
Hi,
I'm using DataAdapter.Update() to update data in a table. My question is; how do I create a trigger that works after the update has completely finished?
For example if my update adds 50 new rows to a table the trigger I've currently got fires after each new row that is added ie 50 times in total. Is it possible to get it to trigger after the last (ie 50th) row is added???
Thanks
View 2 Replies
View Related
Nov 30, 2006
Hi,
I am trying to use DataAdapter.Update to save a file stream into SQl Express.
I have a dialog box that lets user select the file:
openFileDialog1.ShowDialog();
I want to put
openFileDialog1.OpenFile();
Into
this.documentTableAdapter.Update(this.docControllerAlphaDBDataSet.Document.DocumentColumn);
I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers.
Thanks,
Ke
View 1 Replies
View Related
May 2, 2008
Hi!
I am trying to insert data into 2 different tables. I am using dataadapter and dataset.
Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
Call ConnectionString()
Dim insertSQL As New SqlCommand()
insertSQL.Connection = sqlConn
insertSQL.CommandText = "SELECT location.CountryName, location.CityName, location.BuildingName, location.FloorID, rooms.name, rooms.FloorID AS Expr1 FROM location INNER JOIN floors ON location.FloorID = floors.id INNER JOIN rooms ON floors.id = rooms.FloorID"
Dim ds As New DataSet()
Dim da As New SqlDataAdapter()
da.SelectCommand = insertSQL
Dim scb As New SqlCommandBuilder(da)
Try
da.Fill(ds)
Dim ndr = ds.Tables("location").NewRow
Dim ndr2 = ds.Tables("rooms").NewRow
ndr("FloorID") = FloorIDDDL.SelectedValue
ndr("CountryName") = CountryNameTextBox.Text
ndr("CityName") = CityNameTextBox.Text
ndr("BuildingName") = BuildingNameTextBox.Text
ndr2("name") = RoomNameTextBox.Text
ndr2("FloorID") = FloorIDDDL.SelectedValue
ds.Tables("location").Rows.Add(ndr)
ds.Tables("room").Rows.Add(ndr2)
da.Update(ds)
ErrMsgLbl.Text = "Information saved successfully"
Catch ex As Exception
ErrMsgLbl.Text = ex.ToString
End Try
sqlConn.Close()
End Sub
The above code does not throw any error. It also does not update the tables.
Your help will be appreciated.
Thanks!
View 5 Replies
View Related
Jan 18, 2008
My DataAdapter.Update() Method is running this query against my database and all the parameters and the formatting looks correct to me. I was wondering if anyone could identify obvious errors.... Thanks!
exec sp_executesql
N'NewUpdateCommand',
N'@Name nvarchar(50),
@PriContactFName nvarchar(50),
@PriContactLName nvarchar(50),
@PriContactWork nvarchar(20),
@PriContactFax nvarchar(20),
@PriContactCell nvarchar(20),
@PriContactEmail nvarchar(50),
@MainOfficeLocationAddr1 nvarchar(50),
@MainOfficeLocationAddr2 nvarchar(50),
@MainOfficeLocationCity nvarchar(50),
@MainOfficeLocationState nvarchar(50),
@MainOfficeLocationZip nvarchar(15),
@Description nvarchar(50),
@Original_ClientID int,
@ClientID int',
@Name=N'Brinker International',
@PriContactFName=N'Steves',
@PriContactLName=N'Maubley',
@PriContactWork=N'913-876-9876',
@PriContactFax=N'913-098-8765',
@PriContactCell=N'913-987-7654',
@PriContactEmail=N'a@b.c',
@MainOfficeLocationAddr1=N'1234 Burkwood',
@MainOfficeLocationAddr2=NULL,
@MainOfficeLocationCity=N'Tallahassee',
@MainOfficeLocationState=N'Florida',
@MainOfficeLocationZip=N'99111',
@Description=N'This is a good client.',
@Original_ClientID=1,
@ClientID=1
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'NewUpdateCommand'.
View 1 Replies
View Related
Feb 28, 2007
Hi, What is the difference b/w sqldatareader and sqldataadapter? For what purpose are they used in a database connection & how do they differ from each other? Pls explain me in detail.Regards Vijay.
View 1 Replies
View Related
Oct 11, 2006
Why DataAdapter does NOT load relations from DB ?the relations are made using SQL SERVER MANAGEMENTbut when a fill a datatable using dataadapter the relations are not loaded .why ? what is the solution to this problem ?
View 1 Replies
View Related
May 17, 2007
Hi i'm a new to ASP.NET and for some reason when i click the Next button in the code below, the pageIndex does not change. Please assist, Basically what i'm trying to do is to use DataAdapter.fill but passing in the start index and the number of records to pull from the dataset table.
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.OleDb;
public partial class Home : System.Web.UI.Page
{
//ConnectionOleDbConnection dbConn;
//discount that can be change by user using a gui interface
//CurrentPageint pageIndex = 0;double discount = 0.15 ;
protected void Page_Load(object sender, EventArgs e)
{
// homeGridView.Visible = true;
BindList();
}protected string getSpecial(string price,object sale)
{String special = "";if (sale.ToString().CompareTo("True") == 0)
{special = String.Format("{0:C}",double.Parse(price) * (1-discount));
}return special;
}
protected void BindList()
{
//Creating an object for the 'PagedDataSource' for holding the data.
//PagedDataSource objPage = new PagedDataSource();
try
{
//open connection
openConnection();
//sql commandstring columns = "*";
string SqlCommand = "Select " + columns + " from Books";
//create adapters and DataSetOleDbDataAdapter myAdapter = new OleDbDataAdapter(SqlCommand, dbConn);DataSet ds = new DataSet("bSet");
//create tableDataTable dt = new DataTable("Books");myAdapter.Fill(ds, pageIndex, 9, "Books");
Response.Write("Page Index: "+pageIndex);
//create table data viewDataView dv = new DataView(ds.Tables["bTable"]);
booksDataList.DataSource = ds;
booksDataList.DataBind();
myAdapter.Dispose();
dbConn.Close();
}catch (Exception ex)
{
Response.Write("Exception thrown in BindList()");
dbConn.Close();throw ex;
}
}
public void openConnection()
{string provider="Microsoft.Jet.OLEDB.4.0";
string dataSource = "C:/Documents and Settings/Owner/My Documents/Visual Studio 2005/WebSites/E-BookOnline/App_Data/BooksDB.mdb";dbConn = new OleDbConnection("Provider =" + provider + ";" + "Data Source =" + dataSource);
dbConn.Open();
}protected void nextClick(object sender, EventArgs e)
{
pageIndex=pageIndex+1;Response.Write("In nextClick"+pageIndex);
BindList();
}protected void prevClick(object sender, EventArgs e)
{if (pageIndex > 0)
{
pageIndex=pageIndex-1;
BindList();
}
}
}
View 1 Replies
View Related
Aug 14, 2007
I have done some reading on the dataadapter class, but do not have a good handle on how to work with dataadapter after it has been filled by a dataset. For instance, should I use an if statement such as this to update the value in the dataadapter? if (dsSunbelt.Tables[0].Columns[39].Equals("")) { DataRow drUpdate = dsSunbelt.Tables[0].Columns[39].Equals(DateTime.Now); } I am sure it is not hard to see what I am doing, and I hope that I am missing something small, but how do I isolate a dataadapter value to update in the corresponding database?
View 2 Replies
View Related
Jan 31, 2008
I'm reading XML data into a DataTable that is populated into a datagrid. From here I need to update database and wanted to know how to Fill a DATA ADAPTER with a DATA TABLE?
I'm familiar with updating using a SQL command, but in this case, i have the dataTable already created from XML.
Please help.
Thanks,
View 3 Replies
View Related
Apr 11, 2006
i have this code :
if(! IsPostBack)
{
string strConnection = "server=localhost; uid=sa;
pwd=sasasa; database=northwind";
string strCommand = "Select * from Customers";
SqlDataAdapter dataAdapter = new
SqlDataAdapter(strCommand, strConnection);
DataSet dataset = new DataSet();
dataAdapter.Fill(dataset, "Products");
SqlCommandBuilder bldr = new
SqlCommandBuilder(dataAdapter);
DataTable dataTable = dataset.Tables[0];
dgCustomer.DataSource = dataTable;
dgCustomer.DataBind();
}
when i run this code, error like this appear :
Server Error in '/Registeration' Application.
SQL Server does not exist or access denied. 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: SQL Server does not exist or access
denied.Source Error:
Line 41: DataSet dataset = new DataSet();Line 42: Line 43: dataAdapter.Fill(dataset, "Products");Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);Line 45: Source File:
e:asp.net
egisteration
egister.aspx.cs Line: 43
Stack Trace:
[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) Registeration.WebForm1.Page_Load(Object sender, EventArgs e) in e:asp.net
egisteration
egister.aspx.cs:43 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()
Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573
how can i solve this problem??? thank you...
View 1 Replies
View Related
Apr 11, 2006
i have this code :
private void Page_Load(object sender, ....)
{
if(! IsPostBack)
{
string strConnection = "server=localhost; uid=sa;
pwd=**secret**; database=northwind";
string strCommand = "Select * from Customers";
SqlDataAdapter dataAdapter = new
SqlDataAdapter(strCommand, strConnection);
DataSet dataset = new DataSet();
dataAdapter.Fill(dataset, "Products");
SqlCommandBuilder bldr = new
SqlCommandBuilder(dataAdapter);
DataTable dataTable = dataset.Tables[0];
dgCustomer.DataSource = dataTable;
dgCustomer.DataBind();
}
}
when i run this code, error like this appear :
Server Error in '/Registeration' Application.
SQL Server does not exist or access denied. 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: SQL Server does not exist or access
denied.Source Error:
Line 41: DataSet dataset = new DataSet();Line 42: Line 43: dataAdapter.Fill(dataset, "Products");Line 44: SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);Line 45: Source File:
e:asp.net
egisteration
egister.aspx.cs Line: 43
Stack Trace:
[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) Registeration.WebForm1.Page_Load(Object sender, EventArgs e) in e:asp.net
egisteration
egister.aspx.cs:43 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()
Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573
how can i solve this problem??? thank you...
View 1 Replies
View Related
May 7, 2007
Good day,
I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.
I thought the connection is not the problem since I got my columns right. Below is my code snippet.
Thanks!
const string COMMAND_TEXT = @"select distinct somefield as matchcode, count(somefield) "
+ "as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1";
SqlDataAdapter adapter = new SqlDataAdapter(COMMAND_TEXT, connection);
DataTable dt = new DataTable(sometablename);
adapter.Fill(dt);
View 13 Replies
View Related
Jan 28, 2008
For some reason I've had trouble with this today... can anyone provide me with a generic code snippet that programatically allows me to select data from a database limited by a parameter and then fills a datatable?
View 4 Replies
View Related
May 8, 2006
hi friends,
i look forward an answer that solves my problem.
iam trying too populate a DropDown list . here is the codings. Previously it was working. suddenly,
it s generating error.
strConnectionString = "Provider = SQLOLEDB;Integrated Security=False; User ID=sa;Password=;Data Source=GIREESH-AC720F7;Initial Catalog=NorthWind"
in page_load event
dim sql as string
sql = "select AthleteNameKey from athletes"
result_adap = DbAccess.ExecuteAdaP(sql)
result_adap.Fill(result_ds, "athletes")
cboAthleteName.DataSource = "athletes"
cboAthleteName.DataTextField = "AthleteNameKey"
cboAthleteName.DataValueField = "AthleteNameKey"
cboAthleteName.DataBind()
Public Function ExecuteAdaP(ByVal sqls As String) As OleDbDataAdapter
'Dim ds As New OleDbDataAdapter
Dim da As New OleDbDataAdapter(sqls, strConnectionString)
'da.Fill(ds)
Return da
End Function
View 1 Replies
View Related
Oct 7, 2004
I'm using DataList to return vales stored in an SQL database, of which one of the fields contains the date the record was added.
I am trying to fill the dataset with items only from the last 30 days.
I've tried a few different ways, but all the database rows are returned.
What is the WHERE clause I sholud use to do this??
Thanks
View 2 Replies
View Related
Apr 8, 2008
I am reading multiple databases into a single dataset, putting each read into a new datatable in the set. Ultimately, I want to merge the multiple tables (they are all from different DB tables in different locations using the same schema). I am also combining this with data from an Oracle database which is in yet another table in the same dataset.
Here is my question:
I will not be propagating any changes to this data back to its original source, but I will be populating it out to Active Directory. (Don't worry; I'm not asking any AD questions here.) But I do want to run SELECT, DELETE, and UPDATE queries against the data in the dataset in memory without writing it out to a separate file. Based on that type of usage, would I be better to use a DataReader or a DataAdapter to do the read from the original database? If I use a DataReader, can I still update the data in memory? In either case, what's the correct methodology to update data in a dataset in memory?
Thanks!
View 3 Replies
View Related
May 22, 2008
My task is to bind and show 3 different values coming from three different queries into three different columns of GridView. I had done this as mention in below. Program was successful. But I want to excute these three queries in same Stored Procedure. I can do that and stored in seperated variables. I need help how to call these three different values in data adapters and store each value in three different columns of grid view.
Simply I want to below statement in stored procedures and call from program. Can any one help me plz.
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da.Fill(ds,"Dep");
SqlDataAdapter da1 = new SqlDataAdapter("select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID", con);
da1.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da1.Fill(ds,"Exp");
SqlDataAdapter da2 = new SqlDataAdapter("select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID", con);
da2.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]);
da2.Fill(ds,"Inc");
string deposits = Convert.ToString(ds.Tables["Dep"].Rows[0].ItemArray[0]);
string expenses = Convert.ToString(ds.Tables["Exp"].Rows[0].ItemArray[0]);
string income = Convert.ToString(ds.Tables["Inc"].Rows[0].ItemArray[0]);
GridView1.DataSource = ds;
GridView1.DataBind();
GridView1.Rows[0].Cells[0].Text = "Total";
GridView1.Rows[0].Cells[1].Text = deposits
GridView1.Rows[0].Cells[2].Text = expenses;
GridView1.Rows[0].Cells[3].Text = income;
//The above program was success.
// This is another way I had tried. But failed. I am getting Index out of bound error. Can any solve this if possible to u.
SqlCommand cmd = new SqlCommand("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con);
cmd.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
GridView1.Rows[0].Cells[1].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID";
GridView1.Rows[0].Cells[2].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID";
GridView1.Rows[0].Cells[3].Text = cmd.ExecuteScalar().ToString();
ASPX Code for Grid View
<h2>Account Summary</h2><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" emptydatatext="There are no data records to display." Width="238px" >
<Columns>
<asp:TemplateField >
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text="Total"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ><%--HeaderText="Deposits"--%>
<ItemTemplate>
</ItemTemplate>
<HeaderTemplate>
<a href="Deposits.aspx" >Deposits</a>
</HeaderTemplate>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField >
<HeaderTemplate>
<a href="Expenses.aspx">Expenses</a>
</HeaderTemplate>
<ItemStyle HorizontalAlign="Center" />
<ItemTemplate>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField >
<HeaderTemplate>
<a href="Income.aspx">Income</a>
</HeaderTemplate>
<ItemStyle HorizontalAlign="Center" />
<ItemTemplate>
<%-- <%# Eval("Course") %>--%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
Plz solve this.
View 4 Replies
View Related
Jan 2, 2008
What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
View 5 Replies
View Related
Nov 11, 2007
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine.
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks
View 2 Replies
View Related
Feb 16, 2006
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies
View Related
Oct 20, 2006
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
View 1 Replies
View Related
Apr 16, 2015
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
View 9 Replies
View Related
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
Jul 23, 2005
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies
View Related
Nov 9, 2007
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
View 5 Replies
View Related
Jul 23, 2006
I'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
The Stored Procedure:
ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)
/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId
IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId
IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN
View 2 Replies
View Related
May 13, 2008
I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.
View 23 Replies
View Related
Mar 18, 2005
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
View 2 Replies
View Related
Feb 18, 2006
I do realize that his could be posted in a few spots but I think the answer is in the SQL.
I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting.
ex:
<asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox>
I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together.
I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters.
Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update.
There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it.
wbochar
View 1 Replies
View Related