System.Data.SqlClient.SqlException(0x80131904) : Incorrect syntax near '.'

Question

I am trying to populate more Information in order to make a Bank Statement have more information hence i decided to join two tables. This Query works well on SQL management studio. but if i use in Visual studio to create query and show data, it sends this Exception error

System.Data.SqlClient.SqlException(0x80131904) : Incorrect syntax near '.'.

On line 62

My code looks like this :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Reporting.WinForms;

namespace TmpZ
{
    public partial class BalanceSheet : Form
    {
        string constring = ConfigurationManager.ConnectionStrings["ConnData"].ConnectionString;
        public BalanceSheet()
        {
            InitializeComponent();
        }

        private void BalanceSheet_Load(object sender, EventArgs e)
        {

        }

        private void reportViewer1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (accountNo1.Text == "")
            {
                MessageBox.Show("Please Enter Account Number");
            }
            else
            {
                DataTable dtb = new DataTable();
                dtb = GenerateBankStatement(dtb);
                reportViewer1.LocalReport.DataSources.Clear();
                ReportDataSource rpd = new ReportDataSource("DataSet1", dtb);
                reportViewer1.LocalReport.DataSources.Add(rpd);
                reportViewer1.RefreshReport();
            }
        }

        private DataTable GenerateBankStatement(DataTable dt)
        {
            using (SqlConnection cn = new SqlConnection(constring))
            {
                try
                {
                    string dateF = Convert.ToDateTime(dateFrom.Text).ToString("dd-MM-yyyy");
                    string dateT = Convert.ToDateTime(dateTo.Text).ToString("dd-MM-yyyy");
                    //SqlDataAdapter da = new SqlDataAdapter("SELECT [id] as id, [transaction_desc] as transaction_desc,[credit] as credit, [debit] as debit, [balance] as balance, [transaction_date] as transaction_date FROM transactions WHERE(accountNo1 = '" + accountNo1.Text + "') AND(transaction_date BETWEEN '" + dateF + "' AND '" + dateT + "')", cn);
                    //SqlDataAdapter da = new SqlDataAdapter("SELECT [id] as id, [transaction_desc] as transaction_desc,[credit] as credit, [debit] as debit, [balance] as balance, [transaction_date] as transaction_date FROM transactions WHERE(accountNo1 = '" + accountNo1.Text + "')", cn);
                    SqlDataAdapter da = new SqlDataAdapter("SELECT [fullname] as account_info.fullname, [accountNo] as account_info.accountNo, [ccy] as account_info.ccy, [address] as account_info.address, [id] as transactions.id, [transaction_desc] as transactions.transaction_desc, [credit] as transactions.credit, [debit] as transactions.debit, [balance] as transactions.balance, [transaction_date] as transactions.transaction_date FROM  transactions CROSS JOIN account_info WHERE(account_info.accountNo = '" + accountNo1.Text + "') AND(transactions.transaction_date BETWEEN '" + dateF + "' AND '" + dateT + "')", cn);
                    da.Fill(dt);
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            return dt;
        }
    }
}

And line 62 shows the SQLDataAdapter. What did i do wrong?

Why do you put all the as in there? You could just go "SELECT account_info.fullname, account_info.accountNo..." and so on. I think, it should even work, if you just put "fullname" as long as the column name is only in one table. At least that is how I have done it before and it worked out fine. — Geshode
– Geshode, Commented Jan 19, 2018 at 7:40
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Jan 19, 2018 at 7:57

Ray Krungkaew · Accepted Answer · 2018-01-19 07:39:19Z

1

You cannot use . in an alias

SELECT [fullname] as account_info.fullname

I believe this is what you want

SELECT account_info.fullname as [fullname]

answered Jan 19, 2018 at 7:39

Ray Krungkaew

7,0071 gold badge19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TimTim Over a year ago

this works , but does not show the other info except the ones in the tables.

Ray Krungkaew Over a year ago

@TimTim what is the other info?

Collectives™ on Stack Overflow

System.Data.SqlClient.SqlException(0x80131904) : Incorrect syntax near '.'

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related