Solved

when checking if there is a table alreay in my database

Posted on 2008-10-12
13
243 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
Generate Unique ID in VB.NET 21 67
Where is this file? 3 26
C#.net split string and then check appropriate checkboxlist 4 27
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
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…

831 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