none
DAL code in VB.net isn't working. RRS feed

  • Question

  • I sure could use some assistance with this.  I have, within a product Xlinesoft, the ability to utilize DAL to create a new record in a different table from fields in a current table when I am adding a record.  In this example the user is adding a new record to a .Net application and I need certain fields to be copied to a different table and create a new record.

    Example:  User adds a record in table NetAB and populates fields called LastName and FirstName.  As he saves the record, I invoke VB.Net code or I have the option of C# code to take the data from the two fields and copy it to a different table called Table1, add a new record and populate the fields LastName and FirstName with the data from NetAB.  The code I am using is this:

    Dim strSQLSave = new StringBuilder("INSERT INTO Table1 (FirstName, LastName) values (")

    strSQLSave.Append(values("FirstName").ToString() & ",")
    strSQLSave.Append(values("LastName"))

    strSQLSave.Append(")")
    CommonFunctions.db_exec(strSQLSave.ToString(), Nothing)

    Could someone look at this and just see if they see an issue?  I am just missing it all together.

    Thanks 

    Joe Aboulhosn

    Wednesday, February 14, 2018 10:16 PM

All replies

  • Text or string values in a SQL statement need to be enclosed within single quotes and that appears to be missing from your Append statements.

    I really do not know what CommonFunction.db_exec does but if you could rewrite that function using Command Parameters it would be less prone to mistakes (and less susceptible to a SQL Injection exploit).


    Paul ~~~~ Microsoft MVP (Visual Basic)


    • Edited by Paul P Clement IV Wednesday, February 14, 2018 10:25 PM sp
    • Proposed as answer by DA924x Thursday, February 15, 2018 12:56 AM
    Wednesday, February 14, 2018 10:24 PM
  • Hi Joeah,

    In general, we use the following method to insert data into table.

    Private Sub fun()
            Dim str As String = "Data Source=(LOCALDB)\MSSQLLOCALDB;Integrated Security=True"
            Using con As New SqlConnection(str)
                Using cmd As New SqlCommand("INSERT INTO Table1 (FirstName, LastName) values (@FirstName,@LastName)", con)
                    cmd.Parameters.AddWithValue("@FirstName", "firstname")
                    cmd.Parameters.AddWithValue("@LastName", "lastname")
                    con.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub

    And I never use the method you provide to insert data into table, maybe you provide the entire code here, and I can try my best to reproduce your issue.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 15, 2018 2:42 AM
    Moderator
  • Good Morning Paul,

    I have made the changes you suggested and still have an issue.  Here is what I have at this point:

    '**********  Save new data in another table  ************
    Dim strSQLSave = new StringBuilder("INSERT INTO Table1 (FirstName, LastName) values (")

    strSQLSave.Append(values('FirstName').ToString() & ",")
    strSQLSave.Append(values('LastName'))

    strSQLSave.Append(")")
    CommonFunctions.db_exec(strSQLSave.ToString(), Nothing)


    Return True

    When I try to compile, it errors and gives:

    \\ NetABEventsVB.vbproj
    Microsoft (R) Build Engine version 4.7.2556.0
    [Microsoft .NET Framework, version 4.0.30319.42000]
    Copyright (C) Microsoft Corporation. All rights reserved.

    C:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\include\NetAB_TableEvents.vb(28,25): error BC30201: Expression expected. [C:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\NetABEventsVB.vbproj]
    C:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\include\NetAB_TableEvents.vb(29,25): error BC30201: Expression expected. [C:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\NetABEventsVB.vbproj]

    Did I put the single quote in the correct place?

    Joe

    Thursday, February 15, 2018 3:37 PM
  • Did I put the single quote in the correct place?

    When you do get the single quote in the right place, then try entering a name in FirstName or Lastname that contains a single quote, like o'reilly or somename, Jr, and watch what happens. :)

    • Proposed as answer by Cor Ligthert Friday, February 16, 2018 3:55 AM
    Thursday, February 15, 2018 3:54 PM
  • No, it needs to enclose the value. Here is an example (I used the ASCII value of 39 for clarity instead of the single quote literal):

    Dim strSQLSave As String = "INSERT INTO Table1 (FirstName, LastName) values ("
    strSQLSave = String.Concat(strSQLSave, Chr(39), values("FirstName").ToString(), Chr(39), ", ", Chr(39), values("LastName").ToString(), Chr(39), ")")


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, February 15, 2018 6:50 PM
  • Hello Joe,

    If you are sending the query to a method you have no control over and parameters are not an option you could do the following (this is only if you don't have control over the query execution else go back to parameters for the command object).

    Add these two classes to your project.

    Public Class SqlFormatProvider
        Implements IFormatProvider
    
        Private ReadOnly _formatter As New SqlFormatter()
    
        Public Function GetFormat(ByVal pFormatType As Type) As Object Implements IFormatProvider.GetFormat
    
            If pFormatType Is GetType(ICustomFormatter) Then
                Return _formatter
            End If
    
            Return Nothing
    
        End Function
    End Class
    Public Class SqlFormatter
        Implements ICustomFormatter
        Public Function Format(ByVal pFormat As String, ByVal pArg As Object, ByVal pFormatProvider As IFormatProvider) As String _
            Implements ICustomFormatter.Format
    
            If pArg Is Nothing Then
                Return "NULL"
            End If
            If TypeOf pArg Is String Then
                Return "'" & CStr(pArg).Replace("'", "''") & "'"
            End If
            If TypeOf pArg Is Date Then
                Return "'" & CDate(pArg).ToString("MM/dd/yyyy") & "'"
            End If
            If TypeOf pArg Is IFormattable Then
                Return CType(pArg, IFormattable).ToString(pFormat, Globalization.CultureInfo.InvariantCulture)
            End If
    
            Return pArg.ToString()
    
        End Function
    End Class
    

    Add the following code module to the project.

    Public Module Helpers
        Public Function Sql(ByVal formattable As FormattableString) As String
            Return formattable.ToString(New SqlFormatProvider())
        End Function
    End Module

    Try it out

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim values As New Dictionary(Of String, String) From {{"FirstName", "Karen"}, {"LastName", "O'Brien"}}
            Dim strSQLSave = Sql($"INSERT INTO Table1 (FirstName, LastName) values ({values("FirstName")}, {values("LastName")})")
            Console.WriteLine(strSQLSave)
        End Sub
    End Class

    Results

    INSERT INTO Table1 (FirstName, LastName) values ('Karen', 'O''Brien')


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, February 16, 2018 1:55 AM
    Moderator
  • In ASPRunner.Net, my application system, it does most of my work for me automatically.  In certain areas it allows me to add code in either C# or VB.Net to do things.  For example, and this is where I am using it, I can add code in an area of "Before Record is Added".  So before the application adds a record it will do what the C# or VB.Net states to do then adds the record in the normal table (in my case NetAB).  It provides code samples to do specific functions, such as "Save New Data in Another Table" for the Database.  The code sample they provide is:

    ___

    '**********  Save new data in another table  ************
    Dim strSQLSave = new StringBuilder("INSERT INTO AnotherTable (Field1, Field2) values (")

    strSQLSave.Append(values("Field1").ToString() & ",")
    strSQLSave.Append(values("Field2"))

    strSQLSave.Append(")")
    CommonFunctions.db_exec(strSQLSave.ToString(), Nothing)

    ___

    The following areas are in red and per instructions need to be changed to the Table and Field Names of that table.  In red are "AnotherTable", "Field1", and "Field2".

    So I have change these red areas to "Table1" , "LastName", and "FirstName".

    The system should handle the code and do as it is intended, but it does not do as it should.  It gives compile errors, or if it does compile it doesn't do as wanted and save the data to the other table, it does to the NetAB but not to Table1.

    So this is my problem and I hope that I have explained it properly. 

    Best Regards

    Joe

    Friday, February 16, 2018 4:13 PM
  • Did you see what Da924x wrote, it seems to me reasonable that with your current code goes wrong if that is the case. 

    Strange is that in these forums persons drop new messages while there is not even a reply from an OP.


    Success
    Cor


    Friday, February 16, 2018 4:25 PM
  • In ASPRunner.Net, my application system, it does most of my work for me automatically.  In certain areas it allows me to add code in either C# or VB.Net to do things.  For example, and this is where I am using it, I can add code in an area of "Before Record is Added".  So before the application adds a record it will do what the C# or VB.Net states to do then adds the record in the normal table (in my case NetAB).  It provides code samples to do specific functions, such as "Save New Data in Another Table" for the Database.  The code sample they provide is:

    ___

    '**********  Save new data in another table  ************
    Dim strSQLSave = new StringBuilder("INSERT INTO AnotherTable (Field1, Field2) values (")

    strSQLSave.Append(values("Field1").ToString() & ",")
    strSQLSave.Append(values("Field2"))

    strSQLSave.Append(")")
    CommonFunctions.db_exec(strSQLSave.ToString(), Nothing)

    ___

    The following areas are in red and per instructions need to be changed to the Table and Field Names of that table.  In red are "AnotherTable", "Field1", and "Field2".

    So I have change these red areas to "Table1" , "LastName", and "FirstName".

    The system should handle the code and do as it is intended, but it does not do as it should.  It gives compile errors, or if it does compile it doesn't do as wanted and save the data to the other table, it does to the NetAB but not to Table1.

    So this is my problem and I hope that I have explained it properly. 

    Best Regards

    Joe


    I posted a code example for the SQL issue you were encountering. If you are still getting an exception (error) then please post the code you now have and indicate where the exception occurs.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 16, 2018 4:38 PM
  • Paul,

    And you are sure that this fit in PHPRunner which the OP wrote he is using?

    I see dropped in all System.Data code. 


    Success
    Cor

    Friday, February 16, 2018 5:29 PM
  • It would be for ASPRunner.net which is same company but different product.  I don't use PHPRunner although the abilities are the same.

    Joe

    Friday, February 16, 2018 8:53 PM
  • Paul using the above as the VB code, I get this:

    Server Error in '/' Application.

    Syntax error in INSERT INTO statement.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

    Source Error: 

    Line 235:				{
    Line 236:					if ( !MVCFunctions.HandleError() )
    Line 237:						throw e;
    Line 238:				}
    Line 239:				return null;

    Source File: c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\connections\Connection.cs    Line: 237 

    Stack Trace: 

    [OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
       runnerDotNet.Connection.query(XVar sql) in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\connections\Connection.cs:237
       runnerDotNet.Connection.exec(XVar sql) in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\connections\Connection.cs:284
       CallSite.Target(Closure , CallSite , Object , XVar ) +154
       System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2(CallSite site, T0 arg0, T1 arg1) +675
       runnerDotNet.CommonFunctions.db_exec(Object _param_sql, Object _param_conn) in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\connections\dbfunctions_legacy.cs:49
       runnerDotNet.runnerDotNet.eventclassCS_NetAB.BeforeAdd(Object values, Object& message, Object inline, Object pageObject) in C:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\include\NetAB_TableEvents.vb:32
       CallSite.Target(Closure , CallSite , IEventProviderVB_NetAB , Object , Object& , Object , Object ) +386
       CallSite.Target(Closure , CallSite , IEventProviderVB_NetAB , Object , Object& , Object , Object ) +771
       runnerDotNet.eventclass_NetAB.BeforeAdd(Object values, Object& message, Object inline, Object pageObject) in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\include\NetAB_EventsAggregator.cs:86
       CallSite.Target(Closure , CallSite , Object , XVar , Object& , XVar , AddPage ) +312
       CallSite.Target(Closure , CallSite , Object , XVar , Object& , XVar , AddPage ) +771
       runnerDotNet.AddPage.callBeforeAddEvent() in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\Classes\addpage.cs:411
       runnerDotNet.AddPage.processDataInput() in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\Classes\addpage.cs:272
       runnerDotNet.AddPage.process() in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\Classes\addpage.cs:206
       CallSite.Target(Closure , CallSite , Object ) +112
       runnerDotNet.NetABController.add() in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\Controllers\NetAB\add.cs:265
       lambda_method(Closure , ControllerBase , Object[] ) +62
       System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
       System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +182
       System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
       System.Web.Mvc.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() +56
       System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +256
       System.Web.Mvc.<>c__DisplayClass17.<InvokeActionMethodWithFilters>b__14() +22
       System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +190
       System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +311
       System.Web.Mvc.Controller.ExecuteCore() +105
       runnerDotNet.BaseController.ExecuteThread(Object arg) in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\Controllers\BaseController.cs:45
    
    [AggregateException: One or more errors occurred.]
       runnerDotNet.BaseController.ExecuteCore() in c:\Users\joehp\Documents\ASPRunnerNETProjects\NetAB\output\Controllers\BaseController.cs:32
       System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +88
       System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +10
       System.Web.Mvc.<>c__DisplayClassb.<BeginProcessRequest>b__5() +34
       System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +16
       System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +10
       System.Web.Mvc.Async.WrappedAsyncResult`1.End() +50
       System.Web.Mvc.<>c__DisplayClasse.<EndProcessRequest>b__d() +28
       System.Web.Mvc.SecurityUtil.<GetCallInAppTrustThunk>b__0(Action f) +7
       System.Web.Mvc.SecurityUtil.ProcessInApplicationTrust(Action action) +23
       System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +59
       System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
       System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9748665
       System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +48
       System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +159

    And BTW, "Hook'em Horns"

    Friday, February 16, 2018 9:04 PM
  • Can you post what the SQL statement looks like before it is executed? You should be able to dump it out to the Output window or in a MsgBox.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 16, 2018 10:32 PM
  • From NetAB

    SELECT
    ID,
    Photo,
    LastName,
    FirstName,
    Address,
    City,
    [State],
    Zip,
    Email,
    CellPhone,
    HomePhone,
    DOB,
    Notes,
    Xmas,
    Birthday,
    Field1,
    Field2
    FROM NetAB
    ORDER BY LastName DESC, FirstName DESC

    And for Table1

    SELECT
    ID,
    LastName,
    FirstName,
    Field1,
    Field2
    FROM Table1 

    Friday, February 16, 2018 10:36 PM
  • And BTW, "Hook'em Horns"

    And you are doing T-SQL in the manner that you are doing it in a MVC solution? What you are doing is not optimal programming. The program  is wide-open and prone to SQL Injection attacks let alone the way you are doing it that can produce malformed T-SQL  due to input from the user that will make the program terminate.

    What, did you just ignore how to use ASP.NET MVC with  ADO.NET Entity Framework that had to be clearly shown to you if you ever did an ASP.NET MVC tutorial?

    http://www.devx.com/dotnet/Article/34653

    No one does what you are doing with T-SQL in an ASP.NET MVC solution. if he or she knows any better.

    Saturday, February 17, 2018 12:57 PM
  • Thank you for your input and direction.  I am not and have not been trained on proper use of ASP.NET.  I have no ability to change the way ASPRunner.Net is built and/or used.  It is not under my control.  I take your curt answer to heart and I apologize for not being trained and not knowing any better. 

    Sunday, February 18, 2018 5:07 PM
  • ASPRunner.Net? I have never heard of it. It's some 3rd party tool that leads to lazy development, a crutch that does it for the developer.

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/models-data/creating-model-classes-with-the-entity-framework-vb

    I'll give kudos on trying to use a DAL, but you need some basics. 

    https://en.wikipedia.org/wiki/Separation_of_concerns

    http://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-MVC/

    Monday, February 19, 2018 4:59 AM
  • In your initial post you indicated that you were using an INSERT statement but you posted a SELECT statement. So are you using the code I posted or not?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, February 19, 2018 5:36 AM
  • I want to thank everyone for the wonderful response for my request for help and again for your direction.

    I was able to use C# to get this to work and I am very happy with the results.  For those that want to know, this is what I used (I added a few fields to insure it was working properly):

    dynamic tblTable1 = GlobalVars.dal.Table("Table1");
    tblTable1.Value["Field1"] = values["Field1"];
    tblTable1.Value["Field2"] = values["Field2"];
    tblTable1.Value["LastName"] = values["LastName"];
    tblTable1.Value["FirstName"] = values["FirstName"];
    tblTable1.Add();

    Now when I add a record in NetAB table, it creates a record in Table1 and automatically puts the data from the NetAB record.

    Again Thank you for your help on this.

    Joe

    Friday, February 23, 2018 7:35 PM
  • I want to thank everyone for the wonderful response for my request for help and again for your direction.

    I was able to use C# to get this to work and I am very happy with the results.  For those that want to know, this is what I used (I added a few fields to insure it was working properly):

    dynamic tblTable1 = GlobalVars.dal.Table("Table1");
    tblTable1.Value["Field1"] = values["Field1"];
    tblTable1.Value["Field2"] = values["Field2"];
    tblTable1.Value["LastName"] = values["LastName"];
    tblTable1.Value["FirstName"] = values["FirstName"];
    tblTable1.Add();

    Now when I add a record in NetAB table, it creates a record in Table1 and automatically puts the data from the NetAB record.

    Again Thank you for your help on this.

    Joe

    For those who think it has anything with C# or VB, no it is not. 

    The 100% equivalent code in VB would be

        Option Strict Off
    
        Dim tblTable1 = GlobalVars.dal.Table("Table1")
        tblTable1.Value("Field1") = values("Field1")
        tblTable1.Value("Field2") = values("Field2")
        tblTable1.Value("LastName") = values("LastName")
        tblTable1.Value("FirstName") = values("FirstName")
        tblTable1.Add()
    Maybe he solved his problem, but his question has nothing to do with its code. Maybe did his wife/mother now use his keyboard, that has as much chance as using this code instead of that he has shown. 


    Success
    Cor


    Friday, February 23, 2018 10:11 PM