Very New to Programming Need advice .

[pwsincd]

Hi guys , i trying to develop an app for my son so he can easily admin a mysql database , and after lots of googleing i eventually came across brandons youtube tutorials on sql connection .. Thanks for those man , very helpful.

I have progressed quite well but it seems i have over looked something.

I wanted to make the app useful to others so rather than predetermine the connection string i thought to add simple textbox entries for connection to other DB's of this nature.

Ok so to my problem . to aid in debugging i predetermined the connection string variables to save me typing them in. Now that im ready to try the app i remove the predetermined variable strings and want the textbox entries to come into play.

heres my code :

[code2=vbnet]Imports MySql.Data.MySqlClient

Public Class Form1
'common variables
Public Shared IP As String = "removed for security"
Public Shared DBNAME As String = "removed for security"
Public Shared USER As String = "removed for security"
Public Shared PASS As String = "removed for security"
Public Shared conn_ok As Boolean = False
Public Shared connstr As String = "Server=" & IP & ";Database=" & DBNAME & ";Uid=" & USER & ";Pwd=" & PASS & ";"
Public Shared myConnection As New MySqlConnection(connstr)
Public Shared command As New MySqlCommand
Public Shared adapter As New MySqlDataAdapter
Public Shared data As MySqlDataReader

'connection code
'ip address
Private Sub KryptonTextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox1.TextChanged
IP = KryptonTextBox1.Text
connstr = "Server=" & IP & ";Database=" & DBNAME & ";Uid=" & USER & ";Pwd=" & PASS & ";"
End Sub
'Database name
Private Sub KryptonTextBox2_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox2.TextChanged
DBNAME = KryptonTextBox2.Text
End Sub
'username
Private Sub KryptonTextBox3_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox3.TextChanged
USER = KryptonTextBox3.Text
End Sub
'password
Private Sub KryptonTextBox4_TextChanged(sender As System.Object, e As System.EventArgs) Handles KryptonTextBox4.TextChanged
PASS = KryptonTextBox4.Text
End Sub
'connection test
Private Sub KryptonButton1_Click(sender As System.Object, e As System.EventArgs) Handles KryptonButton1.Click
' check connection by retrieving sql version
KryptonRichTextBox1.Text = ""
Dim stm As String = "SELECT VERSION()"
Dim Version As String = ""
Try
myConnection.Open()
TextBox1.Text = "Server=" & IP & ";Database=" & DBNAME & ";Uid=" & USER & ";Pwd=" & PASS & ";"
Dim cmd As MySqlCommand = New MySqlCommand(stm, myConnection)
Version = Convert.ToString(cmd.ExecuteScalar())

KryptonRichTextBox1.Text = "Successful connection : connected to PermissionsEx database version : " & Version
conn_ok = True

Catch ex As MySqlException
KryptonRichTextBox1.Text = "Connection Error : Please verify your database info is correct and your internet connection is valid."
conn_ok = False
TextBox1.Text = "Server=" & IP & ";Database=" & DBNAME & ";Uid=" & USER & ";Pwd=" & PASS & ";"
Finally
myConnection.Close()
End Try
' if connection is ok then check db tables exist
If conn_ok = True Then
Try
Dim sqlquery As String = "SELECT * from permissions"
myConnection.Open()
command.CommandText = sqlquery
command.Connection = myConnection
adapter.SelectCommand = command
data = command.ExecuteReader
Catch extable As MySqlException
KryptonRichTextBox1.Text = "Successful connection : connected to mySQL database version : " & Version & ". However no Permissions EX tables are present , please check your database configuration."
Finally
myConnection.Close()
End Try
Else
KryptonRichTextBox1.Text = "Connection Error : Please verify your database info is correct and your internet connection is valid."
End If
End Sub[/code2]

The problem seems to be that if i run my app and hit my connect button without populating the textboxes it connects using my predetermined values no problems.
If i remove my predetermined values and rely on the textboxes connection fails.

I displayed the connection string in a new textbox to see what was going on , when i connect to the db my outputted textbox string is missing the variables , if i fail to connect using the textboxes , the connection string in the debug textbox is fine .

basically what im saying is i cannot seem to push the textbox entries into a successful connection string.

If screenshots of the app would help let me know.

[brandonio21]

The problem occurs at the beginning of your application. At the very top of your code you say
[code2=vbnet] Public Shared myConnection As New MySqlConnection(connstr)[/code2]

Well, that uses the already set value as the connection string to create the connection. Thus, when you change the values in the textboxes, the connection string may be changing but the connection is not actually refreshing itself.

So, this can be resolved by re-instantiating the connection before it is opened. For example,
[code2=vbnet]Try
            myConnection.Open()[/code2]

Could be replaced with:
[code2=vbnet]Try
myConnection = new MySqlConnection(connstr)
            myConnection.Open()[/code2]

To fix any problems.


