[RESOLVED] update databse sql every records
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
VB Code:
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
Re: update databse sql every records
I don't why that happens, but why don't use a SQL update statement, something like:
VB Code:
objconnection.Execute "update table1 set Field1 = field1-1 where field2 = " & val(me.text1)
Re: update databse sql every records
Quote:
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
Re: update databse sql every records
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.
Re: update databse sql every records
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
Re: update databse sql every records
Quote:
Originally Posted by 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
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 :)
Re: update databse sql every records
Re: update databse sql every records
How Do I make the above code working in SQL database?
the code will run on ACCESS database smootly.
Thank If you have idea
Re: update databse sql every records
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...
Re: update databse sql every records
Re: update databse sql every records
Quote:
Originally Posted by ksuwanto8ksd
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.
Code:
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:
Code:
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.
Re: update databse sql every records
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