April 2, 2010 at 11:54 am
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
April 6, 2010 at 6:20 am
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.
April 7, 2010 at 12:17 am
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
April 7, 2010 at 8:14 am
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"
April 7, 2010 at 2:59 pm
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"
April 8, 2010 at 3:03 am
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 ?
April 8, 2010 at 3:07 am
Try this:
strComment = gvarInfo.strTableNameSql & ".Comment = " & Chr(34) & Me.Comment & chr(34) & ", "
April 8, 2010 at 10:47 pm
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
April 9, 2010 at 12:30 am
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