VB & Database, Update problem with VB6 and SQL Server 6.5

Update problem with VB6 and SQL Server 6.5

Author	Message
Rene #1 / 4	Update problem with VB6 and SQL Server 6.5 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.
Mon, 09 Feb 2004 22:35:25 GMT

Rene #2 / 4	Update problem with VB6 and SQL Server 6.5 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.
Mon, 09 Feb 2004 22:35:20 GMT

Rene #3 / 4	Update problem with VB6 and SQL Server 6.5 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.
Mon, 09 Feb 2004 22:35:25 GMT

Bill #4 / 4	Update problem with VB6 and SQL Server 6.5 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 Quote: > Hello All. > I’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 “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.
Tue, 10 Feb 2004 01:06:29 GMT

Page 1 of 1

[ 4 post ]