Unwanted result when executing command in mysql

[archelle]

Hello..

i have a table named Tuition with fields:

TuitionNum,TotalFee,Fine,levelNum,SchoolYear,DateCreated,Removed,DateLastModified,UserID

I inserted record successfully but when i'm trying to display TotalFee using this code,

myConnString = "server=localhost;" _
                             & "user id=root;" _
                             & "password=fiancee;" _
                             & "database=anneclaire"

        SQL = "SELECT TotalFee FROM Tuition WHERE levelNum='" & TextBox1.Text & "'  AND SchoolYear='" & TextBox2.Text & "'"

        myConnection.ConnectionString = myConnString
        Try
            myConnection.Open()
            myCommand.Connection = myConnection
            myCommand.CommandText = SQL
            TotalFee = myCommand.ExecuteScalar
            Label1.Text = TotalFee

        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If myConnection.State <> ConnectionState.Closed Then myConnection.Close()

        End Try

this gives me a value of 0 in TotalFee whatever levelNum and SchoolYear is given.

What might be the problem?

Please give some advice and code for me to analyze it. Thank u so much for help.

Archelle,

[hericles]

You need to cast the returned object from executeScalar. Use the toString() method and it should work OK.

[archelle]

Can u give sample code how to use toString()....? please....

[archelle]

Hi ...
I insert this code in try block but still same result...

myConnection.Open()
            myCommand.Connection = myConnection
            myCommand.CommandText = SQL
            Tuition = myCommand.ExecuteScalar
            lblTuition.Text = Convert.ToString(Tuition)

            myCommand.Dispose()

Hello..

i have a table named Tuition with fields:

TuitionNum,TotalFee,Fine,levelNum,SchoolYear,DateCreated,Removed,DateLastModified,UserID

I inserted record successfully but when i'm trying to display TotalFee using this code,

myConnString = "server=localhost;" _
                             & "user id=root;" _
                             & "password=fiancee;" _
                             & "database=anneclaire"

        SQL = "SELECT TotalFee FROM Tuition WHERE levelNum='" & TextBox1.Text & "'  AND SchoolYear='" & TextBox2.Text & "'"

        myConnection.ConnectionString = myConnString
        Try
            myConnection.Open()
            myCommand.Connection = myConnection
            myCommand.CommandText = SQL
            TotalFee = myCommand.ExecuteScalar
            Label1.Text = TotalFee

        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If myConnection.State <> ConnectionState.Closed Then myConnection.Close()

        End Try

this gives me a value of 0 in TotalFee whatever levelNum and SchoolYear is given.

What might be the problem?

Please give some advice and code for me to analyze it. Thank u so much for help.

Archelle,

[hericles]

No, cast the executeScalar command as it returns an object which you then try to jam in to Tuition. tuition is of type int right? Casting Tuition after it has incorrectly stored the executeScalar won't fix the issue.

[ChrisPadgham]

Tuition = myCommand.ExecuteScalar.ToString

[hericles]

Sorry, I meant Tuition is of type string in my post above, not int obviously.

[archelle]

I did it as you said..

My Tuition variable was declared as integer because my TotalFee in Tuition Table is integer.

I inserted this code but still nothing happens and it gives me an error "Object reference not set to an instance of an object."

This is the code:

Dim Tuition As Integer
        Dim myCommand As New MySqlCommand


        myConnection.Close()
        myConnString = "server=localhost;" _
                             & "user id=root;" _
                             & "password=fiancee;" _
                             & "database=anneclaire"

        SQL = "SELECT TotalFee FROM Tuition WHERE levelNum=?levelNum AND SchoolYear=?SchoolYear AND Removed='NO'"
        myCommand.Parameters.Clear()
        myCommand.Parameters.AddWithValue("?levelNum", lbllevelNum.Text)
        myCommand.Parameters.AddWithValue("?SchoolYear", lblSchoolYear.Text)

        myConnection.ConnectionString = myConnString
        Try
            myConnection.Open()
            myCommand.Connection = myConnection
            myCommand.CommandText = SQL
            Tuition = myCommand.ExecuteScalar.ToString
            lblTuition.Text = Tuition

            myCommand.Dispose()

        Catch myerror As MySqlException
            MessageBox.Show("Error connecting to the database: " & myerror.Message)
        Finally
            If myConnection.State <> ConnectionState.Closed Then myConnection.Close()
            myCommand.Dispose()
        End Try

[waqasaslammeo]

put on error resume next , on the top of ur code , it will work fine

[GeekByChoiCe]

I guess Tuition is nothing. so Nothing.tostring will raise this kind of exception. I suggest to do
MsgBox(SQL) just before your Try block and see how your SQL Query looks like. eventually even take this string and execute it directly against the database to validate the result.

[archelle]

How do i cast from string to Integer..? im using vb.net and mySql and im aware that when i inserted record (int) in mysql, the mysql converts this into string format. My problem is.. i need to get the value of TotalFee using this query

SQL = "SELECT TuitionNum FROM Tuition WHERE SchoolYear='" & lblSchoolYear.Text & "' " _
            & "AND levelNum='" & lbllevelNum.Text & "' AND Removed='NO'"

            myCommand.Connection = myConnection
            myCommand.CommandText = SQL

            TuitionNum = myCommand.ExecuteScalar

and store it in Tuition. I dont know how to cast from string to integer. Please help me. im struggling with this one and im stuck with this. Please provide some code for me. im using mysql server 5.1 and vb.net...

[adam_k]

I agree with GeekByChoice in both that waqasaslammeo's answer is rubish and that you need to run your SQL query against db and verify that it's working as expected before troubleshooting anything else.
May I suggest the Debug.Print (SQL) instead of msgbox? I find it very usefull to get the contents of queries in the immediate pane instead of a messagebox as I can copy and paste in the db or wherever needed.