Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.clipper.visual-objects > #11848 > unrolled thread
| Started by | simon@abersoftware.co.uk |
|---|---|
| First post | 2018-08-29 03:13 -0700 |
| Last post | 2018-08-29 05:56 -0700 |
| Articles | 3 — 2 participants |
Back to article view | Back to comp.lang.clipper.visual-objects
VO2ADO SQL Connection simon@abersoftware.co.uk - 2018-08-29 03:13 -0700
Re: VO2ADO SQL Connection gianluca.pinoli@proximacentauri.it - 2018-08-29 03:58 -0700
Re: VO2ADO SQL Connection simon@abersoftware.co.uk - 2018-08-29 05:56 -0700
| From | simon@abersoftware.co.uk |
|---|---|
| Date | 2018-08-29 03:13 -0700 |
| Subject | VO2ADO SQL Connection |
| Message-ID | <e3dcfd4e-9289-490c-a2be-c15b26ff3ec0@googlegroups.com> |
Hope someone can help me with an example connection string to an SQL Express database.
I've been using the VO2ADO classes to open MS Access databases over the years but was trying out connecting to SQL tables.
I've looked through Roberts help files etc but cant seem to get the correct connection string in place.
My latest attempt
oConn := AdoConnection{}
oConn:ConnectionTimeout := 30
oConn:Properties:[item, "Prompt"]:Value := AdPromptComplete
oConn:Open("Provider=sqloledb;Network Library=DBMSSOCN;"+;
"Data Source=C:\SQLDatabases\SRPTestSQL2.mdf;"+;
"Initial Catalog=dbo.tblHostels;" ;
,NIL,NIL,NIL)
This returns an unknown error.
Any examples would be helpful.
Simon
[toc] | [next] | [standalone]
| From | gianluca.pinoli@proximacentauri.it |
|---|---|
| Date | 2018-08-29 03:58 -0700 |
| Message-ID | <19e083ca-2784-4cb1-af13-38f3b993b5ca@googlegroups.com> |
| In reply to | #11848 |
Il giorno mercoledì 29 agosto 2018 12:13:43 UTC+2, si...@abersoftware.co.uk ha scritto:
> Hope someone can help me with an example connection string to an SQL Express database.
>
> I've been using the VO2ADO classes to open MS Access databases over the years but was trying out connecting to SQL tables.
>
> I've looked through Roberts help files etc but cant seem to get the correct connection string in place.
>
> My latest attempt
>
> oConn := AdoConnection{}
> oConn:ConnectionTimeout := 30
>
> oConn:Properties:[item, "Prompt"]:Value := AdPromptComplete
>
> oConn:Open("Provider=sqloledb;Network Library=DBMSSOCN;"+;
> "Data Source=C:\SQLDatabases\SRPTestSQL2.mdf;"+;
> "Initial Catalog=dbo.tblHostels;" ;
> ,NIL,NIL,NIL)
>
> This returns an unknown error.
>
> Any examples would be helpful.
>
> Simon
Hi Simon,
I use this method for the connection:
###############################################################
FUNCTION OpenConnection AS AdoConnection
LOCAL sError AS STRING
LOCAL oConn AS AdoConnection
LOCAL uError AS USUAL
LOCAL cbErr AS CODEBLOCK
LOCAL oTestSQLini AS inifilespec
LOCAL sConnString AS STRING
oTestSQLini := inifilespec{WorkDir()+"GDOshop.INI"}
sConnString := AllTrim(oTestSQLini:GetString("AS@R_Java","ConnectionString"))
* ? sConnString
BEGIN SEQUENCE
oConn := AdoConnection{}
oConn:ConnectionTimeout := 5
oConn:ConnectionString := sConnString
oConn:CursorLocation := adUseClient
RECOVER USING uError
warningbox{,"SQL Error", "Error: "+uError:description}:Show()
_Quit()
END
BEGIN SEQUENCE
oConn:Open(NIL,NIL,NIL,NIL)
RECOVER USING uError
END
IF oConn:State <> adStateOpen
// Error occurred
IF oConn:Errors:Count > 0
// Ado Error
sError := oConn:Errors:[Item,0]:Description
ELSEIF IsInstanceOfUsual(uError, #Error)
sError := uError:Description
ELSE
sError := "Unknown error"
END IF
warningbox{,"SQL Error", "Error: "+uError:description}:Show()
_Quit()
ENDIF
*ErrorBlock(cbErr)
* ? "Connessione stabilita"
RETURN oConn
#####################################################
INI FILE:
[AS@R_Java]
ConnectionString=DSN=MTX;Uid=mtxadmin;Pwd=mtxadmin;
Regards
Gianluca
[toc] | [prev] | [next] | [standalone]
| From | simon@abersoftware.co.uk |
|---|---|
| Date | 2018-08-29 05:56 -0700 |
| Message-ID | <541c666a-cb05-4c4a-98b6-7d77dd51d034@googlegroups.com> |
| In reply to | #11848 |
Thanks for the code Gianluca - it pointed me to the fact that I couldnt create a DSN connection either.
My error was with the SQL Server being setup for Windows Authentication and I had to change that to SQL Server and Windows Authentication.
Just in case this helps anyone else the code that works and gives me a connection is:
oConn := AdoConnection{}
oConn:ConnectionTimeout := 30
oConn:Provider := "SQLOLEDB"
oConn:Properties:[item, "Prompt"]:Value := AdPromptComplete
oConn:ConnectionString := "Data Source=AS-SSD-SERVER\SQLEXPRESS;"+;
"Initial Catalog=C:\SQLDatabases\SRPTestSQL2.mdf;"
oConn:Open(NIL,NIL,NIL,NIL)
This prompts for the username and password and connects.
I've simply picked up a whole table into a recordset as follows:
oAdoRecordSet := AdoRecordSet{}
AdoDateTimeAsDate(TRUE) //Only return date part of date/time fields
sTable := "dbo.tblHostels"
sSelect := "*"
cSQL := "SELECT "+sSelect+" FROM "+sTable+" "
oAdoRecordSet:Open(cSQL,oConn,AdOpenStatic,adLockReadOnly,adCmdText)
oAdoServer := AdoServer{oAdoRecordSet}
//Put data into a bBrowser control
oDCmBB1:Use(oAdoServer)
oDCmBB1:Refresh()
Message("AdoRecordset:RecordCount "+AsString(oAdoRecordSet:RecordCount))
Simon
[toc] | [prev] | [standalone]
Back to top | Article view | comp.lang.clipper.visual-objects
csiph-web