Trying to read the SQL Server database for the first time, but SqlDataAdapter.Fill (DataTable) breaks it down

advertisements

This is my first time using a database.

This is my database, it uses just Windows Authentication.

And I'm using the following code to try to connect to it and gather data:

        string connectionString =   "user id=USERNAME;" +
                                    "server=SERVERNAME;" +
                                    "Trusted_Connection=yes;" +
                                    "database=claytonDatabase; " +
                                    "connection timeout=30";

        try
        {
            SqlConnection thisConnection = new SqlConnection(connectionString);
            thisConnection.Open();

            string Get_Data = "SELECT * FROM dbo.location";

            SqlCommand cmd = new SqlCommand(Get_Data);

            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("locationTable");

            sda.Fill(dt); // this line kills it

            foreach (DataRow dataRow in dt.Rows)
            {
                foreach (var item in dataRow.ItemArray)
                {
                    Console.WriteLine(item);
                }
            }
        }
        catch(Exception ex)
        {
            MessageBox.Show("db error" + ex.ToString());
        }

The sda.Fill(dt); line is what breaks the program, resulting in this error if I don't use the try statement:

An unhandled exception of type 'System.Windows.Markup.XamlParseException' occurred in PresentationFramework.dll

Additional information: 'The invocation of the constructor on type 'clayton.MainWindow' that matches the specified binding constraints threw an exception.' Line number '3' and line position '9'.

If there is a handler for this exception, the program may be safely continued.

And this if I do use the try:

at line 0</StackTrace><ExceptionString>System.Windows.Markup.XamlParseException: 'The invocation of the constructor on type 'clayton.MainWindow' that matches the specified binding constraints threw an exception.' Line number '3' and line position '9'. ---&amp;gt; System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.
   at System.Data.Common.DbDataAdapter.GetConnection3(DbDataAdapter adapter, IDbCommand command, String method)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at clayton.MainWindow.ConnectToDatabase() in c:\Users\cclayto\Desktop\C#\clayton\clayton\MainWindow.xaml.cs:line 87
   at clayton.MainWindow..ctor() in c:\Users\cclayto\Desktop\C#\clayton\clayton\MainWindow.xaml.cs:line 34
   --- End of inner exception stack trace ---

I'm not sure how to even test if the problem is with the Fill command or if I'm not actually connecting successfully at all to the SQL database. Any suggestions would be very helpful!


You never set the connection string on the CMD object you create

cmd.Connection = thisConnection

The error message, although somewhat cryptic, does tell you what the error was...

at line 0System.Windows.Markup.XamlParseException: 'The invocation of the constructor on type 'clayton.MainWindow' that matches the specified binding constraints threw an exception.' Line number '3' and line position '9'. ---&gt; System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.