How to fill a dataset from an SQL user-defined function

Oops sorry forgot to mention the error message, which is: Missing SourceTable mapping: 'Table'

wouldnt you want to use executescalar ? your function is returning a value no ?

I thought executescalar was for simple single value results. Will it cope with a table being returned ? And if so how do I get it into a Dataset ?

DbCommand.TableMappings.Add("Table","tblName")

DbDataSet = New DataSet
DbDataSet.Tables.Add("tblName")
DbDataAdapter.Fill(DbDataSet, "tblName")

it should default to "table1" no ? perhaps because its a table object being returned from the function the dataadapter is going a bit nuts ?

You shouldn't use Scalar for this because it is an entire Result Set you are seeking.

All I know...is when I create datasets in this way (Like when importing from Excel...I need to define the TableMappings as shown above.

In this:

DbCommand.TableMappings.Add("Table","tblName")
DbDataSet = New DataSet
DbDataSet.Tables.Add("tblName")
DbDataAdapter.Fill(DbDataSet, "tblName")

is the tblName the name of the table in the sql database ? If so, I can't use it because the user-defined function is putting together a table from several related tables.... unless there is a way of naming the output table from the udf ?

No...basically whatever your query returns from DbCommand will be stored in the Table called tblName...in your case the results of your UDF...so you could do...

DbCommand.CommandText = "SELECT * FROM dbo.[udfMyUDF](1,1,65)"
DbCommand.TableMappings.Add("Table","tblUDFResults")
DbDataSet = New DataSet
DbDataSet.Tables.Add("tblUDFResults")
DbDataAdapter.SelectCommand = DbCommand
DbDataAdapter.Fill(DbDataSet, "tblUDFResults")

that sounded like it might've solved the problem but if I type in:

DbCommand.TableMappings.Add("Table","tblUDFResults")

I get a syntax error: TableMappings is not a member of DbCommand. I looked up TableMappings and MSHelp said it was a member of DataAdapter, so I did this instead:

DbCommand.CommandText = "SELECT * FROM dbo.[udfMyUDF](1,1,65)"
DbDataAdapter.TableMappings.Add("Table", "tblUDFResults")
DbDataSet = New DataSet
DbDataSet.Tables.Add("tblUDFResults")
DbDataAdapter.SelectCommand = DbCommand
DbDataAdapter.Fill(DbDataSet, "tblUDFResults")

But got the same error message: Missing SourceTable mapping: 'tblUDFResults' <groan!>

So near and yet so far !! This is really frustrating!

Incidentally, what does the first "Table" parameter the TableMappings.Add refer to ?

Taking another tack ... do I really need a DataAdapter at all ? All I want to do is get the table into a dataset (I don't ever want to update it) ... is there another way to read it in ?

Well, if you you want to read the data, you could use an SqlDataReader and populate a DataTable yourself.

My understanding is that the datareader reads one row at a time - but the udf returns a whole table so I don't see how this will work ..... unless of course you know better ?

Going back to the mapping problem ... if I generate a typed dataset will that solve it ? (Seems a long way round tho')

Oops, forgot to mention that Read() moves the cursor to the next record (not a single column or byte). Check out the documentation in MSDN.