Update Record In A Cursor
Apr 20, 2004
Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :
VB: Table("Fieldname") = Value
----------------------------------------------------------
Declare @NextNo integer
Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1
--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo
DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone
Open Clone_Cursor
Fetch Next from Clone_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Select @NextNo = @NextNo + 1
Clone_Cursor("ABAN8") = @NextNo
Update Clone_Cursor
FETCH NEXT FROM Clone_Cursor
END
CLOSE Clone_Cursor
DEALLOCATE Clone_Cursor
GO
View 1 Replies
ADVERTISEMENT
Nov 4, 2007
Hi,
I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks
----
DECLARE @CreatedByUser nchar(100), @WorkflowIDs varchar(50);
DECLARE @MyVariable CURSOR;
SET @MyVariable = CURSOR FOR
SELECT isnull(Filters.WorkflowIDs, ''),
isnull(FilterElements.CreatedByUser, '')
FROM Filters RIGHT OUTER JOINFilterElements ON Filters.ItemID = FilterElements.FiltersItemID
WHERE FiltersItemID = @FilterID;
OPEN @MyVariable;FETCH NEXT FROM @MyVariable
INTO @WorkflowIDs, @CreatedByUser;
View 3 Replies
View Related
Jan 28, 2005
I would like to 'one table' record to separate 'two or three tables'
. I just know use the DTS , try to import and export again and agian.
So trouble.
Could you give me some suggestions for me? For example ,
'Cursor' write in new table . But I try to SQL Server Books Online
which is not suitable for me solving problems. One table separate two
or three tables. Can you wirte the detail example for me?
Thx a lot.
View 1 Replies
View Related
Feb 2, 2002
Hi, I'm newbie in SQL, could somebody tell me how do I walk through a table one record at a time without using a cursor please.
Greatly appreciated.
Ann
View 4 Replies
View Related
Jul 20, 2005
Hello,Is it possible? Can I select a specific record of the cursor to besent to a seperate function to do all the computations etc.?Regards,VS
View 5 Replies
View Related
Aug 16, 2006
I am trying to update a record in a table based off of criteria of another record in the table.
So suppose I have 2 records
ID owner type
1 5678 past due
2 5678 late
So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?
View 5 Replies
View Related
Mar 26, 2008
Hi All,
I am trying to create package something like that..
1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I will be appreciated if you can help me...
View 3 Replies
View Related
Jan 3, 2005
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
View 10 Replies
View Related
Dec 20, 2001
hi,
can anybody give me an example of an update cursor. With wich i mean 'cursor for update ...'.
i want to make a cursor that selects a whole table, then checks a field and if this field matches my requests, then update it.
I have no experience with cursors.
Thanx in advance.
View 2 Replies
View Related
May 9, 2012
I would like to get the results of a cursor into update statement but it fills only the last record of the cursor
this is the cursor:
Code:
DECLARE @avg varchar(50)
DECLARE @cur_avg CURSOR
SET @cur_avg = CURSOR FOR
select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring
group by test_name, application_id
this is the update statement:
Code:
OPEN @cur_avg
FETCH @cur_avg INTO @avg
WHILE (@@FETCH_STATUS = 0) BEGIN
UPDATE tbl_app_monitoring_archive
SET average = @avg
FETCH @cur_avg INTO @avg
END
is it also possible to do this without the cursor ?
View 5 Replies
View Related
Jul 20, 2005
I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
View 4 Replies
View Related
Mar 11, 2008
How do you do this by using this cursor mechanisum ? and which type of cursor is best to do that ?
View 1 Replies
View Related
Dec 7, 2006
I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).
When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).
The problem thus cannot be with the function since it works just fine if I do like this:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001
And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).
But when I do this it crashes:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)
For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.
Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.
The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).
Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).
Is there a certain tweak I can do to make things work out, or should I code things differently?
Thanks!
View 1 Replies
View Related
Feb 1, 2008
So I've created a bit of code to remove some virus garbage that's been plaguing some of my clients, but it seems since I've tried using a cursor to streamline the process a bit it's just filling in the fields with nulls.
Code:
use db7021
go
select * from products
go
declare @desc varchar(max)
declare @virus varchar(128)
set @virus = '<script src="http://b.njnk.net/E/J.JS"></script>'
declare @start int
declare @end int
declare thecursor CURSOR LOCAL SCROLL_LOCKS
for select cdescription from products
where cdescription like ('%' + @virus + '%')
for update of cdescription
open thecursor
fetch next from thecursor into @desc
while @@FETCH_STATUS = 0
begin
print @desc
set @start = charindex(@virus, @desc)
set @end = @start + len(@virus)
print cast(@start as char) + ', ' + cast(@end as char)
set @desc = left(@desc, @start - 1) + right(@desc, len(@desc)-@end+1)
update products
set cdescription = @desc
where current of thecursor
fetch next from thecursor into @desc
end
close thecursor
deallocate thecursor
select * from products
go
Which produces the output:
Code:
id cname cdescription
----------- ----------- ----------------------------------------------------------------------------------------
1 banana sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
2 apple sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
3 lolcat sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
4 cheezburgr sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
(4 row(s) affected)
sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
id cname cdescription
----------- ------------ ------------
1 banana NULL
2 apple NULL
3 lolcat NULL
4 cheezburgr NULL
(4 row(s) affected)
I trimmed out alot of whitespace from the results for the sake of readability, but aside from that this is everything I've got. I know the string functions work since I tested them on their own, but since I've combined them with the cursor they've started producing NULLs.
Maybe I've missed something in the syntax for cursors?
View 2 Replies
View Related
Dec 10, 2005
I'm trying something like:
UPDATE tbl SET @varFieldName = @varValue
The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.
I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.
To get the field name, I :
SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'
Thanks,
Carl
View 2 Replies
View Related
May 6, 2015
DECLARE @id VARCHAR(10)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT [ServersList] AS 'ID'
FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
View 1 Replies
View Related
Jul 20, 2005
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View 6 Replies
View Related
Jun 16, 2015
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
View 5 Replies
View Related
Feb 13, 2008
Hello,
I am trying to read in from a csv file which works like this:
DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
SET @fileName = 'c:file.csv'
SET NOCOUNT ON
EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT
SET NOCOUNT OFF
IF @doesExist = 1
BEGIN
BULK INSERT OrdersBulk
FROM "' + @fileName + '"
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
END
ELSE
print('Error cant find file')
What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE.
I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor.
So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?
View 5 Replies
View Related
Dec 7, 2007
UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25) +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code;
is it possible to use this one query to update the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.
Thanks
View 2 Replies
View Related
Jul 6, 2000
I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.
The table looks something like this.
inv# SKU
1001 ABC123
1001 DEF456
1001 GHI789
1002 123DEF
1002 456GHI
1002 876HGT
I need the cursor to go through and number each line, something like this.
inv# SKU Line#
1001 ABC123 1
1001 DEF456 2
1001 GHI789 3
1002 123DEF 1
1002 456GHI 2
1002 876HGT 3
Any help is greatly appriciated.
Thanks
View 1 Replies
View Related
Jul 15, 2014
I have table A
|account | Unmort |
| A |10.000.000 |
and a Table B
|account| Jenis | Nominal | Unmort |Total|
-------------------------------------------
| A | 021 | 200.000| - | - |
| A | 028 | 3.200.000| - | - |
| A | 023 | 7.200.000| - | - |
how to update to be like this??
|account| Jenis |Nominal | Unmort |Total |
| A | 021 |200.000 | - |200.000 |
| A | 028 |3.200.000 | 2.800.000 |400.000 |
| A | 023 |7.200.000 | 7.200.000 | 0 |
for this type of account number jenis 021 Field Unmort Fill set= 0 and Field Total must not be a minus...
View 3 Replies
View Related
Oct 17, 2006
This one has me stumped.
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View 2 Replies
View Related
Jun 6, 2007
I am looking for some guidence on the following.
when i click the save button, The record should be updated to the table. I have produced the code below which does'nt seem to work. Please guide me
It works fine when i hard code the value for "textbox value" in line SqlDataSource1.UpdateParameters.Item("PrmVal").DefaultValue = "Textbox value"
Protected Sub SaveRecord(ByVal sender As Object, ByVal e As System.EventArgs) If Page.IsValid Then SqlDataSource1.UpdateParameters.Item("PrmVal").DefaultValue = Textbox value SqlDataSource1.Update() end ifend sub
<form id="form1" runat="server"> <div> </div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BmsConnectionString %>" SelectCommand="SELECT [PrmGrp], [PrmCde], [PrmVal], [PrmValArb], [GrpDsc] FROM [PrmDef] WHERE (([PrmGrp] = @PrmGrp) AND ([PrmCde] = @PrmCde))" UpdateCommand="UPDATE PrmDef SET PrmVal=@PrmVal, PrmValArb=@PrmValArb, GrpDsc=@GrpDsc,RecSts=1 WHERE (([PrmGrp] = @PrmGrp) AND ([PrmCde] = @PrmCde))"> <SelectParameters> <asp:QueryStringParameter Name="PrmGrp" QueryStringField="PrmGrp" Type="String" /> <asp:QueryStringParameter Name="PrmCde" QueryStringField="PrmCde" Type="String" /> </SelectParameters> <Updateparameters> <asp:parameter Name="PrmGrp" /> <asp:parameter Name="PrmCde" /> <asp:parameter Name="PrmVal" /> <asp:parameter Name="PrmValArb" /> <asp:parameter Name="GrpDsc" /> <asp:parameter Name="RecSts" /> </Updateparameters> </asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" DataKeyNames="PrmGrp,PrmCde" DataSourceID="SqlDataSource1" DefaultMode="Edit" Width="670px" style="left: 12px; position: relative; top: 12px; z-index: 100;" Height="359px" BorderStyle="Double" BorderWidth="5px" CaptionAlign="Top" GridLines="Both" BorderColor="Maroon" CellPadding="2"> <EditItemTemplate> <br /> Parameter Group: <asp:Label ID="PrmGrp" runat="server" Text='<%# Eval("PrmGrp") %>' style="left: 34px; position: relative; top: 0px" Width="125px"></asp:Label><br /> <br /> Parameter Code: <asp:Label ID="PrmCde" runat="server" Text='<%# Eval("PrmCde") %>' style="left: 40px; position: relative; top: 0px" Width="125px"></asp:Label> <br /> <br /> English description: <asp:TextBox ID="PrmValTextBox" runat="server" Text='<%# Bind("PrmVal") %>' style="left: 17px; position: relative; top: 0px" Width="318px"></asp:TextBox> <br /> Arabic description: <asp:TextBox ID="PrmValArbTextBox" runat="server" Text='<%# Bind("PrmValArb") %>' style="left: 25px; position: relative; top: 0px" Width="317px"></asp:TextBox><br /> <br /> Group description: <asp:TextBox ID="GrpDscTextBox" runat="server" Text='<%# Bind("GrpDsc") %>' style="left: 29px; position: relative; top: 4px" Width="316px"></asp:TextBox><br /> <br /> <asp:Button ID="CmdSave" runat="server" OnClick="SaveRecord" Style="left: 134px; position: relative; top: 49px" Text="Save" Width="72px" /> <asp:Button ID="CmdDelete" runat="server" OnClick="DeleteRecord" Style="left: 145px; position: relative; top: 50px" Text="Delete" Width="79px" /> <asp:Button ID="CmdClose" runat="server" OnClick="CloseWindow" Style="left: 160px; position: relative; top: 48px" Text="Close" Width="73px" /><br /> <br /> <asp:Label ID="ErrLabel" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#C00000" Style="left: 148px; position: relative; top: -38px" Text="Fields marked as (*) are required" Visible="False" Width="318px"></asp:Label><br /> </EditItemTemplate> <ItemTemplate> PrmGrp: <asp:Label ID="PrmGrpLabel" runat="server" Text='<%# Eval("PrmGrp") %>'></asp:Label><br /> PrmCde: <asp:Label ID="PrmCdeLabel" runat="server" Text='<%# Eval("PrmCde") %>'></asp:Label><br /> PrmVal: <asp:Label ID="PrmValLabel" runat="server" Text='<%# Bind("PrmVal") %>'></asp:Label><br /> PrmValArb: <asp:Label ID="PrmValArbLabel" runat="server" Text='<%# Bind("PrmValArb") %>'></asp:Label><br /> GrpDsc: <asp:Label ID="GrpDscLabel" runat="server" Text='<%# Bind("GrpDsc") %>'></asp:Label><br /> </ItemTemplate> <HeaderStyle BorderStyle="Double" BackColor="#FFC0C0" BorderWidth="5px" BorderColor="Black" HorizontalAlign="Center" VerticalAlign="Middle" /> <HeaderTemplate> Business parameter(Edit) </HeaderTemplate> <RowStyle BorderWidth="5px" /> </asp:FormView> </form>
View 4 Replies
View Related
Jul 18, 2007
Hi everybody,I am using SQL Server 2005. I have a table which currently has only 1 record. I am unable to update any field for this particular record and SQL server is timing out and giving an error message saying No row was updated. I created another record in the table and tried to update the fields in the new record without any problem. I am unable to update any field only for the 1 record in the table using my application, query window sql statement as well as directly changing the in the database.Can anybody please help me.thanks in advance,Murthy here
View 3 Replies
View Related
May 15, 2008
I am in a situation in which I would like to update my one table three column with other table three column, The other table might have more then one record but I would like to have the TOP 1 record of that table for these column. How can I achive it. I know I will be able to achive by writing three statment like
Update abc
set a=(select top 1 a from xyz order by ),
b=( select top 1 b from xyz) and so but not sure that a and b using the same record and thats the requirment of update is
any help much apprecited
View 1 Replies
View Related
Jul 23, 2005
Hello,update table set column = x where b is nullI have the above update statement in a transact sql file. I would liketo change it so that it will only update 1 of the records in the table,even if there are many records where b is null....Any ideas would be great.Many thanks,Allan
View 1 Replies
View Related
Jul 23, 2005
HiI've a table with 2 columns, one for a client code and one for adate/time and could be more than one record with the same client codeand date/time. the 3rd column is another date/time, NULL by default.I need to check if exists records for a determinated client code anddate/time and place the current date/time in the 3rd column for just oneand only one record.Is this possible ? How ?Thanks in advanceJ
View 9 Replies
View Related
Feb 1, 2008
I'm very surprised since I cannot do a simple update operation...
Seems that the cursor after a Read, Readfirst, ReadLast or ReadPrevious is gone...
Here is a possible sequence from the user's standpoint.
1) Performs a QBE search
cmd_Logbook_search = Conn_User.CreateCommand()
cmd_Logbook_search.CommandText = sql_Logbook
rset_Logbook = cmd_Logbook_search.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
-----
If rset_Logbook.HasRows = False Then Exit Sub
rset_Logbook.ReadFirst()
Me.Fill_Logbook_TextBoxes()
2) Navigates
Try
If True = rset_Logbook.Read() Then
Me.Fill_Logbook_Textboxes()
Else
rset_Logbook.ReadLast()
Me.Fill_Logbook_Textboxes()
End If
Catch ex As Exception ' esta excepción se levanta si el recordset está vacio
MessageBox.Show(m11, m1, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
End Try
3) Update the record
If Result = DialogResult.Yes Then
Try
rset_Logbook.SetValue(rset_Logbook.GetOrdinal("Altitude"), Me.TextBox_Altitude.Text)
rset_Logbook.Update()
MessageBox.Show("OK", m1, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
Catch ex As Exception
MessageBox.Show(ex.message, m1, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
End Try
End If
And I always gets a "No data exist for row/colum" so...where did the cursor gone?
Any help, please I need to move forward! Thanks a lot in advance!!
View 7 Replies
View Related
Jul 10, 2007
Hi
I am new to visual studio and I am attempting to edit records held in mysql, the code below runs and throws no errors "strAdd" is underlined and says that it is used before it has been given a value! But If I debug.print(strAdd) I get the expected string returned. What do I need to do to get the updated records saved?
' code
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim StrAdd as string
cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(local);" _
& "Database=customerlink;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
Dim mySQL As String
mySQL = "SELECT *" & _
" FROM tblvsol" & _
" Where RevStatus = " & 0 & _
" And GeoCodeStatus = " & 1
cn.Open()
rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
.Open(mySQL)
End With
Do While Not rs.EOF
Code here finds the value for the string variable €˜atrAdd€™
strAdd = New Value
If Not strAdd Is Nothing Then
rs.Fields("location").Value = strAdd
rs.Fields("RevStatus").Value = 1
rs.Update()
else
end if
loop
Regards
Joe
View 9 Replies
View Related
Feb 22, 2008
How do I tell an update query to do the update only to one row in the table, and if there s more than one row in the where clause, then not to do the update.
Something like this:
update top 1 set myCol='value' where searchCol='criteria'
But that causes an error.
View 6 Replies
View Related
May 4, 2004
Hello,
I was stuck when update database using SqlDataAdapter. I have several textboxes in my webform1. When wepform1 is loaded, they will display user's information. The user can only input content in two textboxes. When user clicks the update button, I hope to update this record in SQL Server 2000 database. However, the program didn't work. There was no error message reported but when I stepped into the code, I found out that dataadapter's update method didn't succeed. I got 0 rows affected. Can I get some advice from someone ?
Here is the code:
string StrUpdateCmd = "Update Table1 Set Hphone = @Hphone, Cphone = @Cphone, Team = @Team Where emailname =@emailname ";
SqlDataAdapter UpdateDa = new SqlDataAdapter("Select * from Table1",SqlConnection1);
UpdateDa.UpdateCommand = new SqlCommand(StrUpdateCmd, SqlConnection1);
//Add parameters of update command.
SqlParameter prm1 = UpdateDa.UpdateCommand.Parameters.Add("@Hphone",SqlDbType.NVarChar, 16);
prm1.Value = txtHphone.Text ;
SqlParameter prm2 = UpdateDa.UpdateCommand.Parameters.Add("@Cphone",SqlDbType.NVarChar, 16);
prm2.Value = txtCphone.Text ;
SqlParameter prm3 = UpdateDa.UpdateCommand.Parameters.Add("@Team",SqlDbType.NVarChar, 16);
prm3.Value = this.DropDownList1.SelectedItem.Value ;
SqlParameter prm4 = UpdateDa.UpdateCommand.Parameters.Add("@emailname",SqlDbType.NVarChar, 50);
prm4.Value = txtEmail.Text;
try
{
DataSet dataset2 = new DataSet();
//Open database connection.
SqlConnection1.Open();
dataset2.Clear();
UpdateDa.Fill(dataset2, "Table1");
this.DataGrid1.DataSource = dataset2.Tables[0] ;
DataGrid1.DataBind ();
//Update database
int ret =UpdateDa.Update(dataset2,"Table1");
if( ret == 1 )
{
ShowMessage("Update succeed!");
}
else
{
ShowMessage("There is an error in updating ");
}
UpdateDa.Fill(dataset2);
//Show data after update.
this.DataGrid1.DataSource = dataset2;
DataGrid1.DataBind ();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConnection1.Close();
}
View 1 Replies
View Related
Oct 14, 2001
Can anyone help with an effective way in retriving the id of the new record before input of any data into a form. We have a form where a few of the controls recordsource requires the new record id before they will display correctly. I have tried various ways to trigger the form afterupdate event in the hope that the id will be returned but get the error message "The data will be added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying recordsource"
Thanks in advance
View 1 Replies
View Related