Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #11848 > unrolled thread

VO2ADO SQL Connection

Started bysimon@abersoftware.co.uk
First post2018-08-29 03:13 -0700
Last post2018-08-29 05:56 -0700
Articles 3 — 2 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  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

#11848 — VO2ADO SQL Connection

Fromsimon@abersoftware.co.uk
Date2018-08-29 03:13 -0700
SubjectVO2ADO 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]


#11849

Fromgianluca.pinoli@proximacentauri.it
Date2018-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]


#11851

Fromsimon@abersoftware.co.uk
Date2018-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