Display numeric and string type result in same cell of datagridview-CodePudding

I need to display the result of MS access Query in DataGridView as below

Select clbal,Drcr from Ledger

Result in one DataGridView Cell As 2000Dr

I Have tried

Select cstr(clbal) & Drcr as cbal from Ledger

but no value is displayed in DataGridView cell

CodePudding user response：

In your query

Select cstr(clbal) & Drcr as cbal from Ledger

You have a recursion with clbal. I.e., Access is confused by the two clbal once denoting the original value and once the calculated one. The query generates the exception:

Circular reference caused by alias 'cbal' in query definition's SELECT list.

Write

Select Ledger.clbal & Drcr As cbal From Ledger

or, with the name 2000Dr

Select clbal & Drcr As [2000Dr] From Ledger

If you want a space between the two fields:

Select clbal & ' ' & Drcr As [2000Dr] From Ledger

As @June7 commented, the string concatenation operator & automatically converts numeric values to string. Therefore, calling CStr() is not necessary.

CodePudding user response：

As pointed out, you are quite much free to "concentate" field together, but some caution required if the "NEW" or created colum has the same name as existing columns. As a general rule, you should try and use a different column name.

So, say we have a FirstName column, and LastName column.

But, we want to display as full name.

And for just demonstration, we also was to have a FirstName "id" as another column (this shows how a number PK can be combined with a string. As pointed out, you don't need (or in fact want to use cstr(), since that looks to be a VBA function anyway, and you can't use that in c#.

So, assuming a access table like this:

So, in above, the "ID" pk column is a number (long number in Access code/VBA , int in c#)

So, we can in the query builder say write this:

So, when you make a expression in the SQL, give the column a name (AS SomeFieldName).

Also, while Access SQL supports "&" or " " for concatenation, one should probably get in the habit of using " ", and the reason is that the VAST majority of SQL data engines don't support using & for concatenation, but do support " ".

So, if I run above, then I get this:

Now, say c#, asp.net (or whatever platoform you using with c#),

The we have say this markup:

        <asp:GridView ID="GridView1" runat="server" CssClass="table">
        </asp:GridView>

And our c# code to fill out the datatable (which then you can send to the above gridView, or your DataGridView.

The code will look like this:

    void LoadGrid()
    {
        string strSQL =
            @"SELECT ID, (FirstName   ' ' & LastName) AS FullName,
            (FirstName   '[' & ID & ']') AS FirstKey,
            HotelName, Description FROM tblHotelsA
            ORDER BY HotelName";

        DataTable rstData = MyRst(strSQL);
        GridView1.DataSource = rstData;
        GridView1.DataBind();    // databind only need for web grid

    }

    DataTable MyRst(string strSQL)
    {
        DataTable rstData = new DataTable();
        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
        {
            using (OleDbCommand cmdSQL = new OleDbCommand(strSQL, conn))
            {
                cmdSQL.Connection.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

And we get this: