Solved

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

Posted on 2004-10-03
15
275 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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