none
Not working a stored procedure executing in vb net instead of running it via sqlcmd RRS feed

  • Question

  • I created a tored procedure which runs perfect using sqlcmd command, it works also using "Step Into stored procedure" from sserver explorer using Visual studio net. But when i execute this procedure using "ExecuteNonQuery" it doesn't work. Does anyone has any idea why is that?

    Thanks for help


    pp
    • Moved by Bob Beauchemin Friday, May 27, 2011 3:00 PM Moved to a more appropriate forum (From:.NET Framework inside SQL Server)
    Sunday, May 22, 2011 12:16 PM

Answers

  • Do you man the Ipoloipo stored procedure works fine in sqlcmd utility? --YES

    Do you get your expected result when executing the Ipoloipo stored procedure in SQL Server Management Studio with the same parameter values?  --YES

    this is the code of EisProstimou proc

    ALTER procedure [dbo].[EisProstimou]
     @PtPel int,@poso money,@nbrParast int,@dtint DATETIME as
    INSERT INTO Ptkinhseis
                          (PtPelaths, PtPOSO, PtParastatiko, PtHmeromhnia)
    values(@PtPel,@poso,@nbrParast,@dtint)   

     

    The net code snippet is the following;

                Dim DbConnection1 As New SqlClient.SqlConnection(conString)
                Dim PrintDB As SqlClient.SqlCommand

                PrintDB = New SqlClient.SqlCommand
                PrintDB.CommandType = CommandType.StoredProcedure
                PrintDB.CommandText = "testdb3"
                PrintDB.Connection = DbConnection1
                Dim sparam As SqlClient.SqlParameter
                sparam = New SqlClient.SqlParameter("@nbrParast", shei)
                sparam.SqlDbType = SqlDbType.Int
                PrintDB.Parameters.Add(sparam)
              

                sparam.SqlDbType = SqlDbType.Int
              
                sparam = New SqlClient.SqlParameter("@dtint", Today)
                sparam.SqlDbType = SqlDbType.DateTime
                PrintDB.Parameters.Add(sparam)

                Dim DbDataAdapter1 As New SqlClient.SqlDataAdapter
                DbDataAdapter1.SelectCommand = PrintDB
              
                DbConnection1.Open()
                Try
                  
                    PrintDB.ExecuteNonQuery()
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try

    I have to tell that i resolved the problem using another way. I read a microsoft book, i' ll give the title later which describes a way where you can create a sql server project . Well i used this and i created there three functions (poso1,poso2,poso3) the code is bellow:

    Partial Public Class UserDefinedFunctions
        <Microsoft.SqlServer.Server.SqlFunction()> _
        Public Shared Function Poso1(ByVal StrConnection As String, ByVal PtPelaths As SqlInt16, ByVal nbrParast As SqlInt16, ByVal dtADosh As SqlDateTime) As SqlMoney
            ' Add your code here
            Dim conString As String = StrConnection ' "Data Source=PAS2;Initial Catalog=ToebVelventou;Integrated Security=True;Persist Security Info=False;User Instance=False"
            Dim sSql As String, returnvalue As SqlMoney
            sSql = "select @poso1=(SELECT    SUM(PtPOSO) AS SumPoso " & _
            " FROM Ptkinhseis " & _
            " WHERE     (PtPelaths = @PtPelaths) AND (PtParastatiko = @nbrParast) AND (PtHmeromhnia <=CONVERT(DATETIME, @dtADosh, 105)))"
            Using cn As New SqlConnection(conString)
                Using cmd As New SqlCommand(sSql, cn)
                    Dim p As SqlParameter
                    'p = cmd.Parameters.Add("@PtPelaths", SqlDbType.Int)
                    cmd.Parameters.AddWithValue("@PtPelaths", PtPelaths)

                    'p = cmd.Parameters.Add("@nbrParast", SqlDbType.Int)
                    cmd.Parameters.AddWithValue("@nbrParast", nbrParast)

                    'p = cmd.Parameters.Add("@dtADosh", SqlDbType.DateTime)
                    cmd.Parameters.AddWithValue("@dtADosh", dtADosh)

                    p = cmd.Parameters.Add("@poso1", SqlDbType.Money)
                    p.Direction = ParameterDirection.Output
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cn.Close()
                    returnvalue = DirectCast(p.SqlValue, SqlMoney)

                End Using

            End Using
            Return returnvalue

        End Function

     
    End Class

    the same code is for the others (poso2,poso3) just other dates when i use parameter for the PtHmeromhnia field. I also created a StoredProcedure (InsIpoloipo) the code is below:

    Partial Public Class StoredProcedures
        <Microsoft.SqlServer.Server.SqlProcedure()> _
        Public Shared Sub InsIpoloipo(ByVal StrConnection As String, ByVal xreosiKall As SqlMoney, ByVal dtADosh As SqlDateTime, ByVal dtBDosh As SqlDateTime, ByVal parastPist As SqlInt16, ByVal nbrParast As SqlInt16, ByVal parstProstimo As SqlInt16, ByVal PtPelaths As SqlInt16)
            ' Add your code here
            Dim conString As String = StrConnection ' "Data Source=PAS2;Initial Catalog=ToebVelventou;Integrated Security=True;Persist Security Info=False;User Instance=False"
            Dim EisigmenhMera As SqlDateTime = Today
            Dim ypoloipo, poso1, tempPoso, h7, poso2, poso3, i6, prostimo As SqlMoney
            poso1 = SqlServerVelvento.UserDefinedFunctions.Poso1(StrConnection, PtPelaths, parastPist, dtADosh)
            poso2 = SqlServerVelvento.UserDefinedFunctions.Poso2(StrConnection, PtPelaths, parastPist, dtADosh, dtBDosh)
            poso3 = SqlServerVelvento.UserDefinedFunctions.poso3(StrConnection, PtPelaths, parastPist, dtADosh, dtBDosh)
            If poso1.IsNull Then poso1 = 0
            If poso2.IsNull Then poso2 = 0
            If poso3.IsNull Then poso3 = 0
            If prostimo.IsNull Then prostimo = 0

            If Today() <= dtADosh Then
                ypoloipo = xreosiKall - poso1
                Debug.Print("ypoloipo", ypoloipo)
            ElseIf Today() > dtADosh And Today() < dtBDosh Then
                If -poso1 + xreosiKall <= 0 Then
                    tempPoso = -poso1 + xreosiKall
                Else
                    tempPoso = (-poso1 + xreosiKall) * 1.1
                End If
                h7 = tempPoso - poso2
                ypoloipo = h7
                prostimo = -xreosiKall + (poso1 + poso2 + ypoloipo)
                EisigmenhMera = dtADosh
            Else
                tempPoso = xreosiKall - poso1
                If tempPoso > 0 Then
                    tempPoso = (tempPoso * 1.1)
                    h7 = tempPoso - poso2
                    If h7 > 0 Then
                        i6 = h7 - poso3
                        If i6 > 0 Then
                            ypoloipo = i6 + (xreosiKall * 1.1)
                        Else
                            ypoloipo = i6 + (xreosiKall * 1.1)
                        End If
                    Else
                        i6 = xreosiKall + h7
                        If i6 > 0 Then
                            ypoloipo = (i6 * 1.1) - poso3
                        Else
                            ypoloipo = i6 - poso3
                        End If
                    End If
                Else
                    h7 = tempPoso - poso2
                    i6 = h7 + xreosiKall
                    If i6 > 0 Then
                        ypoloipo = (i6 * 1.1) - poso3
                    Else
                        ypoloipo = i6 - poso3
                    End If
                End If

                EisigmenhMera = dtBDosh 'System.Convert.ToDateTime(secDate)
                prostimo = (ypoloipo + poso1 + poso2 + poso3) - xreosiKall * 2
            End If
            If prostimo > 0 Then
                Dim sSql As String = "INSERT INTO Ptkinhseis " & _
                                      " (PtPelaths, PtPOSO, PtParastatiko, PtHmeromhnia)" & _
                            " values(@PtPel,@poso,@nbrParast,@dtint) "
                Using cn As New SqlConnection(conString)
                    Using cmd As New SqlCommand(sSql, cn)
                        cmd.Parameters.AddWithValue("@PtPel", PtPelaths)
                        cmd.Parameters.AddWithValue("@poso", prostimo)
                        cmd.Parameters.AddWithValue("@nbrParast", parstProstimo)
                        cmd.Parameters.AddWithValue("@dtint", EisigmenhMera)
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        cn.Close()
                    End Using
                End Using
            End If



            'EXEC EisProstimou @PtPelaths,@prostimo,@parastProstimo,@dtADosh
            'EXEC EisProstimou @PtPelaths,@prostimo,@parastProstimo,@dtBDosh

        End Sub
    End Class

     

    So i created a dll file which i use it in my project and from there i call the InsIpoloipo procedure with the appropriate parameterss and it works OK. But the first problem i described in my first question remains.

    Than you all for your replies


    pp
    The title of the book is Microsoft Programming ADO.NET 2.0 Core Reference 2005 EDition

    pp
    Saturday, May 28, 2011 7:21 AM

