SELECT ON GRID

MANEESH AN

13y1.6kVisual Basic .NET

ERROR FOUND" Column 'SerialNo' does not belong to table Table. AND IN CORRECT SYNATXX NEAR ';'

ALTER PROCEDURE [dbo].[prctblSalesInvoiceView]

(

@WhereCondition nvarchar(4000)

)

--prctblPurchaseInvoiceView ''

declare @SQLQuery nvarchar(4000);

BEGIN

set @SQLQuery='

select

iSerialNo

,iSalesID

,iAccID

,vAccName as ''CustomerName''

,vBillNo as ''BillNo''

,convert(varchar,dInvoiceDate,106) as ''InvoiceDate''

,vOldNew as OldNew

,deTotAmount as TotalAmount

,deRecAmount as RecAmount

,deBalAmount as BalAmount

,deMakChargesTotal as MakChargesTotal

from tblSales

+@WhereCondition

exec sp_executesql @SQLQuery

END

private void fnInvoiceDetailsrView(string searchvalue)

{

try

{

DataSet ds = new DataSet();

ds.Tables.Clear();

SqlDataAdapter sda = new SqlDataAdapter();

sda.SelectCommand = new SqlCommand("prctblSalesInvoiceView", ClsConnect.conSql);

sda.SelectCommand.CommandType = CommandType.StoredProcedure;

sda.SelectCommand.Parameters.AddWithValue("@WhereCondition", searchvalue);

string s = ClsConnect.conSql.State.ToString();

if (s == "Open")

{

ClsConnect.conSql.Close();

}

ClsConnect.conSql.Open();

sda.Fill(ds);

ClsConnect.conSql.Close();

if (ds.Tables[0].Rows.Count > 0)

{

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

{

ds.Tables[0].Rows[i]["SerialNo"] = i + 1;

}

dgvBillSearch.DataSource = ds.Tables[0];

dgvBillSearch.Columns[0].Width = 60;

dgvBillSearch.Columns[1].Visible = false;

dgvBillSearch.Columns[2].Visible = false;

dgvBillSearch.Columns[3].Width = 114;

dgvBillSearch.Columns[4].Width = 75;

dgvBillSearch.Columns[5].Width = 75;

dgvBillSearch.Columns[6].Width = 75;

dgvBillSearch.Columns[7].Width = 75;

dgvBillSearch.Columns[8].Width = 75;

dgvBillSearch.Columns[9].Width = 75;

}

catch (Exception ex)

{

fnInvoiceDetailsrView("");

// MessageBox.Show(ex.Message);

}

txtCustCodeName.Focus();

}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

venkata kumar13y ago
hi
in stored procedure your passing value from UI.
but your not checked condition with specific column.

ALTER PROCEDURE [dbo].[prctblSalesInvoiceView]
(
@WhereCondition nvarchar(4000)
)
as
--prctblPurchaseInvoiceView ''
declare @SQLQuery nvarchar(4000);
BEGIN

set @SQLQuery='

select

iSerialNo
,iSalesID
,iAccID
,vAccName as ''CustomerName''
,vBillNo as ''BillNo''
,convert(varchar,dInvoiceDate,106) as ''InvoiceDate''
,vOldNew as OldNew
,deTotAmount as TotalAmount
,deRecAmount as RecAmount
,deBalAmount as BalAmount
,deMakChargesTotal as MakChargesTotal

from tblSales where columnname=
'
+@WhereCondition

exec (@SQLQuery)

END
Sunny Sharma13y ago
Check the value that you're passing for @WhereCondition parameter. Do check that you have a semi-colon(;) after your where clause specified.
Iftikar has already answered for your primary error (column not found).
Iftikar Hussain13y ago
what value you have pass for @WhereCondition

Regards,
Iftikar
Iftikar Hussain13y ago
Hi,
try like this

private void fnInvoiceDetailsrView(string searchvalue)
{
try
{
DataSet ds = new DataSet();
ds.Tables.Clear();
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = new SqlCommand("prctblSalesInvoiceView", ClsConnect.conSql);
sda.SelectCommand.CommandType = CommandType.StoredProcedure;

sda.SelectCommand.Parameters.AddWithValue("@WhereCondition", searchvalue);
string s = ClsConnect.conSql.State.ToString();
if (s == "Open")
{
ClsConnect.conSql.Close();
}
ClsConnect.conSql.Open();
sda.Fill(ds);
ClsConnect.conSql.Close();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ds.Tables[0].Rows[i]["iSerialNo"] = i + 1;
}
dgvBillSearch.DataSource = ds.Tables[0];
dgvBillSearch.Columns[0].Width = 60;
dgvBillSearch.Columns[1].Visible = false;
dgvBillSearch.Columns[2].Visible = false;
dgvBillSearch.Columns[3].Width = 114;
dgvBillSearch.Columns[4].Width = 75;
dgvBillSearch.Columns[5].Width = 75;
dgvBillSearch.Columns[6].Width = 75;
dgvBillSearch.Columns[7].Width = 75;
dgvBillSearch.Columns[8].Width = 75;
dgvBillSearch.Columns[9].Width = 75;
}
}
catch (Exception ex)
{
fnInvoiceDetailsrView("");
// MessageBox.Show(ex.Message);
}
txtCustCodeName.Focus();
}

Regards,
Iftikar