[RESOLVED] update databse sql every records

[ksuwanto8ksd]

Hi
This is the code that I write to update database sql. When user type a
number in text1.text press enter, he will open TABEL1 and check record
from first to last to chech field(0), if exist text2 get value field(1)
and value in field(1) is deduted by 1 and update to the TABEL1

The fact here when runtime, every record in the TABEL1 are deducted when
number exist;text1.text=field(0)

When I use change the connectionstring and connect to Acces tabel, it
run smoothly;only 1 record is updated.

If any one could give an idea of how it would work be greatly appreciated
and thanks a lot

Private Sub Text1_KeyPress(KeyAscii As Integer)
 
If KeyAscii = 13 Then
    Label3.Visible = False
    On Error GoTo errhandler 'on error resume next
    Set objConnection = New ADODB.Connection
    objConnection.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=adodata;Data Source=pc14;"
    objConnection.CursorLocation = adUseClient
    objConnection.Open
    Set objRS = New ADODB.Recordset
 
objRS.Open "SELECT * FROM TABLE1", objConnection, adOpenDynamic, adLockOptimistic
    
    objRS.MoveFirst
    Do While Not objRS.EOF
        If Text1.Text = objRS.Fields(0) Then
            
            Text2.Text = objRS.Fields(1)
            If Text2.Text <= 1 Then
                Text1.Text = ""
                Text2.Text = ""
                objConnection.Close
                Label3.Caption = "Sorry! Code expired"
                Label3.Visible = True
                Exit Sub
            Else
                lngSetTop = SetTopMost(hWnd, False)
                Timer1.Enabled = True
                objRS.Fields(1) = objRS.Fields(1) - 1
                objRS.Update
                DisableCtrlAltDelete (True)
                Form1.Show
                AdoDao.Visible = False
                Exit Do
            End If
        
        End If
        objRS.MoveNext
        Loop
      
End If
errhandler:
If Err.Number = 2147467259 Then
    objConnection.Close
    AdoDao.Show
        Label3.Caption = "Server fail"
        Label3.Visible = True
  Exit Sub
End If
End Sub

[anguswalker]

I don't why that happens, but why don't use a SQL update statement, something like:

objconnection.Execute "update table1 set Field1 =  field1-1 where field2 = " & val(me.text1)

[ksuwanto8ksd]

objconnection.Execute "update table1 set Field1 = field1-1 where field2 = " & val(me.text1)

this code should be "update FROM table1 "
but it is not doing anything to the records, anyway thanks for response

[szlamany]

In MS SQL Server...

UPDATE TABLE SET COLUMN=NEWVALUE WHERE CONDITION=SOMETHING

...is valid

Your WHERE clause must not be finding anything.

And the suggestion to use an ACTION query .EXECUTE'd against the DB is a good one. Using the RS to UPDATE the table is not required.

[ksuwanto8ksd]

The update is working but the every records here is around the do...loop and movenext instability. Any comments are welcome.

thanks for you all

[Pradeep1210]

The update is working but the every records here is around the do...loop and movenext instability. Any comments are welcome.

thanks for you all

As I also said in ur last post, after a Connection.Execute, always Requery the recordsets that were connected to the tables affected by that Execute statement. This will ensure that ur recordsets now have the correct data.

Pradeep

[ksuwanto8ksd]

hhaa

[ksuwanto8ksd]

How Do I make the above code working in SQL database?
the code will run on ACCESS database smootly.
Thank If you have idea

[szlamany]

I think there is a general consensus here on the forum that using a DB like SQL server means abandoning old concepts of iterating through records and processing - not using BOUND controls...

SQL Server is much different than ACCESS - it's a true client/server database. We do all our business processing in STORED PROCEDURES, written in T-SQL, on the SERVER - not in the client.

As much as is possible we do it using "set-based logic" - creating temp tables or table variables - using those to perform mass UPDATE's to rows. Occasionaly we fall back on using a CURSOR in the SPROC - but that is always avoided if possible.

In three years of developing major applications with VB and MS SQL, our VB front-end has remained very static, and it calls over 1000 STORED PROCEDURES to perform the data operations.

When we get a RECORDSET back from a SPROC we load it into a VB control (such as a flexgrid) and then lose the RECORDSET. Any updates back to the table are done one row at a time - using an UPDATE SPROC.

That's our philosophy anyway...

[ksuwanto8ksd]

What is a UPDATE SPROC?

[szlamany]

What is a UPDATE SPROC?

Here is an example of one. It's a STORED PROCEDURE - becomes part of the DATABASE itself. Executing this SCRIPT in QUERY ANALYZER loads the SPROC into the database. It's called using ADO from the VB side - passing arguments and getting returned values.