All replies

  • Hi Pliachas,

    "Doesn't work" isn't really descriptive enough to even guess. What error do you get? If you don't get an error, what happens? Are you running the program in VS debugger or directly? Is the SQL Server local or remote?

    Having said that... ;-) my first/best guess: did you use the CommandType.StoredProcedure property with the SqlCommand instance you used with ExecuteNonQuery? The more info on the error, the better the guesses get.

    Cheers, Bob


    Monday, May 23, 2011 2:29 AM
  • The sql server is local. 

    i call the stored procedure with this code

     

    PrintDB = New SqlClient.SqlCommand

                PrintDB.CommandType = CommandType.StoredProcedure

                PrintDB.CommandText = "testdb3"

                PrintDB.Connection = DbConnection1

                Dim sparam As SqlClient.SqlParameter

                sparam = New SqlClient.SqlParameter("@nbrParast", shei)

                sparam.SqlDbType = SqlDbType.Int

                PrintDB.Parameters.Add(sparam)           

                sparam.SqlDbType = SqlDbType.

        sparam = New SqlClient.SqlParameter("@dtint", Today)

                sparam.SqlDbType = SqlDbType.DateTime

                PrintDB.Parameters.Add(sparam)

     

                Dim DbDataAdapter1 As New SqlClient.SqlDataAdapter

                DbDataAdapter1.SelectCommand = PrintDB          

                DbConnection1.Open()

                Try              

                    PrintDB.ExecuteNonQuery()

                Catch ex As Exception

                    Console.WriteLine(ex.Message)

                End Try

     

    i dont't get any errors. The procedure seems to run Ok but i don't have the results i expect in the table.

    On the other hand when i use the sql server explorer window from vb studio to find the stored procedure in the server, when i right click on it and i use "Step Into stored procedure" the procedure works fine.This is the proc

    --------------------

    ALTER procedure [dbo].[EisPro]

    @dtADosh DateTime,

    @dtBDosh DateTime,

    @doseis int,

    @nbrParast int,

    @parastProstimo int

    as


    DECLARE @vendor_id int;

    DECLARE Employee_Cursor CURSOR FOR

    select distinct CodEnoikos from Gia_Syn_Xreoseon(1) order by CodEnoikos;

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor INTO @vendor_id;

    WHILE @@FETCH_STATUS = 0

       BEGIN

    EXEC ipoloipo @vendor_id,@dtADosh,@dtBDosh,@doseis,@nbrParast,@parastProstimo

          FETCH NEXT FROM Employee_Cursor INTO @vendor_id;

    print @vendor_id;

       END;

    CLOSE Employee_Cursor;

    DEALLOCATE Employee_Cursor;

    -----------------------
    if you want i could provide the ipoloipo stored procedure
    Thanks for your reply

     


    pp
    Monday, May 23, 2011 8:50 AM
  • Can you please post the code for Stored Procedure ? Does your Stored Procedure include following statement ?

     

    Create Proc MyProc

    As

    Set NoCount ON ---> This

    Select * from MyTable(nolock)


    If this answer is helpful to you .. Please mark as Answer....
    Monday, May 23, 2011 8:13 PM
  • Hi PLiachasPaschalis,

    I noticed that your stored procedure returns messages using PRINT. PRINT returns a message string not a result to the client. To retrieve a message using ADO.NET, please refer to this link: http://www.dotnetcurry.com/ShowArticle.aspx?ID=344

    There are some other ways to return values or a user-defined message from SQL Server. You can create a temporary table or declare a table variable to contain the messages, then return the temp table/table variable using SELECT FROM and retrieve the result set in ADO.NET.

    For example,

    ALTER procedure [dbo].[EisPro] 
    @dtADosh DATETIME, 
    @dtBDosh DATETIME, 
    @doseis INT, 
    @nbrParast int, 
    @parastProstimo INT 
    AS
    
    DECLARE @tblVendorID TABLE (ID INT IDENTITY(1,1),Vendor_ID INT)
    
    DECLARE @vendor_id INT; 
    DECLARE employee_cursor CURSOR FOR 
    SELECT DISTINCT codenoikos 
    FROM  Gia_syn_xreoseon(1) 
    ORDER BY codenoikos; 
    
    OPEN employee_cursor; 
    FETCH NEXT FROM employee_cursor INTO @vendor_id; 
    
    WHILE @@FETCH_STATUS = 0 
     BEGIN 
       EXEC Ipoloipo 
        @vendor_id, 
        @dtADosh, 
        @dtBDosh, 
        @doseis, 
        @nbrParast, 
        @parastProstimo 
    
       FETCH NEXT FROM employee_cursor INTO @vendor_id; 
    
       --PRINT @vendor_id; 
       INSERT INTO @tblVendorID (Vendor_ID) VALUES (@vendor_id)
     END; 
    
    CLOSE employee_cursor; 
    DEALLOCATE employee_cursor; 
    
    SELECT ID, Vendor_ID FROM @tblVendorID
    

     For more information, please see: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/baf09902-3a0a-4f33-abaf-171b384d96ee/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Tuesday, May 24, 2011 3:02 AM
  • Hi PLiachasPaschalis,

    I noticed that your stored procedure returns messages using PRINT. PRINT returns a message string not a result to the client. To retrieve a message using ADO.NET, please refer to this link: http://www.dotnetcurry.com/ShowArticle.aspx?ID=344

    There are some other ways to return values or a user-defined message from SQL Server. You can create a temporary table or declare a table variable to contain the messages, then return the temp table/table variable using SELECT FROM and retrieve the result set in ADO.NET.

    For example,

    ALTER procedure [dbo].[EisPro] 
    @dtADosh DATETIME, 
    @dtBDosh DATETIME, 
    @doseis INT, 
    @nbrParast int, 
    @parastProstimo INT 
    AS
    
    DECLARE @tblVendorID TABLE (ID INT IDENTITY(1,1),Vendor_ID INT)
    
    DECLARE @vendor_id INT; 
    DECLARE employee_cursor CURSOR FOR 
    SELECT DISTINCT codenoikos 
    FROM Gia_syn_xreoseon(1) 
    ORDER BY codenoikos; 
    
    OPEN employee_cursor; 
    FETCH NEXT FROM employee_cursor INTO @vendor_id; 
    
    WHILE @@FETCH_STATUS = 0 
     BEGIN 
      EXEC Ipoloipo 
      @vendor_id, 
      @dtADosh, 
      @dtBDosh, 
      @doseis, 
      @nbrParast, 
      @parastProstimo 
    
      FETCH NEXT FROM employee_cursor INTO @vendor_id; 
    
      --PRINT @vendor_id; 
      INSERT INTO @tblVendorID (Vendor_ID) VALUES (@vendor_id)
     END; 
    
    CLOSE employee_cursor; 
    DEALLOCATE employee_cursor; 
    
    SELECT ID, Vendor_ID FROM @tblVendorID
    

     For more information, please see: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/baf09902-3a0a-4f33-abaf-171b384d96ee/


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Well the print i used was just for me when i run the proc in sql server managment studio.
    pp
    Tuesday, May 24, 2011 11:57 AM
  • this is the function Ipoloipo i use to make the calculations and insert the results to a table

    ALTER Procedure [dbo].[Ipoloipo]

    @PtPelaths int,

    @dtADosh DateTime,

    @dtBDosh DateTime,

    @doseis int,

    @nbrParast int,

    @parastProstimo int

    )

    AS

     

    Declare @xreosiKall money

    set @xreosiKall=(select PtPoso from Gia_EisXreoseonSynXreos(@doseis,@parastProstimo,@dtADosh) where PtPelaths=@PtPelaths)

    Declare @ypoloipo money

    Declare @tempPoso money

    Declare @h7 money

    Declare @i6 money

    Declare @poso1 money

    Declare @prostimo money

    set @poso1=(SELECT    isnull(SUM(PtPOSO),0) AS SumPoso

    FROM         Ptkinhseis

    WHERE     (PtPelaths = @PtPelaths) AND (PtParastatiko = @nbrParast) AND (PtHmeromhnia <=CONVERT(DATETIME, @dtADosh, 105)))

    Declare @poso2 money

    set @poso2=(SELECT     isnull(SUM(PtPOSO),0) AS SumPoso

    FROM         Ptkinhseis

    WHERE     (PtPelaths = @PtPelaths) AND (PtParastatiko = @nbrParast) AND (PtHmeromhnia between CONVERT(DATETIME, @dtADosh, 105) and CONVERT(DATETIME, @dtBDosh, 105)))

    Declare @poso3 money

    set @poso3=(SELECT     isnull(SUM(PtPOSO),0) AS SumPoso

    FROM         Ptkinhseis

    WHERE     (PtPelaths = @PtPelaths) AND (PtParastatiko = @nbrParast) AND (PtHmeromhnia between CONVERT(DATETIME, @dtBDosh, 105) and '12/31/2011'))

     

    if (GetDate()<=CONVERT(DATETIME, @dtADosh, 105))

    BEGIN

    SET @ypoloipo=@xreosiKall-@poso1

    --print @ypoloipo

    print'DHNTH'

    END

    ELSE 

    IF (getdate()>CONVERT(DATETIME, @dtADosh, 105) and getdate()<=CONVERT(DATETIME, @dtBDosh, 105)) 

    BEGIN

    if -@poso1+@xreosikall<=0 

    BEGIN

    set @tempPoso=-@poso1+@xreosikall

    END

    else 

    BEGIN

    set @tempPoso=(-@poso1+@xreosikall)*1.1

    END

    --print'DHNTH2'

    set @ypoloipo=@tempPoso-@poso2

    --print @ypoloipo

    --PRINT -@xreosikall+(@POSO1+@POSO2+@ypoloipo)

    set @prostimo=-@xreosikall+(@POSO1+@POSO2+@ypoloipo)

    if @prostimo>0 

    EXEC EisProstimou @PtPelaths,@prostimo,@parastProstimo,@dtADosh

     

    END

    ELSE

    BEGIN

    set @tempPoso=@xreosikall-@poso1

    if @tempPoso>0 

    begin

    set @tempPoso=@tempPoso*1.1

    set @h7=@tempPoso-@poso2

    if @h7>0 

    begin

    set @i6=@h7-@poso3

    if @i6>0 

    set @ypoloipo=@i6+@xreosikall*1.1

    else

    set @ypoloipo=@i6+@xreosikall*1.1

    end

    else

    begin

    set @i6=@xreosikall+@h7

    if @i6>0

    set @ypoloipo=@i6*1.1-@poso3

    else

    set @ypoloipo=@i6-@poso3

    end

    end

    else

    begin

    set @h7=@tempPoso-@poso2

    set @i6=@xreosikall+@h7

    if @i6>0

    set @ypoloipo=@i6*1.1-@poso3

    else

    set @ypoloipo=@i6-@poso3

    end

    print @ypoloipo

    print'3'

    set @prostimo=(@ypoloipo+@poso1+@poso2+@poso3)-@xreosikall*2

    if @prostimo>0 

    EXEC EisProstimou @PtPelaths,@prostimo,@parastProstimo,@dtBDosh

    print @prostimo

    END

     

     

    Thanks for your reply


    pp
    Tuesday, May 24, 2011 11:59 AM
  • What do you mean by “runs perfect using sqlcmd command”? Do you man the Ipoloipo stored procedure works fine in sqlcmd utility? Do you get your expected result when executing the Ipoloipo stored procedure in SQL Server Management Studio with the same parameter values? 

    For “when i execute this procedure using "ExecuteNonQuery" it doesn't work”, could you please post the .NET code snippet? I noticed that the Ipoloipo stored procedure just calls another stored procedure named EisProstimou, can you also show us its definition?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Wednesday, May 25, 2011 7:10 AM
  • To add to the answers from Jian Kang and others, an ADO.NET client does not return data from PRINT statements by default, since PRINT is considered to be an informational error message, but you wouldn't want to the change the client control flow like a non-informational error would.

    To enable receiving PRINT messages and other informational messages in an application program. you need to register an InfoMessageEvent handler on the SqlConnection instance as shown here: http://msdn.microsoft.com/en-us/library/a0hee08w.aspx.

    Cheers, Bob

    Since your question is about the client stack rather than SQLCLR, I'll eventually move it to the forum for SqlClient questions, which is the MSDN Forum "ADO.NET managed providers" located here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/threads. Though the name is not particularly intuitive, this is the forum for ADO.NET client-side questions.

    Thursday, May 26, 2011 10:32 PM
  • Do you man the Ipoloipo stored procedure works fine in sqlcmd utility? --YES

    Do you get your expected result when executing the Ipoloipo stored procedure in SQL Server Management Studio with the same parameter values?  --YES

    this is the code of EisProstimou proc

    ALTER procedure [dbo].[EisProstimou]
     @PtPel int,@poso money,@nbrParast int,@dtint DATETIME as
    INSERT INTO Ptkinhseis
                          (PtPelaths, PtPOSO, PtParastatiko, PtHmeromhnia)
    values(@PtPel,@poso,@nbrParast,@dtint)   

     

    The net code snippet is the following;

                Dim DbConnection1 As New SqlClient.SqlConnection(conString)
                Dim PrintDB As SqlClient.SqlCommand

                PrintDB = New SqlClient.SqlCommand
                PrintDB.CommandType = CommandType.StoredProcedure
                PrintDB.CommandText = "testdb3"
                PrintDB.Connection = DbConnection1
                Dim sparam As SqlClient.SqlParameter
                sparam = New SqlClient.SqlParameter("@nbrParast", shei)
                sparam.SqlDbType = SqlDbType.Int
                PrintDB.Parameters.Add(sparam)
              

                sparam.SqlDbType = SqlDbType.Int
              
                sparam = New SqlClient.SqlParameter("@dtint", Today)
                sparam.SqlDbType = SqlDbType.DateTime
                PrintDB.Parameters.Add(sparam)

                Dim DbDataAdapter1 As New SqlClient.SqlDataAdapter
                DbDataAdapter1.SelectCommand = PrintDB
              
                DbConnection1.Open()
                Try
                  
                    PrintDB.ExecuteNonQuery()
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try

    I have to tell that i resolved the problem using another way. I read a microsoft book, i' ll give the title later which describes a way where you can create a sql server project . Well i used this and i created there three functions (poso1,poso2,poso3) the code is bellow:

    Partial Public Class UserDefinedFunctions
        <Microsoft.SqlServer.Server.SqlFunction()> _
        Public Shared Function Poso1(ByVal StrConnection As String, ByVal PtPelaths As SqlInt16, ByVal nbrParast As SqlInt16, ByVal dtADosh As SqlDateTime) As SqlMoney
            ' Add your code here
            Dim conString As String = StrConnection ' "Data Source=PAS2;Initial Catalog=ToebVelventou;Integrated Security=True;Persist Security Info=False;User Instance=False"
            Dim sSql As String, returnvalue As SqlMoney
            sSql = "select @poso1=(SELECT    SUM(PtPOSO) AS SumPoso " & _
            " FROM Ptkinhseis " & _
            " WHERE     (PtPelaths = @PtPelaths) AND (PtParastatiko = @nbrParast) AND (PtHmeromhnia <=CONVERT(DATETIME, @dtADosh, 105)))"
            Using cn As New SqlConnection(conString)
                Using cmd As New SqlCommand(sSql, cn)
                    Dim p As SqlParameter
                    'p = cmd.Parameters.Add("@PtPelaths", SqlDbType.Int)
                    cmd.Parameters.AddWithValue("@PtPelaths", PtPelaths)

                    'p = cmd.Parameters.Add("@nbrParast", SqlDbType.Int)
                    cmd.Parameters.AddWithValue("@nbrParast", nbrParast)

                    'p = cmd.Parameters.Add("@dtADosh", SqlDbType.DateTime)
                    cmd.Parameters.AddWithValue("@dtADosh", dtADosh)

                    p = cmd.Parameters.Add("@poso1", SqlDbType.Money)
                    p.Direction = ParameterDirection.Output
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cn.Close()
                    returnvalue = DirectCast(p.SqlValue, SqlMoney)

                End Using

            End Using
            Return returnvalue

        End Function

     
    End Class

    the same code is for the others (poso2,poso3) just other dates when i use parameter for the PtHmeromhnia field. I also created a StoredProcedure (InsIpoloipo) the code is below:

    Partial Public Class StoredProcedures
        <Microsoft.SqlServer.Server.SqlProcedure()> _
        Public Shared Sub InsIpoloipo(ByVal StrConnection As String, ByVal xreosiKall As SqlMoney, ByVal dtADosh As SqlDateTime, ByVal dtBDosh As SqlDateTime, ByVal parastPist As SqlInt16, ByVal nbrParast As SqlInt16, ByVal parstProstimo As SqlInt16, ByVal PtPelaths As SqlInt16)
            ' Add your code here
            Dim conString As String = StrConnection ' "Data Source=PAS2;Initial Catalog=ToebVelventou;Integrated Security=True;Persist Security Info=False;User Instance=False"
            Dim EisigmenhMera As SqlDateTime = Today
            Dim ypoloipo, poso1, tempPoso, h7, poso2, poso3, i6, prostimo As SqlMoney
            poso1 = SqlServerVelvento.UserDefinedFunctions.Poso1(StrConnection, PtPelaths, parastPist, dtADosh)
            poso2 = SqlServerVelvento.UserDefinedFunctions.Poso2(StrConnection, PtPelaths, parastPist, dtADosh, dtBDosh)
            poso3 = SqlServerVelvento.UserDefinedFunctions.poso3(StrConnection, PtPelaths, parastPist, dtADosh, dtBDosh)
            If poso1.IsNull Then poso1 = 0
            If poso2.IsNull Then poso2 = 0
            If poso3.IsNull Then poso3 = 0
            If prostimo.IsNull Then prostimo = 0

            If Today() <= dtADosh Then
                ypoloipo = xreosiKall - poso1
                Debug.Print("ypoloipo", ypoloipo)
            ElseIf Today() > dtADosh And Today() < dtBDosh Then
                If -poso1 + xreosiKall <= 0 Then
                    tempPoso = -poso1 + xreosiKall
                Else
                    tempPoso = (-poso1 + xreosiKall) * 1.1
                End If
                h7 = tempPoso - poso2
                ypoloipo = h7
                prostimo = -xreosiKall + (poso1 + poso2 + ypoloipo)
                EisigmenhMera = dtADosh
            Else
                tempPoso = xreosiKall - poso1
                If tempPoso > 0 Then
                    tempPoso = (tempPoso * 1.1)
                    h7 = tempPoso - poso2
                    If h7 > 0 Then
                        i6 = h7 - poso3
                        If i6 > 0 Then
                            ypoloipo = i6 + (xreosiKall * 1.1)
                        Else
                            ypoloipo = i6 + (xreosiKall * 1.1)
                        End If
                    Else
                        i6 = xreosiKall + h7
                        If i6 > 0 Then
                            ypoloipo = (i6 * 1.1) - poso3
                        Else
                            ypoloipo = i6 - poso3
                        End If
                    End If
                Else
                    h7 = tempPoso - poso2
                    i6 = h7 + xreosiKall
                    If i6 > 0 Then
                        ypoloipo = (i6 * 1.1) - poso3
                    Else
                        ypoloipo = i6 - poso3
                    End If
                End If

                EisigmenhMera = dtBDosh 'System.Convert.ToDateTime(secDate)
                prostimo = (ypoloipo + poso1 + poso2 + poso3) - xreosiKall * 2
            End If
            If prostimo > 0 Then
                Dim sSql As String = "INSERT INTO Ptkinhseis " & _
                                      " (PtPelaths, PtPOSO, PtParastatiko, PtHmeromhnia)" & _
                            " values(@PtPel,@poso,@nbrParast,@dtint) "
                Using cn As New SqlConnection(conString)
                    Using cmd As New SqlCommand(sSql, cn)
                        cmd.Parameters.AddWithValue("@PtPel", PtPelaths)
                        cmd.Parameters.AddWithValue("@poso", prostimo)
                        cmd.Parameters.AddWithValue("@nbrParast", parstProstimo)
                        cmd.Parameters.AddWithValue("@dtint", EisigmenhMera)
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        cn.Close()
                    End Using
                End Using
            End If



            'EXEC EisProstimou @PtPelaths,@prostimo,@parastProstimo,@dtADosh
            'EXEC EisProstimou @PtPelaths,@prostimo,@parastProstimo,@dtBDosh

        End Sub
    End Class

     

    So i created a dll file which i use it in my project and from there i call the InsIpoloipo procedure with the appropriate parameterss and it works OK. But the first problem i described in my first question remains.

    Than you all for your replies


    pp
    Friday, May 27, 2011 2:32 PM
  • i used the print only for my purpose, even if i remark them the problem exists. if i use the sqlcmd window the print command returns the result i expect and the EisProstimou works ok also.


    pp
    Friday, May 27, 2011 2:36 PM
  • Do you man the Ipoloipo stored procedure works fine in sqlcmd utility? --YES

    Do you get your expected result when executing the Ipoloipo stored procedure in SQL Server Management Studio with the same parameter values?  --YES

    this is the code of EisProstimou proc

    ALTER procedure [dbo].[EisProstimou]
     @PtPel int,@poso money,@nbrParast int,@dtint DATETIME as
    INSERT INTO Ptkinhseis
                          (PtPelaths, PtPOSO, PtParastatiko, PtHmeromhnia)
    values(@PtPel,@poso,@nbrParast,@dtint)   

     

    The net code snippet is the following;

                Dim DbConnection1 As New SqlClient.SqlConnection(conString)
                Dim PrintDB As SqlClient.SqlCommand

                PrintDB = New SqlClient.SqlCommand
                PrintDB.CommandType = CommandType.StoredProcedure
                PrintDB.CommandText = "testdb3"
                PrintDB.Connection = DbConnection1
                Dim sparam As SqlClient.SqlParameter
                sparam = New SqlClient.SqlParameter("@nbrParast", shei)
                sparam.SqlDbType = SqlDbType.Int
                PrintDB.Parameters.Add(sparam)
              

                sparam.SqlDbType = SqlDbType.Int
              
                sparam = New SqlClient.SqlParameter("@dtint", Today)
                sparam.SqlDbType = SqlDbType.DateTime
                PrintDB.Parameters.Add(sparam)

                Dim DbDataAdapter1 As New SqlClient.SqlDataAdapter
                DbDataAdapter1.SelectCommand = PrintDB
              
                DbConnection1.Open()
                Try
                  
                    PrintDB.ExecuteNonQuery()
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try

    I have to tell that i resolved the problem using another way. I read a microsoft book, i' ll give the title later which describes a way where you can create a sql server project . Well i used this and i created there three functions (poso1,poso2,poso3) the code is bellow:

    Partial Public Class UserDefinedFunctions
        <Microsoft.SqlServer.Server.SqlFunction()> _
        Public Shared Function Poso1(ByVal StrConnection As String, ByVal PtPelaths As SqlInt16, ByVal nbrParast As SqlInt16, ByVal dtADosh As SqlDateTime) As SqlMoney
            ' Add your code here
            Dim conString As String = StrConnection ' "Data Source=PAS2;Initial Catalog=ToebVelventou;Integrated Security=True;Persist Security Info=False;User Instance=False"
            Dim sSql As String, returnvalue As SqlMoney
            sSql = "select @poso1=(SELECT    SUM(PtPOSO) AS SumPoso " & _
            " FROM Ptkinhseis " & _
            " WHERE     (PtPelaths = @PtPelaths) AND (PtParastatiko = @nbrParast) AND (PtHmeromhnia <=CONVERT(DATETIME, @dtADosh, 105)))"
            Using cn As New SqlConnection(conString)
                Using cmd As New SqlCommand(sSql, cn)
                    Dim p As SqlParameter
                    'p = cmd.Parameters.Add("@PtPelaths", SqlDbType.Int)
                    cmd.Parameters.AddWithValue("@PtPelaths", PtPelaths)

                    'p = cmd.Parameters.Add("@nbrParast", SqlDbType.Int)
                    cmd.Parameters.AddWithValue("@nbrParast", nbrParast)

                    'p = cmd.Parameters.Add("@dtADosh", SqlDbType.DateTime)
                    cmd.Parameters.AddWithValue("@dtADosh", dtADosh)

                    p = cmd.Parameters.Add("@poso1", SqlDbType.Money)
                    p.Direction = ParameterDirection.Output
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    cn.Close()
                    returnvalue = DirectCast(p.SqlValue, SqlMoney)

                End Using

            End Using
            Return returnvalue

        End Function

     
    End Class

    the same code is for the others (poso2,poso3) just other dates when i use parameter for the PtHmeromhnia field. I also created a StoredProcedure (InsIpoloipo) the code is below:

    Partial Public Class StoredProcedures
        <Microsoft.SqlServer.Server.SqlProcedure()> _
        Public Shared Sub InsIpoloipo(ByVal StrConnection As String, ByVal xreosiKall As SqlMoney, ByVal dtADosh As SqlDateTime, ByVal dtBDosh As SqlDateTime, ByVal parastPist As SqlInt16, ByVal nbrParast As SqlInt16, ByVal parstProstimo As SqlInt16, ByVal PtPelaths As SqlInt16)
            ' Add your code here
            Dim conString As String = StrConnection ' "Data Source=PAS2;Initial Catalog=ToebVelventou;Integrated Security=True;Persist Security Info=False;User Instance=False"
            Dim EisigmenhMera As SqlDateTime = Today
            Dim ypoloipo, poso1, tempPoso, h7, poso2, poso3, i6, prostimo As SqlMoney
            poso1 = SqlServerVelvento.UserDefinedFunctions.Poso1(StrConnection, PtPelaths, parastPist, dtADosh)
            poso2 = SqlServerVelvento.UserDefinedFunctions.Poso2(StrConnection, PtPelaths, parastPist, dtADosh, dtBDosh)
            poso3 = SqlServerVelvento.UserDefinedFunctions.poso3(StrConnection, PtPelaths, parastPist, dtADosh, dtBDosh)
            If poso1.IsNull Then poso1 = 0
            If poso2.IsNull Then poso2 = 0
            If poso3.IsNull Then poso3 = 0
            If prostimo.IsNull Then prostimo = 0

            If Today() <= dtADosh Then
                ypoloipo = xreosiKall - poso1
                Debug.Print("ypoloipo", ypoloipo)
            ElseIf Today() > dtADosh And Today() < dtBDosh Then
                If -poso1 + xreosiKall <= 0 Then
                    tempPoso = -poso1 + xreosiKall
                Else
                    tempPoso = (-poso1 + xreosiKall) * 1.1
                End If
                h7 = tempPoso - poso2
                ypoloipo = h7
                prostimo = -xreosiKall + (poso1 + poso2 + ypoloipo)
                EisigmenhMera = dtADosh
            Else
                tempPoso = xreosiKall - poso1
                If tempPoso > 0 Then
                    tempPoso = (tempPoso * 1.1)
                    h7 = tempPoso - poso2
                    If h7 > 0 Then
                        i6 = h7 - poso3
                        If i6 > 0 Then
                            ypoloipo = i6 + (xreosiKall * 1.1)
                        Else
                            ypoloipo = i6 + (xreosiKall * 1.1)
                        End If
                    Else
                        i6 = xreosiKall + h7
                        If i6 > 0 Then
                            ypoloipo = (i6 * 1.1) - poso3
                        Else
                            ypoloipo = i6 - poso3
                        End If
                    End If
                Else
                    h7 = tempPoso - poso2
                    i6 = h7 + xreosiKall
                    If i6 > 0 Then
                        ypoloipo = (i6 * 1.1) - poso3
                    Else
                        ypoloipo = i6 - poso3
                    End If
                End If

                EisigmenhMera = dtBDosh 'System.Convert.ToDateTime(secDate)
                prostimo = (ypoloipo + poso1 + poso2 + poso3) - xreosiKall * 2
            End If
            If prostimo > 0 Then
                Dim sSql As String = "INSERT INTO Ptkinhseis " & _
                                      " (PtPelaths, PtPOSO, PtParastatiko, PtHmeromhnia)" & _
                            " values(@PtPel,@poso,@nbrParast,@dtint) "
                Using cn As New SqlConnection(conString)
                    Using cmd As New SqlCommand(sSql, cn)
                        cmd.Parameters.AddWithValue("@PtPel", PtPelaths)
                        cmd.Parameters.AddWithValue("@poso", prostimo)
                        cmd.Parameters.AddWithValue("@nbrParast", parstProstimo)
                        cmd.Parameters.AddWithValue("@dtint", EisigmenhMera)
                        cn.Open()
                        cmd.ExecuteNonQuery()
                        cn.Close()
                    End Using
                End Using
            End If



            'EXEC EisProstimou @PtPelaths,@prostimo,@parastProstimo,@dtADosh
            'EXEC EisProstimou @PtPelaths,@prostimo,@parastProstimo,@dtBDosh

        End Sub
    End Class

     

    So i created a dll file which i use it in my project and from there i call the InsIpoloipo procedure with the appropriate parameterss and it works OK. But the first problem i described in my first question remains.

    Than you all for your replies


    pp
    The title of the book is Microsoft Programming ADO.NET 2.0 Core Reference 2005 EDition

    pp
    Saturday, May 28, 2011 7:21 AM