how to control if query is executed

  • Hi,

    I have an sql query and it was running ok, but know I have some trouble's with it. It's been executed and the form close immiadetly. I know that the query then not is executed.

    I'm wondering what I can do todo a check if the update is being processed or not, so that the user will be informed that the update is not happened?

    Regards,

    Sylvain

    Dim cnn As ADODB.Connection

    Dim rst1 As ADODB.Recordset

    Dim strSQL As String

    Set cnn = New ADODB.Connection

    constr = DLookup("ConnectionString", "tblConnections", "Active = True")

    cnn.ConnectionString = constr

    cnn.Open

    strSQL = "UPDATE " & gvarInfo.strTableNameSql & " SET " _

    & strFixedphone _

    & strCellphone _

    & strName _

    & strSurname _

    & strZipcode _

    & strAddress _

    & strMunicipality _

    & strGender _

    & strClientnumber _

    & strLanguage _

    & strComment _

    & strCallbackphone _

    & strEmail1 _

    & strEmail2 _

    & gvarInfo.strTableNameSql & ".ContactAgent = '" & gvarInfo.strUserName & "', " _

    & gvarInfo.strTableNameSql & ".CallbackDate = '" & Format(Me.CallbackDate, "yyyy/mm/dd") & "', " _

    & gvarInfo.strTableNameSql & ".CallbackHour = '" & Format(Me.CallbackDate, "yyyy/mm/dd") & " " & Format(Me.CallbackHour, "hh:mm:ss") & "', " _

    & gvarInfo.strTableNameSql & ".NumCalls = " & Me.NumCalls & ", " _

    & gvarInfo.strTableNameSql & ".Outcome = " & Me.Outcome & ", " _

    & gvarInfo.strTableNameSql & ".privilege = " & Me.CheckPrivi & ", " _

    & gvarInfo.strTableNameSql & ".ContactDate ='" & Format(Now, "yyyy/mm/dd hh:mm:ss") & "', " _

    & gvarInfo.strTableNameSql & ".ContactHour ='" & Format(Now, "hh:mm:ss") & "', " _

    & gvarInfo.strTableNameSql & ".logdate ='" & Format(Now, "yyyy/mm/dd") & "' " & _

    " WHERE (((" & gvarInfo.strTableNameSql & ".EasyCode)=" & Me.EasyCode & "));"

    cnn.Execute strSQL, , adExecuteNoRecords

  • This is off topic and I apoligize.

    I see that you created an SQL query on the fly and executed it.

    Would you know where I can find a good (complete) example of this. I would like to learn more about it.

    .....

    P.S. couldn't you create a select query which you'd run after the update. Depending on your Where statement if any records are returned then the update worked.

    Just a thought from a novice, I hope it applies.

  • Hi,

    Yes I have made a sql select query that's working, but I tought maybe with sql server there are some specific code you can use for to look if the query is done.

    But I have seen know what the problem is why my query isn't executed. It seems that people using the ' sign for the language french and that's the reason when the query is not executed.

    I'm also new to sql query. I search on google to find specific information about what I need.

    If you have any further questions I'm pleased to help you

    Regards,

    Sylvain

  • The second (empty) parameter in this line is RecordsAffected - if you put a variable name there, the variable will tell you after the execution how many rows have been updated

    cnn.Execute strSQL, , adExecuteNoRecords

    Change it to something like this:

    Dim lRecordsUpdated as Long

    cnn.Execute strSQL, lRecordsUpdated , adExecuteNoRecords

    Msgbox lRecordsUpdated & " records have been updated", vbOkOnly + vbInformation, "Update Results"

  • Ken@Work (4/6/2010)


    This is off topic and I apoligize.

    I see that you created an SQL query on the fly and executed it.

    Would you know where I can find a good (complete) example of this. I would like to learn more about it.

    .....

    P.S. couldn't you create a select query which you'd run after the update. Depending on your Where statement if any records are returned then the update worked.

    Just a thought from a novice, I hope it applies.

    Try Google, look up Command Object ADO.

    or Command Object ADO MS Access

    You should get a good start there.

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • Hi Chris,

    Thanks this is exactly what I needed.

    I have used also if then so that the user hasn't the annoying popup. Only if the record isn't saved he gets this information.

    Dim IrecordsUpdated As Long

    cnn.Execute strSQL, IrecordsUpdated, adExecuteNoRecords

    If Irecordsupdate = 0 Then

    MsgBox IrecordsUpdated & " Lead hasn't been updated Please report this to your teamleader ", vbOKOnly + vbCritical, " Lead is NOT Saved"

    End If

    I have another problem maybe know's someone a work around for this.

    strComment = gvarInfo.strTableNameSql & ".Comment = '" & Me.Comment & "', "

    As you can see in the code we use ' sign for the query here in is the data what has to be saved, but the problem is that my users also use this ' sign in there text. In the French language it's much used. Has somebody a tip for this problem ?

  • Try this:

    strComment = gvarInfo.strTableNameSql & ".Comment = " & Chr(34) & Me.Comment & chr(34) & ", "

  • Your problem is not limited to those using French - apostrophes occur all over the place - in names, like O'Malleys Bar, or ownership, as in David's car.

    SQL can handle these nicely if you replace any apostrophe in your variables with two apostrophes - for example, O''Malleys Bar, or David''s car.

    You can do that with your Comment parameter using ...

    strComment = gvarInfo.strTableNameSql & ".Comment = '" & Replace(Me.Comment, "'", "''") & "', "

    This method also has the advantage that it "reduces" the chance of SQL-injection attacks - for example, if someone entered " ' drop table sysobjects" into your Comments parameter they could cause untold damage.

    Chris

  • Chris,

    Your exactly wright, it's not only the french language, but in the dutch language we don't use it very often.

    Your Idea is working by me.

    My problem is Solved

    Thanks

    Sylvain

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply