hello guys (1 Viewer)

Dominic Banda

New member
Local time
Today, 10:12
Joined
Aug 16, 2018
Messages
1
Guys i need help i want to add a value in SQL it showing an error 'Conversion from string "' " to type 'Integer' is not valid.' Using visual basic connected to SQL server.


SQL.ExecQuery("UPDATE ClubMembers SET [AccountBalance] = [AccountBalance] +" & txtamount.Text("' ") &
"WHERE MembersNumber =" & txtrecipientnumber.Text("' "))
 

Cronk

Registered User.
Local time
Tomorrow, 04:12
Joined
Jul 4, 2013
Messages
2,770
And when the SQL string is fixed, the syntax should be
Code:
docmd.runsql (SQL string)
or
Code:
currentdb.execute (SQL string)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:12
Joined
Feb 28, 2001
Messages
26,999
Code:
strQry = "UPDATE ClubMembers SET [AccountBalance] = [AccountBalance] +" & txtamount.Text("' ") & "WHERE MembersNumber =" & txtrecipientnumber.Text("' ")

DoCmd.RunSQL strQry      (OR)

CurrentDB.Execute strQry

I am seeing what appears to be unbalanced quotes (well, apostrophes) inside the quoted strings that end with .Text("' ") and that concerns me.

Look at this article:

https://msdn.microsoft.com/en-us/vba/access-vba/articles/textbox-text-property-access

In Access, I think your use of the .Text property is confusing you. From the article:

When you move the focus to another control, the control's data is updated, and the Value property is set to this new value. The Text property setting is then unavailable until the control gets the focus again.

Since only one control at a time can have focus, ONE of those two references to .Text cannot be right. Further, I don't understand the parenthetical expression. Is that an SQL Server thing that I'm missing because I don't use SQL Server stuff? Your query doesn't appear that it is being run as a pass-through, so I would expect to see Access SQL syntax, and the various concatenations you are using don't make sense in Access SQL.

What (in ENGLISH) were you trying to do? If you were trying to enclose something inside the SQL in apostrophes (which I would understand), you omitted the leading apostrophes for each case and that is the wrong syntax for it anyway.
 

Users who are viewing this thread

Top Bottom