How to call Function that doesn't always return a value 2

[leishad]

I am using VB6 and MSSQL - Adv.Beginner Level. From the msdn website I have copied the code for a function called ExecuteSQL (see below). I was using this function just fine for returning recordsets but now I want it to execute an INSERT query. I am confused as to how to call the function when it is not returning a recordset. I don't need it to return anything - just execute. The only way it seems to allow me to use it is by assigning it as a datasource for a recordset (MySource.datasource = ExecuteSQL(MySQL, MyMsg). While this gives me what I need (runs the query), I really don't need to create this recordset variable yet when I don't assign it I get the message that = is expected.

Can anyone clarify this for me.
---------------------------------------
Code from

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbpj00/html/sqladoisting.asp

---------------------------------------
Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String


On Error GoTo ExecuteSQL_Error

sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"

Else

Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = rst.RecordCount & _
" records found from SQL"
End If
ExecuteSQL_Exit:

Set rst = Nothing
Set cnn = Nothing
Exit Function

ExecuteSQL_Error:
MsgBox "There are no records for the given Criteria"
MsgString = "ExecuteSQL Error: " & _
"There are no records"
Resume ExecuteSQL_Exit
cnn.Close
End Function

 

[stoolpigeon]

The recordset is not created when you do an insert, update or delete so just call the function without parens and it should work.

 

[bclt]

Change to:

Public Sub ExecuteSQL(ByVal SQL As String, MsgString As String)



-bclt

 

[jebenson]

The function should work as written for running non-record-returning SQL (i.e., INSERT, UPDATE and DELETE). This section of the code:

sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"

checks if the SQL passed to the function contains the keywords INSERT, UPDATE or DELETE, and if it does it just executes the code and puts a "success" message in the MsgString parameter. You can get this message by examining the contents of the string you pass to the function fo rhw MsgString parameter. If the kewyords INSERT, UPDATE or DELETE are not found (i.e., this is a SELECT query) the function executes the select code and returns a recordset.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[leishad]

Reply to stool pigeon: The function has to receive the sql variable in order to have something to act on. Errors message Argument not optional/ syntax error

Reply to bclt: I am using this function in a bas module and want to use it for both Select and action queries. I DO agree that a procedure would solve my problem for action queries.

Reply to jebenson: I agree that it 'should' work - and it DOES work but seems to require that I assign it to a recordset variable in order to call it(myrecordset.datasource = ExecuteSQL(var1, var2). Now, of course this DOES make sense as it is declared as an ADODB Recordset and maybe I am just being stubborn - I just thought that the author of the function was thoughtful enough to plan for action queries therefore there must be a way to call it without faking in a recordset variable.

 

[DrJavaJoe]

ExecuteSQL var1, var2



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[leishad]

ok - so I think this is a definite beginner shortcoming - the way I should have been doing it was:

Call ExecuteSQL(var1, var2)

I'm sure someone would have noticed this if I had been thoughtful enough to post my exact code. Lesson learned.

 

[stoolpigeon]

drjavajoe is giving an example of what I said. Don't drop the arguments, drop the parens. A function will not return a value if you list the arguments after the function without the parentheses. Just like when you call a msgbox. If you don't use parens you don't need to worry about a returned value. If you do use parens then you need to handle the value coming back.

 

[leishad]

Thank-you for clarification - this is definitely something that I did not know and indeed it works just fine.

 

[DrJavaJoe]

Sorry stoolpigeon I should have mentioned that.



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[vladk]

I have a comment on the very topic:
Function ALWAYS returns value. The very declaration of this language structure warranties this.