Assigning data from an SQL query to a variable

Hello all,

for a project I am trying to implement PayPal processing for orders

public void CheckOut(Object s, EventArgs e)
{

String cartBusiness ="0413086@.chester.ac.uk";
String cartProduct;
int cartQuantity = 1;
Decimal cartCost;
int itemNumber = 1;

SqlConnection objConn3;
SqlCommand objCmd3;
SqlDataReader objRdr3;

objConn3 =new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd3 =new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);
objConn3.Open();
objRdr3 = objCmd3.ExecuteReader();
cartProduct ="Cheese";
cartCost = 1;
objRdr3.Close();
objConn3.Close();

cartBusiness = Server.UrlEncode(cartBusiness);

String strPayPal ="https://www.paypal.com/cgi-bin/webscr?cmd=_cart&upload=1&business=" + cartBusiness;

strPayPal += "&item_name_" + itemNumber + "=" + cartProduct;
strPayPal += "&item_number_" + itemNumber + "=" + cartQuantity;
strPayPal += "&amount_" + itemNumber + "=" + Decimal.Round(cartCost);

Response.Redirect(strPayPal);

Here is my current code. I have manually selected cartProduct = "Cheese" and cartCost = 1, however I would like these variables to be site by data from the query.

So I want cartProduct = Title and cartCost = Price from the SQL query.

How do I do this?

Thanks

DO NOT use string concatenation like you have now. Use Parameterized Queries.

you close the SQL connection not the reader first. Then loop through the reader, get the values and assign the variables. I would definetely recommend using Try/Catch block to catch any errors.

The following code is for illustration only.

objConn3 =new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
objCmd3 =new SqlCommand("SELECT * FROM catalogue WHERE ID=" + Request.QueryString["id"], objConn3);

Try
If objConn3.State = 0 Then objConn3.Open()

objRdr3 = objCmd3.ExecuteReader();

If DataReader.HasRows Then
Do While objRdr3 .Read()
cartProduct = objRdr3 .Item("cartProduct"))
cartCost = objRdr3 .Item("cartCost"))
Loop

End If
Catch exc As Exception
Response.Write(exc)
Finally

objRdr3.close()
If objConn3.State = ConnectionState.Open Then
objConn3.Close()
End If
End Try

Assert Permission - ISSUE.

