none
Why isn't the Parameterized INSERT query working? RRS feed

  • Question

  • Hi Everyone,

    I took an entire JSON table and modified it to return only eight values; the IP Address, City, Region, Country Name, In_EU, Latitude, Longitude, and Org. Using two separate functions.

    My problem is I can’t get my VB.NET function to store this to the application’s local database. I have tried two different approaches:

    Approach 1:

    Public Function Insert_JSON_for_Attacker(ByVal StrCnn As String,
                                             ByRef DT_Attacker As DataTable) _
                                             As Boolean
            'Purpose:       Stores Attacker JSON data into Database
            'Parameters:    strCnn As String, DT_Attacker As DataTable
            'Returns:       True if Insert successful; False otherwise
            Dim strSQL As String,
                strAttackerID As String,
                lngCountryID As Long,
                lngRegionID As Long,
                strCity As String,
                bInEu As Boolean,
                sngLat As Single,
                sngLong As Single,
                strOrg As String,
                cmd As SqlCommand
    
            strAttackerID = DT_Attacker.Rows(0).Item("AttackerID").ToString
            lngCountryID = CLng(DT_Attacker.Rows(0).Item("CountryID").ToString)
            lngRegionID = CLng(DT_Attacker.Rows(0).Item("RegionID").ToString)
            strCity = DT_Attacker.Rows(0).Item("City").ToString
            bInEu = CBool(DT_Attacker.Rows(0).Item("In_EU").ToString)
            sngLat = CSng(DT_Attacker.Rows(0).Item("Latitude").ToString)
            sngLong = CSng(DT_Attacker.Rows(0).Item("Longitude").ToString)
            strOrg = DT_Attacker.Rows(0).Item("Org_Name").ToString
    
            Using cnn As New SqlConnection(StrCnn)
                Try
                    'Get all Information from 
                    'selected Server's Database table
                    strSQL = "INSERT INTO [tblAtackers] ([AttackerID], " &
                             "[CountryID], [RegionID], [City], [In_EU], " &
                             "[Latitude], [Longitude], [Org_Name]) " &
                             "VALUES (" & strAttackerID & ", " &
                             lngCountryID & ", " & lngRegionID & ", " &
                             strCity & ", " & bInEu & ", " &
                             sngLat & ", " & ", " & strOrg & ");"
                    cnn.Open()
                    cmd = New SqlCommand(strSQL, cnn)
                    cmd.CommandType = CommandType.Text
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                    Return False
                End Try
            End Using
            Return True
    End Function
    

    When I tried to insert data this way I get the error:  Incorrect syntax near '.103'. This tells me exactly squat. What is it? How do I correct it? There is no explanation for either.

    Approach 2:

    Public Function Insert_JSON_for_Attacker(ByVal StrCnn As String,
                                             ByRef DT_Attacker As DataTable) _
                                             As Boolean
            'Purpose:       Stores Attacker JSON data into Database
            'Parameters:    strCnn As String, DT_Attacker As DataTable
            'Returns:       True if Insert successful; False otherwise
            Dim strSQL As String
            Using cnn As New SqlConnection(StrCnn)
                Try
                    strSQL = "INSERT INTO [tblAttackers] ([AttackerID], " &
                             "[CountryID], [RegionID], [City], [In_EU], " &
                             "[Latitude], [Longitude], [Org_Name]) " &
                             "VALUES (@AttackerID, " &
                             "@CountryID, @RegionID, @City, @In_Eu, " &
                             "@Latitude, @Longitude, @Org_Name);"
                    cnn.Open()
                    Dim cmd As SqlCommand = New SqlCommand(strSQL, cnn)
                    With cmd.Parameters
                        .AddWithValue("@AttackerID",
                                      DT_Attacker.Rows(0).Item("AttackerID"))
                        .AddWithValue("@CountryID",
                                      DT_Attacker.Rows(0).Item("CountryID"))
                        .AddWithValue("@RegionID",
                                      DT_Attacker.Rows(0).Item("RegionID"))
                        .AddWithValue("@City",
                                      DT_Attacker.Rows(0).Item("City"))
                        .AddWithValue("@In_EU",
                                      DT_Attacker.Rows(0).Item("In_EU"))
                        .AddWithValue("@Latitude",
                                      DT_Attacker.Rows(0).Item("Latitude"))
                        .AddWithValue("@Longitude",
                                      DT_Attacker.Rows(0).Item("Longitude"))
                        .AddWithValue("@Org_Name",
                                      DT_Attacker.Rows(0).Item("Org_Name"))
                    End With
                    cmd.ExecuteNonQuery()
                    cnn.Close()
    
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace,
                                       "Error in Insert_JSON_for_Attacker")
                    Return False
                End Try
            End Using
            Return True
    End Function
    

    This function uses Parameters in place of the variables I used in Approach 1. It does not throw any exceptions, but it doesn’t write any data into the database table either.

    Here is my initial WAG(Wild Ass Guess) on how the parameterized version works.

    We have the base SQL statement: INSERT INTO [tblAttackers] ([AttackerID], [CountryID], [RegionID], [City], [In_EU], [Latitude], [Longitude], [Org_Name]) VALUES (@AttackerID, @CountryID, @RegionID, @City, @In_Eu, @Latitude, @Longitude, @Org_Name);"

    My guess is the cmd.Parameters method modifies the initial SQL into: 

    INSERT INTO [tblAttackers] ([AttackerID], [CountryID], [RegionID], [City], [In_EU], [Latitude], [Longitude], [Org_Name]) VALUES ’81.159.103.100’, 230, 3889, ‘Fraserburgh’, 1, 57.657, -2.0448, ‘British Telecommunications PLC’);"

    When I look at the table data I just see:

    One short coming I can see is there is no way to see what the cmd.Parameters method did to your base query before it was executed.

    As you can see there is no information underneath the column names, because the INSERT failed.

    Why?

    Thanks,

    One short coming I can see is there is no way to see what the cmd.Parameters method did to your base query before it was executed.

    You can see there is no information underneath the column names, because the INSERT failed.

    Why?

    Thanks,



    MRM256

    Saturday, February 22, 2020 10:00 PM

