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!

SQLMicrosoft SQL ServerVisual Basic Classic

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Bob Schneider

I have not used stored procedures...How does that look in my vb code and in my db?
ste5an

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

ASKER
Bob Schneider

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ste5an

Check the doc or Intellisense, it must be something like adLong instead of adNumeric.
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....
ASKER
Bob Schneider

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

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
ASKER
Bob Schneider

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.
slightwv (䄆 Netminder)

>> 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
Your help has saved me hundreds of hours of internet surfing.
fblack61