how to trap if mch_rs!qtyh is zero - Page 2

[vb5prgrmr]

I don't have your code handy at the moment but lets say you have three text boxes that contain a number, text, and date and they are also an array of text boxes...

strSQL = "INSERT INTO tablename(numericfieldname, textfieldname, datefieldname) VALUES(" & Trim(Text1(0).Text) & ", '" & Trim(Replace(Text1(1).Text, "'", "''")), & ", #" & Text1(2).Text & "#)"

Now, as you can see, the first field that is a numeric field is not surrounded by single ticks (') while the text field is. Also, you can see the replace function that replaces single ticks contained within the text to be inserted with two single ticks ('>''). Now, what this does is it takes a word like don't and transforms it into don''t, but when it is inserted into the database, the database recognizes this as the word is supposed to have a single tick in it. So the database takes don''t and transforms it back into don't so when you pull the value back out of the database, you get what you want don't. Then as you see the third field is wrapped by pound symbols (#), which indicate to access (and access only) that a date is being passed to be inserted (Note: other databases will accept a string value and convert it to a date for you but access will not accept a value wrapped by single ticks and as such must be wrapped by the pound symbol).

Good Luck

[jemz]

hello sir can i ask, what is the purpose of the (0) in the (Trim(Text1(0).Text)

sir do i still put the addnew?like this sir...or this will not be included anymore..hoping for your positive responds...thank you in advance...

With mch_rs

.AddNew

!mch_no = Trim(UCase(m_mchno))

!mch_name = Trim(StrConv(txtname.Text, vbProperCase))

!mch_umsr = Trim(LCase(txtumsr.Text))

!mch_qtyh = Val(txtqtyh.Text)

!mch_uprice = CDbl(txtuprice.Text)

!mch_rstatus = "1"

.Update

.Close

[vb5prgrmr]

Trim(Text1(0).Text) is the index of a textbox array...

.addnew = add new record
no .addnew = edit current record

Good Luck

[jemz]

thanks for the reply, ok sir that means is the index..about the addnew, so i will not use anymore the addnew.if i have this insert into table Statement?am i right sir?hoping for your positive responds...

[vb5prgrmr]

Correct, it is best to use the insert into statement when you can over the .addnew method, so don't use both to insert a record, one or the other...

Good Luck

[jemz]

sir thanks for the reply...sir i have trouble in this..please help me on my insert into...also sir i have problem in checking bof. and eof i put comment on it because it is not needed anymore if i use the insert into table but what i suppose to put in the if statement sir..help me sir..hoping for your positive responds...

Private Sub cmdadd_Click()

sql = "INSERT INTO merchandise_table(mch_no,mch_name,mch_umsr,mch_qtyh,mch_uprice)values('" & txtmchno.Text & "', & txtname.text & " ',"' & txtumsr.text & '", txtqtyh.Text "', & txtuprice.text & "' where mch_no =  '" & m_mchno & "'")
mch_rs.Open sql, con, adOpenDynamic, adLockPessimistic


If mch_rs.BOF = True And mch_rs.EOF = True Then
  
        'With mch_rs
          '  .AddNew
          '   !mch_no = Trim(UCase(m_mchno))
           '  !mch_name = Trim(StrConv(txtname.Text, vbProperCase))
            ' !mch_umsr = Trim(LCase(txtumsr.Text))
            ' !mch_qtyh = Val(txtqtyh.Text)
             '!mch_uprice = CDbl(txtuprice.Text)
            ' !mch_rstatus = "1"
            ' .Update
            '.Close
'End With
MsgBox "Record has been added to the merchandise_table  ...", vbInformation, "Congratz!!!"
clear1
Else

  MsgBox "Duplicate entry...Try another one!!!", vbCritical, "Warning...Duplicate!!!"
  clear1
  Exit Sub
  End If
End Sub

[vb5prgrmr]

Okay, first, if I remember correctly, you want to check to see if a record exists in your database before you do your insert right?... so that means you need to do a select statement first...

pseudo code
strSQL = "SELECT * FROM tablename WHERE fieldname = " & somecriteria
rs.open...

Now at this point, I have to say I'm sorry as I this vital piece of information... This check

If Rs.EOF = True AND Rs.BOF = True Then

is wrong as a recordset cannont be both EOF and BOF so this will always return false... it should be as I noted on pg3 post #23 (and below)...

Okay, so where was I... Oh yeah...

psuedo code
strSQL = "SELECT * FROM tablename WHERE fieldname = " & somecriteria
rs.Open strSQL, ...