Create Procedure frmTeaAttend_ClsAttendanceClosed_T_Post 
@Retstat int OUTPUT, @Rettext varchar(50) OUTPUT, @StuID int, @Yr int,  
@Bldg tinyint, @Crs char(4), @Sectn tinyint, @TimeVec tinyint, 
@ClsAttDate datetime, @ClsAttValue money, @ClsAttClosed varchar(3), @ResetLetter char(1)
as

DECLARE @Rollback int
DECLARE @Count int
DECLARE @LtrStat char(1)


BEGIN TRAN

Set @LtrStat = 'N'

If @ClsAttClosed = 'C'
Begin
	Set @LtrStat = 'V'
End

If @ResetLetter = '1'
Begin
	Set @LtrStat = 'N'
End

UPDATE CLSATTENDANCE_T SET CLSATTCLOSED = @ClsAttClosed, CLSATTVALUE = @ClsAttValue, TDate = GetDate()
	WHERE (STUID = @StuId AND YR = @Yr AND BLDG = @Bldg AND CRS = @Crs AND 
	SECTN = @Sectn AND TIMEVEC = @TimeVec AND CLSATTDATE = @ClsAttDate)

SELECT @Rollback = @@error

If @RollBack = 0
BEGIN

	SET @Count = (SELECT COUNT(*) FROM Letter_T 
		WHERE Stuid = @Stuid AND Yr = @Yr AND Bldg = @Bldg
		AND Crs = @Crs AND Sectn = @Sectn AND TimeVec = @TimeVec
		AND LtrDate = @ClsAttDate AND LtrType = '01')
		
	If @Count < 1 AND @ClsAttClosed = 'C'
	Begin
		INSERT INTO Letter_T Values (@StuId, @Yr, @Bldg, @Crs, @Sectn, @TimeVec, @ClsAttDate, '01', 'V', GetDate(), '', GetDate())
		SELECT @Rollback = @@error
	End

	IF @Count > 0
	BEGIN
		UPDATE Letter_T Set LtrType = '01', LtrStatDate = GetDate(), LtrStat = @LtrStat
			WHERE Stuid = @Stuid AND Yr = @Yr AND Bldg = @Bldg
			AND Crs = @Crs AND Sectn = @Sectn AND TimeVec = @TimeVec
			AND LtrDate = @ClsAttDate AND LtrType = '01'
		SELECT @Rollback = @@error
	END

END

IF @Rollback <> 0
BEGIN
	ROLLBACK TRAN
	SELECT @Retstat = -1
	SELECT @Rettext = 'Failure'
END
ELSE
BEGIN
	COMMIT TRAN
	SELECT @Retstat = 0
	SELECT @Rettext = 'Success'
END

SELECT @Retstat, @Rettext

GO

Here's another example of one:

Create Procedure frmTeaAttend_ClsPosted_T_Post 
@Retstat int OUTPUT, @Rettext varchar(50) OUTPUT, @Yr int,  
@Bldg tinyint, @Crs char(4), @Sectn tinyint, @TimeVec tinyint, 
@ClsPostDate datetime, @ClsPostStat char(1), @TDate DateTime
as

DECLARE @Rollback int
DECLARE @Count int

BEGIN TRAN

SELECT @Count = (SELECT COUNT(*) From ClsPosted_T WHERE YR = @Yr AND BLDG = @Bldg AND CRS = @Crs AND SECTN = @Sectn
        AND TIMEVEC = @TimeVec AND ClsPostDate = @ClsPostDate)

IF @Count < 1
BEGIN
	INSERT INTO ClsPosted_T VALUES (@Yr, @Bldg, @Crs, @Sectn, @TimeVec,
		@ClsPostDate, @ClsPostStat, @TDAte)

	SELECT @RollBack = @@error
END
IF @Count > 0
BEGIN

	UPDATE ClsPosted_T SET ClsPostStat = @ClsPostStat, TDate = @TDate  
	WHERE YR = @Yr AND BLDG = @Bldg AND CRS = @Crs AND SECTN = @Sectn
        AND TIMEVEC = @TimeVec AND ClsPostDate = @ClsPostDate
	SELECT @Rollback = @@error

END

IF @Rollback <> 0
BEGIN
	ROLLBACK TRAN
	SELECT @Retstat = -1
	SELECT @Rettext = 'Failure'
END
ELSE
BEGIN
	COMMIT TRAN
	SELECT @Retstat = 0
	SELECT @Rettext = 'Success'
END

SELECT @Retstat, @Rettext

These are very old - from our early days - I certainly don't like some of what we are doing in these SPROCS - but they will server as examples.

[ksuwanto8ksd]

I'm newbie, I'm now begin with the "update add edit insert delete" records
But your code maybe usefull someday, I will save it for future refference.
thanks and is appreciated