basic dataadapter/dataset question

[Newbie]

Hi,

I have a dataadapter that contains just a single record.

Dim SqlDataAdapterValues As New SqlDataAdapter("select * from
tbl_formatvalue where format_id = " & intFormatInt, dbConnectionIn)
SqlDataAdapterJobs.Fill(dsFormats, "formatvalues")

I want to get the value for column customer_id from this dataset.

usually i would have a dataset with many records, and just do a for each
..... looping through and processing each row.

In this case, as there is only one record in the dataset, is there a better
way to get to the data that using the for each?

Or.. is there a better way of getting my data, is a dataset in this
situation not the best.


Thanks

 

[sloan]

DataSets do not do "singles" very well.

If you create a strong dataset.....you can get at the row like this
(sorry I can only do c# from memory)

Let's say I have a strong dataset called OrganizationDS
and I have "Employee" as a table. EmpID (int) adn SSN (string)

OrganizationDS ds = new OrganizationDS();
ds.Employee.AddNewEmployeeRow ( 101 , "222334444" );

if (null!=ds)
{
if(null!= ds.Employee)
{
if(ds.Employee.Count>0) //<<this might be the Length property
OrganizationDS.EmployeeRow row = ds.Employee[0];
if(null!= row)
{
Console.WriteLine ( row.EmpID ) ;
Console.WriteLine ( row.SSN ) ;

}

//also use the Select
DataRow[] rows = ds.Employee.Select("EmpID=101");
if (null!=rows)
{
if(rows.Length>0)
{
OrganizationDS.EmployeeRow anotherRow = rows[0] as
OrganizationDS.EmployeeRow;
if(null!= anotherRow )
{
Console.WriteLine ( anotherRow .EmpID ) ;
Console.WriteLine ( anotherRow .SSN ) ;

}

}
}

}
}




Here is a GREAT article on different approaches:
http://msdn.microsoft.com/en-us/library/ms978496.aspx
Bookmark it, read it every 3 months (for 2 years) since you are new.
It wont' "take" the first time, but will make more and more sense as you
develop.

Which is where you will read this:
Passing DataSets As Inputs and Outputs
The disadvantages of this option are as follows:
* Representation of a single business entity

 

[Rich P]

Here is how you perform a search on a table in a dataset

Dim drF() As DataRow
drF = dsMain.Tables("MyTable").Select("ID = " & strID)

For Each row As Datarow in drF
console.writeline(row("ID").toString & " " & row("Name"))
Next

you can replace the .Select("ID = ...
with .Select("FirstDate = '" & someStringDate & "'")

The .Select property takes a regular string for the Where part of a sql
statement.

.Select("ID = 1")

.Select("ID = " & someIntVar.ToString)


Rich

 

[Branco Medeiros]

Newbie wrote:

I have a dataadapter that contains just a single record.

Dim SqlDataAdapterValues As New SqlDataAdapter("select * from
tbl_formatvalue where format_id = " & intFormatInt, dbConnectionIn)
SqlDataAdapterJobs.Fill(dsFormats, "formatvalues")

I want to get the value for column customer_id from this dataset.

<snip>

If you only want one customer_id from the query, you could use the
ExecuteScalar method of the SqlCommand:

<example>
'Prepare the command
Dim Cmd As New SqlCommand( _
"select top 1 customer_id " _
& "from tbl_formatvalue " _
& "where format_id = @format_id")

Cmd.Parameters.Add("@format_id")
Cmd.Connection = dbConnectionIn

'... elsewhere in your code

'specify the parameter value
Cmd.Parameters("@format_id").Value = intFormatInt

'fetch the data ad convert the result to integer
Dim CustID As Integer = Ctype(Cmd.ExecuteScalar, Integer)
</example>

Of course, you could build a class to encapsulate all that and have a
method to return the ID already converted to integer and where access
errors etc, would be dealt with...

Dim CustID As Integer = SomeClass.GetCustomerID(intFormatInt)


Hope this helps.

Regards,

Branco.