Error when do a 'select'

turbot_yu
turbot_yu used Ask the Experts™
on
I made a small program running on PDA to check data inside SQL Server database on the server.

When program runup, it will excutive 'SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'' to get all the table list. It runs successful.

After that I manaully key in a SQL(select * from station) in a text window and run it, then got error as below.
Any suggestion?
The C# project code also attached.

   at System.Data.SqlClient.TdsParser.SkipRow(_SqlMetaData[] columns, Int32 startCol)
   at System.Data.SqlClient.TdsParser.SkipRow(_SqlMetaData[] columns)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)
   at System.Data.SqlClient.SqlDataReader.InternalClose(Boolean closeReader)
   at System.Data.SqlClient.SqlDataReader.Close()
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   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 Database_Connection.PDADataSetTableAdapters.StationTableAdapter.Fill(StationDataTable dataTable)
   at Database_Connection.Form1.Form1_Load(Object sender, EventArgs e)
   at System.Windows.Forms.Form.OnLoad(EventArgs e)
   at System.Windows.Forms.Form._SetVisibleNotify(Boolean fVis)
   at System.Windows.Forms.Control.set_Visible(Boolean value)
   at System.Windows.Forms.Application.Run(Form fm)
   at Database_Connection.Program.Main()
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
your c# code is not here but maybe you should try this instead

'SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' '
that one is ok, only the simple one not work, attached again.

Author

Commented:
namespace FW2WM5
{
    public partial class Form1 : Form
    {
        SqlConnection myConn;
        String connStr;

        public Form1()
        {
            InitializeComponent();
            connStr =
                @"Server='203.117.170.74';Database=PDA; User Id=x; Password=x";
        }

        private void button10_Click(object sender, EventArgs e)
        {
            try
            {
                myConn = new SqlConnection(connStr);
                myConn.Open();
                FillList();
            }

            catch (Exception ex)
            {
                MessageBox.Show("Connection error: " + ex.ToString());
            }

            finally
            {
                myConn.Close();
            }
        }

        private void FillList()
        {
            //SqlDataReader myDataReader;
            SqlDataAdapter myAdapter = new SqlDataAdapter(txtSQL.Text, myConn);
            DataSet myDataSet = new DataSet();
            myAdapter.Fill(myDataSet, "Result");
            // dataGridView1.DataSource = myDataSet.DefaultViewManager;
            dataGrid1.DataSource = myDataSet.Tables["Result"].DefaultView;

        }

        private void FillObject()
        {
            try
            {
                myConn = new SqlConnection(connStr);
                myConn.Open();
                SqlDataReader myDataReader;
                SqlCommand myCmd = myConn.CreateCommand();

                myCmd.CommandText = @"SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
                treeView1.BeginUpdate();
                treeView1.Nodes.Clear();
                treeView1.Nodes.Add(new TreeNode("Tables"));
                TreeNode childNode = treeView1.Nodes[0];

                int childCount = 0;
                myDataReader = myCmd.ExecuteReader();
                while (myDataReader.Read())
                {
                    string TableName = (myDataReader[0].ToString()) + "." + (myDataReader[1].ToString());
                    childNode.Nodes.Insert(childCount, new TreeNode(TableName));
                    childCount += 1;
                }
                myDataReader.Close();
                treeView1.Nodes.Add(new TreeNode("Views"));

                SqlCommand myCmdView = myConn.CreateCommand();
                myCmdView.CommandText = @"SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW'";
                TreeNode childNodeView = treeView1.Nodes[1];

                int childCountView = 0;

                myDataReader = myCmdView.ExecuteReader();
                while (myDataReader.Read())
                {
                    string ViewName = (myDataReader[0].ToString()) + "." + (myDataReader[1].ToString());
                    childNodeView.Nodes.Insert(childCount, new TreeNode(ViewName));
                    childCountView += 1;
                }

                myDataReader.Close();


                treeView1.ExpandAll();
                treeView1.EndUpdate();
                myConn.Close();
            } // try
            catch (SqlException myexception)
            {
                foreach (SqlError err in myexception.Errors)
                {
                    MessageBox.Show(err.Message);

                }

            } // catch

        }
        private void Form1_Load(object sender, EventArgs e)
        {
            //if (PDADataSetUtil.DesignerUtil.IsRunTime())
            //{
                // TODO:  ddLãåûdpDADataSet.Station„ؤ AutoFill
                //this.stationTableAdapter.Fill(this.pDADataSet.Station);

            //}
            FillObject();

        }

        private void textBox2_Click(object sender, EventArgs e)
        {

        }

        private void txtSQL_TextChanged(object sender, EventArgs e)
        {

        }

        private void stationNameComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }


    }
}
int.jpg

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial