ExecuteScalar Problems. Need Help

Hi all

I am currently developing a Help Desk for our company. One of my problems is Data lookups in other tables within a SQL 2000 DB. i.e. Client Details and Information in one table (hd_clients) and Client History (hd_history) in another.

'hd_history' contains a column called 'c_id' which references the 'hd_clients' table 'c_id' column A typical One-to-Many relationship. When a user goes to the Help Desk's Service page. I want to display the client's name in one of my GridView's Databound Columns. See Below:

...

<

asp:TemplateFieldHeaderText="Client"SortExpression="c_id"> <ItemTemplate> <asp:LabelID="lblClient"runat="server"Text='<%#GetClient(Eval("c_id")) %>'/> </ItemTemplate></asp:TemplateField>

...

This then calls: GetClientName - Which is as follows.

...

Public

Function GetClientName(ByVal ClientID)Dim ScalarValueAsString =""Dim myConnectionAsNew SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")Dim myCommandAsNew SqlCommand("SELECT [Name] FROM [hd_clients] WHERE [c_id] = @.ClientID", myConnection)

myCommand.Parameters.Add(

"@.ClientID", SqlDbType.Int)

myCommand.Parameters(

"@.ClientID").Value = ClientID Try

myConnection.Open()

ScalarValue = myCommand.ExecuteScalar

Catch exAs Exception

Console.Write(ex.Message)

EndTry

If ScalarValue >""Then Return ScalarValue.ToString Else Return"<span style='color: #CCCCCC'>- NULL -</span>" EndIf

EndFunction

...

This works perfectly on my Laptop (which runs the IIS and SQL Server Instances + VS2005). But, when placed on our production server brings back the '- null -' value instead of the Client's Name. I have set both machines up in exaclty the same way - and cannot get this to work. I have tried 'ExecuteReader' but from what I understand is 'ExecuteScalar' is better for single value lookups.

Any help in this matter would be great and really appreciated. Thanks.

David

Dave_Winchester:

ScalarValue = myCommand.ExecuteScalar

Since ExecuteScalar returns and object, try converting the result to a string before assigning it to the local variable.

|||

Hi

Thanks for the help. Now fixed - Also changed the Exception handling to be a bit better.

Dave