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:TemplateField HeaderText="Client" SortExpression="c_id">

   <ItemTemplate>

      <asp:Label ID="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 ScalarValue As String = ""

Dim myConnection As New SqlConnection("Data Source=XXX; Initial Catalog=XXX; uid=XXX; pwd=XXX")

Dim myCommand As New 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 ex As Exception

      Console.Write(ex.Message)

   End Try

 

   If ScalarValue > "" Then

      Return ScalarValue.ToString

   Else

      Return "<span style='color: #CCCCCC'>- NULL -</span>"

   End If



 

End Function

...

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