.Net Errors

Imports MySql.Data.MySqlClient
Public Class setupForm

    Private Sub TabPage1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim MysqlConn As MySqlConnection
        MysqlConn = New MySqlConnection()
        Dim dr As MySqlDataReader
        Dim cmd As New MySqlCommand


        MysqlConn.ConnectionString = "server=dbserver.com;" _
            & "user id=vbnet;" _
            & "password=dbpassword;" _
            & "database=admin_merchantController"

        Try
            MysqlConn.Open()

            cmd.CommandText = "SELECT companyName FROM merchants WHERE merchantId = ?MID"
            cmd.Parameters.Add(New MySqlParameter("?MID", Form1.mMerchantId))
            cmd.Connection = MysqlConn
            dr = cmd.ExecuteReader

            If dr.HasRows() Then
                dr.Read()
                myCompany.Text = dr(0)
            End If

            MysqlConn.Close()
        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
            MysqlConn.Dispose()

        End Try

Open in new window


Why is it when I take lines 5-8 and put it up at line 3 I get an error with MysqlConn = New MySqlConnection() ?
prowebinteractiveincAsked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
Because you must set the connection inside a sub.
Actually, you can set them outside of any sub. They are initialized after the constructor executes.

The problem is that you can move lines 5, 7, & 8; you cannot move line 6 because it is not a declaration. This code compiles fine:

Imports MySql.Data.MySqlClient
Imports System.Windows.Forms

Public Class setupForm
    Dim MysqlConn As MySqlConnection
    Dim dr As MySqlDataReader
    Dim cmd As New MySqlCommand

    Private Sub TabPage1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MysqlConn = New MySqlConnection()

        MysqlConn.ConnectionString = "server=dbserver.com;" _
            & "user id=vbnet;" _
            & "password=dbpassword;" _
            & "database=admin_merchantController"

        Try
            MysqlConn.Open()

            cmd.CommandText = "SELECT companyName FROM merchants WHERE merchantId = ?MID"
            cmd.Parameters.Add(New MySqlParameter("?MID", Form1.mMerchantId))
            cmd.Connection = MysqlConn
            dr = cmd.ExecuteReader

            If dr.HasRows() Then
                dr.Read()
                myCompany.Text = dr(0)
            End If

            MysqlConn.Close()
        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
            MysqlConn.Dispose()

        End Try
    End Sub
End Class

Open in new window


now you are declaring all of them as variants.
VB.NET does not have a variant type. Object is the closest equivalent. In earlier versions of VB.NET, this is exactly the type that would have been assigned to such a variable. Now, with compiler inference, when you use Dim with no type, the compiler will try to infer the type based on the type of the thing on the right side of the equals sign. If the compiler cannot infer the type, then it will define the thing Dimmed as Object. So sqlStatement will be a String because the thing on the right side of the equals sign is a String. sqlResult doesn't work because connectdb doesn't return anything--it's a Sub.
0
 
MacroShadowCommented:
Because you must set the connection inside a sub.
0
 
prowebinteractiveincAuthor Commented:
yes but I want to be able to reuse this code, and when its in the sub I can reuse the same code
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
MacroShadowCommented:
You can create a sub for connecting and call when ever you need.
0
 
prowebinteractiveincAuthor Commented:
what I did was created a class file shown below, however Im not sure how to call it and send the sql statement string as an arguement

Imports MySql.Data.MySqlClient
Public Class mysqlConnector
    Public Sub connectdb(ByVal sql)
        Dim MysqlConn As MySqlConnection
        MysqlConn = New MySqlConnection()
        Dim dr As MySqlDataReader
        Dim cmd As New MySqlCommand


        MysqlConn.ConnectionString = "server=dbserver.com;" _
            & "user id=vbnet;" _
            & "password=dbPasswpord;" _
            & "database=admin_merchantController"

        Try
            MysqlConn.Open()

            cmd.CommandText = "SELECT companyName FROM merchants WHERE merchantId = ?MID"
            cmd.Parameters.Add(New MySqlParameter("?MID", Form1.mMerchantId))
            cmd.Connection = MysqlConn
            dr = cmd.ExecuteReader

            If dr.HasRows() Then
                dr.Read()
            End If

            MysqlConn.Close()
        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
            MysqlConn.Dispose()

        End Try
    End Sub
End Class

Open in new window

0
 
MacroShadowCommented:
If all you want is to create a connection just call connectdb, (don't forget to remove ByVal sql in the parenthesis, you're not using it anyway).
0
 
prowebinteractiveincAuthor Commented:
well isnt there a way that I can send the Sql statement aswell or better not to do that
0
 
prowebinteractiveincAuthor Commented:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sqlStatement = "SELECT companyName FROM merchants where merchantId =" & Form1.mMerchantId
        Dim sqlResult = mysqlConnector.connectdb(sqlStatement)
    End Sub

Open in new window


getting error EXPRESSION DOESNT PRODUCE A VALUE
0
 
MacroShadowCommented:
Remove Dim sqlResult =.
0
 
MacroShadowCommented:
On a side note; you should be using the appropriate data type for your variables, now you are declaring all of them as variants.
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
why don't you make a small SQL-Wrapper class?

something like



Pulic Class SQLWrapper
   private function GetCommand() as MySqlCommand
       Dim MysqlConn As MySqlConnection
       Dim dr As MySqlDataReader
       Dim cmd As New MySqlCommand

        MysqlConn = New MySqlConnection()

        MysqlConn.ConnectionString = "server=dbserver.com;" _
            & "user id=vbnet;" _
            & "password=dbpassword;" _
            & "database=admin_merchantController"
        cmd.Connection = MysqlConn

   end function

   public function ExecuteSqlForValue(byval sql as string) as object
      Dim valueObject as Object
      try
      Dim MyCommand as MySqlCommand= GetCommand()
      MySqlCommand.Connection.Open()
                  MySqlCommand.CommandText = sql
                  dr = cmd.ExecuteReader
                  If dr.HasRows() Then
                      dr.Read()
                      valueObject  = dr(0)
                  End If
      MySqlCommand.Connection.Close()
        Catch myerror As MySqlException
      MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
      MysqlConn.Dispose()
       return valueObject
   end function

   public function ExecuteSqlForValue(byval sql as string, byval paramList as Dictionary(string,object)) as object
      Dim valueObject as Object
      try
      Dim MyCommand as MySqlCommand= GetCommand()
      MySqlCommand.Connection.Open()
                  MySqlCommand.CommandText = sql
                  foreach(string keystring in paramList.Keys)
                         cmd.AddParameter(keystring , paramList(keystring )
                  next keystring
                  dr = cmd.ExecuteReader
                  If dr.HasRows() Then
                      dr.Read()
                      valueObject  = dr(0)
                  End If
      MySqlCommand.Connection.Close()
        Catch myerror As MySqlException
      MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
      MysqlConn.Dispose()
       return valueObject
   end function
end class
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.