Results 1 to 23 of 23

Thread: [RESOLVED] Creating database table

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Resolved [RESOLVED] Creating database table

    I wrote this code to create database table and when I run it, it returns true which means the table has been created but when I check the database, I don't see the table
    Code:
     public functionCreateTable() as boolean
    dim con as new sqlconnection(cstrimg)
    dim sqlcmd as string= "CREATE TABLE [MASTER TABLE]" &_
    "(" & _
    "[ID KEY] SMALLINT NOT NULL," & _
    "[NAME] TEXT NOT NULL"
    
    Dim Creator as new sql command (sqlcmd, con)
    con.open
    if creator.executeNonQuery() then
    return true
    else
    return false
    con.close
    End function
    Last edited by si_the_geek; Jan 4th, 2018 at 03:24 AM. Reason: fixed typo in tags
    Nothing I post is Self Reliable. Use it at your own risk

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating database table

    The very first thing you need to do is to turn Option Strict On for this project and any other project you're working on, plus turn it On in the IDE options too so that it will be On for every future project. That code will not compile with Option Strict On, so it is bad code. You need to start thinking about your data types more, which will help you write more robust code.

    As for this issue, if the table gets created but you don't see it then you're looking in the wrong place. Are you using a local data file, i.e. an MDF file that is part of your project and gets attached to a SQL Server Express instance at run time?

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Creating database table

    in my signature is a link "I swear I saved my data, where'd it run off to?" ... check it out. It probably applies in this case.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Creating database table

    I have a feeling that we may not be seeing the real code, as there is no ")" put in sqlcmd, and there is an extra space in new sql command

    If so, there is a good chance that the cause of the problem is being hidden from us.

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Creating database table

    Hi,

    could also be a Typo..

    Code:
    dim con as new sqlconnection(cstrimg)
    'should probably be cstring
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    ok this is the actual code I wrote
    Code:
    Public Function CreateArmTable(ByVal TableName As String) As Boolean
            Dim connection As New SqlConnection(CString)
            Dim sqlCmd As String = "CREATE TABLE" & " " & "[" & TableName & "]" & _
        "(" & _
        "[ADMIN_NUMBER] INT NOT NULL PRIMARY KEY, " & _
        "[STUDENT NAME] TEXT NOT NULL, " & _
        "[C.A.1] SMALLINT NULL, " & _
        "[C.A.2] SMALLINT NULL, " & _
        "[C.A.3] SMALLINT NULL, " & _
        "[EXAM] SMALLINT NULL, " & _
        "[TOTAL] SMALLINT NULL, " & _
        "[POSITION] SMALLINT NULL, " & _
        "[GRADE] SMALLINT NULL" & _
        ")"
    
            Dim CreatorCommand As New SqlCommand(sqlCmd, connection)
            connection.Open()
    
            If CreatorCommand.ExecuteNonQuery() Then
                Return True
            Else
                Return False
            End If
            connection.Close()
    
        End Function
    Nothing I post is Self Reliable. Use it at your own risk

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    All my codes option strict are always on
    Nothing I post is Self Reliable. Use it at your own risk

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Creating database table

    Hi,

    this is how I create a Table with Access...

    change the OLE to SQL

    Code:
    Public Sub CreateTable()
            Dim sSQL As String
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\ZooKeeper.mdb")
            'here you create your Table in the Database
            sSQL = sSQL & "  Create Table tbl_Zoo"
            sSQL = sSQL & "( [ZO_ID] Integer Identity"
            sSQL = sSQL & ", [ZO_Firstname] varChar(50)"
            sSQL = sSQL & ", [ZO_Surname] varChar(50) "
            sSQL = sSQL & ")"
            con.Open()
            ExecuteSQL(con, sSQL)
            con.Close()
            con = Nothing
        End Sub
    
    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                         ByVal sSQL As String, _
                                         Optional ByRef ErrMessage As String = Nothing, _
                                         Optional ByVal TransAction As  _
                                         OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Creating database table

    Other than field names I don't care for... I don't see anything wrong with the SQL...

    If you're not getting an error, but not seeing the table... then I suspect the table is being created but not in the database file you're looking in.
    What does your connection string look like? Are you using a connected database (to a SQL Server instance of some kind) or are you using a "LocalFile" ?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    Quote Originally Posted by techgnome View Post
    Other than field names I don't care for... I don't see anything wrong with the SQL...

    If you're not getting an error, but not seeing the table... then I suspect the table is being created but not in the database file you're looking in.
    What does your connection string look like? Are you using a connected database (to a SQL Server instance of some kind) or are you using a "LocalFile" ?

    -tg
    This is my connection string
    Code:
    Private CString As String = "Data Source=(LocalDB)\v11.0 ;AttachDbFilename=|DataDirectory|\karate_Data.MDF;Integrated Security=True;Connect Timeout=30"
    Nothing I post is Self Reliable. Use it at your own risk

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    Quote Originally Posted by ChrisE View Post
    Hi,

    this is how I create a Table with Access...

    change the OLE to SQL

    Code:
    Public Sub CreateTable()
            Dim sSQL As String
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\ZooKeeper.mdb")
            'here you create your Table in the Database
            sSQL = sSQL & "  Create Table tbl_Zoo"
            sSQL = sSQL & "( [ZO_ID] Integer Identity"
            sSQL = sSQL & ", [ZO_Firstname] varChar(50)"
            sSQL = sSQL & ", [ZO_Surname] varChar(50) "
            sSQL = sSQL & ")"
            con.Open()
            ExecuteSQL(con, sSQL)
            con.Close()
            con = Nothing
        End Sub
    
    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                         ByVal sSQL As String, _
                                         Optional ByRef ErrMessage As String = Nothing, _
                                         Optional ByVal TransAction As  _
                                         OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    regards
    Chris
    I'm not writing an Sql database file
    Nothing I post is Self Reliable. Use it at your own risk

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating database table

    Quote Originally Posted by TATARPRO View Post
    This is my connection string
    Code:
    Private CString As String = "Data Source=(LocalDB)\v11.0 ;AttachDbFilename=|DataDirectory|\karate_Data.MDF;Integrated Security=True;Connect Timeout=30"
    And which database are you looking in to see the table? Is it the one in the project folder? If so then that is the wrong database. Post #3 points you to a documentation page that explains how local data files work. The first link in my signature below leads to that same page.

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating database table

    Quote Originally Posted by TATARPRO View Post
    I'm not writing an Sql database file
    You are using a SQL Server database. That's what MDF files are. SqlConnection and SqlCommand are members of the SqlClient namespace, which is specifically for SQL Server.

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating database table

    Quote Originally Posted by TATARPRO View Post
    All my codes option strict are always on
    Except that that's simply not possible. This line would not compile with Option Strict On:
    Code:
    If CreatorCommand.ExecuteNonQuery() Then

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    Quote Originally Posted by jmcilhinney View Post
    You are using a SQL Server database. That's what MDF files are. SqlConnection and SqlCommand are members of the SqlClient namespace, which is specifically for SQL Server.
    I meant i'm not writing an Access database
    Nothing I post is Self Reliable. Use it at your own risk

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    I've continuosly checked the compiler options and my option strict is on and i've compiled the code but it still gives me the same result
    Nothing I post is Self Reliable. Use it at your own risk

  17. #17
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating database table

    You didn't answer my questions in post #12. If you have read the page you were directed to in post #3 then you should understand that you have a source data file in your project folder and a working data file in your output folder and that any changes you make at run time are made to the working copy, which have no effect on the source file and thus can be lost if and when the source file is next copied over the working copy. If you are making changes to the working copy and then looking at the source file for those changes then of course you won't see them.

    Maybe that's not what you're doing but you've been asked to provide information will help us determine whether you are and you've failed to provide it. If we ask you for information, we are doing so because we think we need it in order to help you. Do you want that help or not? If you do then provide the information you're asked for when you're asked for it.

  18. #18
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating database table

    Quote Originally Posted by TATARPRO View Post
    I've continuosly checked the compiler options and my option strict is on and i've compiled the code but it still gives me the same result
    You say that now but you also said it earlier when it clearly wasn't true, so it's hard to know whether you're correct now or still mistaken. More than one person has claimed that they had Option Strict On when it was actually Off. As you can see below, you would not be able to make a call to ExecuteNonQuery the subject of an If statement with Option Strict On.

    Name:  OptionStrictOn.jpg
