How Do I Update A SQLCE SDF Table With A Dataset Returned From A Webservice?
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
ADVERTISEMENT
Mar 11, 2008
I'm having a bit of a problem figuring out how to pass a dataset to sqlce to be appended to a table.
I'm using the CSVDataAdapter.dll to import from a csv file. I'm able to load the data into a dataset but from there I'm having my problem. Since the recordset is not from my table that I want to write, how can I pass the recordset for appending?
Code Snippet
Dim ds = New DataSet()
da = New CSVDataAdapter(apppath + "DBmyfile.csv", True)
'Fills the recordset with CSV file information
da.Fill(ds, "Newfile")
'display in grid
Me.DataGrid1.DataSource = ds.Tables("Newfile")
Could I use - USING?
Code Snippet
Using cmd As New SqlCeCommand("Newfile", conn)
cmd.CommandType = CommandType.TableDirect
'code to manipulate the resultset
Using ds As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable)
End Using
View 13 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
Jan 10, 2008
I'm using a SqlDataSource with a GridView to look at the contents of a table. I'd like to display the number of rows returned by the query in a label above the grid. How do I get a hold of this value? I can't find where it's exposed through the DataSource or the Grid. I can get the number of rows in the currently displayed page, but that's not the value I'm after.
View 6 Replies
View Related
Nov 21, 2006
I have a web page that has been in production for over a year. On occasion the web app users report sporadic errors which last a few minutes then go away. The page loads up a generic DataSet, and then that DataSet is assigned to a datagrid on the page. The error is that a column that is expected to be in the dataset is not found. I know this column must exist in the database because its a required field. After we reset IIS, then all returns to normal and the errors go away. Below is the stack trace.
Exception: System.IndexOutOfRangeException Message: Cannot find column LastName. Source: System.Data at System.Data.DataTable.ParseSortString(String sortString) at System.Data.DataView.UpdateIndex(Boolean force) at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataFilter newRowFilter) at System.Data.DataView..ctor(DataTable table, String RowFilter, String Sort, DataViewRowState RowState) at MySystem.Web.GuestSearch.ShowResults(DataSet ds, Int32 currentPageIndex) at MySystem.Web.GuestSearch.LoadResults(Int32 currentPageIndex) at MySystem.GuestSearch.btnSearch_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain()
Our DBA can find no problems on the database side and our web servers don't log any system event problems either. I do not understand why resetting IIS helps the issue. I need to find the real cause of the problems rather than just resetting IIS all the time. Any ideas?
View 1 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
Sep 13, 2006
I am working on a company sign out sheet for our Intranet. The application accesses a sql database. I tried using a datareader as well as a dataset to access a small piece of information regarding the group that each employee belongs to in the company. My last attempt was to assign the value to the text property of a text box and evaluate the text from the text box with the Select Case but that did not work either.The application is supposed to generate an email to our receptionist as well as to the group technical assistant responsible for each group according to the value that is passed into the Select Case statement. With the datareader as well as the dataset and even now with creating a text box and accessing the text property, I was able to assign the group value to a variable (I can see the value in the subject of the email), but when the variable is supposed to be evaluated by the select case statement it skips right through all of the cases to case else and uses only the receptionist's address in the email as if it doesn't even see the value of the variable. I have searched for a possible answer to the problem I am but so far have had no luck. Any ideas? I included the part of the code that I am having trouble with: '********************************************************************************************' New DataSet is created to determine the group the employee belongs to so that the appropriate' group technical assistant is emailed when employee signs out'******************************************************************************************** ' declare variablesDim sqlGroup As String ' string variable to store sql statement for datasetDim BLGroup As String ' string variable to store the bl group of the employeeDim emailAddress As String ' string variable to store resulting email address Dim groupDS As DataSet ' dataset variableDim groupAdapter As SqlDataAdapter ' sql data adapter variable' sql statement to access group value from the databasesqlGroup = "SELECT BLGroup FROM BLGroupsView WHERE (RTRIM(fname) + ' ' + LTRIM(lname)='" & employee & "')" ' repopen the connection (leftover from working with the datareader)Connection.Open()' create new SqlDataAdapter applying sql statement and connectiongroupAdapter = New SqlDataAdapter(sqlGroup, Connection)' create new dataset groupDS = New DataSet()' populate the datasetgroupAdapter.Fill(groupDS, "BLGroupsView") ' get the value stored in the BLGroup column (only one row is returned at a time) BLGroup = groupDS!BLGroup' Create GroupTextBox TextBox control.Dim GroupTextBox As New TextBox()' Set options for the UserTextBox TextBox control.GroupTextBox.ID = "GroupTextBox"GroupTextBox.Visible = "False"GroupTextBox.Text = BLGroup' just trying anything with this next line, even when i didn't set this the select case statement did not seem to see the value returned by the variableGroupTextBox.runat = "server" ' use a select case statement to evaluate the value of the group (didn't work when I passed in the BLGroup variable eitherSelect Case GroupTextBox.TextCase "bh"emailAddress = "receptionist@myaddress.com; bhassistant@myaddress.com"Case "env"emailAddress = "receptionist@myaddress.com; envassistant@myaddress.com"Case "sw"emailAddress = "receptionist@myaddress.com; swassistant@myaddress.com"Case "fac"emailAddress = "receptionist@myaddress.com; facassistant@myaddress.com"Case "www"emailAddress = "receptionist@myaddress.com; wwwassistant@myaddress.com"Case ElseemailAddress = "receptionist@myaddress.com"End Select '*************************************************************************************************' set new message objectDim myMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage()'**********************************************************' set message properties'********************************************************** myMessage.From = "me@myaddress.com"myMessage.To = emailAddressmyMessage.Subject = employee & " Signing Out at " & TimeOut.Text & " " & BLGroupmyMessage.Body = employee & " Signing Out at " & TimeOut.Text & " for " & Destination.Text & " will potentially return at " & EstTimeIn.Text'***********************************************************' set smtp server namesmtpMail.SmtpServer = "mailserver1"' send email using smtpsmtpMail.Send(myMessage) It sends the message and returns the BLGroup value everytime but only sends to the receptionist. Thanks for your time!
View 1 Replies
View Related
Mar 30, 2004
hi all
Any day, another question. I love you guys.
I want to select a subset of data from a dataset returned by either another subquery or a union.
e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.
select * from
(
select col1, col2, col3 from table1
union
select col1, col2, col3 from table 2
)
where col1 = 'blah'
in essence oracle treats the data returned by the subquery as a table that it would select from.
how would i do the same in mssql?
thank you
James :)
View 5 Replies
View Related
Jan 12, 2007
In one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out.
I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body).
Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method.
Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution).
Thanks.
View 1 Replies
View Related
Feb 15, 2008
I use visual studio 2008 to design a database application.
I am in the peak of finalising the program but I have a snag with updating data in the database.
I could create new rows, add rows and new rows of data to the table but whenever i try to change the data in a cell
it returns an error "Update requires a valid UpdateCommand when passed DataRow collection with modified rows."
I have tried all possible solutions, using table adapter, commands etc but wont get it.
This situation also applies to changes made directly on the data grid and also when using the binding navigator buttons
to edit and save data.
please help me.
Barbara Grayson
View 1 Replies
View Related
Jun 7, 2001
Any help would be appreciated.
Thanks.
Steve
The error below is encounter when a mass update is attempted on our employee table.
Server: Msg 512, Level 16, State 1, Procedure emp_lock_id, Line 8
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
The Trigger code is below
CREATE TRIGGER emp_lock_id ON dbo.EMPLOYEE
FOR UPDATE
AS
DECLARE @NEXT_NUM DECIMAL(17, 12)
DECLARE @NEW_NUM DECIMAL(17, 9)
DECLARE @OLD_LOCK_ID DECIMAL
DECLARE @NEW_LOCK_ID DECIMAL
SET @OLD_LOCK_ID = (SELECT EMP_LOCK_ID FROM DELETED)
SET @NEW_LOCK_ID = (SELECT EMP_LOCK_ID FROM INSERTED)
IF @OLD_LOCK_ID <> @NEW_LOCK_ID
BEGIN
RAISERROR('Record Modified by different user.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
SET @NEXT_NUM = (convert(decimal(17,12), getdate()))
SET @NEW_NUM = @NEXT_NUM * 1000
UPDATE EMPLOYEE SET EMP_LOCK_ID = (@NEW_NUM - floor(@NEW_NUM)) * 1000000000
FROM EMPLOYEE, INSERTED
WHERE EMPLOYEE.EMP_EMPLOYEE_ID = INSERTED.EMP_EMPLOYEE_ID
END
View 6 Replies
View Related
Sep 27, 2004
Hi guys,
I am just trying to do a simple update statement and am getting this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
My statment is like this:
Code:
update [Clients]
SET [OPT-OUT] = 0 WHERE [OPT-OUT] is null
I have used this against another table with success, but get the error against that one. Anyone have any idea?
View 4 Replies
View Related
Oct 3, 2007
I am struggling with syntax for an update statement.
Goal: If a RelationID has invoice history then HasHistory=1
UPDATE [Tbl_Relations]
SET [HasHistory]= 1
FROM [Tbl_Relations] r INNER JOIN
(SELECT RelationID
FROM vw_Invoice_history
GROUP BY RelationID
) inv ON r.RelationID= inv.RelationID
Result: Subquery returned more than 1 value
What am I doing wrong?
Thanks in advance,
Frans
View 9 Replies
View Related
Feb 6, 2006
I am currently upgrading our ppc app (written in .net 2003) to .net 2005 and from sqlce 2.0 to 3.0. The new application runs out memory(storage) when handling lots of data transactions (both in case of using sqlcedatareader, and dataset ). There is no memory leak issue here, sqlce 3.0 simply uses a lot more (3 times more) memory than sqlce 2.0.
Our applicaton runs fine using sqlce 2.0 and .net 2003, but fail due to memory shortage
with the upgrades(which has the same code). Anyone can shed some lights on this issue?
View 3 Replies
View Related
May 2, 2008
I've got a db table, that I created in Sever Studio Management and I can se all the data and everything the
problem is that when I load it to the movile device (windows mobile 5.0) it says that the table is NotAble
but in the management works fine, I can't even do a "select * from table", and if you check the query analyzer
you se the table but it has no columns.
View 2 Replies
View Related
Dec 8, 2007
I would like to perform a large query operation (which takes many seconds), and store that into a designated table which will act as basically a cache for that query. Is there a way to achieve this just using SQL, as opposed to using a data reader, then inserting that back into the designated table with a sqlceresultset.
View 3 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
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
Apr 18, 2007
Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
View 2 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
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
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
Jun 9, 2006
Does anyone have any instructions on how to go about uprading a .sdf file
from sqlce 2.0 to sqlce 3.0?
I found this on an msdn website
(link: http://www.microsoft.com/sql/editions/sqlmobile/upgrading.mspx) but
as you can see its very lacking on detail. (I included the link and content
referring to upgrading the database files so you won't have to go to the link
to see the part referring to upgrading the database.) I tried to run the upgrade.exe on my device but it doesn't explain how you're supposed to point it to the .sdf that you are trying to update. Here is the content on the above web page.
Upgrading Database Files
You can upgrade a database created with an earlier version of SQL Server CE
2.0 to a database created with SQL Server Mobile by using the SQL Server
Mobile Database Upgrade tool (upgrade.exe). The Database Upgrade tool runs on
a smart device.
When you run the SQL Server Mobile Database Upgrade tool, the new SQL Server
Mobile database is created on the smart device. The new database, with the
file name extension .sdf, contains all the data that was in the source
database. You need to reinitialize the upgraded database to continue using it
for replication.
Note: If you are using replication or remote data access (RDA) as a
connectivity solution before upgrading to SQL Server Mobile, you must
synchronize the source SQL Server CE database (subscription database) with
the SQL Server database (Publisher). The reason for synchronizing before
upgrading to SQL Server Mobile is to ensure that any changes that exist in
the tables on the SQL Server CE database are updated on the SQL Server
database, because after the upgrade you must resubscribe or repull using the
new database.
View 3 Replies
View Related
Oct 11, 2006
I presently run on my server SQL SERVER CE 2.0 and want to install, in addition on SQL Server 2.0, SQL Server Mobile 2005.
Is there a procedure to have both installed and running on a computer?
Thanks!
Marco
View 2 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 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
Jun 20, 2006
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
View 2 Replies
View Related
Jun 11, 2006
Hi,I got another question here:I want to use query like this:SELECT * FROM (sp_lock AS T) WHERE objectID = ...The purpose is that I want to query the result set returned by sp_lockusing derived table, but it doesn't work. Why?Thanks,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
View 2 Replies
View Related
Jun 5, 2008
Hi to all,
I'm writting code that needs to pass data returned by a SqlDataSource object to another object (user control to graph data)
Query in SqlDataSource object is OK, I've seen data returned by it at a GridView Object but I need to pass that data to an user control that needs a reference to the table that has the data
... What would be the code that do that task ??
I'm new to develop web applications... using Visual Web Developer Express 2008
Help in advance
View 1 Replies
View Related
Apr 12, 2006
i am writing a sproc that calls another sproc. this 2nd sproc returns 3 or 4 rows. i want to be able to insert these rows into a table. this sproc is inside a cursor, as i have to call it many times.
how do i insert the rows that it returns into another table??
View 10 Replies
View Related