?
Solved

.Net Errors

Posted on 2012-08-12
11
Medium Priority
?
226 Views
Last Modified: 2012-08-13
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() ?
0
Comment
Question by:prowebinteractiveinc
11 Comments
 
LVL 28

Expert Comment

by:MacroShadow
ID: 38285633
Because you must set the connection inside a sub.
0
 

Author Comment

by:prowebinteractiveinc
ID: 38285641
yes but I want to be able to reuse this code, and when its in the sub I can reuse the same code
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 38285662
You can create a sub for connecting and call when ever you need.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:prowebinteractiveinc
ID: 38285714
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
 
LVL 28

Expert Comment

by:MacroShadow
ID: 38285736
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
 

Author Comment

by:prowebinteractiveinc
ID: 38285741
well isnt there a way that I can send the Sql statement aswell or better not to do that
0
 

Author Comment

by:prowebinteractiveinc
ID: 38285742
    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
 
LVL 28

Expert Comment

by:MacroShadow
ID: 38285846
Remove Dim sqlResult =.
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 38285912
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
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 total points
ID: 38286110
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
 
LVL 10

Expert Comment

by:John Claes
ID: 38287125
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

839 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