Newbie Needs Dataset-sqltable Update Code
Jul 1, 2004
I have dataadapter and dataset that reads/writes to SQL tables.
I can read. I can create "new" records.
However, I have not been able to master the "updating" of an existing row.
Can someone provide me specific code for doing this please or tell me what I doing wrong in the code below.
The code I using is below. I don't get error, but changes do not get written to SQL dbase.
For starters, I think I "not" supposed to use the 2nd line(....NewRow). I think this is only for new row, not updating of existing row - but I don't know any other way to get schema of row.
thanks to any who can help
Dim drow As DataRow
drow = Me.dsRequests1.Tables("REQUESTS").NewRow
drow.BeginEdit()
drow.Item("Request_Name") = Me.txtRequestName.Text
drow.Item("Request_Comments_Txt") = Me.txtRequestComments.Text
drow.Item("Requestor_Contact_Id") = Me.txtRequestor.Text
drow.Item("Request_BigX_Status_Type_Cd") = Me.ddlBigXStatus
drow.Item("Request_Action_Type_Cd") = Me.ddlRequestActionRequested.SelectedItem.Text
drow.EndEdit()
Me.DaREQUESTS.Update(Me.dsRequests1.Tables("REQUESTS"))
Me.dsRequests1.AcceptChanges()
View 1 Replies
ADVERTISEMENT
Nov 27, 2005
Hey everyone,
I know this is a very simple problem however I've literally just begun learning about SQL and this is the first ever code i've created using the language.
The error is:
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ')'. Located on line containing "create table Trailer_Type"
create table Tralier
(load_types varchar(20),
Serial_no varchar(5),
constraint trailerkey primary key(Serial_no));
create table Tractor
(Reg_no varchar(8),
Descript varchar(20),
constraint tractorkey primary key(Reg_no));
create table Unit
(Unit_no varchar(4),
Total_length int(3),
Max_load int(2),
constraint unitkey primary key(Unit_no));
create table Maintenance
(Reg_no varchar(8),
Date_in date(8),
Date_out date(8),
Descript varchar(20),
Job_no varchar(6),
Mech_name varchar(20),
constraint maintenancekey primary key(Job_no));
create table Load_type
(load_type varchar(20),
constraint typekey primary key(load_type));
create table Trailer_Type
(constraint holds foreign key (serial_number) references
Trailer(serial_no),
Constraint holds foreign key (load_type)
references Load_type(load_type));
I'd be very greatful if someone could help me.
Thanks
David
View 6 Replies
View Related
Sep 12, 2007
Hi All,
We are post-deployment with a serious reporting issue that's causing us to rethink our reporting solution. We're considering moving from what we have to SQL Server Reporting Services (client side).
I have spent the past couple of days getting up to speed on this feature and seeing if we can easily migrate it into our existing application. However, I've hit a couple of stumbling blocks and was hoping perhaps someone here could either tell me the solution or point me in the right direction.
I'm not having any problems creating basic reports (e.g. flat data).
However, I am having problems creating reports where there are related tables in a dataset.
The way it works with our existing solution is that I get a dataset (which contains several data tables) and point it to the report's datasource. That report expects those tables and I have defined table-relationships in the report which process and display the information correctly.
I'm not having as much luck with RDLC.
I can go into futher detail about how I'm creating the report, but let me just ask these general questions first:
1. Can I set a dataset containing multiple datatables equal to a property on an RDLC report and that RDLC report know how to treat and display the data?
2. Is there a better/smarter than this to get a field selection from my datasource (remember, this information is coming from a stored procedure so connecting directly to the database is not an option):
a. In code, populate dataset
b. In code, write dataset schema to xml (e.g. an xsd file)
c. In Visual Studio, add the XSD file to project
d. Use fields from XSD file to drag and drop fields on report
A fancy example would be nice too. I've googled like crazy the past couple of days and downloaded as many samples as I can find (including the ones on ftponline.com, gotreportviewer.com, "Tudor's WebLog", and several others). However, I have yet to find one that uses grouping and related datatables.
Thank you so much (if, for nothing else, reading this post )
View 4 Replies
View Related
Aug 10, 2007
Hi
I really need your help. I’ve got a table with 4 columns – username (a string of text), dateTime (a date and time), duration (time in minutes), roomID (URL). I really need some help with writing up some code behind to check if a user has permission to continue. I’ve got the first bit, but I need help with collecting the data and testing it:
SqlConnection conn = new SqlConnection(Global.ConnectionString); SqlCommand cmd = new SqlCommand("select * from tblActiveSession where Username=@Username", conn);
SqlParameter paraUsername = cmd.Parameters.Add("@Username", SqlDbType.NVarChar); paraUsername.Value = this.txtUsername.Text;
try { conn.Open()
This is where I need the help. What I need is to test each row against the following:
If now() is within the allowed time frame – greater than dateTime, but less than dateTime + duration, then break the loop and redirect to roomID.
I really hope this makes sence, and I’m greatful for any help.
Many thanksRichard
View 13 Replies
View Related
Jul 23, 2005
Is there any better way to debug a query encased in IIF statements thanto find bugs at runtime? I have an especially ugly CommandText for oneof my datasets, and debugging it when there is an error is nearlyimpossible. I realize that it's much cleaner when you have straightSQL, but otherwise the tool doesn't seem to be very user-friendly.Thanks,Mike
View 1 Replies
View Related
May 9, 2007
I am converting a crystal report to ssrs 2005.
I would like to know what the best method of handling a rather large record selection task.
Here is the crystal version.
if {?WhseRegion} = 'Western' then {R_ShippingLog;1.InvUnitSite} in ['LA','AL','DA','DB','OA','PO','SE','TH','TN']
else if {?WhseRegion} = 'Eastern' then {R_ShippingLog;1.InvUnitSite} in ['AD','BE','CH','CI','EL','FL','IL','JL','KC','KS','MA','MI','MK','NE','NJ','NO','PA','SA','STL']
else {R_ShippingLog;1.InvUnitSite} in ['LA','AL','DA','DB','OA','PO','SE','TH','TN','AD','BE','CH','CI','EL','FL','IL','JL','KC','KS','MA','MI','MK','NE','NJ','NO','PA','SA','STL']
What I trying to figure out is how to incorporate the above in the dataset filter area on the datagrid.
any ideas?
View 9 Replies
View Related
Mar 27, 2008
I am able to export to excel through a stored procedure.But when iopen it the datetime column and int columns are not getting recognised.
View 2 Replies
View Related
Oct 3, 2007
Hi
I'm modifying a report from MS CRM. Sales Pipeline. Not the easiest one but still. I just got to the drilldown report and need to change some things in it. I tested the SQL code as I could in SQL Server Management Studio so that I don't have any misspellings (the report I'm modifying is on a remote location). Anyway, now that I copy my code to the SQL Server Business Intelligence Development Studio and to the dataset I'm modyfing I see that a chunk of code from the bottom is missing ... ??? ... is there a limit on how much I can type?!
I counted in Word that I have 33.176 characters and 878 lines of code. Is that to much?!
The original report has a dataset of 27.529 characters and 724 lines of SQL code.
View 27 Replies
View Related
Jan 2, 2008
Hi there!
For setting different languages on the column haeders i need to access a dataset from a custom code function call. I want to pass a parameter to the function that will allow me to find a specific item in the dataset - like a lookup function. I cannot find a way to get access to the entire dataset and to iterate through its components.
Is there any solution? Every hint will be helpful!
Thanks, Torsten
View 3 Replies
View Related
Jan 30, 2004
If I run a update stored procedure on my SQLServer It work Fine.
But When I try it in my VB code, it's just do nothing not even an error message.
What I've got to do for being able to Update SQLTable with a stored procedure?
That's my VB code:
Dim objConnect As SqlConnection
Dim strConnect As String = System.Configuration.ConfigurationSettings.AppSettings("StringConn")
objConnect = New SqlConnection(strConnect)
Dim objCommand As New SqlCommand("internUpdate", objConnect)
objCommand.CommandType = CommandType.StoredProcedure
Try
Dim objParam As SqlParameter
objParam = objCommand.Parameters.Add("Id", SqlDbType.Int)
objParam.Direction = ParameterDirection.Input
objParam.Value = InternIDValue
objParam = objCommand.Parameters.Add("Address", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Address.Value.Trim()
objParam = objCommand.Parameters.Add("City", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = City.Value.Trim()
objParam = objCommand.Parameters.Add("ProvinceCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myProvince.SelectedValue
objParam = objCommand.Parameters.Add("PostalCode", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = PostalCode.Value.Trim()
objParam = objCommand.Parameters.Add("Phone", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone1.Value.Trim()
objParam = objCommand.Parameters.Add("Phone2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone2.Value.Trim()
objParam = objCommand.Parameters.Add("Email", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress1.Value.Trim()
objParam = objCommand.Parameters.Add("Email2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress2.Value.Trim()
objParam = objCommand.Parameters.Add("EmploymentStatusCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myStatus.SelectedValue
objConnect.Open()
objCommand.ExecuteNonQuery()
objConnect.Close()
Catch ex As Exception
Exit Sub
End Try
Thanks!!
View 1 Replies
View Related
Sep 20, 2007
Hi
I wonder if it is possible to create a dataset in code and then feed it to a Reporting Svcs (RS) report and have it rendered on the data from this dataset.
My collegues does this with Crystal and it would break my heart if I cant do this with (RS)...
I have tried to find a solution but so far, no luck.
Anyone have any ideas?
/F
View 3 Replies
View Related
Apr 6, 2007
Hello all!
I have a question. I have report that have defined dataset. Can I somehow get access to data in this dataset from this report in Code section?
I need to write function that will return value from one field based on 45 parameters (they are values from 4 fields in this datset), like:
dataset fields:
RYear, RMonth, AYear, AMonth, CAtegory, Amount.
I need to get Amount based on RYear, RMonth, AYear, AMonth, Category values that I need to pass as parameters to functions.
Thank you
View 3 Replies
View Related
Aug 9, 2006
I used a function to create dataset as below:
Public Function GetSQLDataSet(ByVal SQL As String) As DataSet
......
MyConnection = New SqlConnection(MyConnectionString)
MyCommand = New SqlCommand(SQL, MyConnection)
MyDataSet = New DataSet
MySQLDataAdapter = New SqlDataAdapter(MyCommand)
MySQLDataAdapter.Fill(MyDataSet)
......
End function
It works fine.
How to code a function to return a dataset in which there are two tables and relationship?
View 1 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
Jun 16, 2007
Hello guys,
I am askng for any help ...i am new at SQL
I want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100.
--------------------------------------------------------------------
There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
-------------------------------------------------------------
I have this so far but it doeant give what i want.
update c
set priority = 1
from customer c inner join orders o
on c.customer_id = o.customer_id
inner join product p
on o.product_id = p.product_id
where importance = 100
View 1 Replies
View Related
Feb 26, 2007
Hi, I am trying to figure out how to update a dataset. Below is my sample code but it gives me this error on the mySqlDataAdapter.Update line.Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. I looked around and it seems like I'm supposed to add a Primary Key but I did that and still get nothing. Below is my page load code... it is based on the quickstart at http://quickstarts.asp.net/QuickStartv20/howto/doc/adoplus/UpdateDataFromDB.aspx but I'm still having trouble and not sure what I'm missing. 1 // Create a new Connection and SqlDataAdapter
2 SqlConnection myConnection = new SqlConnection("Server=localhost;uid=user;pwd=password;database=northwind");
3 SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Customers", myConnection);
4 DataSet myDataSet = new DataSet();
5 DataRow myDataRow;
6
7 // Create command builder. This line automatically generates the update commands
8 // for you, so you don't have to provide or create your own.
9 SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
10
11 // Set the MissingSchemaAction property to AddWithKey because Fill will not cause
12 // primary key & unique key information to be retrieved unless AddWithKey is specified.
13 mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
14 mySqlDataAdapter.Fill(myDataSet, "Customers");
15
16 showData(myDataSet);
17
18 // Set PK of table to CustomerID ??
19 DataColumn[] pk = new DataColumn[1];
20 pk[0] = myDataSet.Tables["Customers"].Columns["CustomerID"];
21 myDataSet.Tables["Customers"].PrimaryKey = pk;
22
23 // Edit some data
24 myDataSet.Tables["Customers"].Rows[0]["ContactName"] = "Maria Anders2";
25 //DataRow myDataRow1 = myDataSet.Tables["Customers"].Rows.Find("ALFKI");
26 //myDataRow1["ContactName"] = "Maria Anders2";
27
28 showData(myDataSet);
29
30 //Update DB
31 mySqlDataAdapter.Update(myDataSet, "Customers");
32 Thanks!
View 2 Replies
View Related
Oct 25, 2004
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
Pam
View 8 Replies
View Related
Jul 23, 2005
I know this is very very silly.But I am stuck and don't know what to do.I have a simple form which has 1 textbox and a 'Save' button.I have created a Database connection and an Adapter with Dataset withthe help of Wizards.I have binded by text box control to one of the database field.When the form gets loaded it displays the field information correctly."Me.SqlDataAdapter1.Fill(DsBasicData1)"However, when I press the save button it does not update the databasebehind it.I have given the following code to the click event of save button."Me.SqlDataAdapter1.Update(DsBasicData1)"Strangely I don't know why the code does not work.
View 1 Replies
View Related
Oct 26, 2007
I have a gridview and a dataset. When I start my application I see the results in my gridview. When I clic on edit and chanche some value and click on update it wil give me this error:ObjectDataSource 'ObjectDataSourcePersoon' could not find a non-generic method 'Update' that has parameters: naam, tussenvoegsel, achternaam, adres, woonplaats, telefoonnummer, original_id, Original_naam, Original_tussenvoegsel, Original_achternaam, Original_adres, Original_woonplaats, Original_telefoonnummer. Does someone know whats the problem??
View 10 Replies
View Related
Feb 6, 2007
Do I have to use condition split?, then union all?
if in script, I can use update from <tablename> Set column = isnull(column, 'NA'). It's so simple.
I'm also wondering can we run SQL Script against input dataset in a SSIS component?
View 6 Replies
View Related
Feb 5, 2007
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
HELP!!
View 5 Replies
View Related
May 23, 2007
Here is my problem: I've created a table in MSSQL named MyReferences and then a typed dataset to connect to it.
Then I realized that I needed to add a column to MyReferences table and then I did it.
The problem is that I cannot find a way to add the new column to the typed dataset. I am unable to view it in the available columns.
Can anyone help me?
I'm using Visual Studio 2005 sp1 and C#.
Best regards
Alessandro
View 1 Replies
View Related
Feb 15, 2008
(Hope this isn't a "stupid" question, but I haven't been able to find a straight-forward answer anywhere)"
I currently have code that iterates through a dataview's records, making a change to a field in some of the records. The way I have this coded, a conection has to opened & closed for each individual record that's updated:
dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)" dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit) dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int)
Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView) For Each myRow As DataRowView In myDataView If myRow("FkUsersAnswerID") = myRow("AnswerID") Then intCorrect = 1 Else intCorrect = 0 End If dsrcUserIae.UpdateParameters.Item("blnCorrect").DefaultValue = intCorrect dsrcUserIae.UpdateParameters.Item("ID").DefaultValue = myRow("ID") intUpdateResult = dsrcUserIae.Update() Next
It seems like I should be able to do something like this (call update once), but I'm not sure how...
dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)" dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit) dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int)
Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView) For Each myRow As DataRowView In myDataView If myRow("FkUsersAnswerID") = myRow("AnswerID") Then myRow("blnCorrect") = 1 Else myRow("blnCorrect") = False End If Next
intUpdateResult = dsrcUserIae.Update() 'Want all changed myRow("blnCorrect") to be updated to datasource
Can anybody explain how to do the bulk update? I've seen some info about AcceptChanges and Merge, but I'm not sure if they apply here, or if they more for Transactions.
View 1 Replies
View Related
Jun 14, 2007
I have an existing call to a webservice that updates an MDB with data from a dataset.
I was now moving to a process using SQL Compact Edition sdf instead of an mdb.
I belive I have to DROP/Truncate the table in the SDF file, then create a new table then somehow insert the results of the retrieved dataset into the new table. DOes anybody have any pointers to help me out?
Here is my old way of doing it, using access.
Thanks.
Private Sub retrieveData(ByVal intMode As Integer)
'This process will call a webservice passing a parameter which in turn executes a SQL stored procedure, returning a dataset
Dim MyObj As localhost.Service1 = New localhost.Service1
Dim ds As DataSet
ds = MyObj.RetrieveSQLdata(txtSecureString, intMode)
Dim myConnection As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand
Dim Connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & FileName & ";Persist Security Info=False"
myConnection = New OleDbConnection(Connstr)
myConnection.Open()
cmd.Connection = myConnection
Dim mystr As String = ""
For Each row As DataRow In ds.Tables(0).Rows
mystr = ""
mystr = mystr & "INSERT INTO CustMast ( "
mystr = mystr & "StoreNo, CustNo, ShipNo"
mystr = mystr & "values("
mystr = mystr & "'" & row("StoreNo").ToString & "', "
mystr = mystr & "'" & row("CustNo").ToString & "', "
mystr = mystr & "'" & row("ShipNo").ToString & "')"
cmd.CommandText = mystr
cmd.ExecuteNonQuery()
Next
System.Windows.Forms.Application.DoEvents()
cmd.Dispose()
myConnection.Close()
myConnection.Dispose()
End Sub
View 1 Replies
View Related
Jun 11, 2007
Hello,
I need to change my usernames in a column from JSmith to ABCSmith. What would be my update statement to make that change? I need something that would basically start at position 1, for a length of 1, then use that to replace with ABC...
Here is what I have been trying:
UPDATE tblusers,
SET userLoginID = replace(userloginID, 1, 'ABC')
Thanks in advance.
View 6 Replies
View Related
Mar 22, 2007
Like the subject says, I'm using strongly typed datasets. I'm using to designer to create the datasets and the methods. I can select and insert, but I can't update or delete. I right click on the adapter bar and select Add Query. I sleect 'Use SQL Statements'I select 'Update' (or 'Delete')I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'I can click on next, but anything else gives me errors. I'd list them, but I'm clearly doing something wrong and it's probably obvious. Diane
View 5 Replies
View Related
Feb 5, 2015
Imagine the following scenario: two tables (say, "requests" and "details") are joined in a 1:n relationship on MSSQL 2008. Both tables contain an ID (autoincrement field) and a timestamp field for proper concurrency management. Data access in the frontend is provided by a typed dataset in VS 2010. There are SPs on the server which select, update, insert or delete data in each of the two tables (so, 8 SPs alltogether: uspRequestsSelect, uspDetailsInsert etc.). These SPs are used for data access in the dataset. The GUI is a Windows form with 2 datagridviews, one for request datatable and one for the child-relation-based datatable FK_request_details. So, each request shows its details. The form works well so far.
Now, trouble strikes. A business rule says: "the first details row of a request (=row with lowest ID) always has a 0 in column "additional fee". For additional detail rows, this field has to be set to constant value 45". In short: the first detail row of each request is free, second and later details are charged 45 €.
So, I created a SP "uspRequestFeeManager", which recalculates all (!) detail rows of a request. This SP is called in uspDetailsInsert, uspDetailsUpdate and uspDetailsDelete, as each of this cases causes the additional fee to be recalculated for all rows (as rowcount can change). In Management Studio, this works as well!
But: as the uspRequestFeeManager changes data even for rows the user did NOT touch, there's a concurrency exception in my frontend in the following case:
In a request with 2 detail rows (first row has fee = 0, second = 45), the user deletes the row with fee = 0. Committing via TableAdapterManager calls uspDetailsDelete, which calls uspRequestFeeManager, which sets the remaining single details row to a fee value of 0 (which is correct!). This causes TableAdapterManager.UpdateAll to fail ("concurrency exception; delete command has handled 0 of 1 expected records"), as uspRequestFeeManager has "edited" a row which the user didn't touch, and thus updated its timestamp as well. So, the list is out of sync.
The uspRequestFeeManager looked like a good idea... but it seems not to be.
Approach would be: instead of calling the uspRequestFeeManager from within the SP, call it programmatically after TableAfterManager.UpdateAll, and after that, 're-fill' the details datatable with the updated data. But that would transfer business logic from server to client. I don't like that...
View 3 Replies
View Related
Feb 24, 2006
Hi,
I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?
SqlCommand snUpdate = conn.CreateCommand();
snUpdate.CommandType = CommandType.Text;
snUpdate.CommandText = "Update TestTable set shipdate = @shipdate";
snUpdate.Parameters.Add("@shipdate", SqlDbType.Char, 10, "shipdate");
string jdate ="";
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();
ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);
}
da.Update(ds, "Table1");
conn.Close();
-Thanks
View 4 Replies
View Related
Apr 18, 2008
Hi I and using gridview. And binding the data in the code behind.I need to use update command in code behind. How do I achieve this? I protected void lookUP (object sender, EventArgs e)
{
string strSql, strConn;
System.Text.StringBuilder whereClause = new System.Text.StringBuilder();
strConn = ConfigurationManager.ConnectionStrings["drake_CSMConnectionString1"].ConnectionString;
SqlConnection Conn = new SqlConnection(strConn);
if (newClientName.Text != "")
whereClause.Append("'" + newClientName.Text + "'");
strSql = "SELECT * FROM [ftsCSM] where [client_name] = " + whereClause.ToString();
SqlDataAdapter dataAdapter = new SqlDataAdapter(strSql, Conn);
DataSet ds2 = new DataSet();
dataAdapter.Fill(ds2, "ftsCSM");
DataTable dataTable2 = ds2.Tables["ftsCSM"];
int totalRec = dataTable2.Rows.Count;
Clients.DataSource = ds2;
Clients.DataBind();
} // end of lookup()
View 1 Replies
View Related
Dec 15, 2007
Where am I going wrong? I have no idea where to start to look for any incorrect code because it all looks ok! Please help!!!
I am entering a record into 5 textboxes - I can see the new record when I open the datagrid view, but when I close the programme and reopen it the record disappears, this code looks ok to me so where is it wrong? Obviously the record is not being written to the database so that when I reopen the programme there is nothing to fill the dataset..........any ideas?
Your help is appreciated!!!
Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSave.Click
Mainform.SqlConnection1.Open()
Dim TheNewRow As DataRow
TheNewRow = Mainform.Dataset1.Tables("ChartTable").NewRow
TheNewRow("Chart Number") = TB1CN.Text
TheNewRow("Chart Title") = TB2CT.Text
TheNewRow("Edition Date") = TB3ED.Text
TheNewRow("Print Date") = TB4PD.Text
TheNewRow("Edition Number") = TB5EN.Text
Mainform.Dataset1.Tables("ChartTable").Rows.Add(TheNewRow)
Mainform.SqlDataAdapter1.Update(Mainform.Dataset1)
Mainform.SqlConnection1.Close()
Me.Visible = False
End Sub
Regards,
Steve
View 4 Replies
View Related
May 31, 2008
In the device emulator when it starts I see two records that were added to the SDF in the VS2008 IDE. I added a third record and after tapping on the Save menu my navigation control shows all three records. I then closed the application. I went to Memory and stopped the form. I went to the File Editor and restarted the application. It only showed the original 2 records. In the File Editor I tapped on the SDF to open it in the Query Analyzer. It also only shows the original two records.
The Dataset appears to be updated, but not the bound table in the SDF. Can any one help? Code is below.
bsTEST is the binding SOurce
navTEST is my custom Navigation control
Private Sub HandleMenus(ByVal Sender As Object, ByVal EA As EventArgs) Handles mnuAdd.Click, mnuCancel.Click, mnuSave.Click
With Me
Select Case True
Case Sender Is .mnuAdd
.Menu = .mnuCancelSave
txtLOC.ReadOnly = False
bsTEST.AddNew()
.navTest.Visible = False
Case Sender Is .mnuCancel
.Menu = .mnuMain
txtLOC.ReadOnly = True
With bsTEST
.CancelEdit()
.Position = navTest.CurrentRecord = -1
End With
.navTest.Visible = True
Case Sender Is .mnuSave
.Menu = .mnuMain
txtLOC.ReadOnly = True
With .navTest
.RecordCount += 1
.CurrentRecord = 1
End With
.navTest.Visible = True
bsTEST.EndEdit()
bsTEST.Position = 0
taTEST.Update("Locations")
End Select
End With
View 4 Replies
View Related
Mar 12, 2005
I am trying to perform a simple update to a SQL Server database table and I can't figure out why this simple UPDATE command doesn't work. I am performing the same thing in another page for owner information and it works just fine. It seems that some of my code won't work even though the syntax is correct and there is no reason for it not to work.
I have hardcoded text for some of the fields and the UPDATE code below works but when I try to use the information that may be in the Textbox, the code won't do the UPDATE. Please help me figure out what is going on.
Thanks
Sub btnUpdate_Click_1(sender As Object, e As EventArgs)
Dim UserCode as String
Dim PropertyCode as String
Dim UnitCode as String
Dim address1 as String
Dim address2 as String
Dim address3 as String
Dim city as String
Dim zip_Code as String
Dim description as String
Dim price_Range as String
Dim state as String
Dim type_Property as String
Dim property_Status as String
if chkChangeState.Checked = True then
state = ddl_State.SelectedItem.Value
else
state = lblState.Text
end if
if chkPropertyType.Checked = True then
type_Property = ddlPropertyType.SelectedItem.Value
else
type_Property = lblPropertyType.Text
end if
if chkPropertyStatus.Checked = True then
property_Status = ddlPropertyStatus.SelectedItem.Value
else
property_Status = lblPropertyStatus.Text
end if
txtPropertyCode.ReadOnly = False
txtUnitCode.ReadOnly = False
'address1 = txtAddress1.Text
'address2 = txtAddress2.Text
'address3 = txtAddress3.Text
'city = txtCity.Text
'zip_Code = txtZipCode.Text
'description = txtDescription.Text
'price_Range = txtPriceRange.Text
UserCode = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
PropertyCode = txtPropertyCode.Text
UnitCode = txtUnitCode.Text
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "UPDATE [Property_db] SET [Address1]=@Address1, [Address2]=@Address2, [Address3]=@Address3, [City]=@City, [State]=@State, [Zip_Code]=@Zip_Code, [Type_Property]=@Type_Property, [Description]=@Description, [Property_Status]=@Property_Status, [Price_Range]=@Price_Range WHERE ([Property_db].[Code] = @Code) AND ([Property_db].[Prop_Code] = @Prop_Code) AND ([Property_db].[Unit_Code] = @Unit_Code)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_code.ParameterName = "@Code"
dbParam_code.Value = UserCode
dbParam_code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_code)
Dim dbParam_prop_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_prop_Code.ParameterName = "@Prop_Code"
dbParam_prop_Code.Value = PropertyCode
dbParam_prop_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_prop_Code)
Dim dbParam_unit_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_unit_Code.ParameterName = "@Unit_Code"
dbParam_unit_Code.Value = UnitCode
dbParam_unit_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_unit_Code)
Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address1.ParameterName = "@Address1"
dbParam_address1.Value = txtAddress1.Text
dbParam_address1.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address1)
Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address2.ParameterName = "@Address2"
dbParam_address2.Value = txtAddress2.Text
dbParam_address2.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address2)
Dim dbParam_address3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_address3.ParameterName = "@Address3"
dbParam_address3.Value = txtAddress3.Text
dbParam_address3.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_address3)
Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_city.ParameterName = "@City"
dbParam_city.Value = txtCity.Text
dbParam_city.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_city)
Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_state.ParameterName = "@State"
dbParam_state.Value = state
dbParam_state.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_state)
Dim dbParam_zip_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_zip_Code.ParameterName = "@Zip_Code"
dbParam_zip_Code.Value = txtZipCode.Text
dbParam_zip_Code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_zip_Code)
Dim dbParam_type_Property As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_type_Property.ParameterName = "@Type_Property"
dbParam_type_Property.Value = type_Property
dbParam_type_Property.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_type_Property)
Dim dbParam_description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_description.ParameterName = "@Description"
dbParam_description.Value = txtDescription.Text
dbParam_description.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_description)
Dim dbParam_property_Status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_property_Status.ParameterName = "@Property_Status"
dbParam_property_Status.Value = property_Status
dbParam_property_Status.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_property_Status)
Dim dbParam_price_Range As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_price_Range.ParameterName = "@Price_Range"
dbParam_price_Range.Value = txtPriceRange.Text
dbParam_price_Range.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_price_Range)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
'if Not rowsAffected then
Response.Redirect("./editproperty.aspx")
'end if
End Sub
View 1 Replies
View Related
Sep 17, 2005
Not sure what happened to my post, it seems to have disappeared. Here we go again. I have a stored procedure that I would like some feedback on, as I feel it may be inefficient as coded:
@ZUserID varchar(10)
AS
SET NOCOUNT ON
DECLARE @counter int
SET @counter = 0
WHILE @counter < 10
BEGIN
SET @counter = @counter + 1
IF EXISTS(SELECT * FROM tblWork WHERE UserID = @ZUserID And LineNumber = @counter)
BEGIN
UPDATE tblWork SET
TransID = Null,
TransCd = Null,
InvoiceNo = Null,
DatePaid = Null,
Adjustment = Null,
Vendor = Null,
USExchRate = Null
WHERE
UserID = @ZUserID And LineNumber = @counter
END
ELSE
INSERT INTO tblWork
(LineNumber,TransCd,UserID)
VALUES
(@counter,'P',@ZUserID)
END
View 2 Replies
View Related