Views: 178
Size:  10.3 KB

  19. #19
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating database table

    Quote Originally Posted by TATARPRO View Post
    ok this is the actual code I wrote
    Code:
    Public Function CreateArmTable(ByVal TableName As String) As Boolean
            Dim connection As New SqlConnection(CString)
            Dim sqlCmd As String = "CREATE TABLE" & " " & "[" & TableName & "]" & _
        "(" & _
        "[ADMIN_NUMBER] INT NOT NULL PRIMARY KEY, " & _
        "[STUDENT NAME] TEXT NOT NULL, " & _
        "[C.A.1] SMALLINT NULL, " & _
        "[C.A.2] SMALLINT NULL, " & _
        "[C.A.3] SMALLINT NULL, " & _
        "[EXAM] SMALLINT NULL, " & _
        "[TOTAL] SMALLINT NULL, " & _
        "[POSITION] SMALLINT NULL, " & _
        "[GRADE] SMALLINT NULL" & _
        ")"
    
            Dim CreatorCommand As New SqlCommand(sqlCmd, connection)
            connection.Open()
    
            If CreatorCommand.ExecuteNonQuery() Then
                Return True
            Else
                Return False
            End If
            connection.Close()
    
        End Function
    In this code, you are never closing the connection because the call to Close is after the Return statements. That's (one reason) why you use Using blocks. If you create the connection with a Using statement then it will be automatically closed when execution leaves the block, regardless of how that happens. You can hit the End Using statement directly, hit a Return statement or even throw an exception and the object created by the Using statement will still be disposed. Disposing a database connection means closing it.

  20. #20

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    excuse me which version of Vs are you using cause mine is Vs 2012 and it shows no errorsName:  1.PNG
Views: 220
Size:  6.2 KB
    Nothing I post is Self Reliable. Use it at your own risk

  21. #21
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Creating database table

    I don't have VS 2012 installed anywhere to test but the screenshot I provided was from VS 2017 and I just tested VS 2010 and it behaved the same way. I very much doubt that things changed after VS 2010 and then changed back again before VS 2017. If you really do have Option Strict On and you're not seeing a compilation error then your IDE is broken and you should repair or reinstall.

    Name:  OptionStrictOn2.png
