Executescalar, the right approach

[Morten Snedker]

On a web-form the user enters basic data (company name, phone,
areacode etc).

Many we have already registrered and some not. For those we have we
wish to make the enter of data easier by looking them up.

This looks up 1 value:

Sub LookUpVVS()

Dim sqlCon As New SqlConnection
sqlCon.ConnectionString = conString

Try
sSQL = "SELECT Firmanavn FROM tblVVS WHERE
Telefon=@TelefonNr"
sqlCon.Open()
Dim cmd As New SqlCommand(sSQL, sqlCon)

Dim p1 As New SqlParameter("@TelefonNr",
Data.SqlDbType.Int)
p1.Value = Me.txtTelefon.Text
cmd.Parameters.Add(p1)

Me.txtFirma.Text = cmd.ExecuteScalar().ToString

sqlCon.Close()
sqlCon.Dispose()

Catch ex As Exception
Response.Write(ex.Message)
Exit Sub
End Try

End Sub
In total I have 10 fields I wish to look up and put into the webform.
Is Executescalar the wrong approach? I ask cause I don't know if
there's a simpler way to use Excecutescalar.

I'm thinking that I should use a dataset instaed that holds the entire
record.

Regards /Snedker

[Eliyahu Goldin]

Yes, it is a wrong approach. If you need 10 values, you don't need to call
ExecuteScalar 10 times. You should get them all in one single select. You
don't have to use a dataset. A datareader is another option.

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
"Morten Snedker" <morten_spammenot_ATdbconsult.dkwrote in message
news:64********************************@4ax.com...

On a web-form the user enters basic data (company name, phone,
areacode etc).

Many we have already registrered and some not. For those we have we
wish to make the enter of data easier by looking them up.

This looks up 1 value:

Sub LookUpVVS()

Dim sqlCon As New SqlConnection
sqlCon.ConnectionString = conString

Try
sSQL = "SELECT Firmanavn FROM tblVVS WHERE
Telefon=@TelefonNr"
sqlCon.Open()
Dim cmd As New SqlCommand(sSQL, sqlCon)

Dim p1 As New SqlParameter("@TelefonNr",
Data.SqlDbType.Int)
p1.Value = Me.txtTelefon.Text
cmd.Parameters.Add(p1)

Me.txtFirma.Text = cmd.ExecuteScalar().ToString

sqlCon.Close()
sqlCon.Dispose()

Catch ex As Exception
Response.Write(ex.Message)
Exit Sub
End Try

End Sub
In total I have 10 fields I wish to look up and put into the webform.
Is Executescalar the wrong approach? I ask cause I don't know if
there's a simpler way to use Excecutescalar.

I'm thinking that I should use a dataset instaed that holds the entire
record.

Regards /Snedker

[Karl Seguin [MVP]]

In the situation you are describing, executeScalar won't scale very well.
Today you have 10 fields, tomorrow you might have 100 - that'll be a
problem!

If it's only a single row of data, but multiple columns, ExecuteReader and
DataReaders are likely the most efficient and scalable solution...DataSets
would work too, but are heavier (they can be cached though, which may or
may not be applicable in your case...)

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"Morten Snedker" <morten_spammenot_ATdbconsult.dkwrote in message
news:64********************************@4ax.com...

On a web-form the user enters basic data (company name, phone,
areacode etc).

Many we have already registrered and some not. For those we have we
wish to make the enter of data easier by looking them up.

This looks up 1 value:

Sub LookUpVVS()

Dim sqlCon As New SqlConnection
sqlCon.ConnectionString = conString

Try
sSQL = "SELECT Firmanavn FROM tblVVS WHERE
Telefon=@TelefonNr"
sqlCon.Open()
Dim cmd As New SqlCommand(sSQL, sqlCon)

Dim p1 As New SqlParameter("@TelefonNr",
Data.SqlDbType.Int)
p1.Value = Me.txtTelefon.Text
cmd.Parameters.Add(p1)

Me.txtFirma.Text = cmd.ExecuteScalar().ToString

sqlCon.Close()
sqlCon.Dispose()

Catch ex As Exception
Response.Write(ex.Message)
Exit Sub
End Try

End Sub
In total I have 10 fields I wish to look up and put into the webform.
Is Executescalar the wrong approach? I ask cause I don't know if
there's a simpler way to use Excecutescalar.

I'm thinking that I should use a dataset instaed that holds the entire
record.

Regards /Snedker

[bruce barker \(sqlwork.com\)]

executescaler returns the first column value of the first row returned.
itsproably not what you want.

-- bruce (sqlwork.com)

"Morten Snedker" <morten_spammenot_ATdbconsult.dkwrote in message
news:64********************************@4ax.com...

On a web-form the user enters basic data (company name, phone,
areacode etc).

Many we have already registrered and some not. For those we have we
wish to make the enter of data easier by looking them up.

This looks up 1 value:

Sub LookUpVVS()

Dim sqlCon As New SqlConnection
sqlCon.ConnectionString = conString

Try
sSQL = "SELECT Firmanavn FROM tblVVS WHERE
Telefon=@TelefonNr"
sqlCon.Open()
Dim cmd As New SqlCommand(sSQL, sqlCon)

Dim p1 As New SqlParameter("@TelefonNr",
Data.SqlDbType.Int)
p1.Value = Me.txtTelefon.Text
cmd.Parameters.Add(p1)

Me.txtFirma.Text = cmd.ExecuteScalar().ToString

sqlCon.Close()
sqlCon.Dispose()

Catch ex As Exception
Response.Write(ex.Message)
Exit Sub
End Try

End Sub
In total I have 10 fields I wish to look up and put into the webform.
Is Executescalar the wrong approach? I ask cause I don't know if
there's a simpler way to use Excecutescalar.

I'm thinking that I should use a dataset instaed that holds the entire
record.

Regards /Snedker

[Flinky Wisty Pomm]

If you have the option, the best way to handle a single row as opposed
to a single datum is to create a stored procedure with the fields
returned as OUTPUT parameters.

You can then use ExecuteNonQuery and read the values out of the
Parameters collection of your command.

Generally, I use this heuristic

1 value : ExecuteScalar
1 row : ExecuteNonQuery with output params
N rows : ExecuteReader

Morten Snedker wrote:

On a web-form the user enters basic data (company name, phone,
areacode etc).

Many we have already registrered and some not. For those we have we
wish to make the enter of data easier by looking them up.

This looks up 1 value:

Sub LookUpVVS()

Dim sqlCon As New SqlConnection
sqlCon.ConnectionString = conString

Try
sSQL = "SELECT Firmanavn FROM tblVVS WHERE
Telefon=@TelefonNr"
sqlCon.Open()
Dim cmd As New SqlCommand(sSQL, sqlCon)

Dim p1 As New SqlParameter("@TelefonNr",
Data.SqlDbType.Int)
p1.Value = Me.txtTelefon.Text
cmd.Parameters.Add(p1)

Me.txtFirma.Text = cmd.ExecuteScalar().ToString

sqlCon.Close()
sqlCon.Dispose()

Catch ex As Exception
Response.Write(ex.Message)
Exit Sub
End Try

End Sub
In total I have 10 fields I wish to look up and put into the webform.
Is Executescalar the wrong approach? I ask cause I don't know if
there's a simpler way to use Excecutescalar.

I'm thinking that I should use a dataset instaed that holds the entire
record.

Regards /Snedker