manipulate the data in the database
 

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

Re: manipulate the data in the database
 

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

Re: manipulate the data in the database
 

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

Re: manipulate the data in the database
 

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?

Re: manipulate the data in the database
 

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()

Re: manipulate the data in the database
 

You need to execute the query, after parameters add.

insertcmd.ExecuteNonQuery()

Re: manipulate the data in the database
 

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.

Re: manipulate the data in the database
 

hjdaniel.sun,

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.

Re: manipulate the data in the database
 

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

Re: manipulate the data in the database
 

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.