Working on adding something to a site that someone else built in Webmatrix. When I alter the SQL Query it throws a syntax error, even though it worked fine before. I've been beating my head against the wall with this error.
All I did was add: WHERE ub_acct.afac_status ='A'to the end of the SQL Query
Server Error in '/' Application.
Incorrect syntax near 'afac_acct_no'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'afac_acct_no'.
Source Error:
foreach (var row in db.Query(selectQueryString, pnumb, accountNo, serviceAddr, address1, address2, drvID, rgtrID, trnsmtrID))
The code is:
@{
string selectQueryString = "";
var db = Database.Open("derpdatabase");
var output = "";
bool search = false;
string pnumb = "";
string accountNo = "";
string serviceAddr = "";
string address1 = "";
string address2 = "";
string drvID = "";
string rgtrID = "";
string trnsmtrID = "";
int pnumbNo = 0;
int accountNoNo = 0;
int drvid = 0;
int rgtrid = 0;
int trnsmtrid = 0;
if(IsPost)
{
pnumb = Request.Form["pnumb"].Trim();
accountNo = Request.Form["accountNo"].Trim();
serviceAddr = Request.Form["serviceAddr"].Trim();
address1 = Request.Form["address1"].Trim();
address2 = Request.Form["address2"].Trim();
drvID = Request.Form["drvID"].Trim();
if (pnumb.IsInt())
{
pnumbNo = int.Parse(pnumb);
}
if (accountNo.IsInt())
{
accountNoNo = int.Parse(accountNo);
}
if (drvID.IsInt())
{
drvid = int.Parse(drvID);
}
if (rgtrID.IsInt())
{
rgtrid = int.Parse(rgtrID);
}
if (trnsmtrID.IsInt())
{
trnsmtrid = int.Parse(trnsmtrID);
}
if ( pnumbNo != 0 || accountNoNo != 0 || serviceAddr != "" || address1 != "" || address2 != "" || drvid !=0 || rgtrid !=0 || trnsmtrid !=0)
{
selectQueryString = "SELECT afac_pnumb, afac_acct_no, afac_service_addr, afac_addr1, afac_addr2, afmr_driver_id, afmr_touchread_register, afmr_mxu FROM ub_acct JOIN ub_driver_trans ON ub_acct.afac_pnumb = ub_driver_trans.afm_trans_pnumb JOIN ub_driver_register ON ub_driver_trans.afm_driver_id = ub_driver_register.afmr_driver_id WHERE ub_acct.afac_status ='A'";
search = true;
}
if (pnumbNo != 0)
{
selectQueryString += "afac_pnumb = @0 AND ";
}
if (accountNoNo != 0)
{
selectQueryString += "afac_acct_no = @1 AND ";
}
if (!String.IsNullOrWhiteSpace(serviceAddr))
{
selectQueryString += "afac_service_addr LIKE '%' + CAST(@2 AS nvarchar) + '%' AND ";
}
if (!String.IsNullOrWhiteSpace(address1))
{
selectQueryString += "afac_addr1 LIKE '%' + CAST(@3 AS nvarchar) + '%' AND ";
}
if (!String.IsNullOrWhiteSpace(address2))
{
selectQueryString += "afac_addr2 LIKE '%' + CAST(@4 AS nvarchar) + '%' AND ";
}
if (drvid != 0)
{
selectQueryString += "afmr_driver_id = @5 AND ";
}
if ( rgtrid != 0)
{
selectQueryString += "afmr_touchread_regidter = @6 AND ";
}
if (trnsmtrid != 0)
{
selectQueryString += "afm_mxu = @7 AND ";
}
if (selectQueryString.EndsWith(" AND "))
{
selectQueryString = selectQueryString.Remove(selectQueryString.Length - 5);
}
}
}
<div id="topBar"></div>
<div id="title" class="centerInline centerBlock heavyNarrowFont brownText fillWidth">CIC Search</div><br /><br />
<div id="searchBody" class="centerBlock centerInline fillWidth">
<form id="searchForm" class="centerInline centerBlock fillWidth" method="post" action="">
<table id="searchTable" class="centerInline centerBlock fillWidth">
<tr>
<td class="left">pnumb:</td>
<td class="right"><input id="pnumb" name="pnumb" type="text" maxlength="4" /></td>
</tr>
<tr>
<td class="left">Account #:</td>
<td class="right"><input id="accountNo" name="accountNo" type="text" maxlength="5" /></td>
</tr>
<tr>
<td class="left">Service Address:</td>
<td class="right"><input id="serviceAddr" name="serviceAddr" type="text" maxlength="75" /></td>
</tr>
<tr>
<td class="left">Address 1:</td>
<td class="right"><input id="address1" name="address1" type="text" maxlength="75" /></td>
</tr>
<tr>
<td class="left">Address 2:</td>
<td class="right"><input id="address2" name="address2" type="text" maxlength="75" /></td>
</tr>
<tr>
<td class="left"><span class="drvID">Driver ID:</span></td>
<td class="right"><input id="drvID" name="drvID" type="text" maxlength="16" /></td>
</tr>
<tr>
<td id="searchBtnCell" class="centerInline" colspan="2">
<br />
<br />
<input type="submit" value="Search" class="button centerBlock" />
</td>
</tr>
</table>
</form><br />
<div id="resultContainer">
@if (IsPost && search == true)
{
<hr /><br />
foreach (var row in db.Query(selectQueryString, pnumb, accountNo, serviceAddr, address1, address2, drvID, rgtrID, trnsmtrID))
{
<div class="result fillWidth grayGradient">
<span class="resultLabel">pnumb #: </span><span class="resultValue"> @row.afac_pnumb</span><br />
<span class="resultLabel">Account #: </span><span class="resultValue"> @row.afac_acct_no</span><br />
<span class="resultLabel">Service Address: </span><span class="resultValue"> @row.afac_service_addr</span><br />
<span class="resultLabel">Address 1: </span><span class="resultValue"> @row.afac_addr1</span><br />
<span class="resultLabel">Address 2: </span><span class="resultValue"> @row.afac_addr2</span><br />
<span class="resultLabel">Driver ID: </span><span class="resultValue"> @row.afmr_driver_id</span><br />
<span class="resultLabel">Register ID: </span><span class="resultValue"> @row.afmr_touchread_register</span><br />
<span class="resultLabel">Transmitter ID: </span><span class="resultValue"> @row.afmr_mxu</span><br />
</div>
}
}
</div>
</div>
<br /><br />
