Please can you help with SQL

Hi,

Can anyone help me with an SQL problem.

Do you know if your able to include multiple lines of SQL in VB?

And are you allowed to define SQL variables in VB or do all statments have to begin with SELECT.

Thank you very much for all your help

Sajjad

I didn't fully understand the question ..

but you can make any SQL statment in VB like..

select ... from ... where ...
insert into .... (fields) values (....)
delete from .... where ....

practically you can do all the things that you can do with the SQL analyzer (you send it to SQL server anyway).

Thanx for replying,

What I am asking is:

Are you able to add multiple line of SQL code in VB or are there any special parameters or syntax that you must include in VB to run a multi-line SQL querie.
For insance:
SELECT * FROM table
WHERE ID = (SELECT ID FROM othertable WHERE NAME = 'TEXT1.TEXT')

The above statment is on multiple lines, if you get me.


Also I need to declare variables in my program. And was wondering can you declare SQL variables in VB or do your statments all have to begin with SELECT, DELETE UPDATE. For instance:
DECLARE @Hello
SELECT @HELLO = ID FROM sometable WHERE ID = (SELECT ID FROM othertable WHERE NAME = 'TEXT1.TEXT')

If you dont undertand me then please email me @ [email protected]

Thank you for all you help...once again

Sajjad

same rules as vb
the seperator is the underscore _

Code:

---

Sub ClientServerX2()

  Dim dbsCurrent As Database
  Dim qdfBestSellers As QueryDef
  Dim qdfBonusEarners As QueryDef
  Dim rstTopSeller As Recordset
  Dim rstBonusRecipients As Recordset
  Dim strAuthorList As String

  ' Open a database from which QueryDef objects can be
  ' created.
  Set dbsCurrent = OpenDatabase("DB1.mdb")

  ' Create a temporary QueryDef object to retrieve
  ' data from a Microsoft SQL Server database.
  Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
  With qdfBestSellers
      .Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;" & _
          "DSN=Publishers"
      .SQL = "SELECT title, title_id FROM titles " & _
        "ORDER BY ytd_sales DESC"
      Set rstTopSeller = .OpenRecordset()
      rstTopSeller.MoveFirst
  End With

  ' Create a temporary QueryDef to retrieve data from
  ' a Microsoft SQL Server database based on the results from
  ' the first query.
  Set qdfBonusEarners = dbsCurrent.CreateQueryDef("")
  With qdfBonusEarners
      .Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;" & _
        "DSN=Publishers"
      .SQL = "SELECT * FROM titleauthor " & _
        "WHERE title_id = '" & _
        rstTopSeller!title_id & "'"
      Set rstBonusRecipients = .OpenRecordset()
  End With

  ' Build the output string.
  With rstBonusRecipients
      Do While Not .EOF
        strAuthorList = strAuthorList & "  " & _
            !au_id & ":  $" & (10 * !royaltyper) & vbCr
        .MoveNext
      Loop
  End With

  ' Display results.
  MsgBox "Please send a check to the following " & _
      "authors in the amounts shown:" & vbCr & _
      strAuthorList & "for outstanding sales of " & _
      rstTopSeller!Title & "."

  rstTopSeller.Close
  dbsCurrent.Close

End Sub

---

Hi,

Thanx for all your replies...

Bye the way I'm not using Microsoft SQL server, just accessing a Access datababase (.mdb)

However, I'm still having problems with the SQL variable.

Can you define it as:

Dim DB as Database
Dim RS as Recorset

Set DB = OpenRecordset ("C:\myData.mdb)
Set RS = DB.OpenRecordset("DECLARE @ID" & _
"SELECT @ID = (SELECT age FROM directory" & _
"WHERE name = 'Text1.Text")

Set RS = DB.OpenRecordset ("SELECT * FROM listing" & _
"WHERE ID = @ID")


Can you check my code, and check that you can define variables in the way I have, and that Text1.Text has the correct sintax around it.

Thank you very much
Sajjad

I don't think Access supports the DECLARE statement. It would probably be easier to make it all into one large SQL statement:

Code:

---

Dim DB as Database
Dim RS as Recordset

Set DB = OpenDatabase("C:\myData.mdb")

Set RS = DB.OpenRecordset("SELECT * FROM listing WHERE ID = (SELECT age FROM directory WHERE name = " & Text1.Text & ")")

---

Don't know if this will work, but it's the sort of thing.

[Edited by parksie on 09-09-2000 at 03:49 PM]

Thank you very much for everybodies input. I am VERY greatful.

I would also appreciate any other ideas from anyone else.

Thanx
Sajjad