I am submitting data to a SQL server using .net
cmdObject = DBConnection.CreateCommand()
cmdObject.commandText = (SQLRequest as string)
readerObject = cmdObject.ExecuteReader()
readerObject.close()
Is there a way to get the reponse from SQL? When I use something like PHP there is the option to get the error/information/status message.
If I submit a bad SQLQuery string it’ll return the error message from SQL. But if it’s successful I want to know what the new GUID is that it assigned the row.
Solved it I should use a .ExecuteScalar() instead of ExecuteReader.
Then on my SQL insert command tack on “; SELECT @@Identity as ‘Identity’”
Then the Execute Reader does the select and returns the last created ID.
Glad you solved it … I was following this thread to hoping to see how/if exceptions are passed out of .net into mxs, though! Can you get at them, if you set things up right?
As far as error messages go I don’t know. I have a try(SQL.Insert DB string)catch(Print “Insert Failed”)
system in place right now.
Thanks for following up with a post on how you solved it Gavin. I see so many people ask questions without bothering to let people know their solution.
With stuff like this, it’s always great to have an answer just in case!
function Insert DBConnection TableName DataArray ID:"NULL" =
(
SQLRequest = ("INSERT INTO " + (TableName as string) + " VALUES ("+(ID as string))
for o in DataArray do
(
append SQLRequest (", '" + (o as string) +"'")
)
append SQLRequest "); SELECT @@IDENTITY AS 'Identity';"
cmdObject = DBConnection.CreateCommand()
cmdObject.commandText = (SQLRequest as string)
readerObject = cmdObject.Executescalar()
return readerObject
),
Here is the function I use.
DataArray is an array of all the columns so if you have a table (with ID by default set to “null”) as such:
|SmallTable|
[PrimaryKeyCol(AutoIncrement)],[ColOne], [ColTwo], [ColThree]
You would say (Note SQL is the struct that contains all my SQL functions):
SQL.Insert DBConn “SmallTable” #(’’,32,“Hello”)
If you need to specify a key value it assumes it’s in the first column and fills it in.
It might be easier for some people to execute generic SQL instructions but I like keeping everything Max-Native.