Solved

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

Posted on 2004-10-03
15
274 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
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 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: 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

Suggested Solutions

Title # Comments Views Activity
Modal Popup Extender control 1 49
Obtain cell value using column ID in DataGrid using VB.NET 2 34
Create a datatable in vb.net dynamically 1 38
Visual studio 2015 1 34
This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 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