Solved

when checking if there is a table alreay in my database

Posted on 2008-10-12
13
238 Views
Last Modified: 2013-11-26
HI all
I am trying to see if there is already a table in my database the guy that hav been trying to help me have been great but it seems that i am now getting another error

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"

When the code runs

Below is my code thanks for the help in advance

my other question id is ID:23807380
Module update

    Public Sub dbu()

        connect()
 

        Dim rs As New ADODB.Recordset

        Dim dt As DataTable = cnn1.GetSchema("Tables")

        Dim sql As String

        Dim results() As DataRow = dt.Select("table_name='update'")
 

        If results.Length = 0 Then 'if not found
 

            sql = "CREATE TABLE Update (ID integer PRIMARY KEY ,Update char(255))"

            Debug.Print(sql)

            SplashScreen1.updateinfo.Text = ("Stars Datase Update (New Table,""Update"")")

            MsgBox("Stars Datase Update (New Table,""Update"")")

            rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
 

        End If
 
 

        results = dt.Select("table_name='Job_list'")

        If results.Length = 0 Then 'if not found

            sql = "CREATE TABLE Job_List (ID integer PRIMARY KEY ,Joblist char(255))"

            Debug.Print(sql)

            SplashScreen1.updateinfo.Text = ("Stars Datase Update (New Table,""Job_List"")")

            MsgBox("Stars Datase Update (New Table,""Job_List"")")

            rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

        End If

        cnn1.Close()

    End Sub
 

End Module

Open in new window

0
Comment
Question by:awolarczuk
  • 7
  • 6
13 Comments
 
LVL 13

Expert Comment

by:TechTiger007
ID: 22696705
You are trying to create a table with name "Update". Update is a keyword in most dbms. Try using the name within square bracketss "[Update]" or try with another name for the table
0
 

Author Comment

by:awolarczuk
ID: 22696718
Dim dt As DataTable = cnn1.GetSchema("Tables")

getting the error on this part of the code mate but have changed the update to updatet
0
 
LVL 13

Expert Comment

by:TechTiger007
ID: 22696753
0
 

Author Comment

by:awolarczuk
ID: 22696772
thanks mate but what i am looking at here i am a bit lost if you wouldnt mind making it a example i learn better that way
0
 
LVL 13

Expert Comment

by:TechTiger007
ID: 22696864
The code snippet was in C# and it was using data adaptor factory to create classes

I have converted the code to vb.net and made it a function. I am facing some issue with my MS Access so unable to test it below is the code. Try it out

Private Function TableExist(ByVal TableName As String) As Boolean
        Dim userTables As DataTable
        Dim connection As OleDbConnection = New OleDbConnection

        'c:\test\test.mdb
        connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\test\database1.mdb"

        'We only want user tables, not system tables
        Dim restrictions(4) As String
        restrictions(3) = "Table"

        connection.Open()

        'Get list of user tables
        userTables = connection.GetSchema("Tables", restrictions)

'Use this block or the one below. This should be more effective. Also check if the column name is "table_name" in the userTables
        Dim results() As DataRow = userTables.Select("table_name='update'")
        If (results.Length > 0) Then
            TableExist = bTableExist
        End If
'Use this block or the one above
        Dim bTableExist As Boolean = False
        For i = 0 To userTables.Rows.Count
            If userTables.Rows(i)("table_name") = TableName Then
                bTableExist = True
                Exit For
            End If
        Next

        TableExist = bTableExist
    End Function
0
 

Author Comment

by:awolarczuk
ID: 22696896
hey tech thanks mate

This is the code i am using i am hoping that you could help me fix this so it works if not mearge it with yours if you wouldn't mind i am having a little bit of troube understand your correctly, maybe i have been at this to long today but i just need to get this little part done and it all done my whole app

Thanks in advance mate
Module update

    Public Sub dbu()

        connect()
 

        Dim rs As New ADODB.Recordset

        Dim sql As String
 

        sql = "select * from MSysNavPaneGroups where [name] =  job_information "

        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
 

        If (rs.BOF Or rs.EOF) Then

            GoTo updatet

        End If

updatet:

        sql = "CREATE TABLE Job_Information (ID integer PRIMARY KEY ,Joblist char(255))"

        Debug.Print(sql)

        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)

        SplashScreen1.updateinfo.Text = ("Stars Datase Update (New Table,""Update"")")

        rs.Update()

        MsgBox("Stars Datase Update (New Table,""Update"")")

        cnn1.Close()

    End Sub
 

End Module

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Accepted Solution

by:
TechTiger007 earned 500 total points
ID: 22697026
What is the issue you are facing with this part of code?
I find that your select query is missing quotes. Change it to
sql = "select * from MSysNavPaneGroups where [name] =  'job_information'"
0
 

Author Comment

by:awolarczuk
ID: 22698397
Record(s) cannot be read; no read permission on 'MSysNavPaneGroups'.

Mate the above is the error i am facing wit this part any ideas
0
 
LVL 13

Expert Comment

by:TechTiger007
ID: 22698460
Check if the userid used to connect to the database has got access to read data
For connection string check this out
http://www.connectionstrings.com/?carrier=access
Also check the account under which your application is running has got read permission on the file
0
 

Author Comment

by:awolarczuk
ID: 22701129
i am using the normal sa log with no password what do i need to change it to to get ths working or is there another way
0
 
LVL 13

Expert Comment

by:TechTiger007
ID: 22705254
So I am assuming that you have specified "sa" as user id and "" as password in your connection string. and "sa" has got "read" privilege to this database table MSysNavPaneGroups. Am I right?
0
 

Author Comment

by:awolarczuk
ID: 22708173
it has got read for all the tables i have added but this is system table is there another way i need to do this
0
 

Author Comment

by:awolarczuk
ID: 22729358
ok i have just read the comment i put maybe doesnt make a lot of sence

Ok

I am using the normal Sa with no password to access the Tables

when i try to read the system table from acess i get a read error

This connection is working all hte say through my code just doesnt work here

is there something esle i need to do for system tables
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

746 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

12 Experts available now in Live!

Get 1:1 Help Now