hi guys
I would like an example of string connection to SQL server 2008 with ADO
thank you
xPROVIDER := "SQLOLEDB"
xSOURCE := "Your Server"
xCatalog := "your database"
xUserId := "xxxxxxx"
xPASSWORD := "xxxxxxx"
// example using my local sql server
xPROVIDER := "SQLOLEDB"
xSOURCE := "RICKLIPKIN-PC\SQLEXPRESS"
xCatalog := "vehicle"
xUserId := "xxxxxx"
xPASSWORD := "xxxxxxx"
xString := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
// create global connection
//
// global connection string
oCn := CREATEOBJECT( "ADODB.Connection" )
TRY
oCn:Open( xString )
CATCH oErr
Saying := "Could not open a Global Connection to Database "+xSource
MsgInfo( Saying )
RETURN(.F.)
END TRY
// open a recordset with global connection
oRsAGEN := TOleAuto():New( "ADODB.Recordset" )
oRsAGEN:CursorType := 1 // opendkeyset
oRsAGEN:CursorLocation := 3 // local cache
oRsAGEN:LockType := 3 // lockoportunistic
cSQL := "SELECT AGENEID,AGENCY,OWNER FROM AGENCY WHERE agency ='"+xAGENCY+"'"
TRY
oRSAGEN:Open(cSQL,oCn )
CATCH oErr
MsgInfo( "Error in Opening AGENCY table" )
Return(.f.)
END TRYthank you Rick
I'll try it!
local cServer := "GNRHP\SQLEXPRESS"
local cUser := "SA"
local cPassword := "<yourpassword>"
local cDatabase := "FWH"
local cTable := "CUSTOMER"
local oCn, oRs
oCn := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase, cUser, cPassword }, .t. )
if oCn == nil
? "Connect Fail"
else
oRs := FW_OpenRecordSet( oCn, cTable )
if oRs == nil
? "Fail to open table"
else
XBROWSER oRs FASTEDIT TITLE cTable
oRs:Close()
endif
oCn:Close()
endif
return nil oCn := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase }, .t. )function Main()
local cServer := "MYSERVER\MSSQLSERVER08"
local cUser := "sa"
local cPassword := "pass"
local cDatabase := "DATABASE"
local cTable := "TABLE"
local oCn, oRs
oCn := FW_OpenAdoConnection( { "SQLOLEDB", cServer, cDatabase, cUser, cPassword }, .t. ) //--> LINE 14
if oCn == nil
? "Connect Fail"
elselocal Connessione := "Provider=SQLOLEDB;server=MYSERVER\MSSQLSERVER08;database=DATABASE;uid=sa;pwd=pass"
local oWnd
oCn := FW_OpenAdoConnection( Connessione )
oRs := FW_OpenRecordSet( oCn, "SELECT * FROM TABLE order by FIELD1", 1 )
DEFINE WINDOW oWnd TITLE "Auto edit browse"
@ 0,0 XBROWSE oBrw OF oWnd AUTOCOLS CELL LINES NOBORDER RECORDSET oRs
WITH OBJECT oBrw
:CreateFromCode()
END
oWnd:oClient := oBrw
ACTIVATE WINDOW oWnd ON INIT (obrw:gotop(), oBrw:SetFocus())
oRs:Close()
oCn:Close() oCn := FW_OpenAdoConnection( { "SQLOLEDB", cServer, cDatabase, cUser, cPassword }, .t. ) //--> LINE 14 oCn := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase, cUser, cPassword }, .t. ) //--> LINE 14nageswaragunupudi wrote:As always we keep repeating our advice to use FWH functions to connect to any ADO Server, instead of trying your own connection string. This has the advantage that FWH creates suitable connection string based on the version of provider installed on the target computer. For example, any of these providers could be installed on your client's computer viz, SQLNCLI11, SQLNCLI10,SQLNCLI or the default SQLOLEDB. FWH function searches for the latest provider (driver) installed and establishes connection.
This is a sample of our recommended usage:
local cServer := "GNRHP\SQLEXPRESS" local cUser := "SA" local cPassword := "<yourpassword>" local cDatabase := "FWH" local cTable := "CUSTOMER" local oCn, oRs oCn := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase, cUser, cPassword }, .t. ) if oCn == nil ? "Connect Fail" else oRs := FW_OpenRecordSet( oCn, cTable ) if oRs == nil ? "Fail to open table" else XBROWSER oRs FASTEDIT TITLE cTable oRs:Close() endif oCn:Close() endif return nil
It is also possible that the server is configured to login with Windows security. In that case you can use:
oCn := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase }, .t. )
If you use FW functions, you need not again ask this question : "How to connect?" to any database, be it MySql, MsSql, Oracle, etc.
XBROWSER oCnThank you, Mr. Rao
Short and sweet, done
nageswaragunupudi wrote:This is a sample of our recommended usage:
local cServer := "GNRHP\SQLEXPRESS" local cUser := "SA" local cPassword := "<yourpassword>" local cDatabase := "FWH" local cTable := "CUSTOMER" local oCn, oRs oCn := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase, cUser, cPassword }, .t. ) if oCn == nil ? "Connect Fail" else oRs := FW_OpenRecordSet( oCn, cTable ) if oRs == nil ? "Fail to open table" else XBROWSER oRs FASTEDIT TITLE cTable oRs:Close() endif oCn:Close() endif return nil