Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
want to execute update statement in vb6

Unanswered want to execute update statement in vb6

  • Tuesday, January 01, 2013 11:39 AM
     
     
    Sign In to Vote
    0

    hello there

           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

     

All Replies

  • Tuesday, January 01, 2013 11:50 AM
     
     
    Sign In to Vote
    0

     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"
         cnAdo.Execute sql

    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
     
     
    Sign In to Vote
    0

    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, esquel@sommarskog.se
     
  • Tuesday, January 01, 2013 3:03 PM
     
      Has Code
    Sign In to Vote
    0

    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;"

    Regards
    Satheesh

     
  • Tuesday, January 01, 2013 3:27 PM
     
     
    Sign In to Vote
    1

    Moderators,

    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

    Thanks

    :-)



    Cor