SQL - Immediate Help Needed!! :-)

[Hexman]

I am currently porting a Linux based application using MySQL for data storage, to Windows NT, using VisualBasic & MSSQL Server 7.0. I am already familiar with the SQL language, and only need to know how to interface it with VB. I have used the DataView window to open my SQL server and view my database & tables. Could someone please provide me with some sample VB source code for connecting to a MSSQL server, performing a query, retrieving the number of results, and actually retrieving the results.

Thanks!

[Gen-X]

I can quickly give you an idea how to set it up to get it to use DAO 3.5 and ODBC to connect to SQL Server.

First you need to configure an ODBC connection in the control Panel and provide a DSN name (lets use OneDB for this example).

Then in Visual Basic you need to go into "References" and include the Microsoft DAO 3.5 Library and put a tick in it

Opening and closing a database

Dim wrkDatabase as Workspace
Dim dbsDatabase as Database

' Create a workspace
Set wrkDatabase = CreateWorkspace("OneDB", "user","pwd", dbUseODBC)

' Open the database
Set dbsDatabase = wrkDatabase.OpenDatabase("OneDB", dbDriverNoPrompt, False, "ODBC;DSN=OneDB;")

' When finished with it
dbsDatabase.Close
wrkDatabase.Close

Now to retrieve information :

Dim rstData as Recordset

' Use this to read data only
Set rstData = dbsDatabase.OpenRecordset("SELECT * FROM table", dbOpenSnapshot, dbReadOnly, dbReadOnly)

'OR

' Use to get something to update later
Set rstData = dbsDatabase.OpenRecordset("SELECT * FROM table", dbOpenDynaset, dbRunAsync, dbOptimistic)

'Now you can use the following

' How many records retrieved
MsgBox rstData.RecordCount

'How to go through them one by one
Do While Not rstData.EOF
  ' How to access fields
  MsgBox rstData("Field")
  ' Moving to the next record
  rstData.MoveNext
Loop

' Then close it down
rstData.Close
Set rstData = nothing

Thats it in a nutshell with HEAPS missed out

[Hexman]

Thanks very much for your sample source code, it's just what I'm looking for to get me started. About setting up the ODBC connection, how exactly do I do that? I'm running Win2K Professional with SQL Server 7.0 Standard installed here too. I went to Control Pannel -> Administrative Tools -> Data Sources (ODBC), but now I'm lost..Could you please explain what to do next? When I click ADD it asks me for a driver to use for data access, I selected SQL Server, since I'm accessing from an SQL server, right?

Also about your code, could you give me some simple code on adding a table, and modifying one? When I wrote this software under Linux I had something like this
$query = "UPDATE * FROM table WHERE blah='$blah' AND blah2='$blah2'"
..it's the exact same language, right?

[Hexman]

Doh

I didn't mean code for adding a table, I meant code for adding a new entry to a table, and modifying (updating) values in an entry, or multiple entries in a table.

[Gen-X]

Ok, When you add a new ODBC data source you do select SQL Server as your driver.

From there the next screen you get differs depending on the version of ODBC installed. Basically though it is asking you for a "Name". This is the DSN name and is what you will use in your code to refer to it (ie OneDB in our example). The description can be anything you like and the "Server" should be the machine you have SQL Server installed on, use [localhost] if its on the same machine.

Then it will take you to another screen where you select if you either want to use Windows Authentication (ie your login name is used and compared to the SQL Server logins list) OR SQL Server Authentication (You can temporarily give the username of "sa" and its password for testing purposes). Then another screen will give you the option of setting the default database. Click this and select the database you created on SQL Server.

Now skip through all the rest until it asks you to "TEST" your connection and make sure it says "TEST SUCCESSFUL".

You've created the connection

Now as for creating or updating records... There are 2 ways.

Method 1 - Execute

sSQL = "UPDATE Table Set Field1 = 'fred' WHERE key = 1"

dbsDatabase.Execute(sSQL)

OR

Method 2 - Recordset

Set rstData = dbsDatabase.OpenRecordset("SELECT * FROM Table", dbOpenDynaset, dbRunAsync, dbOptimistic)

Do While Not rstData.Eof
  If rstData("Key") = 1 Then
    ' Modifying the CURRENT Record
    rstData.Edit                 ' Set to edit mode
    rstData("Field1") = "fred"   ' Change the field value
    rstData.Update               ' Save the record
  End if
  rstData.MoveNext
Loop

' Adding a NEW Record
rstData.AddNew
rstData("Key") = 3
rstData("Field1") = "john"
rstData.Update

rstData.Close
Set rstDate = nothing

Have a look at the help files on the AddNew, Edit, Update and other functions and it should logically lead you from there to give you all the examples you need.

[Hexman]

Maybe there's something totally wrong with me, I dont know, but I just can't get the darn thing to add a record!! I tried your code for opening the recordset, but that didn't work so I removed the 3 optional flags at the end and it still didn't work :-)

userdb is the DSN name aswell as the database name. userinfo is a table containing about 30 fields for user information. Perhaps it didn't add it because I didn't include all the fields to be added, only included login & password?

Do you think I could get in touch with you via email or ICQ? You're the only person who has helped me like this, and I desperately need to get this done for work. My email is [email protected], and ICQ is 1510247



Dim wrkDatabase As Workspace
Dim dbsDatabase As Database

' Create a workspace
Set wrkDatabase = CreateWorkspace("userdb", "", "", dbUseODBC)

' Open the database
Set dbsDatabase = wrkDatabase.OpenDatabase("userdb", dbDriverNoPrompt, False, "ODBC;DSN=userdb;")

Dim rstData As Recordset

Set rstData = dbsDatabase.OpenRecordset("SELECT * FROM userinfo")


' Adding a NEW Record
rstData.AddNew
rstData("login") = "matt"
rstData("password") = "iscool"
rstData.Update

rstData.Close
Set rstData = Nothing

' When finished with it
dbsDatabase.Close
wrkDatabase.Close

[Sebastian Zich]

In the property DataSource of your data control (or a RecordSet variable) you can assign a string with the SQl statement.
And you excute it I belive with recalc or refresh.

I hope this wath you need.

Regards