SqlTransaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SqlTransaction

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2010-08-05 : 15:44:16

Hi,

My code:

string connectionString = "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\\aspnetdb.mdf;User Instance=true";
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();

SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Transaction = sqlTransaction;

string CreditCardNumberEncrypt = Encrypt(CreditCardNumber.Text);
string NameOnCardEncrypt = Encrypt(NameOnCard.Text);

MembershipUser myObject = Membership.GetUser();
string UserID = myObject.ProviderUserKey.ToString();

SqlConnection dataConnection = new SqlConnection();
dataConnection.ConnectionString = "data source=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\\aspnetdb.mdf;User Instance=true";
dataConnection.Open();

SqlCommand dataCommand = new SqlCommand("INSERT INTO tbl_Payments (UserId, PaymentType) " +
"VALUES (@UserId, @PaymentType);SELECT SCOPE_IDENTITY()", dataConnection);

dataCommand.Parameters.Add("@UserId", UserID);
dataCommand.Parameters.Add("@PaymentType", DropDownList4.Text);

int UserId = Convert.ToInt32(dataCommand.ExecuteScalar().ToString());
Label3.Text = (UserId.ToString());

SqlCommand dataCommand2 = new SqlCommand("INSERT INTO tbl_PaymentDetails (UserId, CardId, AttributeName, Value) " +
"VALUES (@UserId, @CardId, @AttributeName, @Value)", dataConnection);

string year = YYYY.Text;
string yearEnd = year.Substring(year.Length - 2);

var TestRecords = new[] {
new { UserID = UserID,
CARDID = (UserId.ToString()),
Attribute = "Credit card number",
Value = CreditCardNumberEncrypt },
new { UserID = UserID,
CARDID = (UserId.ToString()),
Attribute = "Name on card",
Value = NameOnCardEncrypt },
new { UserID = UserID,
CARDID = (UserId.ToString()),
Attribute = "Expiry Date",
Value = MM.Text +"/" + yearEnd }};
foreach (var item in TestRecords)
{
// SqlDataSource1.InsertParameters["UserId"].DefaultValue = item.UserID;

dataCommand2.Parameters.AddWithValue("@UserId", item.UserID);
dataCommand2.Parameters.AddWithValue("@CardId", item.CARDID);
dataCommand2.Parameters.AddWithValue("@AttributeName", item.Attribute);
dataCommand2.Parameters.AddWithValue("@Value", item.Value);
dataCommand2.ExecuteNonQuery();

// dataCommand.Parameters.AddWithValue

// SqlDataSource1.InsertParameters["UserId"].DefaultValue = item.UserID;
// SqlDataSource1.InsertParameters["CARDID"].DefaultValue = item.CARDID;
// SqlDataSource1.InsertParameters["AttributeName"].DefaultValue = item.Attribute;
// SqlDataSource1.InsertParameters["Value"].DefaultValue = item.Value;
// SqlDataSource1.Insert();

}

Why this piece of code does not work:

dataCommand2.Parameters.Add("@UserId", item.UserID);
dataCommand2.Parameters.Add("@CardId", item.CARDID);
dataCommand2.Parameters.Add("@AttributeName", item.Attribute);
dataCommand2.Parameters.Add("@Value", item.Value);
dataCommand2.ExecuteNonQuery();

If I use this code:

SqlDataSource1.InsertParameters["UserId"].DefaultValue = item.UserID;
SqlDataSource1.InsertParameters["CARDID"].DefaultValue = item.CARDID;
SqlDataSource1.InsertParameters["AttributeName"].DefaultValue = item.Attribute;
SqlDataSource1.InsertParameters["Value"].DefaultValue = item.Value;
SqlDataSource1.Insert();

works!

I do not want to use sqldatasource1 for storing data.

Please help me,
thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-05 : 16:42:01

Please post the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

programer
Posting Yak Master

221 Posts

Posted - 2010-08-05 : 16:53:31

quote:
Originally posted by tkizer

Please post the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi,

here was a mistake:
SqlCommand dataCommand2 = new SqlCommand("INSERT INTO tbl_PaymentDetails (UserId, CardId, AttributeName, Value) " +
"VALUES (@UserId, @CardId, @AttributeName, @Value)", dataConnection);

SqlParameter myUserId2 = new SqlParameter("@UserId", item.UserID);
SqlParameter myCardId = new SqlParameter("@CardId", item.CARDID);
SqlParameter myAttributeName = new SqlParameter("@AttributeName", item.Attribute);
SqlParameter myValue = new SqlParameter("@Value", item.Value);

dataCommand2.Parameters.Add(myUserId2);
dataCommand2.Parameters.Add(myCardId);
dataCommand2.Parameters.Add(myAttributeName);
dataCommand2.Parameters.Add(myValue);
dataCommand2.ExecuteNonQuery();

Now it works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources