Can any one tell me what is wrong with my coding?

Hi Experts,
I'm having problem with this chunk of code I've written. Looks ok to me but it doesnt work the way I want it. Here the code is

Set rsMBR_CODE = Server.CreateObject("ADODB.Recordset")
  rsMBR_CODE.Open "Select * From members Where MBR_CODE='" & Replace(sMBR_CODE,"'","''") & "'", cnn, 2, 3
  If rsMBR_CODE.EOF Then
        rsMBR_CODE.AddNew
      rsMBR_CODE("MBR_CODE") = sMBR_CODE
    rsMBR_CODE.Update
  Else
    Response.Redirect "URL to another page"
  End If

the field type is Number. everytime I try to add a new member code it appears as a Zero. Help me please.
ahmad_fidentiaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DarthModConnect With a Mentor Commented:
Submitted to PAQ with points refunded (50)

DarthMod
Community Support Moderator
0
 
ileanarcCommented:
Hi,

I do not understand why do you treat MBR_CODE as a string if the field type is Number.

You should get an error when you try to open the recordset, saying that the data types do not match.

Ileana.
0
 
ahmad_fidentiaAuthor Commented:
Hello Ileana,
thanx for the quick response. can you tell me how I am supposed to treat MBR_CODE if not as a string. how is the correct way to redefine it and turn it into an integer?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
somchaiuCommented:
Your code could be adjust to

Set rsMBR_CODE = Server.CreateObject("ADODB.Recordset")
  rsMBR_CODE.Open "Select * From members Where MBR_CODE=" & sMBR_CODE, cnn, 2, 3
  If rsMBR_CODE.EOF Then
       rsMBR_CODE.AddNew
     rsMBR_CODE("MBR_CODE") = sMBR_CODE
    rsMBR_CODE.Update
  Else
    Response.Redirect "URL to another page"
  End If

But I think the code to produce sMBR_CODE is the problem.
0
 
ileanarcCommented:
Yeap,

I tend to agree with somchaiu. In order to see the value of the sMBRC variable, place

Response.Write(sMBR_CODE)

right above "Set rsMBR_CODE".... and delete the "respose.redirect ..." from the end of the asp page (if the page redirects)

One of the problems could be a redefinition of the variable.

Ileana.
0
 
zapthedingbatCommented:
what is the value of sMBR_CODE when it is assigned to MBR_CODE ?

at a guess I'd say this is a datatype issue. T
ry converting sMBR_CODE to an int before assigning it

rsMBR_CODE("MBR_CODE") = cInt(sMBR_CODE)
rsMBR_CODE.Update
0
 
Lord_McFlyCommented:
Just like to make an observation...

A Replace() is being performs - this is normally done to make it SQL safe - this also implies its of DataType Text
=========================================================================
 rsMBR_CODE.Open "Select * From members Where MBR_CODE='" & Replace(sMBR_CODE,"'","''") & "'", cnn, 2, 3
 

You are comparing MBR_CODE & sMBR_CODE which obviously returns a matching record, you then update it with the same value - this means that no change has been made to the record
========================================================================================================================
rsMBR_CODE("MBR_CODE") = sMBR_CODE
rsMBR_CODE.Update

All-in-all this snippet of code updates a field with the same value making it appear as tho nothing has happened.
0
 
ileanarcCommented:
Lord McFly,

You are right about the first thing - the replace function - but I do not agree with your second statement.

There's an "AddNew" method before the assignement "rsMBR_CODE("MBR_CODE") = sMBR_CODE". The logic is the following: if there is no record with the same MBR_Code, than  add a new one. Am I missing sth?

Thanks,
Ileana.
0
 
Lord_McFlyCommented:
Sorry missed that :)

Ok, you say its not working - what do you mean by this...

• Is procudes an error
• Executes fine but the record isn't in the DB

Also - you mention that it doesn't do what you want it to do - just to be clear, you want to add a new record if it does not exists - where you expecting it to do anything else?
0
 
Lord_McFlyCommented:
Do you need any more assistance on this question?
0
 
ahmad_fidentiaAuthor Commented:
Okay Experts,
I really appreciate your responses and comments. I found out where my mistake was. and I corrected it right away. now it's working fine exactly as far as my concern. here I'll tell you guys what the problem was:
I opened members table once to see if the member id already existed. if it did not exist then it could add a new one. right in the bottom of that code I opened the same table  trying to  add to the other fields except for member id. but what it was doing was, it was adding to the other fields meantime it replaced the value of member id  to zero. so I had to change the whole concept of my coding. thanks again.
0
 
Lord_McFlyCommented:
As you answered you own question I would suggest you request a refund and close this question - cheers.

I answered my question myself. What do I do?
---------------------------------------------------------------
http://www.experts-exchange.com/Web/help.jsp#hi70
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.