Solved

Insert from Datagrid

Posted on 2006-07-20
8
218 Views
Last Modified: 2010-04-23
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
Comment
Question by:bbarnette
  • 5
  • 3
8 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17150785
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
 

Author Comment

by:bbarnette
ID: 17150959
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
 
LVL 34

Expert Comment

by:Sancler
ID: 17152070
Is this function used to insert one record at a time?

Roger
0
 

Author Comment

by:bbarnette
ID: 17152793
The intent was for it to insert how ever many records the user added.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 34

Expert Comment

by:Sancler
ID: 17152875
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
 
LVL 34

Expert Comment

by:Sancler
ID: 17153149
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
 
LVL 34

Accepted Solution

by:
Sancler earned 125 total points
ID: 17156253
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
 

Author Comment

by:bbarnette
ID: 17157263
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now