Tuesday, January 01, 2013 11:39 AM
I have a following statement in sql if i run it int sql query anylizer its work properly. I want to execute it in my vb6 project. Means how can i execute following statement in vb6.
declare @txnno int
set @txnno = 1
UPDATE toll_veh_out SET @txnno = txn_no = @txnno + 1 where shift_date = '2012-11-01' and out_plaza_id = 1 and booth_no = 13
thank you in advance
Tuesday, January 01, 2013 11:50 AM
Sorry, I am writing this from the memory
Dim sql As String
Public cnAdo As ADODB.Connection
sql = "Update users set rolecode =" & @txnno & "= txn_no =" & @txnno+1 & " where shift_date ="2012-11-01" and out_plaza_id = 1 and booth_no = 13"
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
Tuesday, January 01, 2013 12:23 PM
So everytime you run this, you want to set txn_no = 2 for these specific parameters?
This is not a VB6 forum, but assuming that all values are parameters, and you want to increment the value of txn_no, it would be:
cmd = new ADODB.SqlCommand
cmd.Connection = new ADODB.SqlConnection connstr
cmd.CommandType = adCmdText
cmd.CommandText = "UPDATE toll_veh_out SET ? = txn_no = txn_no + 1 " & _
"WHERE shift_date = ? AND out_plaza_id = ? and booth_no = ?"
cmd.CreateParameter "@txnno", adInt, adDirectionOutput
cmd.CreateParameter "@shiftdate", adDateTime, , , shiftdate
cmd.CreateParameter "@plazaid", adInt, , , plaza_id
cmd.CreateParameter "@plazaid", adInt, , , booth_no
cmd.Execute , , adExecuteNorecords
txnno = cmd.Parameters("@txnno").Value
Again, this is not a VB6 forum, and I only do VB6 left-handedly. There may be errors and the code is untested. What I wanted to highlight is how you deal with the parameters, as people often try to build a query string by concatenation, which opens for SQL injections and other forms of misery.
Erland Sommarskog, SQL Server MVP, email@example.com
Tuesday, January 01, 2013 3:03 PM
You can do this way also
Dim con as new ADODB.Connection
con.open "ConnectionString" ' Put the connection string here
con.execute "declare @txnno int; " & _ "set @txnno = 1;" & _ "UPDATE toll_veh_out SET @txnno = txn_no = @txnno + 1 where shift_date = '2012-11-01' and out_plaza_id = 1 and booth_no = 13;"
Tuesday, January 01, 2013 3:27 PM
Can this thread be moved back to the Transact SQL forum, just to inform Naomi that VB6 is of topic here.
She is known enough for the rest about the forums to move it herself to Off Topic