Answers

  • Karen,

    It took me a while, but I finally figured out how to use SQL Server Management Studio to query the LocalDB in my VB.NET project. In my defense I use SSMS to access the SQL Server Developer’s edition I have on my Server PC.  I didn’t even know where to begin when it came to accessing the LocalDB.

    To keep the confusion to a minimum I am going to make step by step instructions to help others.

    Step 1: Find the connection string for your LocalDB and copy the section outlined.

    Step 2: Open SQL Server Management Studio.

    Step 3: Paste the LocalDB server name into the Server name pull down box and change Authentication to Windows Authentication.

    Step 4: Click on the Connect button. After SSMS connects SSMS object explorer will show you something like:

    Step 5: Verify that this is the database you are writing and reading information by writing a simple SQL to check out your latest VB.NET coding attempt. Here I am checking my INSERT query to see if my VB.NET code in my project functioned properly.

    My initial mistake was trying to check the projects database by using the Visual Studio's IDE. I didn't quite understand what Peter Fleischer was trying to tell me until I found out how to connect to my LocalDB using SSMS.

    I hope others will learn from my mistake.

    Thanks,


    MRM256

    • Marked as answer by MRM256 Monday, February 24, 2020 4:29 PM
    Monday, February 24, 2020 4:29 PM
  • If you never used SSMS, right click on the table and follow the path shown below. Tweak the INSERT and for the parameters setup values for testing and execute.

    In regards to SQL-Injection, using parameters is a bonus but the real benefit is to properly format SQL values.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by MRM256 Monday, February 24, 2020 4:06 PM
    Sunday, February 23, 2020 1:51 AM
    Moderator

