11/25/2024 6:22:46 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
How to use OleDbParameters with a query
Posted: 11 Dec 08 11:32 AM
|
Sorry for asking what appears to be a very basic question. I've never worked with oledb or SalesLogix.
In psudocode I want to do this:
string sql = "Select * from accounts where accountID = @accountID";
OleDbParameter parm = new OleDbParameter("@accountID", "xyz123");
OleDbCommand cmd = new OleDbCommand(sql);
cmd.Parameters.Add(parm);
// etc...
I am getting the error message: "Must declare the scalar variable"@accountID"
I am able to connect to the server and run other queries.
Thanks for any help. Code is below.
Sam
====================================================================================
using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
///
/// Summary description for SLXAccounts
///
public class SLXAccounts
{
public static SLXAccount GetAccount(string accountID)
{
DataSet ds = SLXAccounts.GetAccounts(accountID);
SLXAccount account = null;
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
account.AccountID = Utilities.ObjectToString(dr["AccountID"]);
account.AccountName = Utilities.ObjectToString(dr["Account"]);
}
return account;
}
public static DataSet GetAccounts(string accountID)
{
string sql = "SELECT AC.ACCOUNTID, AC.ACCOUNT, AC.ACCOUNT, AD.ADDRESS1, AD.ADDRESS2, " +
"AD.CITY, AD.STATE, AD.POSTALCODE, AD.COUNTRY FROM sysdba.ACCOUNT AC " +
"JOIN sysdba.ADDRESS AD on AC.ADDRESSID = AD.ADDRESSID " +
"WHERE AC.STATUS = 'Active' " +
"AND (AC.ACCOUNTID = @AccountID OR @AccountID IS NULL) ORDER BY AC.ACCOUNT ";
OleDbParameter[] parms = new OleDbParameter[1];
parms[0] = new OleDbParameter("@AccountID", accountID);
return SLXAccounts.ExecuteDataSet(sql, CommandType.Text, parms);
}
public static DataSet ExecuteDataSet(string sql, CommandType commandType, OleDbParameter[] parms)
{
string connectionString = ConfigurationManager.ConnectionStrings["SLXString"].ToString();
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, con);
cmd.CommandType = commandType;
if (parms.Length > 0)
cmd.Parameters.AddRange(parms);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
|
|
|
| |
| |
|
Re: How to use OleDbParameters with a query
Posted: 12 Dec 08 11:10 AM
|
Thank you - I'll use that.
I'm surprised that paramaterized queries dont work the same way they do with the sqlcommand object. Is that a saleslogix feature or an oledb feature ? |
|
|
|
Re: How to use OleDbParameters with a query
Posted: 15 Dec 08 10:59 AM
|
Replace your named parameters with ?.
for example:
SELECT COUNT(*) FROM ACCOUNT WHERE ACCOUNTID = ?
We use parameters 95% of the time and they work great.
Timmus |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|