C# SqlDataAdapter.Fill giving error about data conversion when passed datatable parameter

Question

I'm trying to fill a datatable using a SqlDataAdapter in C#. I'm not very familiar with the objects, and am basically working off a template of someone else's code to try to figure out how it works. Here's the basic form.

        SqlCommand command = new SqlCommand(@"SELECT * FROM tblEmployees WHERE Name = " + firstSSN,connection);

        SqlDataAdapter adapter = new SqlDataAdapter(command.CommandText.ToString(), connection.ConnectionString.ToString());

        SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
        DataTable table = new DataTable();
        table.Locale = System.Globalization.CultureInfo.InvariantCulture;
        adapter.Fill(table);

And that works fine on their form. I tried doing the same with mine, but got an error about trying to convert a nvarchar to a column of data type int. I looked through MSDN and tried the following: 1) Adding the columns to the DataTable with the appropriate names/types/primary keys from tblEmployees. 2) Adding the TableMapping to the DataAdapter, though I'm not 100% sure I have the syntax on this part right. I do:

adapter.TableMappings.Add("work", "dbo.tblEmployees");

for that. The DataTable is named "work", but I'm unsure if I have the syntax right, I put in the table name as it appears in SQL Server Management Studio but don't know how I test if it's linking up correctly.

Thanks for any help you can provide. I've been beating my head on this to the point that I'm on the verge of approaching my goal an entirely different way and throwing away what I have.

Answer 1

At first glance, when looking at the first line of your code, look where firstSSN is:

@"Select * From tblEmployees WHERE Name = " + firstSSN

I would imagine that 'Name' is the nvarchar field that the error message is referencing, and if firstSSN is an integer value only, try putting '' marks around firstSSN like this:

@"Select * From tblEmployees WHERE Name = '" + firstSSN + "'"

Otherwise, make sure that the type of the field "Name" matches the type of the parameter you are passing into your command. This is most likely just a data type issue.

One recommendation I have for you - I just started experimenting with LINQ the other day, and find it pretty handy to use for pulling out, manipulating, and filtering data into an easy to use record set. There's all kinds of documentation available on how to execute queries with LINQ, so have a look if you have a few minutes. Hope this helps.