?
Solved

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

Posted on 2004-10-03
15
Medium Priority
?
278 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
Technology Partners: 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

770 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