Solved

Insert from Datagrid

Posted on 2006-07-20
8
222 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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 …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

786 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