Again Problems with 3.4

Oct 10, 2013 at 4:17 PM
Edited Oct 10, 2013 at 4:28 PM
Hello James,
i think I found another Bug. I use the SQLServer.SQLServer.DoesDatabaseExist Function.
Source:
constr = String.Format("Data Source={0};Initial Catalog=Master;User Id={1};Password={2};Pooling=false", DBServer, DBUsername, DBPassword)
If Not Utilities.SQL.SQLServer.SQLServer.DoesDatabaseExist(My.Settings.Datenbank_DBName, constr) Then
'Do something
End If
Problem description:
Computer 1 (Win 7 64 Bit, Dev Machine) everything works as expected. If the Database exists, the if block is skipped.

Computer 2 (Win 7 64 Bit) I get an exception stating that the Parameter value can not be converted from String to Int32. Her is the stacktrace:
System.FormatException: Parameterwert konnte nicht von String in Int32 umgewandelt werden. ---> System.FormatException: Die Eingabezeichenfolge hat das falsche Format.
   bei System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   bei System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   bei System.String.System.IConvertible.ToInt32(IFormatProvider provider)
   bei System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   bei System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType)
   bei System.Data.SqlClient.SqlParameter.GetCoercedValue()
   bei System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   bei System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
   bei System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   bei System.Data.Common.DbCommand.ExecuteReader()
   bei Utilities.SQL.SQLHelper.ExecuteReader(Boolean CreateTransaction, Boolean Cache, Boolean NotUsed)
   bei Utilities.SQL.SQLHelper.ExecuteReader(Boolean CreateTransaction, Boolean Cache)
   bei TestDB.Form1.Button1_Click(Object sender, EventArgs e)
   bei System.Windows.Forms.Control.OnClick(EventArgs e)
   bei System.Windows.Forms.Button.OnClick(EventArgs e)
   bei System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   bei System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   bei System.Windows.Forms.Control.WndProc(Message& m)
   bei System.Windows.Forms.ButtonBase.WndProc(Message& m)
   bei System.Windows.Forms.Button.WndProc(Message& m)
   bei System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   bei System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   bei System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
To narrow the problem down, I used a small app, only non .NET reference is your lib. The code I used here is:
        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)
        Dim sqlstring As String = "SELECT * FROM Master.sys.Databases WHERE name=@Name"
        Using helper As New SQLHelper(sqlstring, CommandType.Text, constr)
            helper.AddParameter("@Name", DBName.Text, ParameterDirection.Input)
            helper.ExecuteReader()
            MsgBox(helper.Read.ToString)
        End Using
Curious this is working:
        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)
        Dim sqlstring As String = String.Format("SELECT * FROM Master.sys.Databases WHERE name='{0}'", DBName.Text)
        Using helper As New SQLHelper(sqlstring, CommandType.Text, constr)
            helper.ExecuteReader()
            MsgBox(helper.Read.ToString)
        End Using
This works on both machines: (I can even change it to ExecuteReader like in your function!)
        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)

        Dim sqlstring As String = "SELECT database_id FROM Master.sys.Databases WHERE name=@Name"
        Using tmpConn As New SqlConnection(constr)
            Using sqlCmd As New System.Data.SqlClient.SqlCommand(sqlstring, tmpConn)
                tmpConn.Open()
                sqlCmd.Parameters.Add("@Name",SqlDbType.NVarChar).Value = DBName.Text
                Dim dbID As Integer = CInt(sqlCmd.ExecuteScalar())
                MsgBox((dbID > 0).ToString)
                tmpConn.Close()
            End Using
        End Using
If I connect Computer 1 to DB on Computer 2 it works works as expected too. But no matter what I do, it is not working on Computer 2. Any suggestions?

Forgot to mention, that the older Version 3.3 runs on both Machines!

Best regards,
Michael
Oct 11, 2013 at 8:20 PM
Edited Oct 11, 2013 at 8:20 PM
I don't do much work with VB.Net any more so I may be going down the wrong path but the issue would be due to the AddParameter extension for DbCommand. The default parameter type that I use is an DbType.Int32. Some how it is going through and added the parameter and not able to figure out the DbType of the object being passed in (which seems odd as it should be a string). Assuming it's going down the path that I think it is, it should be using the:
public static DbCommand AddParameter(this DbCommand Command, string ID, string Value = "", ParameterDirection Direction = ParameterDirection.Input)
Extension method. So I just went in and just set the DbType to DbType.String since it should always be that. Anyway, I just pushed up a fix to NuGet. If it doesn't work, let me know as it might be using one of the other extensions.
Oct 14, 2013 at 9:06 AM
Edited Oct 14, 2013 at 11:02 AM
Hello James,
what should I say: it is working - almost.
I wrote a couple of tests to narrow the problem down and almost all tests are working now on this machine. But one test is failing:
        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)
        Dim sqlstring As String = "SELECT database_id FROM Master.sys.Databases WHERE name=@Name"
        Using helper As New SQLHelper(sqlstring, CommandType.Text, constr)
            Dim para As New Utilities.SQL.MicroORM.Parameter(Of String)("@Name", DbType.String, DBName.Text, ParameterDirection.Input, "@")
            helper.AddParameter(para)
            Dim dbID As Integer = helper.ExecuteScalar(Of Integer)()
            MsgBox((dbID > 0).ToString)
        End Using
