Solved

Insert from Datagrid

Posted on 2006-07-20
8
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:bbarnette
ID: 17152793
The intent was for it to insert how ever many records the user added.
0
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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