Solved

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

Posted on 2004-10-03
15
258 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now