[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Insert from Datagrid

I have been working on this function, and it is partially working. All fields are inserted into the database except for the CUSTNO, PROJECT, and LAB_NO (I cant understand why). The current CUSTNO and LAB_NO are passed to the function. Can you tell why this is not working or if there is any way to utilize these values that are passed in the following code?

    Sub InsertDataDS(ByRef sSlctCust As String, ByRef sSlctRprt As String)
        Dim command As SqlClient.SqlCommand

        ' Create the InsertCommand.
        command = New SqlClient.SqlCommand("INSERT INTO LLCOND" & sLocation & _
            " (CUSTNO, PROJECT, LAB_NO, CYLINDER, [LOAD], POUNDS, REQUIRED," & _
            "CYL_DIA, AREA_SQ, AGE, SLUMP, AIR, AMBIENT, CONCRETE, DATE_TEST)" & _
            " VALUES (@CUSTNO, @PROJECT, @LAB_NO, @CYLINDER, @LOAD, @POUNDS, @REQUIRED," & _
            " @CYL_DIA, @AREA_SQ, @AGE, @SLUMP, @AIR, @AMBIENT, @CONCRETE, @DATE_TEST)", cn)


        ' Add the parameters for the InsertCommand.
        command.Parameters.Add("@CUSTNO", SqlDbType.NVarChar, 6, "CUSTNO")
        command.Parameters.Add("@PROJECT", SqlDbType.NVarChar, 6, "PROJECT")
        command.Parameters.Add("@LAB_NO", SqlDbType.NVarChar, 13, "LAB_NO")
        command.Parameters.Add("@CYLINDER", SqlDbType.NVarChar, 6, "CYLINDER")
        command.Parameters.Add("@LOAD", SqlDbType.NVarChar, 8, "LOAD")
        command.Parameters.Add("@POUNDS", SqlDbType.NVarChar, 5, "POUNDS")
        command.Parameters.Add("@REQUIRED", SqlDbType.NVarChar, 5, "REQUIRED")
        command.Parameters.Add("@CYL_DIA", SqlDbType.NVarChar, 5, "CYL_DIA")
        command.Parameters.Add("@AREA_SQ", SqlDbType.NVarChar, 5, "AREA_SQ")
        command.Parameters.Add("@AGE", SqlDbType.NVarChar, 3, "AGE")
        command.Parameters.Add("@SLUMP", SqlDbType.NVarChar, 6, "SLUMP")
        command.Parameters.Add("@AIR", SqlDbType.NVarChar, 3, "AIR")
        command.Parameters.Add("@AMBIENT", SqlDbType.NVarChar, 6, "AMBIENT")
        command.Parameters.Add("@CONCRETE", SqlDbType.NVarChar, 6, "CONCRETE")
        command.Parameters.Add("@DATE_TEST", SqlDbType.SmallDateTime, 4, "DATE_TEST")

        Try
            'insert field into the database
            daData.InsertCommand = command

            daData.Update(dsData, "LLCOND" & sLocation)
        Catch ex As Exception
            'if error occurred, display error message
            MsgBox(ex.ToString, MsgBoxStyle.Information, )

        End Try

    End Sub

Thanks,

BB
0
bbarnette
Asked:
bbarnette
  • 5
  • 3
1 Solution
 
SanclerCommented:
From the problem you describe, it seems that the datatable called "LLCOND" & sLocation does not have values in its fields/columns called CUSTNO, PROJECT, and LAB_NO.  Your SQL commandtext instructs the dataadapter to get the values for those fields from those columns.  Although you say that two of them - CUSTNO and LAB_NO - are passed to the function, the function makes no use of the two arguments - sSlctCust and sSlctRprt - that are passed to it.  And I'm not sure, anyway, that you can mix up this sort of look-up approach that a dataadapter's insert command uses with the specification of particular values for parameters that can be used with ExecuteNonQuery.  The point about a dataadapter is that it will, with one .Update call, deal with all changed rows, so it needs to get its parameter values row by row.  With ExecuteNonQuery a single row is dealt with at a time, so specific values can be given for each time it is called.

I've written in general terms because I'm not sure precisely what your set-up and objective is.  But if you come back with specifics I (or someone) will try to help further.

Roger
0
 
bbarnetteAuthor Commented:
In the datagrid when I add a new record it does not include the value for the CUSTNO, but this value would be available from any of the other records in the datagrid or from the value passed. This why these values are passed to the function, because I had tried to use them in SQL statement and parameter fields. Needless to say, without success.

All of the fields listed in the SQL statement except for the CUSTNO, PROJECT, and LAB_NO are entered, so this is why I was looking to try to get the three other fields from a different source.

BB
0
 
SanclerCommented:
Is this function used to insert one record at a time?

Roger
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bbarnetteAuthor Commented:
The intent was for it to insert how ever many records the user added.
0
 
SanclerCommented:
In that case I think you need to add columns for CUSTNO, PROJECT and LAB_NO to the datatable concerned - "LLCOND" & sLocation.  You can do that programatically if they are not already there but, if they exist in the table in the SQL database table that you are updating, I assume they must already be there or, if they are not, you can adjust your dataadapter's select command to bring them over with the rest of the data.  You don't have to show those columns in your data grid.  You can put the values in them with code rather than from user input.  But unless you have columns in the source datatable, with appropriate values in them, when you call the dataadapter's .Update the dataadapter will not be able to send those values to the target table in the database.

An alternative approach, rather than using the dataadapter .Update, would be to write code (a) to extract from the datatable just those rows which had been added, (b) cycle through those rows one by one (c) build the command's commandtext (or - better - fill in the parameter values for a pre-prepared command) with values from the row concerned (d) add the "standard" values for the three parameters concerned and then (e) call ExecuteNonQuery on command.

As I said before, unfortunately I don't think there is a way in which you can combine the two approaches.

Roger
0
 
SanclerCommented:
Once I start thinking about this in an "in principle" way, rather than my usual reactionary "that's not how it works" way, I reckon it might be possible.  So hang on.  I need to check some things, and can't do so immediately.  But I'll try and come back within a few hours.

Roger
0
 
SanclerCommented:
Sorry I've been so long.  But I wanted to check things out on SQL (rather than the OleDb I normally use) so I needed to get back to a machine that had that installed.

The way to tackle this is to put the 'default' or 'hard-coded' values in the CommandText in place of parameters.  So the CommandText would be the same down to

        command = New SqlClient.SqlCommand("INSERT INTO LLCOND" & sLocation & _
            " (CUSTNO, PROJECT, LAB_NO, CYLINDER, [LOAD], POUNDS, REQUIRED," & _
            "CYL_DIA, AREA_SQ, AGE, SLUMP, AIR, AMBIENT, CONCRETE, DATE_TEST)" & _

but then, in the VALUES clause, you would need to replace @CUSTNO, @PROJECT, @LAB_NO with the actual values you wanted to go in those fields and then carry on with the list of parameters.  So I imagine it would go something like

            " VALUES ('" & sSlctCust & "', '" & <Project Name> & "', '" & sSlctRprt & "', @CUSTNO, @PROJECT, @LAB_NO, @CYLINDER, @LOAD, @POUNDS, @REQUIRED," & _
            " @CYL_DIA, @AREA_SQ, @AGE, @SLUMP, @AIR, @AMBIENT, @CONCRETE, @DATE_TEST)", cn)

I say "something like" as I am not sure whether sSlctCust is the CUSTNO value and sSlctRprt is the LAB_NO value, both of which you say are passed to the function: and I've no idea where the value for PROJECT is to come from.

You will also, of course, need to get rid of these three lines

        command.Parameters.Add("@CUSTNO", SqlDbType.NVarChar, 6, "CUSTNO")
        command.Parameters.Add("@PROJECT", SqlDbType.NVarChar, 6, "PROJECT")
        command.Parameters.Add("@LAB_NO", SqlDbType.NVarChar, 13, "LAB_NO")

as you are no longer using those as parameters.

It's different from any approach I've come across before, but it seems to work.

Roger

0
 
bbarnetteAuthor Commented:
Roger,

This is what I found as well, it was the only thing that made sense. FYI, The code I ended up with as is below. The code in the parameters did not change, only what is posted here.Thanks for all your help!

BB

  Sub InsertDataDS(ByRef sSlctCust As String, ByRef sSlctProj As String, ByRef sSlctRprt As String)
        Dim command As SqlClient.SqlCommand

        ' Create the InsertCommand.
        command = New SqlClient.SqlCommand("INSERT INTO LLCOND" & sLocation & _
            " (CUSTNO, PROJECT, LAB_NO, CYLINDER, [LOAD], POUNDS, REQUIRED," & _
            "CYL_DIA, AREA_SQ, AGE, SLUMP, AIR, AMBIENT, CONCRETE, DATE_TEST)" & _
            " VALUES (" & "'" & sSlctCust & "'" & ", " & "'" & sSlctProj & "'" & _
            ", " & "'" & sSlctRprt & "'" & ", @CYLINDER, @LOAD, @POUNDS, @REQUIRED," & _
            " @CYL_DIA, @AREA_SQ, @AGE, @SLUMP, @AIR, @AMBIENT, @CONCRETE, @DATE_TEST)", cn)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now