[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

VERY URGENT!!!!! 500Points... Access with sql query....

Posted on 2004-10-03
15
Medium Priority
?
283 Views
Last Modified: 2010-04-23
Hy,

I want to see a simple example on how to insert, update, search and delete a record in an Access Database. All made with sql querys. I know the sql qury part but don't know the vb part....

Thanks a lot. I'm waiting for your response.

And....please....IT"S URGENT.....:((((((
0
Comment
Question by:drcyrus3d
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12212908
SELECT * FROM tblExample WHERE Id = ?
in the Where clause you must the search criteria of your rows.

UPDATE tblExample SET Value1 = ?, Value2 = ? WHERE Id = ?
here in the Where clause you must put the search criteria of you rows to Update.

DELETE * FROM tblExample WHERE Id = ?
the same thing.

INSERT INTO tblExample VALUES(1,'George', 'Hello')
Between the () you must put the values in the order that they are created in the Table.

I hope this could help you :)
0
 
LVL 1

Expert Comment

by:telefoniaip
ID: 12213044
Are you using visual.net ?
Do you need use a ODBCCONNECTION or ado sqlconnection ?
Do you need see the result for SELECT option in a data grid  o only make a select ?
I can help you but need more information.

Regards
Toto
0
 
LVL 4

Expert Comment

by:eozz_2000
ID: 12213068
You asked as an urgent question... where are you?, do you have any doubt about my answer?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:drcyrus3d
ID: 12213140
I want to connect to an access database...and i want to use sql querys to select items. I know sql statements.... but i don't where to start with programming...that's the ideea. I want a clear example a simple program that connects to the database and it has a text box in wich i put the sql query and the program take it and executes it.

0
 
LVL 1

Author Comment

by:drcyrus3d
ID: 12213144
I found this for a conenction and a simple sql but how do i get the data after i use a select, insert, update or delete????

Private Function GetDataFromAccessDB() As Boolean
    Dim oConn As ADODB.Connection
    Dim oRec  As ADODB.Recordset
    Dim sSQL As String
    Dim sDB As String

On Error GoTo ErrHandler

    Set oConn = New ADODB.Connection
    Set oRec = New ADODB.Recordset
   
    'your access database name alongwith path
    sDB = "D:\VB Samples\test.mdb"
   
    'build connection string
    oConn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & sDB
   
    'build your query
    sSQL = "SELECT * FROM tblEmployee WHERE EmployeeId > " & txtX.Text & " AND EmployeeId < " & txtY.Text
    Set oRec = oConn.Execute(sSQL, lngRecs)
   
    oRec.Close
    oConn.Close
    Exit Function
ErrHandler:
    MsgBox "" & Err.Number & ", " & Err.Description, vbCritical, "Error"
End Function

I'm new to vb.net ...:((...and it changed a lot from vb 6.
How do i refer to the element that the select statement searches for??? how can i print thre result with a messagebox??? Thanks a lot.
0
 
LVL 1

Author Comment

by:drcyrus3d
ID: 12213146
Sorry for the delay but my ISP had some problems.... :(( i didn't had internet connection for almost an hour. :((((((
0
 
LVL 34

Expert Comment

by:flavo
ID: 12213239
at the top of the class add

imports system.data.Oledb


Then you can use (NOTE ** AIR CODE ** - NO VS on hand)

dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\myPath\myDb.mdb"
dim Con as new OleDbconnection(sConnectionString)
dim rs as oleDbdatareader
dim sSQL as String = SELECT * FROM tblEmployee WHERE EmployeeId > " & Me.txtX.Text & " AND EmployeeId < " & Me.txtY.Text
dim cmdqSel as new OledbCommand(sSQL, con)
dim sMsg as string

while rs.read()
    sMsg = sMsg & chr(10) & rs.getvalue(0) & rs.getvalue(1) 'get first 2 rows into Msgbox
wend

'clean up - i think these are right
cmdqSel.dispose()
rs.close()
rs.dispose()
con.close()

msgbox sMsg

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 12213241
>> 'get first 2 rows into Msgbox

'get first 2 columns into Msgbox

0
 
LVL 1

Author Comment

by:drcyrus3d
ID: 12213270
i get this error at the "While rs.read()" line.

An unhandled exception of type 'System.NullReferenceException' occurred in Access with SQL.exe

Additional information: Object reference not set to an instance of an object.

Any idea???
0
 
LVL 34

Expert Comment

by:flavo
ID: 12213279
yeah, never opened it

Add this

rs = cmdqSel.executereader()

just beofre while rs.read()

Dave
0
 
LVL 1

Author Comment

by:drcyrus3d
ID: 12213296
I''ve inserted it just as you said and it gives me another error for the new linwe added:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: ExecuteReader requires an open and available Connection. The connection's current state is Closed.
0
 
LVL 34

Expert Comment

by:flavo
ID: 12213322
damn!

That's cause we never opened the conneciton

need to add this

con.Open()

before
dim cmdqSel as new OledbCommand(sSQL, con)
0
 
LVL 1

Author Comment

by:drcyrus3d
ID: 12213347
Another error ar the rs=cmdqSel.ExecuteReader()

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

:)
0
 
LVL 34

Expert Comment

by:flavo
ID: 12213353
hmmm... im stumped... Can you post the code you are using now...
0
 
LVL 34

Accepted Solution

by:
flavo earned 1500 total points
ID: 12213374
ok, just did atest with web matrix

dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\myPath\myDb.mdb"
dim Con as new OleDbconnection(sConnectionString)
dim rs as oleDbdatareader
Dim sSQL as String = "SELECT * FROM tbTest"
Dim cmdqSel as new OledbCommand(sSQL, con)
con.open()

rs = cmdqSel.ExecuteReader()

While rs.Read()

     Response.Write(rs.GetValue(1))
     Response.Write("<br/>")


End While

con.Close

It works
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question