Update problem with VB6 and SQL Server 6.5

Update problem with VB6 and SQL Server 6.5

Post by Rene » Fri, 24 Aug 2001 23:35:25



Hello All.
Im using db-library to connect to an SQL Server 6.5
database from VB6.  I have a Sheridan SSDBGrid that
displays the information from the database and the grid is
unbound.  The first time I do an update there is no
problem, but when I try a second time I get an Object
Required #424.  However, the update is still completed,
but when I try a third time the connection is dead.

The weird thing is that I only get this problem when doing
the update through a stored procedure.   This is basically
the code I use when updating the database with a stored
procedure call:

Dim lResult As Long
Dim mlResult As Long

mlResult = SqlRpcInit(connection, "stored proc name", 0)

SQLCHAR, -1, Len(cboData(0).Text), cboData(0).Text)

lResult = SqlRpcSend(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlOk(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit

Like I said it works perfectly for the first update and
then the SqlOk function returns a 0 the second time.  When
I try running the stored procedure in SQL itself, it works
fine.  Also, if I change the code to the following it
works fine:

Dim sSQl As String
Dim lResult As Long

sSQl = "UPDATE field  SET field  = '" cboData(0).Text & "'
WHERE field = 01"

lResult = SqlCmd(connection, sSQl)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlExec(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlResults(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit

Thanks in advance for your help.

 
 
 

Update problem with VB6 and SQL Server 6.5

Post by Rene » Fri, 24 Aug 2001 23:35:20


Hello All.
Im using db-library to connect to an SQL Server 6.5
database from VB6.  I have a Sheridan SSDBGrid that
displays the information from the database and the grid is
unbound.  The first time I do an update there is no
problem, but when I try a second time I get an Object
Required #424.  However, the update is still completed,
but when I try a third time the connection is dead.

The weird thing is that I only get this problem when doing
the update through a stored procedure.   This is basically
the code I use when updating the database with a stored
procedure call:

Dim lResult As Long
Dim mlResult As Long

mlResult = SqlRpcInit(connection, "stored proc name", 0)

SQLCHAR, -1, Len(cboData(0).Text), cboData(0).Text)

lResult = SqlRpcSend(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlOk(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit

Like I said it works perfectly for the first update and
then the SqlOk function returns a 0 the second time.  When
I try running the stored procedure in SQL itself, it works
fine.  Also, if I change the code to the following it
works fine:

Dim sSQl As String
Dim lResult As Long

sSQl = "UPDATE field  SET field  = '" cboData(0).Text & "'
WHERE field = 01"

lResult = SqlCmd(connection, sSQl)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlExec(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlResults(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit

Thanks in advance for your help.

 
 
 

Update problem with VB6 and SQL Server 6.5

Post by Rene » Fri, 24 Aug 2001 23:35:25


Hello All.
Im using db-library to connect to an SQL Server 6.5
database from VB6.  I have a Sheridan SSDBGrid that
displays the information from the database and the grid is
unbound.  The first time I do an update there is no
problem, but when I try a second time I get an Object
Required #424.  However, the update is still completed,
but when I try a third time the connection is dead.

The weird thing is that I only get this problem when doing
the update through a stored procedure.   This is basically
the code I use when updating the database with a stored
procedure call:

Dim lResult As Long
Dim mlResult As Long

mlResult = SqlRpcInit(connection, "stored proc name", 0)

SQLCHAR, -1, Len(cboData(0).Text), cboData(0).Text)

lResult = SqlRpcSend(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlOk(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit

Like I said it works perfectly for the first update and
then the SqlOk function returns a 0 the second time.  When
I try running the stored procedure in SQL itself, it works
fine.  Also, if I change the code to the following it
works fine:

Dim sSQl As String
Dim lResult As Long

sSQl = "UPDATE field  SET field  = '" cboData(0).Text & "'
WHERE field = 01"

lResult = SqlCmd(connection, sSQl)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlExec(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
lResult = SqlResults(connection)
If lResult <> SUCCEED Then GoTo ProcessSQL_Exit

Thanks in advance for your help.

 
 
 

Update problem with VB6 and SQL Server 6.5

Post by Bill » Sat, 25 Aug 2001 02:06:29


1) I don't know why you sent this three times.
2) I don't know why you're using DB-Lib.
3) I don't know why you're swearing.

None of these are really necessary. There are better ways to do all of
these. Consider that DB-Lib is no longer supported by SS 7 or 2000--while it
still might work, the interface does not support the new datatypes and new
TDS protocol. While I wrote a book on DB-Lib and VB some time ago, I don't
recall enough of it to help...

--
William (Bill) Vaughn
President Beta V Corporation
www.betav.com


> Hello All.
> I&#8217;m using db-library to connect to an SQL Server 6.5
> database from VB6.  I have a Sheridan SSDBGrid that
> displays the information from the database and the grid is
> unbound.  The first time I do an update there is no
> problem, but when I try a second time I get an &#8220;Object
> Required #424&#8221;.  However, the update is still completed,
> but when I try a third time the connection is dead.

> The weird thing is that I only get this problem when doing
> the update through a stored procedure.   This is basically
> the code I use when updating the database with a stored
> procedure call:

> Dim lResult As Long
> Dim mlResult As Long

> mlResult = SqlRpcInit(connection, "stored proc name", 0)

> SQLCHAR, -1, Len(cboData(0).Text), cboData(0).Text)

> lResult = SqlRpcSend(connection)
> If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
> lResult = SqlOk(connection)
> If lResult <> SUCCEED Then GoTo ProcessSQL_Exit

> Like I said it works perfectly for the first update and
> then the SqlOk function returns a 0 the second time.  When
> I try running the stored procedure in SQL itself, it works
> fine.  Also, if I change the code to the following it
> works fine:

> Dim sSQl As String
> Dim lResult As Long

> sSQl = "UPDATE field  SET field  = '" cboData(0).Text & "'
> WHERE field = &#8216;01&#8217;"

> lResult = SqlCmd(connection, sSQl)
> If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
> lResult = SqlExec(connection)
> If lResult <> SUCCEED Then GoTo ProcessSQL_Exit
> lResult = SqlResults(connection)
> If lResult <> SUCCEED Then GoTo ProcessSQL_Exit

> Thanks in advance for your help.

 
 
 

1. VB6/ADO/SQL Server 6.5 problem with DISTINCT?

I a VB 6 app I am writing, I use the following code:
SELECT DISTINCT
  DW_Employees_V.EmployeeName , GEN_SystemEngineers.EmployeeNumber
FROM
  GEN_SystemEngineers INNER JOIN  DW_Employees_V ON
  DW_Employees_V.EmployeeNumber = GEN_SystemEngineers.EmployeeNumber
WHERE
  GEN_SystemEngineers.PlantID = '0780' and
  GEN_SystemEngineers.AssetCode = '0781'

Using the query tool, this works fine, thank you very much (one name,
one empID, sorted by name) (Also works the same in Access  ;-)  )

But, as soon as I include the code in my VB app,:
1) the DISTINCT keyword is ignored, and
2) the results are not sorted by name.

Any ideas why this is happening?  I'm not sure how to verify what
version of ADO (although I'm pretty sure it's 2.x), etc., I have
running on the server.

MANY thanks to all responders.

Sent via Deja.com http://www.deja.com/
Before you buy.

2. SQL Server does not exist or access denied

3. VB6.0 to SQL server 6.5 connectivity problem

4. Setup error in VB6

5. Problem in executing same SQL Update stmt many times in SQL Server 6.5

6. OH-ORACLE APPLICATIONS ANALYST

7. migrating sql databases sever 6.5 from sql server 6.5 to another sql server 6.5

8. Reports...

9. Update problem with VFP 5.0 and SQL server 6.5

10. Problem usind ADO VB6 x SQL 6.5

11. SQL Server 6.5 poor insert/update performance when access by terminal servers

12. Getting VB6 to connect to SQL Server 6.5 via TCP/IP

13. Problem Upsizing SQL Server 6.5 to SQL Server 2000