|
|
My application runs on a network of 15 to 16 clients on a SQL Server 2000. Some of the modules perform transaction, i.e. they run more than one sql. I have tied the different sql statemels under the following procedure:
con.Open()
trans = con.BeginTransaction()
Try
'First Sql
cmd = New SqlClient.SqlCommand("INSERT INTO table1(col1, cl2, col3, col4) "VALUES('" & col1 & "','" & col2 & "','" & col3 & "','" & col4 & "')", con, trans)
cmd.ExecuteNonQuery()
'Second Sql
cmd = New SqlClient.SqlCommand("update table2 set colbl='" & col1 & "',colcl='" & t & "' where colbl =" & col1 & "", con, trans)
cmd.ExecuteNonQuery()
trans.Commit()
Catch ex As Exception
trans.Rollback()
End Try
con.Close()
Out of 700-800 inserts and updates, the system ignores the update statement. The value is inseted through the first statement, but it is not updated. So it shows a wrong balance. I am not able to diagonise the actual cause. The network is very good. My question is :
1. Even if the network fails, why does the rollback doesn’t happen.
2. Is my above code a right way for a commit procedure.
Please help. Thanks in advance.
|
|
|
|
|
The real way to do this is to put it all in a stored proc. This code seems to me like it should work, but a stored proc just makes more sense, and also allows you to pass parameters in a safer way, this code is ripe for SQL injection attacks.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hi,
Thanks so much for taking so much interest in reading and replying to my query.
Well, as advised by you I have asked my team to start working with paramaterized queries and sooner we will update it.
In the mean time what is churning me that why the Rollback doesnt take place. My application runs three modules. Each runs two sql statement; one Insert and one Update.
Insert is running fine in all the three modules. Update is running fine in two modules.
But in one problamatic module, the sql insert command is generated but the consequent update field is not getting updated.
Its like that say if Rs. 100 is added to an account in say Table1 than Table2 which holds all the balances of the accounts in Table1 needs to be updated.
Because its not getting updated, it shows a wrong balance. Thought the number of errors are one out of 1000 updates.
I have tested this commit part in my server by giving some wrong sql query and the rollback takes place. But while its live its giving no solution to us.
Can you please help with this commit part. Weather my process of commit is right. As such the transaction is very simple. If my query has any problem, than how come 999 time its workig fine and creating error just once.
Thanks again in advance.
|
|
|
|
|
vb_buddy wrote: In the mean time what is churning me that why the Rollback doesnt take place.
Since you've made the big mistake of using string concantentation to build the SQL statements, various input CAN create a valid statement, but that executes in a successful, but not as you expected, manner. It's entirely possible the update IS running successfully. So, since there's no error, there's no rollback either.
Check the input that's going to these statments.
|
|
|
|
|
Well, here is the code, I have also given the SQL field type.
Anyways, what are string concatenation. What is the difference between insert through string concatenation and parameterized queries. Can you please focus me that where am i going wrong.
con.Open()
trans = con.BeginTransaction()
Try
cmd = New SqlClient.SqlCommand("INSERT INTO issuec(cen, cses, ctdate, cttime, caction, camt, cde, cdch, crt, crr, cda, uc, cty) " & _
"VALUES('" & cen & "','" & frm.txtcses.Text.Trim & "','" & s_date & "','" & s_time & "','" & temp & "','" & a11 & "','" & sdepo & "','" & b & "','" & type & "','" & reason & "','" & damage & "'," & ccde & ",'" & cs & "')", con, trans)
cmd.ExecuteNonQuery()
cmd = New SqlClient.SqlCommand("update master set ccb='" & newbalance & "', cact='" & t & "',cldate='" & s_date & "', cl='" & lc & "', cde=" & cnt & ", cty='" & cs & "' where cen=" & cen & "", con, trans)
cmd.ExecuteNonQuery()
trans.Commit()
Catch ex As Exception
trans.Rollback()
End Try
con.Close()
Field type:
Sql1:
------
cen: numeric
cses: int
ctdate: datetime
cttime: char
caction : char
camt: numeric
cde : numeric
cdch : numeric
crt : char
crr : varchar
cda : char
uc : int
cty : char
Sql2:
------
ccb : numeric
cact : char
cldate : datetime
cl : cahr
cde : int
cty : char
cen : numeric
I have also given the field type for the above code in SQL.
Regards
|
|
|
|
|
vb_buddy wrote: What is the difference between insert through string concatenation and parameterized queries.
Parameterized queries, using SqlParameter objects, automatically escape certain characters for you. These characters, like the ', can completely change the meaning of an SQL statement.
vb_buddy wrote: Can you please focus me that where am i going wrong.
I already did. You're using string contantenation to build SQL statements.
BTW: Your SQL column names are utter junk. What column holds what data?? You've thrown away the entire meaning of "self documenting code".
|
|
|
|
|
To further what Christian said, execute the statements in a SQL Query Analyzer session to ensure that the update is in fact working against a field that exists. The statements listed show an insert into table1, but an update against table2.
Tim
|
|
|
|
|
When you update, you compare the colbl field with the value in col1 as a numeric value, but then you set the field with col1 as a string value. What are the types of the col1 variable, and the colbl field, really?
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Hello,
I'd like to know what is the best way to store product information, and where I can find a good tutorial about it. My idea was to use a Microsoft Acces database, looked best to me. Any suggestions are welcome! 
--Zaegra--
|
|
|
|
|
Access is unsuitable for all but the most trivial applications. It is only useful in single user, small data size applications.
|
|
|
|
|
What would you recommend?
|
|
|
|
|
SQL Server Express Edition is free, and works the same as the bigger versions, so you can learn without paying.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
I'll second Christians recommendation. SQL Server Express will give you a dramatically better platform to begin developing and you will get exposure in SQL Server which is a clear upgrade path and a valid learning curve. Access is a dead end as a learning point.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Like Christian said, SQL Server Express.
|
|
|
|
|
And now for a tutorial?
I would like to know too!
|
|
|
|
|
I'd like to be able to let my clients write code through code provider, such as
Dim Diff = Close - Open
...where Diff, Close, open are actuallyi double Arrays()
I see I can do it by overloading the - operator, but only if either Close or Open are new types. But I don't want to have to create a new type -- I just want to use plain old Close()as double and Open() as double in the syntax.
Is there anyway around this? Why does the operator overloading require a new type?
Thanks!
|
|
|
|
|
cstrader232 wrote: Is there anyway around this?
No.
cstrader232 wrote: Why does the operator overloading require a new type?
Because you can't modify the behavior of the System.Array class. You can only modify the behavior of a class you create FROM the Array class, which, won't be usable in the manner you want because that will ALWAYS use the System.Array class.
You simply cannot do what you want without requiring the user to use your custom type.
|
|
|
|
|
OK, fair enough... just wanted to be sure. Thanks and Happy weekend!
|
|
|
|
|
Dave Kreskowiak wrote: Because you can't modify the behavior of the System.Array class. You can only modify the behavior of a class you create FROM the Array class,
Can't you add code to existing classes in 3.5 now ? Or am I thinking of Objective C ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Christian Graus wrote: Can't you add code to existing classes in 3.5 now ? Or am I thinking of Objective C ?
Not that I know of, or heard of anyway. Maybe in C# it's possible? 
|
|
|
|
|
I guess the most likely thing is that I'm thinking of something I read in an OC book....
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
If it was C#, you could make a wrapper type for an array, and implement an implicit conversion from you type to an array, to let the clients assign your type to an array variable and get the array instead.
Sadly, VB.NET doesn't support implicit conversions.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Hi all.
I have created a website that will be used internally. I want to create a web form in vb so scroll bars and menu's are gone and the site will load in a clean window.
I have created this form and when i load it up and click on a button (a JAVASCRIPT button) it comes up with the following error
'A runtime error has occured, do you wish to debug?
Line:35
Error: parent.frames.2 is null or not an object.
I have added the site to a server using IIS and it works throught internet explorer but i cannot get it to work with the vb form. I am pretty sure it's something to do with the buttons in my script as the message comes up whenever you click on one.
Any help would be appreciated.
David
|
|
|
|
|
Wat do you mean by VB form, you have a browser control in a VB program ?
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
