The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 03 Nov 08 9:12 PM
|
I am having a problem with executing a SQL statement that works fine in ADO Explorer but fails with an ADOException error using IList\NHibernate. I am not doing anything with the data yet… Need to get past this part first.
----Here is the error----
{"could not execute query\r\n[ SELECT UserText1 FROM UserProfile WHERE UserID = 'ADMIN' ]\r\n[SQL: SELECT UserText1 FROM UserProfile WHERE UserID = 'ADMIN' ]"}
-------------------------
----Here is the code snippet from inside Visual Studio----
protected void cmdTest_ClickAction(object sender, EventArgs e) {
Sage.SalesLogix.Security.SLXUserService CurUser = Sage.Platform.Application.ApplicationContext.Current.Services.Get() as Sage.SalesLogix.Security.SLXUserService;
string mCurUserID = CurUser.UserId.Trim();
NHibernate.ISession session = new Sage.Platform.Orm.SessionScopeWrapper();
string mySQL = "SELECT UserText1 FROM UserProfile WHERE UserID = '" + mCurUserID + "' ";
//Error occurs on this line
IList list = session.CreateSQLQuery(mySQL.ToString()).AddScalar("UserText", NHibernate.NHibernateUtil.AnsiChar).List();
if (list != null)
{
int myCount = int.Parse(list[0].ToString());
if (myCount != 0)
{
this.DialogService.ShowMessage("Record Found");
}
}
}
---------------------
Does anyone see anything out of whack?
--Ken--
|
|
|
| |
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 04 Nov 08 3:24 PM
|
Thanks for the response Nick... I changed the line to
--------------
string mySQL = "SELECT Usertext1 FROM UserProfile WHERE UserId = '" + mCurUserID + "' ";
--------------
...but am still getting the same error.
In my testing I did find something strange. When I look at the Packages>SalesLogix Security Support>UserProfile>Properties in AA, I see all the properties including all the Usertext# and Usernum# fields. However, when if I try to do something like this in Visual Studio
---------------------------
Sage.Entity.Interfaces.IUserProfile uProf;
uProf.[the dropdown]
---------------------------
When I look at the drop down list, I only see a limited amount of properties like SSN, Birthdate, EmployeeNumber, etc… However, the dropdown is missing all the Usertext# and Usernum# fields. I try to RMB and on the UserProfile in AA and ‘Update Properties’ but it shows all the Usertext# and Usernum# as already having an ‘Existing’ status.
So anyone have any clues?
--Ken--
|
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 04 Nov 08 4:57 PM
|
Ok... Now I went and broke it 
As mentioned before, when I was in VS and created an entity for UserProfile I did not see all the UserDef# fields listed. I did see some like SSN, EmployeeNumber, etc… So I know it was seeing something.
------------------
Sage.Entity.Interfaces.IUserProfile uProf;
uProf.[not all the properties showed up]
------------------
I look in the AA under UserProfile and it showed all the properties including the UserDef# fields… I thought, ok… I just drop and recreate them. I did that and now when I was in VS and tried the little script above it showed all the UserDef# properties that I was looking for before. GREAT !!!! Mission Accomplished.
Now I went back to AA and rebuilt\re-deployed the site and now get this right after I login. Mission Impossible
---------------------------
Method 'get_UserDate1' in type 'Sage.SalesLogix.Security.UserProfile' from assembly 'Sage.SalesLogix.Security, Version=7.2.2.1871, Culture=neutral, PublicKeyToken=null' does not have an implementation.
---------------------------
I am not sure how to recover besides trying to bring the UserProfile entity back from a backup through a bundle or something.
OMG, it should not be this difficult should it…
Can someone provide any advice, or maybe a bullet?
---Ken---
|
|
|
| |
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 05 Nov 08 4:33 AM
|
Yes it does look like the usertext properties do not actually exist for the userprofile entity - which means HQL will fail. I know its best practice to use HQL, but in some circumstances it is necessary to just use SQL. I dont know what you are trying to once you get the data but can it be achieved in SQL?
Cheers,
Nick |
|
|
| |
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 05 Nov 08 5:00 PM
|
Ken, here is a code snip I used in a previous project. This connection is pretty slick as you don't need and usernames or passwords.
Sage.Platform.Data.IDataService data = Sage.Platform.Application.ApplicationContext.Current.Services.Get();
System.Data.OleDb.OleDbConnection objConnection = (System.Data.OleDb.OleDbConnection)data.GetConnection();
string strTeamId = "";
if (objConnection != null)
{
string strSQLQuery = "SELECT SECCODEID, SECCODEDESC " +
"FROM SECCODE " +
"WHERE (SECCODEDESC = 'Consumer Support')";
System.Data.OleDb.OleDbCommand objCommand = new System.Data.OleDb.OleDbCommand(strSQLQuery, objConnection);
System.Data.OleDb.OleDbDataAdapter objAdapter = new System.Data.OleDb.OleDbDataAdapter(objCommand);
System.Data.DataSet dsResult = new System.Data.DataSet();
objAdapter.Fill(dsResult);
if (dsResult != null)
{
System.Data.DataTable dtTeam = dsResult.Tables[0];
if (dtTeam.Rows.Count > 0)
{
strTeamId = dtTeam.Rows[0]["SECCODEID"].ToString();
}
}
if (objConnection.State == System.Data.ConnectionState.Open)
{
objConnection.Close();
}
} |
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 06 Nov 08 5:30 AM
|
Hi Ken,
This is also an option, a bit less overhead than previous example. Datasets are good to work with in certain circumstances but to get one record out of the db (as it looks like what you are trying to do) can be done like:
string yourVal = string.Empty;
string sql - string.Empty;
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr))
{
conn.Open();
using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand())
{
sql = "SELECT USERTEXT1 FROM USERPROFILE WHERE USERID = 'ADMIN'";
cmd.Connection = conn;
cmd.CommandText = sql;
object coverName = cmd.ExecuteScalar() }
}
|
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 06 Nov 08 5:40 AM
|
Hi Ken,
This is also an option, a bit less overhead than previous example. Datasets are good to work with in certain circumstances but to get one record out of the db (as it looks like what you are trying to do) can be done like:
|string yourVal = string.Empty;
|string sql = string.Empty;
| Sage.Platform.Data.IDataService service = Sage.Platform.Application.ApplicationContext.Current.Services.Get();
| string connStr = service.GetConnectionString();
| using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr))
| {
| conn.Open();
| using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand())
| {
| sql = "SELECT USERTEXT1 FROM USERPROFILE WHERE USERID = 'ADMIN'";
| cmd.Connection = conn;
| cmd.CommandText = sql;
| yourVal = cmd.ExecuteScalar().ToString();
| }
| }
May have to check for nulls on the ExecuteScalar, cant remember! 
Just in case you wondering, you could also do a simple update using the same method..ie.
|string sql = string.Empty;
| Sage.Platform.Data.IDataService service = Sage.Platform.Application.ApplicationContext.Current.Services.Get();
| string connStr = service.GetConnectionString();
| using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr))
| {
| conn.Open();
| using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand())
| {
| sql = "UPDATE SOMETHING SET VALUE = 'SEOMTHING' WHERE SOMETHING = SOMETHING";
| cmd.Connection = conn;
| cmd.CommandText = sql;
| cmd.ExecuteNonQuery();
| }
| }
Useful to know I guess. The syntax is off the top of my head so dont shoot me if you get red lines in VS 
The using statements will automagically close your connections and objects for you as well, which is pretty neat. |
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 06 Nov 08 11:37 AM
|
Thanks for the response Steve and Nick.
Just an FYI: I noticed the ‘Include’ checkbox was not selected on the UserProfile entity in AA for the UserDef# fields. I was like, WoooHooo… Easy enough. …NOT. I went ahead and set all the ‘Includes’ but then the Web Client choked right after the login with
-----
Method 'get_Usertext1' in type 'Sage.SalesLogix.Security.UserProfile' from assembly 'Sage.SalesLogix.Security, Version=7.2.2.1871, Culture=neutral, PublicKeyToken=null' does not have an implementation.
-----
However when I dropped back into VS, I now saw all the UserDef# fields that I was looking for. This obviously didn’t help much though since it breaks the Web Client.
I am now going to try your examples above using straight SQL
Thanks again for the help
|
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 06 Nov 08 11:38 AM
|
So Where I Am At Now
….So I am now trying your way to issues a straight SQL statement to retrieve the data using you example you provided. THANK YOU…
I am running into a small hurdle with the code though. The line:
-------
Sage.Platform.Data.IDataService service = Sage.Platform.Application.ApplicationContext.Current.Services.Get();
-------
Gives me an error
-------
The type arguments for method Sage.Platform.Application.ServiceCollection.Get()' cannot be inferred from the usage. Try specifying the type arguments explicitly.
-------
Am I missing an include or something. I have this running as a C# script right off a button on the form. Do I need to make this as a business rule? Or am I missing something?
|
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 06 Nov 08 12:07 PM
|
Originally posted by Ken Poggensee
I am running into a small hurdle with the code though. The line:
-------
Sage.Platform.Data.IDataService service = Sage.Platform.Application.ApplicationContext.Current.Services.Get();
-------
Gives me an error
-------
The type arguments for method Sage.Platform.Application.ServiceCollection.Get()' cannot be inferred from the usage. Try specifying the type arguments explicitly.
|
|
The forums will intrerpret angle-brackets as tags, so you were missing some of the code that Nick posted. You want that line to look like this:
Sage.Platform.Data.IDataService service = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Data.IDataService>();
Change that and give it a try. |
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 13 Nov 08 9:52 AM
|
Just want to make another post and say THANK YOU to everyone. I got everything to work as I needed. You guys rock and have been an tremendous help through my learning curve.
For anyone elses reference, the code below is what I used for executing a raw SQL statement.
BTW: Is it ok to use the try\catch the way I did to check and see if the record is there or not?
----------------------------
Sage.Platform.Data.IDataService service = Sage.Platform.Application.ApplicationContext.Current.Services.Get();
//Get userid
Sage.Platform.Security.IUserService userService = Sage.Platform.Application.ApplicationContext.Current.Services.Get();
string curUserID = userService.UserId;
curUserID = curUserID.Trim();
//Get connection string
string connStr = service.GetConnectionString();
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr))
{
//Open connection
conn.Open();
using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand())
{
//Build and execute SQL
sql = "SELECT USERID FROM USERPROFILE WHERE USERID = '" + curUserID + "' AND USERTEXT1 LIKE '%ApproveSOAR%'";
cmd.Connection = conn;
cmd.CommandText = sql;
try
{
//If the statements executes then there is a value
yourVal = cmd.ExecuteScalar().ToString();
rgApprove.Enabled = true;
}
catch
{
//SQL returned a null. There is no ApproveSOAR in UserText1
rgApprove.Enabled = false;
}
|
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 14 Nov 08 2:23 PM
|
Originally posted by Ken Poggensee
BTW: Is it ok to use the try\catch the way I did to check and see if the record is there or not? |
|
One thing to keep in mind is that exceptions are expensive. If you can check for something and avoid an exception being thrown and caught then it is always a far better route as things will perform *much* better.
In your code, what is throwing the exception is the use of ToString on the returned value in cases where there was no returned value. So, instead of doing this:
try
{
//If the statements executes then there is a value
yourVal = cmd.ExecuteScalar().ToString();
rgApprove.Enabled = true;
}
catch
{
//SQL returned a null. There is no ApproveSOAR in UserText1
rgApprove.Enabled = false;
}
do something more like this:
object o = cmd.ExecuteScalar().ToString();
if (o != null) yourVal = o.ToString();
rgApprove.Enabled = (o != null);
A simple test for null should get past that without the exception being thrown.
-Ryan
|
|
|
| |
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 16 Apr 09 2:24 PM
|
Hi, sorry if is an old post.
But im trying to do a grid with a custom query. I little bit complicated to do it with QUery Builder and i was trying to do this inside a Bussines Rule but i don't have the System.Data namespace.
How can i try this?
thanks. |
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 16 Apr 09 2:54 PM
|
For a business rule try to mirror something like the sample script below, Then your datasource on your form uses the getByMethod to reference your business rule (GetTemplateOptions in my case). Not sure about your System.Data.namespace, but I figured this would give you a direction...
--Ken--
---------------------------------------
public static void GetTemplateOptions( IBidSpec bidspec, out object result)
//be sure the line above says 'out onject'
{
if (bidspec.TemplateLink != null)
{
//Create obj for what you want to return (iTemplateOptions in my case)
IRepository repository = EntityFactory.GetRepository();
//Open expression factory and get ready to add conditions
IQueryable qry = (IQueryable)repository;
IExpressionFactory ef = qry.GetExpressionFactory();
ICriteria criteria = qry.CreateCriteria();
//add you conditions here.
criteria.Add(ef.Eq("Templateheaderid", bidspec.TemplateLink.Id.ToString()));
result = criteria.List();
}
}
|
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 16 Apr 09 2:59 PM
|
Hi, Ken. Thanks for your reply.
I already been fighting with this 'cause my query is not simple. Here it is. I dont know how to build this. I've been trying to do a simple query but nothing is coming from the query. Do you know if the fields on the Grid have to match the ones that the query is using? thanks again
"Select C.CampaignName as Campaign, " +
"C.Status, C.StartDate As [Start Date], C.EndDate As [End Date], " +
"(Select Count(Distinct(EntityId)) From Campaign C Inner Join CampaignTarget ct " +
"On c.CampaignId = ct.CampaignId Inner Join Contact con On con.ContactId = ct.EntityId " +
"Where con.AccountId = '" + account.Id.ToString() + "') as [# Contacts Targeted], " +
"'' as [# Contacts w/Trans], " +
"(Select Count(Distinct(ProductId)) From Campaign C Inner Join CampaignProduct cp " +
"On c.CampaignId = cp.CampaignId Inner Join Contact con On con.ContactId = ct.EntityId " +
"Where con.AccountId = '" + account.Id.ToString() + "') as Products, '' as [# Transactions], " +
"'' as Sales From Campaign C Inner Join CampaignTarget ct " +
"On C.CampaignId = ct.CampaignId Where ct.EntityId = '" + account.Id.ToString() + "'" |
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 16 Apr 09 3:35 PM
|
Yup, you got a lot of stuff going on in there. You might want to try to dump that right into you data source object like you were trying to do before. But then it comes back to your system.data namespace issue you were having...
I have not used this approach yet so maybe somebody else will chime in. You might also want to scroll up a few postings to Steve Knowles response, It looks similar to what he is doing there.
--Ken-- |
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 16 Apr 09 3:39 PM
|
BTW: I saw you were doing a lot of sums there to... Here is a BR that will return a count for all the primary contact of an account. (yes there should only be one, but...)
Don't think it helps with your situation, but I thought I'd throw it out there.
---------------------
int result = qry.CreateCriteria()
.Add(ef.Eq("Account", account))
.Add(ef.Eq("IsPrimary", true))
.SetProjection(proj.Count(“Id”))
.UniqueResult();
|
|
|
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 05 May 09 11:47 AM
|
Ken,
I used your final example above to be able to run a SQL Select query, but I am getting the error below when I build.
"The type or namespace name 'Data' does not exist in the namespace 'System' (are you missing an assembly reference?)"
The error occurs on any line that i use "System.Data."
I have the following in my Using section. Did I miss one I need?
using System;
using Sage.Entity.Interfaces;
using Sage.Form.Interfaces;
Thanks!
|
|
|
| |
| |
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 14 May 09 9:05 AM
|
I followed the Steps below. I hope this helps.
The System.Data.dll is in the .NET folder, so something like C:\Windows\Microsoft.NET\Framework\v2.0.50727, the reference should be added to the CodeSnippet XML document which is held in:
C:\Documents and Settings\All Users\Application Data\Sage\Platform\Configuration\Global
The file being called:
codesnippets.xml
Add a line to reference the System.Data.dll at the top (along with the other references), so a line like:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Data.dll (inside the Reference Brackets, sorry it takes stuff inside brackets out of my post)
Inside the node.
Close App Architect, log back in and try a build again. |
|
|
| |
|
Re: ADOException with a SQL statement that runs Ok in ADOExplorer
Posted: 15 May 09 4:14 AM
|
Hi Ronnie,
I solve the problem by adding to codesnippets.xml. Thank you very much. Your great help is really appreciated.
By the way, I am having this error "Type partmeter 'Tservie for 'Public Function Get(ofTservice)() as Tservice' cannot be inferred". Do you have idea on this? Is it similar error to what I have asked?
Thanks
|
|
|
| |
|