Ad:

VB.NET Discussion Thread
Marked Solved
Views: 1316

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Jul 3rd, 2009

manipulate the data in the database

Expand Post »

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

Similar Threads

view and edit data from database in VB.NET
how VB get data from database randomly in Visual Basic 4 / 5 / 6
Login and retrieve user data from database in ASP.NET
How to insert data into database? in JSP
inser data into database in JSP

hjdaniel.sun

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

19 posts
since Jun 2009

Permalink

Jul 5th, 2009

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

babbu

Reputation Points: -1

Solved Threads: 23

Posting Whiz in Training

Offline

207 posts
since Jun 2009

Permalink

Jul 10th, 2009

Re: manipulate the data in the database

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

hjdaniel.sun

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

19 posts
since Jun 2009

Permalink

Jul 10th, 2009

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?

hjdaniel.sun

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

19 posts
since Jun 2009

Permalink

Jul 10th, 2009

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?

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

hjdaniel.sun

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

19 posts
since Jun 2009

Permalink

Jul 10th, 2009

Re: manipulate the data in the database

You need to execute the query, after parameters add.

insertcmd.ExecuteNonQuery()

martonx

Reputation Points: 10

Solved Threads: 8

Junior Poster in Training

Offline

51 posts
since May 2008

Permalink

Jul 10th, 2009

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.

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.

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.

babbu

Reputation Points: -1

Solved Threads: 23

Posting Whiz in Training

Offline

207 posts
since Jun 2009

Permalink

Jul 10th, 2009

Re: manipulate the data in the database

hjdaniel.sun,

Quote ...

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.

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,527 posts
since Oct 2008

Permalink

Jul 10th, 2009

Re: manipulate the data in the database

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

hjdaniel.sun

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

19 posts
since Jun 2009

Permalink

Jul 11th, 2009

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.

hjdaniel.sun

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

19 posts
since Jun 2009

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Upload file via PHP upload form.

Next Thread in VB.NET Forum Timeline: Receiving data from modem with MScomm

DaniWeb Message

Cancel Changes

User Name
Password