I think this will work, atleast. I hope this helps.

[pwsincd]

Thanks for the assistance Brandon , that indirectly worked , however i had to add an extra line which seemed to help also.

Code:

connstr = "Server=" & KryptonTextBox1.Text & ";Database=" & KryptonTextBox2.Text & ";Uid=" & KryptonTextBox3.Text & ";Pwd=" & KryptonTextBox4.Text & ";"
            myConnection = New MySqlConnection(connstr)
            myConnection.Open()

Needless to say that you pointing out that i hadnt re-instated the connection made me realise what i had done. Thanks man .

What i now want add is that if any of connection string textboxes is empty then the connect button isnt visable. so i guess that :

Code:

If KryptonTextBox1.Text = Nothing Then
            KryptonButton1.Visible = False
        Else
            KryptonButton1.Visible = True

would be correct . Only i dont really understand where it should appear in my code. Can you point me in the right direction .
I did have it in the Form load sub and the button isnt visable , but when i enter content to the textbox it doesnt reappear. I dont fully understand how the code is executed and it what order i guess.
Cheers again brandonio you have inspired an old school BBC Basic programmer to pick up where i left off twenty yrs ago lmao..

[brandonio21]

Well, what you can actually do is create a method that checks all of the textbox's text contents, and if they are all empty, set the button's visible property to false.

This can be done like so:
[code2=vbnet]Public Sub CheckForText() Handles KryptonTextBox1.TextChanged, KryptonTextBox2.TextChanged, KryptonTextBox3.TextChanged, KryptonTextBox4.TextChanged
'this will be called anytime the textbox's text is changed
If (KryptonTextBox1.Text Is Nothing) Then 'the user didn't type in text for this box
KryptonButton1.Visible = False 'set the button to be invisible
Return 'exit the sub since we don't need to do anything else
ElseIf (KryptonTextBox2.Text Is Nothing) Then
KryptonButton1.Visible = False
Return
ElseIf (KryptonTextBox3.Text Is Nothing) Then
KryptonButton1.Visible = False
Return
ElseIf (KryptonTextBox4.Text Is Nothing) Then
KryptonButton1.Visible = False
Return
Else
KryptonButton1.Visible = True
Return
End If
End Sub[/code2]

Essentially, this sub will be called anytime any of those four textbox's text is changed, and if there is no text entered, the button will become invisible. If this is not the case and all textboxes have text, the button will be visible.

Hopefully this makes sense.

[pwsincd]

Thanks for that Brandon , what i had to do was set the button invisible in the first instance for that code to work.
However if i enter text into the box the button appears <!-- s --><img src="{SMILIES_PATH}/icon_e_smile.gif" alt="" title="Smile" /><!-- s --> but if i then clear the box the button remains.

Also i tried to set it so that all four boxes require a string before the button appears. I tried with a series of IF statements but to no avail.. any pointers on this ?

[brandonio21]

Well the code snippet above should do what you want it to do; however, we may be having problems with our usage of the "Nothing" comparison.

Try this:
[code2=vbnet]Public Sub CheckForText() Handles KryptonTextBox1.TextChanged, KryptonTextBox2.TextChanged, KryptonTextBox3.TextChanged, KryptonTextBox4.TextChanged
'this will be called anytime the textbox's text is changed
If (KryptonTextBox1.Text.Equals("")) Then 'the user didn't type in text for this box
KryptonButton1.Visible = False 'set the button to be invisible
Return 'exit the sub since we don't need to do anything else
ElseIf (KryptonTextBox2.Text.Equals("")) Then
KryptonButton1.Visible = False
Return
ElseIf (KryptonTextBox3.Text.Equals("")) Then
KryptonButton1.Visible = False
Return
ElseIf (KryptonTextBox4.Text.Equals("")) Then
KryptonButton1.Visible = False
Return
Else
KryptonButton1.Visible = True
Return
End If
End Sub[/code2]

[pwsincd]

Ok this works but only for textbox 1 the other 3 seem to ignore the checks and it stays invisable. is it to do with the handles statement in the sub declaration maybe ?

[brandonio21]

I tested the code in a sample application very similar to yours, and I produced the wanted results. Are your text boxes named anything different from the standard KrypronTextBox ?

Either way, since this is only working with a number of the text box selection, the problem most likely lies within the Handles statement after the sub declaration. Be sure that all the text box's TextChanged events are linked to this sub.

[pwsincd]

Ok what happens is this :
When running the app textbox1 gets the emphasis instantly , therefor i dont need to click the textbox to type. When i do type (without clicking) the button appears and disapears if i delete the text. None of the other threee do this.

However if i click the first box and then enter text the button doesnt appear until i add text to all four boxes. Crazy , this is ok.

Simpleset thing would be i guess to stop textbox1 being where the cursor is upon my app running , but i guess its no biggy.

Cheers for your help Brandon , very much .