Hi denisT,
you seem to the man in the know.
I’m ok at maxscript (I thought!) but want to get into databases, specifically ms access.
I’ve trawled the internet for a while (probably not very well) and tried loads of examples but can’t seem to even connect to a simple mdb. Can you point me towards a good book or site for a complete noob?
Just look at my feeble attempt based on the MaxScript help file!

DogConn = createOLEObject “ADODB.Connection”
–<OLEObject:ADODB.Connection>
DogConn.open “Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\ emp\MyAssets1.mdb;”
–undefined
recordSet = createOLEObject “ADODB.Recordset”
–<OLEObject:ADODB.Recordset>
recordSet.Open “SELECT * from Assets” DogConn
–undefined

Thanks

Hi bingheyuren,

I think you should try with sqlexpress, it free, faster and up-to-date.
Try the code below, it works on my pc

dotnet.loadassembly "System.Data"
lst_test=dotnetObject "System.Windows.Forms.Listbox"

constring = "Data Source=server\\sqlexpress;Initial Catalog=staffdata;User Id=sa;Password=1234"
con = dotNetObject "System.Data.SqlClient.SqlConnection" constring

dataset = dotNetObject "System.Data.Dataset"
tableadapter = dotNetObject "System.Data.SqlClient.SqlDataAdapter" "SELECT * FROM staff" constring 
tableadapter.fill dataset "staff"

lst_test.DataSource=dataset.tables.item["staff"]
lst_test.DisplayMember = "username"


hForm = dotNetObject "System.Windows.Forms.Form"
hForm.Size = dotNetObject "System.Drawing.Size" 310 335
hForm.Text = ".Net 2.0 Form with TextBox"
hForm.Controls.Add(lst_test)
hForm.TopMost = true
FormBorderStyle = dotNetClass "System.Windows.Forms.FormBorderStyle"
hForm.FormBorderStyle = FormBorderStyle.FixedDialog
hForm.ShowInTaskbar = false
hForm.MinimizeBox = false
hForm.MaximizeBox = false

hApp = dotNetClass "System.Windows.Forms.Application"
hApp.Run hForm

Hi batigolnguyen,

It works for me! well sort of, it falls over at

tableadapter.fill dataset “staff”

–the error message is…
– Runtime error: dotNet runtime exception: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

I’ve had a look around for clues but I’m stumped!
Do I need to set up my database in a particular way?

Thanks!

oh, you looks like don’t have experience with database or sqlserver.
You have to set up sqlexpress in your PC, create database, create tables and put it into my code :curious:

Yup that’s right but I’m learning! I’ve learnt the access database has to be a project ‘.adp’ file, not a mdb.
It seems to connect to the database but filling the dataset won’t work.
Perhaps I need to populate the table in a particular way, with the right fields, or because I have no ID field?
I’ll go away and learn some more about databasing, thanks for your help!

This works only for SQL Server or SQL Express, because it use System.Data.SqlClient.SqlConnection of dotNetFramework. If you want to use MS Access you should try OleDatabaseConnection. I’ve already worked with MS Access but in C#, for maxscript have no experience. Sorry I cannot help you about this issue, if you change your idea and move to SQL database, I may be help you.

Hi batigolnguyen,

I went away and learnt about dbms, sql, my sql, adodb, odbc and my head is still spinning!
Anyway I got connections working to:

 --An MS Access mdb with adodb... 
TestConn=createOLEObject "ADODB.Connection"
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Asset_Tracking1.mdb;User Id=admin;Password=;"
 
--and dotnet...
DBConnection = dotNetObject "System.Data.OleDb.OleDbConnection"
DBConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Asset_Tracking1.mdb;User Id=admin;Password=;"
 
--connection to mySQL with adodb...
TestConn=createOLEObject "ADODB.Connection"
TestConn.Open "driver={MySQL ODBC 5.1 Driver}; server=localhost; database=myDatabase; user=myId; password=myPassword"
 
--and dotnet...
dotNet.loadAssembly "C:\\Program Files\\MySQL\\mysql-connector\\bin\\MySql.Data.dll" 
DBConnection = dotNetObject "MySql.Data.MySqlClient.MySqlConnection"
connectionString = "Database=mydatabase;Data Source=localhost;User Id=myId;Password=myPassword" 
DBConnection.ConnectionString = connectionString 

I then made a basic script with listview interface and it can save and retrieve models to and from the mySQL database.

I need a sit down now, oh, I already am, phew.
Got there in the end! Thanks to everyone I nicked code off.

Thanks