Member Avatar for DeOiD

hi

i'm making a little application in vb.net and i need to use a data base. so i used some of my aspx functions and the same data base. the problem is that every time i run the application and do something that needs to get values from that db it gives this error: "error in FROM..."

i'm using this code:

Dim DBliga As OleDbConnection
        Dim DBcomando As OleDbCommand
        Dim DBdatareader As OleDbDataReader
        Dim result

        DBliga = New       OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=I:\Visual Studio 2005\Projects\gestao financeira\gestao financeira\gefi.accdb;Persist Security Info=False;")

        DBliga.Open()

        DBcomando = New OleDbCommand("select nome from user", DBliga)

        DBdatareader = DBcomando.ExecuteReader()
        If DBdatareader.Read Then
            result = DBdatareader.Item(0)
            DBliga.Close()
            DBdatareader.Close()
            seleccionar = result
        End If

the table exists so i don´t know whats the problem...i'm guessing that i can't use access 2007..... does anyone knows whats the problem?

  • 3 Contributors
  • 3 Replies
  • 423 Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by aadi_capri

Dani AI

Generated

Good catch by — the “error in FROM…” here is the classic Access SQL parser hitting an identifier that’s a reserved word. Microsoft Access treats words like USER as reserved; an unescaped identifier with that name will make the query fail. Surrounding such names with square brackets or renaming the table removes the ambiguity. (support.microsoft.com)

Practical choices: rename the table to a nonreserved identifier (for example, Users or TblUser) so future code, tools and drivers do not stumble. If renaming is not possible, always escape object names you generate with square brackets. Centralizing connection logic (as suggested) makes it simple to change provider, path, or credentials in one place. (support.microsoft.com)

A few robustness tips that cut down on bugs shown in the thread: prefer ExecuteScalar when a query returns a single value; wrap connections/commands/readers in Using blocks so they are disposed automatically; and when using a DataReader either close it before closing the connection or call ExecuteReader with CommandBehavior.CloseConnection so closing the reader also closes the connection. These practices prevent resource leaks and ordering problems (for example, closing a connection while a reader is still open). (learn.microsoft.com)

' VB.NET pattern: single-value read with Using + ExecuteScalar
Using cn As New OleDbConnection(connectionString)
    Using cmd As New OleDbCommand("SELECT ColumnName FROM TableName", cn)
        cn.Open()
        Dim obj = cmd.ExecuteScalar()
        Dim result = If(obj IsNot Nothing, obj.ToString(), String.Empty)
    End Using
End Using

Deployment note: .accdb files require the Access/ACE provider on the machine and the provider’s bitness must match the process (install the Access Database Engine redistributable if needed). Avoid hard-coded absolute paths in connection strings; keep the DB path in configuration and deploy the file with the app. (microsoft.com)

Member Avatar for DeOiD

i found the anwser and it's really stupid...

this is my original sql string: "select nome from user" this is the working string: "select nome from [user]" no comments...

Member Avatar for anesaka

hi

i'm making a little application in vb.net and i need to use a data base. so i used some of my aspx functions and the same data base. the problem is that every time i run the application and do something that needs to get values from that db it gives this error: "error in FROM..."

i'm using this code:

Dim DBliga As OleDbConnection
        Dim DBcomando As OleDbCommand
        Dim DBdatareader As OleDbDataReader
        Dim result

        DBliga = New       OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=I:\Visual Studio 2005\Projects\gestao financeira\gestao financeira\gefi.accdb;Persist Security Info=False;")

        DBliga.Open()

        DBcomando = New OleDbCommand("select nome from user", DBliga)

        DBdatareader = DBcomando.ExecuteReader()
        If DBdatareader.Read Then
            result = DBdatareader.Item(0)
            DBliga.Close()
            DBdatareader.Close()
            seleccionar = result
        End If

the table exists so i don´t know whats the problem...i'm guessing that i can't use access 2007..... does anyone knows whats the problem?

you can try this my code below

Imports System.Data.OleDb

Module Module1
Public Function connect() As OleDbConnection
'connection to database access 2007 using password
Dim con As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\databasename.accdb;Persist Security Info=True;Jet OLEDB:Database Password=123;"
Dim conec As New OleDbConnection(con)
conec.Open()
Return conec
End Function
End Module

note: this syntac put in formload on your project
---------------
Dim Adapter As New OleDbDataAdapter("select * from tablename order by name asc", Module1.connect)
Dim dt As New DataTable("tablename")
Adapter.Fill(dt)
DataGridView1.DataSource = dt
---------------
your database must put in bin folder in your application
with this code you can access anywhere for your database
n this database using password, if your database not using password
change Persist Security Info=false and without password.

for more goto to http://vbcode.my-php.net

Member Avatar for aadi_capri

deodid dude you are a life saver.......your suggestion for [user],really helped i have been going through the problem for two days........and finally i have also found the stupid answer for it....
thanks a lot brother.....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.