Hi All,
I am using a .net dll in my RDL, I have done all the steps to implement the
custom assembly in the report.
--
Message posted via http://www.sqlmonster.comLook at this thread, you have to get Reporting Services trust your DLL.
http://groups.google.de/groups?hl=de&lr=&threadm=OAxg3EQRFHA.4028%40tk2msftngp13.phx.gbl&rnum=1&prev=/groups%3Fq%3Dreporting%2Bservices%2Bassembly%2Bgac%26hl%3Dde%26lr%3D%26scoring%3Dd%26selm%3DOAxg3EQRFHA.4028%2540tk2msftngp13.phx.gbl%26rnum%3D1
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"BALAJI KRISHNAN via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb
im Newsbeitrag news:25b6837f5aaf4a7589ef4ac1bd3cd615@.SQLMonster.com...
> Hi All,
> I am using a .net dll in my RDL, I have done all the steps to implement
> the
> custom assembly in the report.
> --
> Message posted via http://www.sqlmonster.com|||Sorry hit the Post Message button... Remaining Message...
I have done all the steps...
Step 1) Copying the dll to the C:\Program Files\Microsoft SQL Server\MSSQL\
Reporting Services\ReportServer\bin\
and
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ - Folders
Step 2) Create Permission Set and Code Group and changing the
rssrvpolicy.config file...
See my Permission Set and Code Group added to the file
<PermissionSet class="NamedPermissionSet"
version="1"
Name="CCPermissionSet">
<IPermission class="SecurityPermission"
version="1"
Flags="Assertion, Execution"/>
</PermissionSet>
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="CCPermissionSet"
Name="CCCodeGroup"
Description="">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="file://C:/Program
Files/Microsoft SQL Server/MSSQL/Reporting
Services/ReportServer/bin/CreditCardDecrypt.dll"/>
</CodeGroup>
Step 3) Refering the dll in the RDL (Properties - Reference - map dll)
Step 4) This is the Important step...Where I am missing...I need to give
the Assert permission to my code..I dont know what Assert Permission I need
to give for my dll...(As we give for SqlClientPermission...)
See my dll code...
public static string DecryptCardInfo(string cc,string subjectName,string
storeName)
{
string sCreditCard = "";
try
{
SecurityPermission secPerm = new SecurityPermission
(PermissionState.Unrestricted);
secPerm.Assert();
WSEX509.X509CertificateStore.StoreLocation location =WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
WSEX509.X509CertificateStore.StoreProvider provider =WSEX509.X509CertificateStore.StoreProvider.System;
WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
(provider, location, storeName);
bool fopen = store.OpenRead();
if(fopen)
{
WSEX509.X509CertificateCollection certs =store.FindCertificateBySubjectString(subjectName);
if (certs.Count > 0)
{
WSEX509.X509Certificate cer = certs[0];
WSECRY.RSACryptoServiceProvider rsaCsp =(WSECRY.RSACryptoServiceProvider)cer.Key;
byte[] cipherData = Convert.FromBase64String(cc);
byte[] plainData = rsaCsp.Decrypt(cipherData, false);
sCreditCard = Encoding.UTF8.GetString(plainData);
}
}
if (store != null)
store.Close();
return sCreditCard;
//return cc;
}
catch(Exception ex)
{
return ex.ToString();
}
}
}
I am in Need your help Immediately..because I am stuck with this for the
past days..and have no clue for this...
If you need any more information please send a note I can give u
immediately...
Thanks in advance...
balaji.
--
Message posted via http://www.sqlmonster.com|||Try to place you DLL in the GAC to identify wheter it is a security problem.
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> schrieb im
Newsbeitrag news:7f6dadd296e74d5a9a51a62bac995613@.SQLMonster.com...
> Sorry hit the Post Message button... Remaining Message...
> I have done all the steps...
> Step 1) Copying the dll to the C:\Program Files\Microsoft SQL
> Server\MSSQL\
> Reporting Services\ReportServer\bin\
> and
> C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ - Folders
> Step 2) Create Permission Set and Code Group and changing the
> rssrvpolicy.config file...
> See my Permission Set and Code Group added to the file
> <PermissionSet class="NamedPermissionSet"
> version="1"
> Name="CCPermissionSet">
> <IPermission class="SecurityPermission"
> version="1"
> Flags="Assertion, Execution"/>
> </PermissionSet>
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="CCPermissionSet"
> Name="CCCodeGroup"
> Description="">
> <IMembershipCondition class="UrlMembershipCondition"
> version="1"
> Url="file://C:/Program
> Files/Microsoft SQL Server/MSSQL/Reporting
> Services/ReportServer/bin/CreditCardDecrypt.dll"/>
> </CodeGroup>
>
> Step 3) Refering the dll in the RDL (Properties - Reference - map dll)
> Step 4) This is the Important step...Where I am missing...I need to give
> the Assert permission to my code..I dont know what Assert Permission I
> need
> to give for my dll...(As we give for SqlClientPermission...)
>
> See my dll code...
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> string sCreditCard = "";
> try
> {
> SecurityPermission secPerm = new SecurityPermission
> (PermissionState.Unrestricted);
> secPerm.Assert();
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> //return cc;
> }
> catch(Exception ex)
> {
> return ex.ToString();
> }
> }
> }
>
> I am in Need your help Immediately..because I am stuck with this for the
> past days..and have no clue for this...
> If you need any more information please send a note I can give u
> immediately...
> Thanks in advance...
> balaji.
> --
> Message posted via http://www.sqlmonster.com|||Hi Jens,
I have placed the dll in the GAC sucessfully.
But still I got #Error...
Balaji
--
Message posted via http://www.sqlmonster.com|||CAN MICROSOFT FOLKS HELP ON THIS....
STILL FACING THE ISSSUE..
THANKS IN ADVANCE
BALAJI
--
Message posted via http://www.sqlmonster.com|||Hi Balaji,
Is your problem is solve of acessing .net dll in reporting service.
I am also having the same problem where my .Net dll reads the config file to
get another file URL which has stored another file URL and using this new URL
(XML file) i am accessing the result through XMldocument and returing the
result back. But the security issue is the main issue which i am not able to
solve. If you can help me out i'll be helpful to you.
Thanks
Jasvinder
"BALAJI KRISHNAN via SQLMonster.com" wrote:
> Hi All,
> I am using a .net dll in my RDL, I have done all the steps to implement the
> custom assembly in the report.
> --
> Message posted via http://www.sqlmonster.com
>