Insert SQL Query not executed

Question

I am doing the programming on my computer and it works fine-the program, the database itself, inserting to the database is also working fine. But when I publish it and install the program on another computer. It crashes and does not execute the INSERT command.

Here is my code.

Private Sub cmdBlank_Click(sender As System.Object, e As System.EventArgs) Handles cmdBlank.Click
strTariff1 = txtPart1.Text & " " & txtPName1.Text & " " & txtQty1.Text & " " & txtU1.Text
strTariff2 = txtPart2.Text & " " & txtPName2.Text & " " & txtQty2.Text & " " & txtU2.Text
strTariff3 = txtPart3.Text & " " & txtPName3.Text & " " & txtQty3.Text & " " & txtU3.Text
strTariff4 = txtPart4.Text & " " & txtPName4.Text & " " & txtQty4.Text & " " & txtU4.Text
'strTariff5 = txtPart5.Text & " " & txtPName5.Text & " " & txtQty5.Text & " " & txtU5.Text

Call saveToDb()
frmreportax.Show()
End Sub

Private Function saveToDb()
    conn.Close()

    Dim cmdAdd, cmdCount, cmdAdd2 As New iDB2Command
    Dim sqlAdd, sqlCount, sqlAdd2 As String
    Dim curr1, curr2, curr3, curr4 As String
    Dim count As Integer

    conn.ConnectionString = str
    conn.Open()

    'Check for duplicate entry
    sqlCount = "SELECT COUNT(*) AS count FROM cewe WHERE transport=@transport AND blnum=@blnum"

    With cmdCount
        .CommandText = sqlCount
        .Connection = conn

        .Parameters.AddWithValue("@transport", frmPart1.txtTransport.Text)
        .Parameters.AddWithValue("@blnum", frmPart1.txtNo.Text)
    End With

    count = Convert.ToInt32(cmdCount.ExecuteScalar())

    If count <> 0 Then
        MsgBox("Duplicate Entry: " & frmPart1.txtTransport.Text, vbOKOnly + vbExclamation)
    Else

        sqlAdd = "INSERT INTO cewe (page) " & _
                    "VALUES (@page) "

        With cmdAdd
            .Parameters.AddWithValue("@page", Val(frmPart1.txtPage.Text))
            .CommandText = sqlAdd
            .Connection = conn
            .ExecuteNonQuery()
        End With
    end if


    cmdAdd.Dispose()
    cmdAdd2.Dispose()
    conn.Close()
end function

Please tell me what I am doing wrong? When I run and install the program on my PC, it works perfectly fine. But when I run/install it on another PC, it crashes after the cmdBlank is clicked.

It could be a problem with the database connection (either the installation or the configuration to run correctly with the application). Log exceptions\errors through the application to know what exactly the problem is. — souser, Sep 30 '14 at 04:28
Check out the logging module of enterprise library. Or you could simply log the exception details being thrown by the insert in a simple text file. — souser, Oct 02 '14 at 01:12

score 0 · Answer 1 · answered Sep 30 '14 at 04:30

0

There could be a number of things causing the issue but the first place to look is any error logs or crash report that may give some indication of the problem. Try debugging or logging to get a better picture. Beyond that there are some small suggestions that may help below.

Does the other computer have access to the database you are pointing to? Is the database connection pointing to localhost? In which case you will want to ensure that you have the same credentials (host, username, password, port etc.) set up on the database server on new computer. Are database drivers installed on new computer? What are the fundamental differences between the two machines?

answered Sep 30 '14 at 04:30

cherrysoft

1,165
8
17

hi! aside from this command, I have another form which retrieves data from database. I am using AS/400 iSeries DB2. It works well, data is being fetched from database and is being displayed in datagridview. But in this specific button, it adds a new data to the database. Whenever it adds data, or the program runs on the INSERT command, the application crashes suddenly. This only happens on the test pc. It works fine on my pc. – shesxue28 Sep 30 '14 at 04:37
I imagine it is something to do with the drivers so. You could make a tiny program to try to connect from the test box to see what happens. You could also check to see if the test box has access to the AS/400 but would troubleshoot in the following order. Check drivers (versions etc.), check that machine has access, write small program to get access. – cherrysoft Sep 30 '14 at 04:43
I think the program has access to the database because if none, problem would have arise from the Viewing/Fetching of records from database. From then, it should have crashed already. But it does not, it displays all the records. It also counts the number of records present in the database. I think the main problem is the INSERT COMMAND. Where can I do this? I mean I am not using ADO.NET or DSN for connection. Here's my connection string: – shesxue28 Sep 30 '14 at 04:48
Imports IBM.Data.DB2.iSeries Module modCEWE Public conn As New iDB2Connection Public str As String = "Datasource=10.0.1.11;UserID=edith;password=edith;DefaultCollection=impexplib" End Module – shesxue28 Sep 30 '14 at 04:49
If you are using any real data I would suggest editing with dummy data. I don't know anything about permissions on DB2 but does user from your origin IP have relevant permissions? If you are sure that this is happening at the insert point (you can only determine this by properly debugging - set a break point on the data retrieval portion of code to ensure that data is retrieved and step through accordingly). The code will bomb out at some point but at least you will know exactly where. – cherrysoft Sep 30 '14 at 04:54
Noted. I tried installing VS2010 on the test pc. Same happens, it crashes during the INSERT COMMAND. And this is what it says: "The xxx.exe managed to exit at code 0" Something like that. – shesxue28 Sep 30 '14 at 05:04

score 0 · Accepted Answer · answered Sep 30 '14 at 07:15

0

AS400 iSeries DB2 needs to be updated to version 6.xx.0800 and did the tweak!

Installer can be found here http://www-03.ibm.com/systems/power/software/i/access/windows_sp.html

Problem solved!

answered Sep 30 '14 at 07:15

shesxue28

13
3

Insert SQL Query not executed

2 Answers2