Hi there everyone,
I wanted to use wildcards in a serach form which queries a SQL database for student attendance. I followed the guide found here to write the appropriate code, which works fine in the client application....in fact it works great!! My actual code is shown below:
public void btnQuery_Clicked(object sender, ClickedEventArgs e)
{
//Create an XPathNavigator object for the main data source
XPathNavigator xnMain = this.MainDataSource.CreateNavigator();
//Create an AdoQueryConnection from the main data source by "casting" the default
//data connection to an "AdoQueryConnection"
AdoQueryConnection cn = (AdoQueryConnection)this.DataConnections["Main connection"];
//Obtain the default SQL command for the form.
string strOrigSQLCommand = cn.Command.ToString();
// Obtain the query node that you want to change.
XPathNavigator xnAttendQuery = xnMain.SelectSingleNode("/dfs:myFields/dfs:queryFields/q:RegisterMarks", this.NamespaceManager);
//Obtain the text that was entered for the wildcard character search, and then clear the current query parameter so that InfoPath will leave the current query parameter blank.
string strStudRef = xnAttendQuery.SelectSingleNode("@StudRef", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@StudRef", this.NamespaceManager).SetValue(string.Empty);
//MessageBox.Show(strStudRef);
string strSurname = xnAttendQuery.SelectSingleNode("@Surname", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Surname", this.NamespaceManager).SetValue(string.Empty);
//MessageBox.Show(strSurname);
string strForenames = xnAttendQuery.SelectSingleNode("@Forenames", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Forenames", this.NamespaceManager).SetValue(string.Empty);
string strReference = xnAttendQuery.SelectSingleNode("@Reference", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Reference", this.NamespaceManager).SetValue(string.Empty);
string strName = xnAttendQuery.SelectSingleNode("@Name", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Name", this.NamespaceManager).SetValue(string.Empty);
string strDay = xnAttendQuery.SelectSingleNode("@Day", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Day", this.NamespaceManager).SetValue(string.Empty);
string strDate = xnAttendQuery.SelectSingleNode("@Date", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Date", this.NamespaceManager).SetValue(string.Empty);
string strCurrentStatus = xnAttendQuery.SelectSingleNode("@Current_status", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Current_status", this.NamespaceManager).SetValue(string.Empty);
string strMark = xnAttendQuery.SelectSingleNode("@Mark", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Mark", this.NamespaceManager).SetValue(string.Empty);
string strMarkDescription = xnAttendQuery.SelectSingleNode("@Mark_Description", this.NamespaceManager).Value;
xnAttendQuery.SelectSingleNode("@Mark_Description", this.NamespaceManager).SetValue(string.Empty);
//Have InfoPath construct an SQL command that is based on all other field values.
string strMySQLCommand = cn.BuildSqlFromXmlNodes(xnAttendQuery);
//Add Fields to the query so that Job_Title can support wildcard characters.
if (strStudRef != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strStudRef.Contains("%"))
strMySQLCommand += "[StudRef] LIKE '" + strStudRef + "'";
else
strMySQLCommand += "[StudRef] LIKE '" + strStudRef + "%'";
}
//Check Surname
//MessageBox.Show(strSurname);
if (strSurname != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strSurname.Contains("%"))
strMySQLCommand += "[Surname] LIKE '" + strSurname + "'";
else
strMySQLCommand += "[Surname] LIKE '" + strSurname + "%'";
}
if (strForenames != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strForenames.Contains("%"))
strMySQLCommand += "[Forenames] LIKE '" + strForenames + "'";
else
strMySQLCommand += "[Forenames] LIKE '" + strForenames + "%'";
}
if (strReference != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strReference.Contains("%"))
strMySQLCommand += "[Reference] LIKE '" + strReference + "'";
else
strMySQLCommand += "[Reference] LIKE '" + strReference + "%'";
}
if (strName != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strName.Contains("%"))
strMySQLCommand += "[Name] LIKE '" + strName + "'";
else
strMySQLCommand += "[Name] LIKE '" + strName + "%'";
}
if (strDay != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strDay.Contains("%"))
strMySQLCommand += "[Day] LIKE '" + strDay + "'";
else
strMySQLCommand += "[Day] LIKE '" + strDay + "%'";
}
if (strDate != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strDate.Contains("%"))
strMySQLCommand += "[Date] LIKE '" + strDate + "'";
else
strMySQLCommand += "[Date] LIKE '" + strDate + "%'";
}
if (strCurrentStatus != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strCurrentStatus.Contains("%"))
strMySQLCommand += "[Current status] LIKE '" + strCurrentStatus + "'";
else
strMySQLCommand += "[Current status] LIKE '" + strCurrentStatus + "%'";
}
if (strMark != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strMark.Contains("%"))
strMySQLCommand += "[Mark] LIKE '" + strMark + "'";
else
strMySQLCommand += "[Mark] LIKE '" + strMark + "%'";
}
if (strMarkDescription != string.Empty)
{
if (strMySQLCommand != string.Empty)
strMySQLCommand += " AND ";
//Check whether the user entered the wildcard character (%) as part of the title.
if (strMarkDescription.Contains("%"))
strMySQLCommand += "[Mark Description] LIKE '" + strMarkDescription + "'";
else
strMySQLCommand += "[Mark Description] LIKE '" + strMarkDescription + "%'";
}
//Construct the full query string.
string strSQLQuery = strOrigSQLCommand;
if (strMySQLCommand != string.Empty)
strSQLQuery += " WHERE " + strMySQLCommand;
//MessageBox.Show(strSQLQuery);
//Set the command and run the query.
cn.Command = strSQLQuery;
cn.Execute();
//Restore all the user entries to the Query fields so that the user entries will
//be available if you want to change and to rerun the query.
xnAttendQuery.SelectSingleNode("@StudRef", this.NamespaceManager).SetValue(strStudRef);
xnAttendQuery.SelectSingleNode("@Surname", this.NamespaceManager).SetValue(strSurname);
xnAttendQuery.SelectSingleNode("@Forenames", this.NamespaceManager).SetValue(strForenames);
xnAttendQuery.SelectSingleNode("@Reference", this.NamespaceManager).SetValue(strReference);
xnAttendQuery.SelectSingleNode("@Name", this.NamespaceManager).SetValue(strName);
xnAttendQuery.SelectSingleNode("@Day", this.NamespaceManager).SetValue(strDay);
xnAttendQuery.SelectSingleNode("@Date", this.NamespaceManager).SetValue(strDate);
xnAttendQuery.SelectSingleNode("@Current_status", this.NamespaceManager).SetValue(strCurrentStatus);
xnAttendQuery.SelectSingleNode("@Mark", this.NamespaceManager).SetValue(strMark);
xnAttendQuery.SelectSingleNode("@Mark_Description", this.NamespaceManager).SetValue(strMarkDescription);
//Restore the default table command (for the next time).
cn.Command = strOrigSQLCommand;
//Clean up
xnMain = null;
cn = null;
xnAttendQuery = null;
}
Like I say..this worked fine.......until I converted the connection file and published to a SharePoint library. I have approved the connection file, I have tried using a SSO mapping to run the connection as Administrator...still no luck.
I just get the error saying the data source could not be connected to.
I assume I am referencing the connection in my code in a way that is not supported in published connections.
I would really appreciate some help with this.
Thank you very much in advance of your assistance.
Cheers
Chris