If Rs.RecordCount <> 0 And Rs.BOF = False And Rs.EOF = False Then
  'record exists so notify user
  msgbox "record exists"
Else
  'no record exists so insert information
  sql = "INSERT INTO merchandise_table(mch_no, mch_name, mch_umsr, mch_qtyh, mch_uprice) VALUES('" & txtmchno.Text & "','" & txtname.text & "','" & txtumsr.text & "','" & txtqtyh.Text "','" & txtuprice.text & "')"
  commandobject.execute sql
  MsgBox "Record added"
End If

NOTE: Insert statement does not need/use a where statement.
ALSO: I wrapped each value in the values section in the above with single ticks as you had some of the fields either starting with a single tick or ending with a single tick, so if any of those fields are of the numeric data types, you will need to remove those single ticks...

Good Luck

[jemz]

hello sir thanks for the patience,..sir i forgot the mch_rstatus .sir, how to put this in the insert into table. my mch_rstatus = "1". kindly try to look my code above in my mch_rstatus. ...okey sir, so the insert statement does not need the where statement..thank you for giving me this idea sir..i will write again if i have some doubt in my code...more power to you..hoping for your positive responds...

[vb5prgrmr]

Okay, you are saying you want to edit a field? You want to set some field in some table = to some value?

strSQL = "UPDATE tablename SET fieldname = " & somevalue

if string then ... '" & somevalue & "'"
if number, as show in code block
if date, as I have previously shown you...

Good Luck

[jemz]

okey sir, thanks for the reply i will write again if i have doubt in my code..thank you sir...more power to you..hoping for your positive responds.

[jemz]

hello sir, here is my code sir and correct me if i am wrong with this my insert into table...hoping for your positive responds....

Private Sub cmdadd_Click()
If mch_rs.BOF = True And mch_rs.EOF = True Then
  sql = "INSERT INTO merchandise_table(mch_no, mch_name, mch_umsr, mch_qtyh, mch_uprice) VALUES('" & txtmchno.Text & "','" & txtname.Text & "','" & txtumsr.Text & "','" & txtqtyh.Text & "','" & txtuprice.Text & "')"
   Set mch_rs = con.Execute(sql)
       
MsgBox "Record has been added to the merchandise_table  ...", vbInformation, "Congratz!!!"
clear1
Else

  MsgBox "Duplicate entry...Try another one!!!", vbCritical, "Warning...Duplicate!!!"
  clear1
  Exit Sub
  End If
End Sub

here i check sir if the merchandise is already exist in my database and this will display to the txtfield...

Private Sub txtmchno_KeyDown(KeyCode As Integer, Shift As Integer)
 m_mchno = Trim(txtmchno.Text)
   If KeyCode = 13 Then
     If Len(Trim(txtmchno.Text)) <> 5 Then
       MsgBox "The field is empty or you have invalid input... please input 5 characters only..", vbInformation, "Invalid Input...Input again merchandise no."
       Call highlight(txtmchno)
       Exit Sub
       End If
       
     sql = "select * from merchandise_table where mch_no = '" & m_mchno & "'"
     Set mch_rs = con.Execute(sql)
     
     If mch_rs.BOF = True And mch_rs.EOF = True Then
       txtname.SetFocus
     Else
       
        Display
        End If
       ' mch_rs.Close
      End If
End Sub

[vb5prgrmr]

Everything looks good except for line 4, you should use a command object there and not a recordset object, beyond that, the only thing I can say anything about is you indenting of the code in that section needs to be cleaned up a little...

Good Luck

[jemz]

thank you for the reply sir , about my line 4 what is the command object?..can you show me sir what is the command object...thank you in advance..hoping for your positive responds.

[vb5prgrmr]

You know what, it should actually be easier for you to use the connection object to execute you sql insert statement, but here is an example of both from an old test prog...

Option Explicit

Dim S As String

Private Sub Form_Load()

Dim adoCn00 As ADODB.Connection
Dim adoCm00 As ADODB.Command
Dim adoRs00 As ADODB.Recordset

Set adoCn00 = New ADODB.Connection
adoCn00.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\z\temp.mdb;User Id=admin;Password=;"

S = "INSERT INTO tblTest(vText) VALUES('Test Line 1')"
adoCn00.Execute S

Set adoCm00 = New ADODB.Command
adoCm00.ActiveConnection = adoCn00

S = "INSERT INTO tblTest(vText) VALUES('Test Line 2')"
adoCm00.CommandText = S
adoCm00.Execute S

End Sub

Good Luck

[jemz]

thank you sir for the example. i will write again sir if i have some doubt in my program..hoping for your positive responds..more power to you sir..

[jemz]

hello sir, i have some question about that you said that in line 4 i should use the command object not recordset object..can you tell me sir why should i use the comman object not a record set object...?hoping for your positive responds....

[vb5prgrmr]

Because you are inserting a record, not returning a record. When records are returned, use a recordset. When just updating, inserting, or deleting, use either the connection object, or the command object.

Good Luck

[jemz]

gud morning sir..thank you for the reply..okey thank you for explaining me.this really big help to me...more power to you... i will write again for my code about the command object...

[jemz]

here sir,my line 4..correct me if i am wrong sir...hoping for your positive responds...

Private Sub Form_Load()

Set con = New ADODB.Connection
Set user_rs = New ADODB.Recordset
Set cmd = New ADODB.Command


con.Open "provider = microsoft.jet.oledb.4.0; data source = " & App.Path & "\password.mdb;persist security info =false; jet oledb:database password=eldenz;"


If mch_rs.BOF = True And mch_rs.EOF = True Then
  sql = "INSERT INTO merchandise_table(mch_no, mch_name, mch_umsr, mch_qtyh, mch_uprice, mch_rstatus) VALUES('" & txtmchno.Text & "','" & txtname.Text & "','" & cboumsr & "','" & Txtqtyh.Text & "','" & txtuprice.Text & "','" & 1 & "')"
  con.Execute sql

[AndreRet]

The insert code looks fine. Just remember that when you are done with the connection object, close it -

con.Close

I think I will add a bit of praise for vb5prgrmr here. Jemz started with a load of coding totally "distorted". vb5prgrmr took him under his wing and if you have followed all Jemz's other posts/questions, you will notice that he has been taken very far with his coding skills by vb5prgrmr. This is what this site is all about, helping others that are willing to try first and then ask questions.

WELL DONE VB5!!!!
Nicely picked up Jemz...

[jemz]

hello sir thank you for the reply...yes i would like to thank to all who help me and teaching me to develop my skill...more power to you...and i hope you could help more people who needs your help....i love this forum..this is the stepping stone to become a programmer...

[jemz]

hello sir thank you for helping me always, i hope you could help more people like me who needs your help..thank you sir..more power to you....

[AndreRet]

From my side it is always a pleasure Jemz. You are really picking up quickly and I am sure that you WILL become a great programmer.

Keep it up...

[jemz]

i hope sir i will become a great programmer so that i can also help those people who needs my help...i would lilke to thank vb5prgrmr for teaching me and explaining me in times when i have difficulties. also to all who reply and helping me .....more power to all of you..please continue helping people who needs helps....

[vb5prgrmr]

Thank you Andre. ...

Jemz, in your posted code I do not see where you are opening the recordset (could be you did not post it and it goes where line 9 or 10 is...). Also your insert statement looks good but if any of those fields in your database are numbers, then you will want to remove those single ticks (') from around the value to be entered. Then I have a question about this part...

... txtuprice.Text & "','" & 1 & "')"

If that is a boolean field, then use...

... txtuprice.Text & "'," & True & ")"

If not and it is a numeric field, then as I previous stated, remove the single ticks from around it...

... txtuprice.Text & "',1)"

but if it is a text field...

txtuprice.Text & "','1')"

And if you have not figured it out yet, what I am trying to say is that you do not need to seperate out "constant" values (hard coded values) you want to insert.

Good Luck

[jemz]

hello sir thank you for the reply...okey sir i already remove the single stick.yeah it is numeric...thank you for helping me sir .. i will write again if i a have problem again..more power to you...

[jemz]

hello sir is this what you mean opening my recordset?,correct me if i am wrong...hoping for your positive responds...

Private Sub txtmchno_KeyDown(KeyCode As Integer, Shift As Integer)
 m_mchno = Trim(txtmchno.Text)
   If KeyCode = 13 Then
     If Len(Trim(txtmchno.Text)) <> 5 Then
       MsgBox "The field is empty or you have invalid input... please input 5 characters only..", vbInformation, "Invalid Input...Input again merchandise no."
       Call highlight(txtmchno)
       Exit Sub
       End If
       
     sql = "select * from merchandise_table where mch_no = '" & m_mchno & "'"
     Set mch_rs = con.Execute(sql)
     
     If mch_rs.BOF = True And mch_rs.EOF = True Then
       txtname.SetFocus
     Else
       
        Display
        End If
       ' mch_rs.Close
      End If
End Sub

[vb5prgrmr]

Gotcha now, at least I think I do, and I think you have it!

Good Luck

[jemz]

thank you sir, thank you for all the effort and sharing your knowledge in programming...more power to you...