App to create db/tables not working

Printable View

I am not getting any error messages and its not creating the db/tables for some reason. any chance you can look at my code and see if you can notice anything? I am not noticing any problems. Would like to ask for suggestions for this app too.

VB Code:

Option Explicit
 
Public strCreate As String
Public conn As ADODB.Connection
 
Private Sub cmdDB_Click()
    Set conn = New ADODB.Connection
    
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MYSQL ODBC 3.51 Driver};" _
    & "SERVER=" & txtIP.Text _
    & "UID=" & txtUsername.Text _
    & "PWD=" & txtPassword.Text _
    & "OPTIONS=" & 1 + 2 + 8 + 32 + 2048 + 16384
        
    CreateDB
    cmdDB.Enabled = False
    txtIP.Text = ""
    txtUsername.Text = ""
    txtPassword.Text = ""
    txtIP.SetFocus
End Sub
 
Private Sub Form_Load()
    cmdDB.Enabled = False
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    Unload Me
End Sub
 
Public Function CreateDB()
    strCreate = "CREATE DATABASE test1"
    CreateTable
End Function
 
Public Function CreateTable()
    strCreate = "CREATE TABLE ClientInformation( "
    strCreate = strCreate & "id int(11) NOT NULL "
    strCreate = strCreate & "CompanyName TEXT NOT NULL, "
    strCreate = strCreate & "AddressLine1 TEXT NOT NULL, "
    strCreate = strCreate & "AddressLine2 TEXT NOT NULL, "
    strCreate = strCreate & "City TEXT NOT NULL, "
    strCreate = strCreate & "State TEXT NOT NULL, "
    strCreate = strCreate & "Zip TEXT NOT NULL, "
    strCreate = strCreate & "PhoneNumber TEXT NOT NULL, "
    strCreate = strCreate & "FaxNumber TEXT NOT NULL, "
    strCreate = strCreate & "Email TEXT NOT NULL, "
    strCreate = strCreate & "Website TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE Inventory( "
    strCreate = strCreate & "ItemNumber TEXT NOT NULL, "
    strCreate = strCreate & "ProductName TEXT NOT NULL, "
    strCreate = strCreate & "ProductQuantity TEXT NOT NULL, "
    strCreate = strCreate & "ProductPrice TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE Logo( "
    strCreate = strCreate & "CompanyLogo TEXT NOT NULL) TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE OperatorPicture( "
    strCreate = strCreate & "id int(11) NOT NULL, "
    strCreate = strCreate & "Username TEXT NOT NULL, "
    strCreate = strCreate & "Picture TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE Orders( "
    strCreate = strCreate & "CompanyName TEXT NOT NULL "
    strCreate = strCreate & "OrderRefNum TEXT NOT NULL, "
    strCreate = strCreate & "DateOfOrder DATE NOT NULL, "
    strCreate = strCreate & "DateOrderCompleted DATE NOT NULL, "
    strCreate = strCreate & "DateOrderShipped DATE NOT NULL, "
    strCreate = strCreate & "ProductName TEXT NOT NULL, "
    strCreate = strCreate & "ProductDescription TEXT NOT NULL, "
    strCreate = strCreate & "ProductQuantity TEXT NOT NULL, "
    strCreate = strCreate & "ProductPrice TEXT NOT NULL, "
    strCreate = strCreate & "PriceSubTotal TEXT NOT NULL, "
    strCreate = strCreate & "PriceTotal TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE SalesTax( "
    strCreate = strCreate & "SalesTax TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE StockCheck( "
    strCreate = strCreate & "InventoryMinimumCount TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "INSERT INTO StockCheck(InventoryMinimumCount) "
    strCreate = strCreate & "VALUES ('1')"
    
    strCreate = "CREATE TABLE Users( "
    strCreate = strCreate & "id int(11) NOT NULL, "
    strCreate = strCreate & "Username TEXT NOT NULL, "
    strCreate = strCreate & "Password TEXT NOT NULL, "
    strCreate = strCreate & "Operator TEXT NOT NULL, "
    strCreate = strCreate & "OperatorID TEXT NOT NULL, "
    strCreate = strCreate & "FullAccess TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "INSERT INTO Users(id,Username,Password,Operator,OperatorID,FullAccess) "
    strCreate = strCreate & "VALUES ('','admin','password','Operator Name','OperatorID','Yes')"
    
    strCreate = "CREATE TABLE YourCompanyInformation( "
    strCreate = strCreate & "id int(11) NOT NULL, "
    strCreate = strCreate & "CompanyName TEXT NOT NULL, "
    strCreate = strCreate & "AddressLine1 TEXT NOT NULL, "
    strCreate = strCreate & "AddressLine2 TEXT NOT NULL, "
    strCreate = strCreate & "City TEXT NOT NULL, "
    strCreate = strCreate & "State TEXT NOT NULL, "
    strCreate = strCreate & "Zip TEXT NOT NULL, "
    strCreate = strCreate & "PhoneNumber TEXT NOT NULL, "
    strCreate = strCreate & "FaxNumber TEXT NOT NULL, "
    strCreate = strCreate & "Email TEXT NOT NULL, "
    strCreate = strCreate & "Website TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
