[RESOLVED] I Need some advice and help

[nokmaster]

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

i made a database in ms access with tables

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:

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

[okosv]

Use SQL query

[okosv]

Using this code you'll get just '3333333333'

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

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

[nokmaster]

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

br

[ganeshmoorthy]

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

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

[Green Africa]

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

[nokmaster]

thanks guys for your help..

sql code, can be use in adodc?

[Green Africa]

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

rsRecset.Open "Select * from table"

[nokmaster]

@Green Africa

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

thanks and advance

br

[ganeshmoorthy]

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

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

[nokmaster]

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..

[Green Africa]

Ok hope this helps

[nokmaster]

thanks a lot..

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

br

[nokmaster]

@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,

[Green Africa]

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

[nokmaster]

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

thanks and advanced

br

[Green Africa]

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

[nokmaster]

Thank you so much for your help.

Best Regards,