[RESOLVED] I Need some advice and help

hello, i am creating a simple registration database and i used the adodc component

i made a database in ms access with tables

Quote:

---

pin
mobile number
date registered
expiry date

---

now, i have 4 TextBox in my vb Form1 ; txtPin,txtMobileNumber,txtDateR,txtEDate and 6 Command Button ; add pin,save pin,delete account,update account,cancel,edit account

first i saved 5 pins in my database..

Example:

Quote:

---

1111111111
2222222222
3333333333
4444444444
5555555555

---

now, i have 2 TextBox ; txtReceived_MobileNumber,txtReceived_Message

if received a message from txtReceived_Message
with the format: PowSMS 3333333333 i want this 3333333333 find first in my database if this is exist, if exist i want the mobile number from txtReceived_MobileNumber register in pin 3333333333 also appear the registered date and expiry date..

Thanks and Advance

br
:)

Use SQL query

Using this code you'll get just '3333333333'

VB Code:

strRecievedMessage="PowSMS 3333333333"
strRecievedMessage=Replace(strRecievedMessage,"PowSMS","")
strRecievedMessage=Trim(strRecievedMessage)

Than using sql query search in database, etc...

anyone can help with this? sample code etc.. in newbie in this field :(

br

PowSMS 3333333333, is this should check your pin in the database. Use Okosv's code to get this number alone and use

VB Code:

Sql = "Select * From YourTableName Where Pin = '" & strReceivedMessage & "'"
Rs.Open Sql, Cn, .....
If Not Rs.EOF Then
    MsgBox "Mobile Number : " & (rs!MobileNumber & "") & " Date Registered : " & (rs!DateRegistered & "") & " Expiry Date : " & (Rs!ExpiryDate & "")
Else
    MsgBox "No Matching PIN Found in the Database !"
End If
Rs.Close

This should do it...

'set Reference "Microsoft ActiveX Data Objects 2.6 Library"
'create conection object and connect to DB
Dim conDB As ADODB.Connection
Set conDB = New ADODB.Connection
'I use a ADO contole to build the connection string
conDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\gaisrun\gais.mdb;Persist Security Info=False"
Dim rsRecset As ADODB.Recordset
Set rsRecset = New ADODB.Recordset

txtReceived_Message.Text = Replace(txtReceived_Message.Text, "PowSMS", "")
rsRecset.Open "Select * from table where pin = '" & Trim(txtReceived_Message.Text), conDB 'Add single quotes if pin is of type text
If rsRecset.EOF = False Then
rsRecset.MoveFirst
'Good idea to make your table fields one word
txtPin.Text = rsRecset!pin
txtMobileNumber.Text = rsRecset!mobileNumber
txtDateRegistered.Text = rsRecset!DateRegistered
txtExpiryDate.Text = rsRecset!expiryDate
End If
rsRecset.Close

thanks guys for your help..

sql code, can be use in adodc?

No you create an ADODB.Recordset
ie.
Dim rsRecset As ADODB.Recordset
Set rsRecset = New ADODB.Recordset

rsRecset.Open "Select * from table"

@Green Africa

opss no idea with, can you make sample source code? with the same procedure of my post

thanks and advance

br
:wave:

Im just putting the above codes into a proc...

VB Code:

Private Sub DisplayDetails()
    strRecievedMessage=Replace(txtReceived_Message,"PowSMS","")
    'set Reference "Microsoft ActiveX Data Objects 2.6 Library"
    'create conection object and connect to DB
    Dim conDB As ADODB.Connection
    Set conDB = New ADODB.Connection
    'I use a ADO contole to build the connection string
    conDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\gaisrun\gais.mdb;Persist Security Info=False"
    Dim rsRecset As ADODB.Recordset
    Set rsRecset = New ADODB.Recordset
    rsRecset.Open "Select * from table where pin = '" & Trim(strReceivedMessage) & "'", conDB  
    If Not rsRecset.EOF Then
        rsRecset.MoveFirst
        'Good idea to make your table fields one word
        txtPin.Text = rsRecset!pin
        txtMobileNumber.Text = rsRecset!mobileNumber
        txtDateRegistered.Text = rsRecset!DateRegistered
        txtExpiryDate.Text = rsRecset!expiryDate
    End If
    rsRecset.Close
End Function

can you make it in source and zip then upload here so that i can check it properly.. thanks guys for your time helping me.. :)

Ok hope this helps

thanks a lot..

i will try.. ill be back in 1 hour..

br
:wave:

@Green Africa

hi, tried your code and it works fine, how to add data from table accounts with sql? like add,save,update,delete

br,
:wave:

add & save\update is almost the same.

You need to open your recordset for update ie rsRecset.Open "Select * from table where pin = '" & Trim(strReceivedMessage) & "'", conGais, adOpenDynamic, adLockOptimistic

then for Insert\Add

rsRecset.AddNew

then assign all the values to the recordset ie

rsRecset!Pin = txtPin.Text
rsRecset!mobileNumber = txtMobileNumber

after you assigned all the information your save it to the database by

rsRecset.Update

The Delete is the Easiest ie

rsRecset.Open "Delete from table where pin = '" & Trim(strReceivedMessage) & "'", conGais, adOpenDynamic, adLockOptimistic

aha.. thanks for that code.. and last favor sir can you attach it in zip...

thanks and advanced

br
:wave:

Thats it hope you'll get the hang of it good luck

Thank you so much for your help.

Best Regards,
:wave: