7 Replies - 2232 Views - Last Post: 02 October 2012 - 04:23 AM Rate Topic: -----

#1 Poojaaa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 29-September 12

Extracting a particular value from sql2000 database & then updatin

Posted 30 September 2012 - 06:06 AM

Private Sub Command1_Click()
Dim conn1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim rs1 As New ADODB.Recordset
Dim amnt As Double
Dim d_value As Double
Dim accessconnect As String
Dim accnt_no As String
Dim stSQL As String
accessconnect = " Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=POOJA-D4FD585E2\SQL2000"
conn1.ConnectionString = accessconnect
conn1.Open
accnt_no = Val(Text1.Text)
Text2.Text = amnt
If Len(Text1.Text) > 0 And Len(Text2.Text) > 0 Then
    cmd1.ActiveConnection = conn1
    cmd1.CommandText = " select balance from account where account_number= ' " & accnt_no & " ' "
    Set rs1 = cmd1.Execute
    dValue = rs1("balance")
    
 
   stSQL = " update account set balance = balance + ' " & amnt & " ' where account_number = ' " & accnt_no & " ' "
    conn1.Execute stSQL
    conn1.Close
MsgBox (" AMOUNT DEPOSITED SUCCESSFULLY ")
Else
MsgBox ("Enter some value")
End If
End Sub


Private Sub Text1_KeyPress(KeyAscii As Integer)
If Not ((KeyAscii >= 48 And KeyAscii <= 57) Or KeyAscii = 45 Or (KeyAscii >= 65 And KeyAscii <= 90)) Then
MsgBox "enter proper values!"
KeyAscii = 0
End If
End Sub


Private Sub Text2_KeyPress(KeyAscii As Integer)
If Not ((KeyAscii >= 48 And KeyAscii <= 57) Or KeyAscii = 127 Or KeyAscii = 8) Then
MsgBox "enter proper value"
KeyAscii = 0
End If
End Sub




This code fails to update database any suggestions??? Attaching error screenshot

Attached File(s)



Is This A Good Question/Topic? 0
  • +

Replies To: Extracting a particular value from sql2000 database & then updatin

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9366
  • View blog
  • Posts: 35,187
  • Joined: 12-June 08

Re: Extracting a particular value from sql2000 database & then updatin

Posted 30 September 2012 - 10:14 AM

' where account_number = ' " & accnt_no & " ' "

Spaces - remove them so the tick marks are right up near the value

Also use parameters and not this sort of method.. it leaves you open, vulnerable, and is not a good programming practice.

' where account_number = '" & accnt_no & "' "

Was This Post Helpful? 0
  • +
  • -

#3 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Extracting a particular value from sql2000 database & then updatin

Posted 01 October 2012 - 03:03 AM

where did you get the value for the amount variable

i am not sure i think you wrongly assigned the variable
	Text2.Text = amnt



would be like this
	amnt =val(Text2.Text)



and also follow the suggestions of modi123_1 too
eliminate the spaces and also remove the single Quote(') around the amnt variable

i think this will solve your problem

This post has been edited by thava: 01 October 2012 - 03:05 AM

Was This Post Helpful? 0
  • +
  • -

#4 Poojaaa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 29-September 12

Re: Extracting a particular value from sql2000 database & then updatin

Posted 01 October 2012 - 03:28 AM

IT IS NOT WORKING
Was This Post Helpful? 0
  • +
  • -

#5 Poojaaa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 29-September 12

Re: Extracting a particular value from sql2000 database & then updatin

Posted 01 October 2012 - 03:53 AM

The spaces and single quotes are the part of the syntax. the user defined variables are declared as
' " & amt & " '
What i want to do is a simple deposit transaction at bank. Balance should be added to the amount user want to deposit and new value shold be updated in database
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9366
  • View blog
  • Posts: 35,187
  • Joined: 12-June 08

Re: Extracting a particular value from sql2000 database & then updatin

Posted 01 October 2012 - 08:07 AM

yes, and we are telling you the sql syntax needs changing.
Was This Post Helpful? 0
  • +
  • -

#7 Poojaaa  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 29-September 12

Re: Extracting a particular value from sql2000 database & then updatin

Posted 02 October 2012 - 02:58 AM

View Postmodi123_1, on 01 October 2012 - 08:07 AM, said:

yes, and we are telling you the sql syntax needs changing.

No Use SIR it's not working
Was This Post Helpful? 0
  • +
  • -

#8 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,781
  • Joined: 26-March 09

Re: Extracting a particular value from sql2000 database & then updatin

Posted 02 October 2012 - 04:23 AM

Please post your code as you now have it, with the changes suggested by modi123_1, so we can see what you have changed.

Do you get any errors?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1