SQL 101

[BadDudes]

---

post your sql scripts here ...

simple script:
select * from emp;

get the date ( Oracle Database )
select sysdate from dual ;

[StyM]

script for retrieving the sum of a field.

select sum(amount) from payments

[maldito_guapito]

here's a simple SQL script using view creating an aging report source

CREATE OR REPLACE VIEW AGING as
SELECT ClientName,
SUM(DECODE(TRUNC((ASOF-INVOICEDATE)/30),0,AMOUNT,NULL)) THIS,
SUM(DECODE(TRUNC((ASOF-INVOICEDATE)/30),1,AMOUNT,NULL)) THIRTY,
SUM(DECODE(TRUNC((ASOF-INVOICEDATE)/30),2,AMOUNT,NULL)) SIXTY,
SUM(DECODE(TRUNC((ASOF-INVOICEDATE)/30),3,AMOUNT,NULL)) NINETY,
SUM(AMOUNT) TOTAL
FROM INVOICE, ASOF
GROUP BY CLIENTNAME;

[kyle_nexus]

have any of you guys tried connecting Visual Basic 6 Application with MSSQL 2000 server?

[maldito_guapito]

@kyle
i have tried conneting VB via ODBC to mySQL but using a lower version.
Using a connection string pass thru Data Source Name.

[BadDudes]

have any of you guys tried connecting Visual Basic 6 Application with MSSQL 2000 server?

yup i did it before ... hmmm naahhh its vb6 on MSsql 7 server ... sorry .. why whats the issue with MSsql2000?

[kyle_nexus]

^^^ can u give me the connection code? i can't find the answer of how to connect vb6 w/ mssql 2k in the net.

i hope the code will also work with mssql2k. thanks.

[maldito_guapito]

&kyle for my inputs...
there are a lot of ways on how to connect to a database servers using VB. not just mySQL. it depends on how you design your program and which is appropraite for it.
the ff are some of the ways.
1. Using ADO (Active X Data Object) - no need to code just define your source of connection
2. Data Environment (Active X Designer) - no codes , set up your Data Link Properties
3. ODBC (Open Database Connectivity) - the flexible one , you need code to connect to the database
via Data source name

[maldito_guapito]

though there are a lot of ways below is a sample connection string to mySQL. Put this in a module
so that you can access the string anytime. then use connection object reference this string to connect.


Public Function connectString() As String
Dim cnStr As String
'SQL Server using ODBC
Â* Â*' SQL Server using an ODBC Data Source
'Â* Â*connectString = "Provider=MSDataShape;" & _
'Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*"DSN=Phonebook;" & _
'Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*"UID=root;PWD=;" & _
'Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*"DATABASE=Pbook"
'Â* Â*cnStr = connectString

Â* Â*
Â* Â*' SQL Server using OLE DB Provider
Â* Â*'ConnectString = "ProviderÂ* Â* Â* Â* = MSDataShape" & _
Â* Â*'Â* Â* Â* Â* Â* Â* Â* Â* "Data SourceÂ* Â* Â*= (local);" & _
Â* Â*'Â* Â* Â* Â* Â* Â* Â* Â* "Initial Catalog = Employees;" & _
Â* Â*'Â* Â* Â* Â* Â* Â* Â* Â* "User IdÂ* Â* Â* Â* Â*= sa;" & _
Â* Â*'Â* Â* Â* Â* Â* Â* Â* Â* "PasswordÂ* Â* Â* Â* = ; "
End Function

[kyle_nexus]

thanks maldito_guapito.

MySQL and MSSQL are two different things.