manipulate the data in the database

[hjdaniel.sun]

Hi I used the Data Source Configuration Wizard to connect to my access database, how do I write code to manipulate the data from the database? I read the book "Microsoft Visual Basic 208 step by step", it only teaches how to display data on the form. So how do I write the code so that I can input data and search if the database has this data at runtime?
Cheers,
Daniel

[babbu]

u need to use queries to insert data.
eg.
u begin with establishing a connection with the statement

dim oledbcon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\abc.accdb;Persist Security Info = false;")

u then open the connection using the statement
oledbcon.open

insert query
oledbcom.CommandText = ("INSERT INTO Customer (Company, Address, Telephone) values (?,?,?)")
oledbcom.Connection = oledbcon

adding the parameters.
each question mark in the above statement indicates a parameter.

Dim oledbparam As OleDb.OleDbParameter = _
oledbcom.Parameters.Add("@Company", OleDb.OleDbType.VarChar, 30)
oledbparam.Value = txtCompName.Text.Trim()

oledbparam = oledbcom.Parameters.Add("@Address", OleDb.OleDbType.VarChar, 80)
oledbparam.Value = txtAddress.Text.Trim

oledbparam = oledbcom.Parameters.Add("@Telephone", OleDb.OleDbType.VarChar, 15)
oledbparam.Value = txtContact.Text.Trim

execute the query
oledbcom.ExecuteNonQuery()

close the connection using
oledbcon.close

[hjdaniel.sun]

Hey, Thank you for you code. I don't get the parameter. what is this for?

[hjdaniel.sun]

what is the difference between the "?" in the insert query and the value you add into the parameters? are they the same? if so, do I just write the same value?

[hjdaniel.sun]

This is my code. when I run it. there is nothing happen in my database1.mdb Could you please tell me the problem?

'build access database connection
Dim Conn As New OleDbConnection("provider = microsoft.jet.oledb.4.0;data source = D:\access\Database1.mdb;User ID = Admin;Password =;Persist Security Info = false")
'open database 
Conn.Open()
'insert query and select query, in this case I don't know if select query is needed, but visual studio return errors when I do fill dataset without the select command        
Dim insertSQl As String = "INSERT INTO tbl1(User, password) values (Dave, 123456)"
Dim selectSQL As String = "SELECT * from tbl1"
Dim adapter As New OleDbDataAdapter()
Dim selectcmd As New OleDbCommand(selectSQL, Conn)
adapter.SelectCommand = selectcmd
Dim insertcmd As New OleDbCommand(insertSQl, Conn)
adapter.InsertCommand = insertcmd
'I don't know what the parameters do in the code        
insertcmd.Parameters.Add("@User", OleDbType.VarChar, 30).Value = "Dave"
insertcmd.Parameters.Add("@password", OleDbType.VarChar, 30).Value = "123456"
Dim User As New DataSet
adapter.Fill(User, "User")
Dim password As New DataSet
adapter.Fill(password, "password")
Conn.Close()

[martonx]

You need to execute the query, after parameters add.

insertcmd.ExecuteNonQuery()

[babbu]

regarding ur parameter question
"?" indicates that this is the parameter. it is just any value.
but the value is decided by some other control.
eg.

insert into customer values "abc"

this is the normal query u require in the database
but if u wish to write this statement in .net where the name is taken from a textbox.

insert into customer ?

the question mark indicates that the value is to be taken from some control.
assign a value to it.

[__avd]

hjdaniel.sun,

manipulate the data in the database

You are struggling with programming. Keep one thing in mind, we will help you in case you have a little problem. We will try out best but my suggestion to you that - Don't waste your time. Get some books - Database, .net framework, VB.NET language (OOP. .NET programming requires fundamental knowledge of Object-Oriented paradigm.

[hjdaniel.sun]

Thank you, but I do reading. "Step by step VB.NET" and msdn

[hjdaniel.sun]

I found the problem. it is somehow the insert statement. it seems that I can't put specific columns that I want to insert data in, in the insert query... I then change it to "INSERT INTO tbl1 VALUES (?, ?)" it works now.

[babbu]

yes you can my friend
the column names should also be enclosed within brackets

[mister p]

HI, guys how does one add the date and time onto the status bar? im coding in vb.net 2008

[hjdaniel.sun]

You can try to use timer control. grab a label and a timer control from the toolbox. double click on the timer control and go into the coding environment. type in label1.text = timestring
you can also try system.datetime.now method as well
you need to type in label1.text = system.datetime.now
Hope this helps