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

Can't Fetch Record From Cursor

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

'One Table' Record To Separate 'two Or Three Tables' Using Cursor?

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

How Do I Walk Through A Table One Record At A Time Without Using A Cursor

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

Passing A Specific Cursor Record To A Function

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

Update A Record Based Of A Record In The Same Table

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

Lookup && Update Record && Insert Record

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

Trigger To Update One Record On Update Of All The Tables Of Database

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

Update Cursor

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

Using Cursor For Update Statement?

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

UPDATE CURSOR - Declaring And Use.

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

How Do U Update A Row In A Table Using Cursor ?

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

Error On Update, But Not When Using Cursor

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

Cursor Update Creating Nulls

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

Variable As Field Name In CURSOR FOR UPDATE

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

T-SQL (SS2K8) :: Cursor Table Update

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

One Statement Update - Join, No Cursor ?

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

Transact SQL :: Update Statement With Cursor

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

Read The Csv In A Cursor Instead Of Bulk Update

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

Is It Possible To Use This One Query To Update The QUAL_SCORE Field Without Using Cursor

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

Cursor Count Loop - Update Table

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

SQL 2012 :: How To Update And Join Table With Cursor

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

SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare

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

Update Record

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>        &nbsp;</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: &nbsp;                <asp:TextBox ID="PrmValTextBox" runat="server" Text='<%# Bind("PrmVal") %>' style="left: 17px; position: relative; top: 0px" Width="318px"></asp:TextBox>&nbsp;                                <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 />                &nbsp;                <asp:Button ID="CmdSave" runat="server" OnClick="SaveRecord" Style="left: 134px; position: relative; top: 49px"                    Text="Save" Width="72px" />&nbsp;                <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>        &nbsp;&nbsp;&nbsp;&nbsp;    </form>

View 4 Replies View Related

Not Able To Update A Record!

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

Update With Top 1 Record

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

Update Only One Record

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

How To Update Just One Record

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

How To Update A Record??

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

Record Set Update

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

How To Update Only One Record

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

Cannot Update Record In Database.

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

How Can I Retrieve The Id Of A New Record Before Update

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







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