Database Search

TechHome

• MicroSoft Jet OLEDB Sort Numbers in a Text Field
• MySql Example
• udfSQLExecute GetString
• use ADO to Count Data in Excel

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

udfSQLExecute GetString

 Keywords: ADO SQLExecute udfSQLExecute GetString SQLOLEDB.1 SQLServer SQL Server Express

#DefineFunction udfSQLExecute( sqlstmt, objDB, colsep, rowsep, nullchar, berrmsg )
   ; Lars Doornbos
   IntControl( 73, 1, 0, 0, 0 ) ;Error Handler
   SQL_result = ''
   sqlstmt = StrTrim( StrReplace( sqlstmt, @CRLF, ' ' ) )
   objRS = objDB.Execute( sqlstmt )
   If objRS.Fields.count() ;-- so there is a result...
      If !( objRS.bof && objRS.eof )
         SQL_result = objRS.Getstring(2,-1,colsep,rowsep, nullchar)
         SQL_result = ItemRemove(-1,SQL_result,rowsep)
      EndIf
   EndIf
   objRS = 0
   Return SQL_result

   :wberrorhandler
      If berrmsg
         Pause( "***error***", wberroradditionalinfo )
      EndIf
      If VarType( objRS ) == 1024 Then objRS=0
      Return 0
#EndFunction

ADOConString="Provider=SQLOLEDB.1;User Id=MyID;Password=MyPassword;Catalog=MyDB;Data Source=MyServer;"
;ADOConString= "Provider=SQLOLEDB.1; Data Source=[servername]; Initial Catalog=[database]; Integrated Security=SSPI;" ;Windows authentication instead of a username password
objDB = CreateObject('ADODB.Connection')
objDB.CursorLocation = 3
objDB.Provider = "SQLOLEDB.1"
objDB.ConnectionString = ADOConString
objDB.Open()


rtv = udfSQLExecute(`select * from yourtable`, objDB, @TAB, @LF, '-' , 1)
rtv = udfSQLExecute(`insert into yourtable (name) values ('test')`, objDB, @TAB, @LF, '-' , 1)
rtv = udfSQLExecute(`select getdate()`, objDB, @TAB, @LF, '-' , 1) ;SQLserver

objDB.Close()
objDB = 0

Article ID:   W18067

Filename:   udfSQLExecute GetString.txt

File Created: 2013:01:14:08:44:50

Last Updated: 2013:01:14:08:44:50