End Function
 
Private Sub txtIP_Change()
    If txtIP.Text <> "" And txtPassword.Text <> "" And txtUsername.Text <> "" Then
        cmdDB.Enabled = True
    Else
        cmdDB.Enabled = False
    End If
End Sub
 
Private Sub txtIP_GotFocus()
    txtIP.SelStart = 0
    txtIP.SelLength = Len(txtIP.Text)
End Sub
 
Private Sub txtPassword_Change()
    If txtIP.Text <> "" And txtPassword.Text <> "" And txtUsername.Text <> "" Then
        cmdDB.Enabled = True
    Else
        cmdDB.Enabled = False
    End If
End Sub
 
Private Sub txtPassword_GotFocus()
    txtPassword.SelStart = 0
    txtPassword.SelLength = Len(txtPassword.Text)
End Sub
 
Private Sub txtUsername_Change()
    If txtIP.Text <> "" And txtPassword.Text <> "" And txtUsername.Text <> "" Then
        cmdDB.Enabled = True
    Else
        cmdDB.Enabled = False
    End If
End Sub
 
Private Sub txtUsername_GotFocus()
    txtUsername.SelStart = 0
    txtUsername.SelLength = Len(txtUsername.Text)
End Sub

Dec 3rd, 2005, 02:30 PM
dglienna

Re: App to create db/tables not working

Why go thru all the bother? Just ship with a blank db, or maybe a record for the default admin in it.
Dec 3rd, 2005, 02:54 PM
BrailleSchool

Re: App to create db/tables not working

Quote:

Originally Posted by dglienna

Why go thru all the bother? Just ship with a blank db, or maybe a record for the default admin in it.

The app isnt going to be using Access so the db would have to be created and some records populated. Could ship with an .sql file but would prefer for an app to create it for the admin automatically. all they would have to supply is the ip address and login details.
Dec 3rd, 2005, 03:20 PM
randem

Re: App to create db/tables not working

BrialleSchool,

Aren't you missing code to actually create the database? All you do is create the string for a create statement.
Dec 3rd, 2005, 03:54 PM
BrailleSchool

Re: App to create db/tables not working

Quote:

Originally Posted by randem

BrialleSchool,

Aren't you missing code to actually create the database? All you do is create the string for a create statement.

i tried strSQL.Execute and VB6 shouted at me lol
Dec 3rd, 2005, 05:18 PM
si_the_geek

Re: App to create db/tables not working
You need to use something like this to execute them:
VB Code:
```
conn.execute strCreate
```
Another comment I have from your code is the way that you set the Enabled state of the button in the _Change events. From personal experience I would recommend creating a sub to set the enabled state, and call this from all the change events - not only does it reduce repetition, but that way if the conditions for enabling it change (such as the IP must contain 3 dots) then you only need to change it in one place.

You could also do the same sort of thing for 'common work' in the _GotFocus events, eg:
VB Code:
```
Private Sub txtPassword_GotFocus()
    Call SelectWholeTextBox(txtPassword)
End Sub
 
Sub SelectWholeTextBox(oTextBox as TextBox)
  With oTextBox
    .SelStart = 0
    .SelLength = Len(.Text)
  End With
End Sub
```
..this not only saves code, but you could also put it into a module (along with other 'generic' functions) so that you can use these functions easily in the rest of your program, and your next project.
Dec 3rd, 2005, 05:24 PM
BrailleSchool

Re: App to create db/tables not working
Quote:
Originally Posted by si_the_geek

You need to use something like this to execute them:

VB Code:

conn.execute strCreate

Another comment I have from your code is the way that you set the Enabled state of the button in the _Change events. From personal experience I would recommend creating a sub to set the enabled state, and call this from all the change events - not only does it reduce repetition, but that way if the conditions for enabling it change (such as the IP must contain 3 dots) then you only need to change it in one place.

You could also do the same sort of thing for 'common work' in the _GotFocus events, eg:

VB Code:

Private Sub txtPassword_GotFocus()
Call SelectWholeTextBox(txtPassword)
End Sub

Sub SelectWholeTextBox(oTextBox as TextBox)
With oTextBox
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub

..this not only saves code, but you could also put it into a module (along with other 'generic' functions) so that you can use these functions easily in the rest of your program, and your next project.
ok thanks for the sub, will definately add that to this project and my other one. still have a lot to learn. when it came to the execute, thought it was something like that but wasnt sure.

