Hi,
I’m working on an asset browser. I use mySQL to power the browsing and .NET to talk to the database. I’m not that big an expert but i’ve got my brother helping me. We had a problem we couldn’t figure out.
I’ve connected to a database and created a new row in a table in this database
function fn_createRecord tableName =
(
local dataTable = fn_selectData("SELECT * FROM " + tableName)
dataTable.NewRow()
)
newAsset = fn_createRecord "asset" --adding a new row to the table
This works, but now i want to add values to the different columns of the new row. I’d like to use the columnNames to assign values to the column. Like so:
columnName = dotnetObject "system.string" "assetName"
--reading from a dataRow works
newAsset.item[columnName] --this returns the value in the column "assetName"
--writing to a dataRow does't work
newAsset.item[columnName] = "some value" --this doesn't work
How do i add a value to a column? I’ve checked the dataRow class in the .NET documentation. It doesn’t list an “addValue” method (or something like it). The examples for visualBasic or C# just use the = sign to assign values to the columns. This doesn’t work for me in max. Can someone help me?
Klaas
You’re probably going to need to use an “update” command.
If you go the ODBC route it’s:
recordSet.update #("id", "name") #(1,"newname")
http://forums.cgsociety.org/showthread.php?f=98&t=384588
Similarly SQL will be something like:
“UPDATE Table SET column = ‘value’ WHERE ID = ‘#’”
hi,
we’ve tried quite a bit but haven’t found a satisfying solution. i’d like to call my columns by their names, this prevents me from rewriting my scripts when i decide to add or remove columns. As i wrote earlier, i can read from a row in a datatable. Just by calling the rownumber and columnname. It’s just very convenient!
So assigning a value to this row should be as easy, shouldn’t it? It appears that max doesn’t want to assign values to a row in a datatable.
Ofer wrote some excellent material on this: http://forums.cgsociety.org/showthread.php?f=98&t=605581
But he’s assigning and calling the data with sql-queries. This is too messy for me (call me childish). So i’m just fishing here to find if somebody can answer me how to write to a dotnet row in a dataTable?
Klaas
Hi Klaas,
I’ve also been looking for a way to do this some time ago, and eventually came up with this:
(
local Table = dotNetObject "System.Data.DataTable"
Table.Columns.Add "Test_Column"
local Row = Table.NewRow()
[b]Row.set_Item[/b] "Test_Column" (dotNetObject "System.String" "Test_Value")
Table.Rows.Add Row
)
You need to give this method a dotnet class (entering an mxs string literal won’t work), but at least you can set a value by using a column name. I don’t know why this works and Row.Item = … doesn’t. The property is documented as read/write and like you said it is how it’s done in Microsoft’s examples.
Hope this helps!
Cheers,
Martijn
oh my,
that just might work for me. I’ll try it out in my script, but it looks very promising.
I wonder: how much time did it take you to find out this alternative syntax?
Thanks and de groeten
Klaas
This is probably a stupid question, but are you using any auto generated keys on the table?? Would this cause any issues normally any way??
Shane
Well Shane,
since i have no clue what you’re talking about i’ll assume it’s a very smart question.
Klaas
lol! Surprisiningly, not many people do (have any idea of what I’m talking about ;))
Basically, many SQL databases offer the ability to have a “key” column with an value which is automatically generated by the database itself. It is normally used to aid in data relationships and referential integrity but producing a unquie key value.
You MIGHT have an issue with .net and MySQL when creating a new record as .net MIGHT want to assign a value to the column but the driver/database won’t let it.
This is VERY BIG MIGHT, but you’d be surprised sometimes…
Shane
.NET can get really picky in how you alter a table with an AI Key. I find it easiest to simply inject generic SQL without using all the fancy .net methods if performance isn’t a concern. Let MySQL worry about keeping itself straight.
ah,
i see. My tables all have a unique ID in their first column. This ID is auto-generated, yes. Isn’t that generally good database practice? But creating a unique number myself shouldn’t be a big problem. I’ll try that one out as well to see if that let’s me set values in mySQL rows directly.
Thanks for the info,
Klaas
Hi grabjacket!
Yes auto generated keys are a great idea!! They remove a lot of the potentional of the same key been generated simultanously between two or more clients, BUT, you are using (what mounts to) a 3rd party library to access the database…it simply may not be able to comprehend that a given column is “special” column…
You really need to read the docs, both the MySQL and the .net, on the subject, they might provide you with the correct configuration required to get these types of columns to work in .net
Shane