Views: 190
Size:  6.4 KB

  22. #22

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    Quote Originally Posted by jmcilhinney View Post
    I don't have VS 2012 installed anywhere to test but the screenshot I provided was from VS 2017 and I just tested VS 2010 and it behaved the same way. I very much doubt that things changed after VS 2010 and then changed back again before VS 2017. If you really do have Option Strict On and you're not seeing a compilation error then your IDE is broken and you should repair or reinstall.

    Name:  OptionStrictOn2.png
Views: 190
Size:  6.4 KB
    Ok thanks. I created a new project and I copied the code into the new project and it showed me the errors as you predicted and I've solved it.
    Nothing I post is Self Reliable. Use it at your own risk

  23. #23

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: Creating database table

    Quote Originally Posted by ChrisE View Post
    Hi,

    this is how I create a Table with Access...

    change the OLE to SQL

    Code:
    Public Sub CreateTable()
            Dim sSQL As String
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=C:\ZooKeeper.mdb")
            'here you create your Table in the Database
            sSQL = sSQL & "  Create Table tbl_Zoo"
            sSQL = sSQL & "( [ZO_ID] Integer Identity"
            sSQL = sSQL & ", [ZO_Firstname] varChar(50)"
            sSQL = sSQL & ", [ZO_Surname] varChar(50) "
            sSQL = sSQL & ")"
            con.Open()
            ExecuteSQL(con, sSQL)
            con.Close()
            con = Nothing
        End Sub
    
    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                         ByVal sSQL As String, _
                                         Optional ByRef ErrMessage As String = Nothing, _
                                         Optional ByVal TransAction As  _
                                         OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    regards
    Chris
    Chris, thanks to you also. I learned from the code you presented and then I modified it to Sql Server and it worked.
    Code:
       Public Function CreateArmTable(ByVal Tablename As String) As Integer
            Dim connection As New SqlConnection(CSString)
            Dim sqlCmd As String = "CREATE TABLE " & " " & Tablename.ToUpper & _
        "(" & _
        "[ADMIN_NUMBER] INT NOT NULL PRIMARY KEY, " & _
        "[STUDENT NAME] TEXT NOT NULL, " & _
        "[C.A.1] SMALLINT NULL, " & _
        "[C.A.2] SMALLINT NULL, " & _
        "[C.A.3] SMALLINT NULL, " & _
        "[EXAM] SMALLINT NULL, " & _
        "[TOTAL] SMALLINT NULL, " & _
        "[POSITION] SMALLINT NULL, " & _
        "[GRADE] SMALLINT NULL" & _
        ")"
            Dim result As Integer = 0
            Dim CreatorCommand As New SqlCommand(sqlCmd, connection)
            Try
                connection.Open()
                result = CreatorCommand.ExecuteNonQuery()
                connection.Close()
            Catch ex As System.Data.SqlClient.SqlException
                MsgBox("There is already a table in the database with that" & vbCrLf & "tablename you are trying to use.")
            End Try
            Return result
        End Function
    Nothing I post is Self Reliable. Use it at your own risk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width