VB.NET

VB.NET RSS

manipulate the data in the database

Please support our VB.NET advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jun 2009

Posts: 19

Reputation: hjdaniel.sun is an unknown quantity at this point

Solved Threads: 1

hjdaniel.sun hjdaniel.sun is offline

Offline

Newbie Poster

manipulate the data in the database

Jul 3rd, 2009

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

•

Join Date: Jun 2009

Posts: 132

Reputation: babbu has a little shameless behaviour in the past

Solved Threads: 13

babbu

Offline

Junior Poster

Re: manipulate the data in the database

Jul 5th, 2009

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

•

Join Date: Jun 2009

Posts: 19

Reputation: hjdaniel.sun is an unknown quantity at this point

Solved Threads: 1

hjdaniel.sun hjdaniel.sun is offline

Offline

Newbie Poster

Re: manipulate the data in the database

Jul 10th, 2009

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

•

Join Date: Jun 2009

Posts: 19

Reputation: hjdaniel.sun is an unknown quantity at this point

Solved Threads: 1

hjdaniel.sun hjdaniel.sun is offline

Offline

Newbie Poster

Re: manipulate the data in the database

Jul 10th, 2009

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?

•

Join Date: Jun 2009

Posts: 19

Reputation: hjdaniel.sun is an unknown quantity at this point

Solved Threads: 1

hjdaniel.sun hjdaniel.sun is offline

Offline

Newbie Poster

Re: manipulate the data in the database

Jul 10th, 2009

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

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
'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()
'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()

Last edited by hjdaniel.sun; Jul 10th, 2009 at 7:02 am. Reason: I didn't write the code nicely

•

Join Date: May 2008

Posts: 51

Reputation: martonx is an unknown quantity at this point

Solved Threads: 8

martonx

Offline

Junior Poster in Training

Re: manipulate the data in the database

Jul 10th, 2009

You need to execute the query, after parameters add.

insertcmd.ExecuteNonQuery()

•

Join Date: Jun 2009

Posts: 132

Reputation: babbu has a little shameless behaviour in the past

Solved Threads: 13

babbu

Offline

Junior Poster

Re: manipulate the data in the database

Jul 10th, 2009

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.

Help with Code Tags

VB.NET Syntax (Toggle Plain Text)

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.

Help with Code Tags

VB.NET Syntax (Toggle Plain Text)

insert into customer ?

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

•

Join Date: Oct 2008

Posts: 2,639

Reputation: adatapost has much to be proud of

Solved Threads: 472

adatapost

Offline

Posting Maven

Re: manipulate the data in the database

Jul 10th, 2009

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.

Failure is not fatal, but failure to change might be. - John Wooden

•

Join Date: Jun 2009

Posts: 19

Reputation: hjdaniel.sun is an unknown quantity at this point

Solved Threads: 1

hjdaniel.sun hjdaniel.sun is offline

Offline

Newbie Poster

Re: manipulate the data in the database

Jul 10th, 2009

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

•

Join Date: Jun 2009

Posts: 19

Reputation: hjdaniel.sun is an unknown quantity at this point

Solved Threads: 1

hjdaniel.sun hjdaniel.sun is offline

Offline

Newbie Poster

Re: manipulate the data in the database

#10

Jul 11th, 2009

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.