Re: App to create db/tables not working

my code still isnt working ugh.

VB Code:

Option Explicit
 
Public strCreate As String
Public conn As ADODB.Connection
 
Private Sub cmdDB_Click()
On Error GoTo dberr
    Set conn = New ADODB.Connection
    Dim strSvr As String
    Dim strUid As String
    Dim strPwd As String
    
    strSvr = txtIP.Text
    strUid = txtUsername.Text
    strPwd = txtPassword.Text
    
    conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MYSQL ODBC 3.51 Driver}; " & _
                            "SERVER=" & strSvr & ";" & _
                            "UID=" & strUid & ";" & _
                            "PWD=" & strPwd & ";" & _
                            "OPTIONS=" & 1 + 2 + 8 + 32 + 2048 + 16384
    
    conn.Open conn.ConnectionString
    
    CreateDB
    DoEvents
    CreateTable
    
    cmdDB.Enabled = False
    txtIP.Text = ""
    txtUsername.Text = ""
    txtPassword.Text = ""
    txtIP.SetFocus
    
dberr:
    If Err.Number = -2147467259 Then
        MsgBox "Cannot connect to Database Server", vbExclamation, "Connection Error."
        Exit Sub
    Else
        Open App.Path & "\ErrorLog\error.txt" For Append As #1
        Print #1, Now() & vbTab & Err.Number & ": " & Err.Description
        Close #1
        Exit Sub
    End If
End Sub
 
Private Sub Form_Load()
    cmdDB.Enabled = False
 
    'check for error folder and create one if it doesn't exist
    If Dir(App.Path & "\ErrorLog", vbDirectory) = "" Then
        MkDir App.Path & "\ErrorLog"
    End If
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    Unload Me
End Sub
 
Public Function CreateDB()
    strCreate = "CREATE DATABASE paddy_test1"
    conn.Execute strCreate
End Function
 