All replies

  • Hello,

    When creating any non-simple query first and always create the query in SSMS (SQL-Server Management Studio) which is totally free. 

    Write, in this case the INSERT statement with parameters e.g. (I didn't get everything or knew field types)

    USE [ForumExample];
    
    
    DECLARE @AttackerID INT = 1;
    DECLARE @CountryID INT = 3;
    DECLARE @Latitude DECIMAL = 57.657;
    DECLARE @Longitude DECIMAL = -2.0448;
    DECLARE @City NVARCHAR(MAX) = N'Whatever';
    
    INSERT INTO [dbo].[tblAttackers] ( [AttackerID] ,
                                       [CountryID] ,
                                       [Latitude] ,
                                       [Longitude] ,
                                       [City] )
    VALUES ( @AttackerID, @CountryID, @Latitude, @Longitude, @City );
    

    Validate the query works then save it. Implement the query in code, if and when it fails take the values to be inserted and insert them into the saved query parameters, run it, fix the failure if it's data perhaps a incorrect field type etc.



    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, February 22, 2020 11:28 PM
    Moderator
  • Your paramerized version is always going to mitigate malformed T-SQL.

    Your building of the T-SQL string can result in malformed T-SQL, becuase if you get an apostrophe cominng in the T-SQL,  that is an ESC sequence,  and the T-SQL is malformed with a syntax error.  

    http://www.utteraccess.com/forum/remove-apostrophe-string-t1961126.html

    Saturday, February 22, 2020 11:39 PM
  • When I tried to insert data this way I get the error:  Incorrect syntax near '.103'.This tells me exactly squat. What is it? How do I correct it? There is no explanation for either.

    You can figure it out by using the VS debugger,  stopping before you execute cmd.ExecuteNonQuery, use Quickwatch to view the content of the 'cmd' object that has the T-SQL it's about to execute, copy and paste it into the query execution pane in SSMS and execute the T-SQL. It will point to the syntax error

    Saturday, February 22, 2020 11:49 PM
  • Hi Karen,

    The database is an .MDF file created as the LocalDB inside the application. 

    I read and wrote to LocalDBs using Approach 1, but SQL injection attacks have gotten everyone paranoid. Never understood that, because the SQL was being build inside the application.

    I will try the SSMS approach tomorrow.

    Thanks,


    MRM256

    Sunday, February 23, 2020 1:19 AM
  • If you never used SSMS, right click on the table and follow the path shown below. Tweak the INSERT and for the parameters setup values for testing and execute.

    In regards to SQL-Injection, using parameters is a bonus but the real benefit is to properly format SQL values.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by MRM256 Monday, February 24, 2020 4:06 PM
    Sunday, February 23, 2020 1:51 AM
    Moderator
  • Karen,

    This works: INSERT INTO [tblAttackers] ([AttackerID], [CountryID], [RegionID],[City], [In_EU], [Latitude], [Longitude], [Org_Name]) VALUES ('81.159.103.100', 230, 3889, 'Fraserburgh', 1, 57.657, -2.0448, 'British Telecommunications PLC');

    This doesn't: strSQL = "INSERT INTO [tblAttackers] ([AttackerID], " &
                             "[CountryID], [RegionID], [City], [In_EU], " &
                             "[Latitude], [Longitude], [Org_Name]) " &
                             "VALUES ('" & [strAttackerID] & "', " &
                             [lngCountryID] & ", " & [lngRegionID] & ", '" &
                             [strCity] & "', " & [intIn_Eu] & ", " &
                             [sngLat] & ", " & [sngLong] & ", '" & [strOrg] & "');"

    The first one functions because the values that I want in the database table are hardcoded into the SQL string.

    I want to make the SQL dynamic. Meaning I want to change those values in the SQL before it is executed.

    I used to do a dynamic SQL all the time. What changed?

    Thanks,


    MRM256

    Sunday, February 23, 2020 4:58 PM
  • Hi,

    You can see there is no information underneath the column names, because the INSERT failed.

    If you use localDB mdf you must check which version you use. The localDB mdf is part of the project and is then copied to the bin folder for execution. INSERT will then also take effect in mdf in bin folder..


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Sunday, February 23, 2020 5:10 PM
  • I used to do a dynamic SQL all the time. What changed?

    Then you were lucky and no string data that had an apostrophe was in the data that would create malformed T-SQL. 

    Sunday, February 23, 2020 5:15 PM
  • Hi Peter,

    This is the simplest INSERT query I could find and it still doesn't work.

    Public Function Insert_JSON_for_Attacker(ByVal StrCnn As String,
                                                 ByRef DT_Attacker As DataTable) _
                                                 As Boolean
            'Purpose:       Stores Attacker JSON data into Database
            'Parameters:    strCnn As String, DT_Attacker As DataTable
            'Returns:       True if Insert successful; False otherwise
            Dim mysqlConn As Data.SqlClient.SqlConnection
            Dim command As Data.SqlClient.SqlCommand
            Dim strSQL As String,
                strAttackerID As String,
                lngCountryID As Long,
                lngRegionID As Long,
                strCity As String,
                intIn_Eu As Integer,
                sngLat As Single,
                sngLong As Single,
                strOrg As String,
                query As String
    
            strAttackerID = DT_Attacker.Rows(0).Item("AttackerID").ToString
            lngCountryID = CLng(DT_Attacker.Rows(0).Item("CountryID").ToString)
            lngRegionID = CLng(DT_Attacker.Rows(0).Item("RegionID").ToString)
            strCity = DT_Attacker.Rows(0).Item("City").ToString
            intIn_Eu = CBool(DT_Attacker.Rows(0).Item("In_EU").ToString)
            sngLat = CSng(DT_Attacker.Rows(0).Item("Latitude").ToString)
            sngLong = CSng(DT_Attacker.Rows(0).Item("Longitude").ToString)
            strOrg = DT_Attacker.Rows(0).Item("Org_Name").ToString
    
            mysqlConn = New Data.SqlClient.SqlConnection
            mysqlConn.ConnectionString = StrCnn
            query = "INSERT INTO [tblAttackers] ([AttackerID], " &
                     "[CountryID], [RegionID], [City], [In_EU], " &
                     "[Latitude], [Longitude], [Org_Name]) " &
                     "VALUES ('" & strAttackerID & "', " &
                     lngCountryID & ", " & lngRegionID & ", '" &
                     strCity & "', " & intIn_Eu & ", " &
                     sngLat & ", " & sngLong & ", '" & strOrg & "');"
    
            Dim reader As Data.SqlClient.SqlDataReader
            Try
                mysqlConn.Open()
                command = New SqlClient.SqlCommand(query, mysqlConn)
                reader = command.ExecuteReader
                mysqlConn.Close()
                MessageBox.Show("Data Saved")
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
            'Return True
        End Function
    
    

    Looks like something is broken.

    Thanks,


    MRM256

    Sunday, February 23, 2020 10:09 PM
  • Mark,

    Where is the .mdf located that you are looking at in SSMS?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, February 23, 2020 10:16 PM
  • Karen,

    This works: INSERT INTO [tblAttackers] ([AttackerID], [CountryID], [RegionID],[City], [In_EU], [Latitude], [Longitude], [Org_Name]) VALUES ('81.159.103.100', 230, 3889, 'Fraserburgh', 1, 57.657, -2.0448, 'British Telecommunications PLC');

    This doesn't: strSQL = "INSERT INTO [tblAttackers] ([AttackerID], " &
                             "[CountryID], [RegionID], [City], [In_EU], " &
                             "[Latitude], [Longitude], [Org_Name]) " &
                             "VALUES ('" & [strAttackerID] & "', " &
                             [lngCountryID] & ", " & [lngRegionID] & ", '" &
                             [strCity] & "', " & [intIn_Eu] & ", " &
                             [sngLat] & ", " & [sngLong] & ", '" & [strOrg] & "');"

    The first one functions because the values that I want in the database table are hardcoded into the SQL string.

    I want to make the SQL dynamic. Meaning I want to change those values in the SQL before it is executed.

    I used to do a dynamic SQL all the time. What changed?

    Thanks,


    MRM256

    In SSMS, right click on the database, select generate scripts for the table and post the results here?

    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, February 23, 2020 10:30 PM
    Moderator
  • Karen,

    The MDF file is located on my PC named MRM-WINX-001 along with SSMS 2017. How do I use SSMS to connect to my LocalDB CounterAttackLDB.mdf?

    Thanks,


    MRM256

    Monday, February 24, 2020 12:42 AM
  • Bonnie,

    I can't use SSMS to view the CounterAttackLDB.mdf file located on MRM-WINX-001. I not sure how to make it look at the database on C:\Users\Quecu\OneDrive\Documents\Visual Studio 2017\Projects\CounterStrike\CounterStrike.

    Thanks,


    MRM256

    Monday, February 24, 2020 12:46 AM
  • Bonnie,

    I can't use SSMS to view the CounterAttackLDB.mdf file located on MRM-WINX-001. I not sure how to make it look at the database on C:\Users\Quecu\OneDrive\Documents\Visual Studio 2017\Projects\CounterStrike\CounterStrike.

    Thanks,


    MRM256

    You should be able to use this for the server name (LocalDb)\v11.0

    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, February 24, 2020 2:52 AM
    Moderator
  • Hi,
    try following function. Show output in immediate window (Debug.Print):

      Public Function Insert_JSON_for_Attacker(ByVal StrCnn As String,
                                                 ByRef DT_Attacker As DataTable) _
                                                 As Boolean
        'Purpose:       Stores Attacker JSON data into Database
        'Parameters:    strCnn As String, DT_Attacker As DataTable
        'Returns:       True if Insert successful; False otherwise
        Dim query = "INSERT INTO [tblAttackers] ([AttackerID], " &
                    "[CountryID], [RegionID], [City], [In_EU], " &
                    "[Latitude], [Longitude], [Org_Name]) " &
                    "VALUES (@AttackerID, @CountryID, @RegionID, @City, @In_Eu, @Lat, @Long, @Org;"
        Try
          Using mysqlConn As New Data.SqlClient.SqlConnection
            mysqlConn.Open()
            '
            Debug.Print($"ConnectionString: {StrCnn}")
            '
            ' check recordcount
            Using command As New SqlClient.SqlCommand("SELECT Count(*) FROM [tblAttackers]", mysqlConn)
              Debug.Print($"Recordcount bevore: {command.ExecuteScalar}")
            End Using
            '
            Using command As New SqlClient.SqlCommand(query, mysqlConn)
              With command.Parameters
                .AddWithValue("@AttackerID", DT_Attacker.Rows(0).Item("AttackerID"))
                .AddWithValue("@CountryID", DT_Attacker.Rows(0).Item("CountryID"))
                .AddWithValue("@RegionID", DT_Attacker.Rows(0).Item("RegionID"))
                .AddWithValue("@City", DT_Attacker.Rows(0).Item("City"))
                .AddWithValue("@In_Eu", DT_Attacker.Rows(0).Item("In_EU"))
                .AddWithValue("@Lat", DT_Attacker.Rows(0).Item("Latitude"))
                .AddWithValue("@Long", DT_Attacker.Rows(0).Item("Longitude"))
                .AddWithValue("@Org", DT_Attacker.Rows(0).Item("Org_Name"))
              End With
              Dim cnt = command.ExecuteNonQuery
              Debug.Print($"Data Saved: {cnt} rows")
            End Using
            '
            ' check recordcount
            Using command As New SqlClient.SqlCommand("SELECT Count(*) FROM [tblAttackers]", mysqlConn)
              Debug.Print($"Recordcount after: {command.ExecuteScalar}")
            End Using
            '
          End Using
          Return True
        Catch ex As Exception
          MessageBox.Show(ex.Message)
        End Try
        Return False
      End Function


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Monday, February 24, 2020 6:55 AM
  • Karen,

    It took me a while, but I finally figured out how to use SQL Server Management Studio to query the LocalDB in my VB.NET project. In my defense I use SSMS to access the SQL Server Developer’s edition I have on my Server PC.  I didn’t even know where to begin when it came to accessing the LocalDB.

    To keep the confusion to a minimum I am going to make step by step instructions to help others.

    Step 1: Find the connection string for your LocalDB and copy the section outlined.

    Step 2: Open SQL Server Management Studio.

    Step 3: Paste the LocalDB server name into the Server name pull down box and change Authentication to Windows Authentication.

    Step 4: Click on the Connect button. After SSMS connects SSMS object explorer will show you something like:

    Step 5: Verify that this is the database you are writing and reading information by writing a simple SQL to check out your latest VB.NET coding attempt. Here I am checking my INSERT query to see if my VB.NET code in my project functioned properly.

    My initial mistake was trying to check the projects database by using the Visual Studio's IDE. I didn't quite understand what Peter Fleischer was trying to tell me until I found out how to connect to my LocalDB using SSMS.

    I hope others will learn from my mistake.

    Thanks,


    MRM256

    • Marked as answer by MRM256 Monday, February 24, 2020 4:29 PM
    Monday, February 24, 2020 4:29 PM
  • Great to hear you have this figured out. Since you are able to connect now you might be interested in this free add-in which I use at work.

    https://www.apexsql.com/sql-tools-refactor.aspx


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, February 24, 2020 5:13 PM
    Moderator