While this one is working:
        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)
        Dim sqlstring As String = "SELECT * FROM Master.sys.Databases WHERE name=@Name"
        Using helper As New SQLHelper(sqlstring, CommandType.Text, constr)
            helper.AddParameter("@Name", DBName.Text, ParameterDirection.Input)
            helper.ExecuteReader()
            MsgBox(helper.Read.ToString)
        End Using
Its not important for me for now. But I think the problem should be corrected everywhere?

Thanks for your help and this great library!

Michael
Oct 16, 2013 at 6:35 PM
OK, I've tried this (even created a unit test based off of it and a VB app to test with). I can't recreate the issue. The only way that I can get an error is if Server.Text, Username.Text, Passwort.Text, or DBName.Text is null... Any chance the user name and password don't have access to master? I seriously don't know what's going on...
Oct 17, 2013 at 9:17 AM
This cannot be the reason. I made those tests with the sa user. As I said: ALL tests are working on my machine. Only Button4_Click is not working on the machine I am trying to install my programm. Here is the complete test:
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click


        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)
        Dim sqlstring As String = "SELECT * FROM Master.sys.Databases WHERE name=@Name"
        Using helper As New SQLHelper(sqlstring, CommandType.Text, constr)
            helper.AddParameter("@Name", DBName.Text, ParameterDirection.Input)
            helper.ExecuteReader()
            MsgBox(helper.Read.ToString)
        End Using

    End Sub


    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)
        Dim sqlstring As String = String.Format("SELECT * FROM Master.sys.Databases WHERE name='{0}'", DBName.Text)
        Using helper As New SQLHelper(sqlstring, CommandType.Text, constr)
            helper.ExecuteReader()
            MsgBox(helper.Read.ToString)
        End Using

    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)

        Dim sqlstring As String = "SELECT database_id FROM Master.sys.Databases WHERE name=@Name"
        Using tmpConn As New SqlConnection(constr)
            Using sqlCmd As New System.Data.SqlClient.SqlCommand(sqlstring, tmpConn)
                tmpConn.Open()
                sqlCmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = DBName.Text
                Dim dbID As Integer = CInt(sqlCmd.ExecuteScalar())
                MsgBox((dbID > 0).ToString)
                tmpConn.Close()
            End Using
        End Using


    End Sub

    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)
        Dim sqlstring As String = "SELECT database_id FROM Master.sys.Databases WHERE name=@Name"
        Using helper As New SQLHelper(sqlstring, CommandType.Text, constr)
            Dim para As New Utilities.SQL.MicroORM.Parameter(Of String)("@Name", DbType.String, DBName.Text, ParameterDirection.Input, "@")
            helper.AddParameter(para)
            Dim dbID As Integer = helper.ExecuteScalar(Of Integer)()
            MsgBox((dbID > 0).ToString)
        End Using
    End Sub

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Dim constr = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", Server.Text, "Master", Username.Text, Passwort.Text)
         MsgBox(Utilities.SQL.SQLServer.SQLServer.DoesDatabaseExist(DBName.Text, constr).ToString)
    End Sub
It's the Code in an normal windows form four text fields (DBServer, Username, Passwort, DBName) and four buttons. Nothing else. For me it's no problem, that test4 is not working, I am using only test5 in my project. But we both know: Bugs are like submarines, they rise up if no one does expect it ;-)

Michael
Oct 18, 2013 at 12:03 AM
Edited Oct 18, 2013 at 1:57 AM
Well if I can figure out how to recreate the issue, I'll debug it. It's seriously making me want to throw the computer out the window... Out of curiosity, if you switch to using StringParameter instead of Parameter(Of String), does that fix the issue? That's the only potential issue that I can think of. Or perhaps does someone in ops hate the owner of the other machine? :) Like I said though, I'm uploading a unit test based on it. I'll test on a couple of systems but thus far, no dice.

Also, since you actually use the library and have commented/pointed out bugs a couple of times, I thought I'd ask your opinion. I'm working on version 4.0 which is going to be a bit different (mainly simplifying a couple of items, letting people plug in other libraries, etc). Any wish list items you want?