Public Function CreateTable()
    strCreate = "CREATE TABLE ClientInformation( "
    strCreate = strCreate & "id int(11) NOT NULL "
    strCreate = strCreate & "CompanyName TEXT NOT NULL, "
    strCreate = strCreate & "AddressLine1 TEXT NOT NULL, "
    strCreate = strCreate & "AddressLine2 TEXT NOT NULL, "
    strCreate = strCreate & "City TEXT NOT NULL, "
    strCreate = strCreate & "State TEXT NOT NULL, "
    strCreate = strCreate & "Zip TEXT NOT NULL, "
    strCreate = strCreate & "PhoneNumber TEXT NOT NULL, "
    strCreate = strCreate & "FaxNumber TEXT NOT NULL, "
    strCreate = strCreate & "Email TEXT NOT NULL, "
    strCreate = strCreate & "Website TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE Inventory( "
    strCreate = strCreate & "ItemNumber TEXT NOT NULL, "
    strCreate = strCreate & "ProductName TEXT NOT NULL, "
    strCreate = strCreate & "ProductQuantity TEXT NOT NULL, "
    strCreate = strCreate & "ProductPrice TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE Logo( "
    strCreate = strCreate & "CompanyLogo TEXT NOT NULL) TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE OperatorPicture( "
    strCreate = strCreate & "id int(11) NOT NULL, "
    strCreate = strCreate & "Username TEXT NOT NULL, "
    strCreate = strCreate & "Picture TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE Orders( "
    strCreate = strCreate & "CompanyName TEXT NOT NULL "
    strCreate = strCreate & "OrderRefNum TEXT NOT NULL, "
    strCreate = strCreate & "DateOfOrder DATE NOT NULL, "
    strCreate = strCreate & "DateOrderCompleted DATE NOT NULL, "
    strCreate = strCreate & "DateOrderShipped DATE NOT NULL, "
    strCreate = strCreate & "ProductName TEXT NOT NULL, "
    strCreate = strCreate & "ProductDescription TEXT NOT NULL, "
    strCreate = strCreate & "ProductQuantity TEXT NOT NULL, "
    strCreate = strCreate & "ProductPrice TEXT NOT NULL, "
    strCreate = strCreate & "PriceSubTotal TEXT NOT NULL, "
    strCreate = strCreate & "PriceTotal TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE SalesTax( "
    strCreate = strCreate & "SalesTax TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "CREATE TABLE StockCheck( "
    strCreate = strCreate & "InventoryMinimumCount TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "INSERT INTO StockCheck(InventoryMinimumCount) "
    strCreate = strCreate & "VALUES ('1')"
    
    strCreate = "CREATE TABLE Users( "
    strCreate = strCreate & "id int(11) NOT NULL, "
    strCreate = strCreate & "Username TEXT NOT NULL, "
    strCreate = strCreate & "Password TEXT NOT NULL, "
    strCreate = strCreate & "Operator TEXT NOT NULL, "
    strCreate = strCreate & "OperatorID TEXT NOT NULL, "
    strCreate = strCreate & "FullAccess TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    
    strCreate = "INSERT INTO Users(id,Username,Password,Operator,OperatorID,FullAccess) "
    strCreate = strCreate & "VALUES ('','admin','password','Operator Name','OperatorID','Yes')"
    
    strCreate = "CREATE TABLE YourCompanyInformation( "
    strCreate = strCreate & "id int(11) NOT NULL, "
    strCreate = strCreate & "CompanyName TEXT NOT NULL, "
    strCreate = strCreate & "AddressLine1 TEXT NOT NULL, "
    strCreate = strCreate & "AddressLine2 TEXT NOT NULL, "
    strCreate = strCreate & "City TEXT NOT NULL, "
    strCreate = strCreate & "State TEXT NOT NULL, "
    strCreate = strCreate & "Zip TEXT NOT NULL, "
    strCreate = strCreate & "PhoneNumber TEXT NOT NULL, "
    strCreate = strCreate & "FaxNumber TEXT NOT NULL, "
    strCreate = strCreate & "Email TEXT NOT NULL, "
    strCreate = strCreate & "Website TEXT NOT NULL "
    strCreate = strCreate & ") TYPE = MyISAM;"
    conn.Execute strCreate
End Function
 
Private Sub txtIP_Change()
    If txtIP.Text <> "" And txtPassword.Text <> "" And txtUsername.Text <> "" Then
        cmdDB.Enabled = True
    Else
        cmdDB.Enabled = False
    End If
End Sub
 
Private Sub txtIP_GotFocus()
    Call SelectWholeTextBox(txtIP)
End Sub
 
Private Sub txtPassword_Change()
    If txtIP.Text <> "" And txtPassword.Text <> "" And txtUsername.Text <> "" Then
        cmdDB.Enabled = True
    Else
        cmdDB.Enabled = False
    End If
End Sub
 
Private Sub txtPassword_GotFocus()
    Call SelectWholeTextBox(txtPassword)
End Sub
 
Private Sub txtUsername_Change()
    If txtIP.Text <> "" And txtPassword.Text <> "" And txtUsername.Text <> "" Then
        cmdDB.Enabled = True
    Else
        cmdDB.Enabled = False
    End If
End Sub
 
Private Sub txtUsername_GotFocus()
    Call SelectWholeTextBox(txtUsername)
End Sub
 
Sub SelectWholeTextBox(oTextBox As TextBox)
  With oTextBox
    .SelStart = 0
    .SelLength = Len(.Text)
  End With
End Sub

Dec 4th, 2005, 12:58 PM
umilmi81

Re: App to create db/tables not working

What's the error? Remember, you can't create a table that already exists. You'll have to drop them if they exist and recreate them. Or just not create if they already exist.

You can do it in the SQL itself

Code:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerAddress]') AND type in (N'U')) DROP TABLE [dbo].[CustomerAddress]
Dec 4th, 2005, 01:07 PM
BrailleSchool

Re: App to create db/tables not working

Quote:

Originally Posted by umilmi81

What's the error? Remember, you can't create a table that already exists. You'll have to drop them if they exist and recreate them. Or just not create if they already exist.

You can do it in the SQL itself

Code:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerAddress]') AND type in (N'U')) DROP TABLE [dbo].[CustomerAddress]

nothing exists because nothing is created
when it comes to errors, all errors are written to an error.log but its empty.
Dec 4th, 2005, 02:39 PM
si_the_geek

Re: App to create db/tables not working
You are still missing a few Executes in the CreateTable sub - you need to run it each time you have it before each new CREATE or INSERT.

It is actually creating the database? If not, you will need to check out the documentation for MySQL to see what it wants from a CREATE DATABASE statement.

As to the issue of the code not creating any tables, I think that it probably is - just not where you are expecting them to be. The problem is that you are in no way specifying which DB to make the tables in, so they are probably in the default database (whatever that is for MySQL).

In order to fix this, you will either need to change DB's before creating tables (I guess using something like "CHANGE DATABASE paddy_test1"), or specify the database before the table names, eg:
VB Code:
```
strCreate = "CREATE TABLE [b]paddy_test1.[/b]ClientInformation( "
```
Dec 4th, 2005, 03:04 PM
BrailleSchool

Re: App to create db/tables not working

Quote:

Originally Posted by si_the_geek

It is actually creating the database? If not, you will need to check out the documentation for MySQL to see what it wants from a CREATE DATABASE statement.

I have the SQL right because I have double checked a million times. As for your other suggestions about the create tables, ive made those alterations. this is the first time im doing this stuff through vb.