Solved

Insert from Datagrid

Posted on 2006-07-20
8
229 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

621 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