Link to home
Start Free TrialLog in
Avatar of Bob Schneider
Bob SchneiderFlag for United States of America

asked on

Quicker SQL Server Data Modify VB6

I want to set several values in a database on our server to "0".  Is there any way quicker than this?

        Set rs = New ADODB.Recordset
        sql = "SELECT Place, FnlScnds, PlaceOnTeam, TeamPlace, TeamPts, ElpsdTime, RaceTime FROM IndRslts WHERE RaceSrvrID = " & lThisRace         rs.Open sql, srvr_conn, 1, 2         Do While Not rs.EOF             rs(0).Value = 0             rs(1).Value = 0             rs(2).Value = 0             rs(3).Value = 0             rs(4).Value = 0             rs(5).Value = "00:00"             rs(6).Value = "00:00"             rs.Update             rs.MoveNext         Loop         rs.Close         Set rs = Nothing

Open in new window

I thought about deleting the records and reinserting them?  Is that faster?


Thank you!

ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Schneider

ASKER

I have not used stored procedures...How does that look in my vb code and in my db?
Or something like this:

Sql = "UPDATE IndRslts SET Place = 0, FnlScnds = 0, PlaceOnTeam = 0, TeamPlace = 0, TeamPts = 0,  ElpsdTime = 0, RaceTime = 0 WHERE RaceSrvrID = ?;"

Set cmd = New ADODB.Command
cmd.ActiveConnection = yourConnection
cmd.CommandText = Sql
cmd.CommandType = adCmdText
Set parameter = cmd.CreateParameter("RaceSrvrID", adNumeric, adParamInput)
parameter.Value = lThisRace
cmd.Parameters.Append parameter
cmd.Execute

Open in new window

Thank you!  I got a "The precision is invalid" error on the cmd.Execute
        sql = "UPDATE IndRslts SET Place = 0, FnlScnds = 0, PlaceOnTeam = 0, TeamPlace = 0, TeamPts = 0,  ElpsdTime = '00:00', RaceTime = '00:00' "
        sql = sql & "WHERE RaceSrvrID = " & lThisRace & ";"
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = srvr_conn
        cmd.CommandText = sql
        cmd.CommandType = adCmdText
        Set Parameter = cmd.CreateParameter("RaceSrvrID", adNumeric, adParamInput)
        Parameter.Value = lThisRace
        cmd.Parameters.Append Parameter
        cmd.Execute

Open in new window

Check the doc or Intellisense, it must be something like adLong instead of adNumeric.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I agree with Jim:  Stored procedure.

There are many reasons to do this.  Security and control are at the top.

There are millions of examples on the Internet on creating them.  They may seem overwhelming at first but they aren't that bad once you understand the syntax:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15 

>> sql = "SELECT Place, FnlScnds, PlaceOnTeam, TeamPlace, TeamPts, ElpsdTime, RaceTime FROM IndRslts WHERE RaceSrvrID = " & lThisRace
 
NEVER use string concatenation for anything accessing the database.  It opens you up to SQL Injection.

ste5an fixed the code snippet above by using bind variables.  If you cannot do stored procedures, then bind variables are MANTADTORY!

Why is this important?

What if lThisRace contained the string "1 or 1=1"?  What would get updated?

Before you state:  It is a value from a dropdown or validated by the app or ???   There are almost always hacks/hols/??? in the front end that can be bypassed....
I thought stored procedures were in the database?  Is that where this goes?  If so, how do I access it from my VB6 program...and where do I put it in my database?  Sorry, this is pretty new to me.
Yes, stored procedures are created in the database.  Typically created in the same schema as the table owner.

>>If so, how do I access it from my VB6 program

I haven't done VB6 for too many years, so I'll defer to the Internet:
https://bytes.com/topic/visual-basic/answers/548319-how-call-sql-server-stored-procedures-visual-basic


Here's a very simple example:
create table bob(racesrvid int,  place int);

insert into bob values
(1,1),
(2,2);


create procedure zero_race @p_racesrvid int
as
begin
	update bob
	set place = 0
	where racesrvid = @p_racesrvid
end;

Open in new window


Working fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8ce2423d4e2ca83cda95c6c1738daa28
So as I wade through this, it looks like ste5an gave me the code to call the stored procedure but now I have to write it in sql server.  That is where I am stuck right now.

I genuinely appreciate all everyone has done to help me on this so far.
>> it looks like ste5an gave me the code to call the stored procedure

I see code to execute an update statement.  Google shows and my old memory recalls calling a stored procedure is a little different:
http://www.nigelrivett.net/VB/VBExecSPSimple.html

Mainly:
    cmd.CommandType =  adCmdStoredProc
instead of:
cmd.CommandType = adCmdText