Insert record using parameters

19 Nov 2005 6:21 AM

pjcraig

This is driving me crazy! I have a form that a user will access from
another form by selecting the item that they wish to view. When they
open the new form, I pass through the id of the item they selected so
that only the details for the given item are displayed. In order for
this to work, I using the following code.... This is working no
problems at all:
       SqlSelectCommand1.Parameters("@blast_no").Value = blastNo

So this displays the records for the given blast number in my datagrid.
Users are able to insert records from this form but I don't want them
to have to type in the blast number (this should auto be inserted given
the current blast no you are viewing). so i tried:
        SqlInsertCommand1.Parameters("@blast_no").Value = blastNo

but when the user makes the insert, I get an error that blast_no does
not accept nulls. I have tried inserting a hardcoded value instead of
blastno just incase but still no good.

Bellow is a mock up of snippets of the code i am using to show my
problem, in my 'real' code, i am using a datagrid which is handling the
insert for me so i am unable to use the variable in the insert eg.
theDatarow(1) = Me.blastNo

Public Sub New(ByVal blastNo As Integer)
    MyBase.New()
        Me.blastNo = blastNo
        With SqlSelectCommand1
            .Parameters("@blast_no").Value = Me.blastNo
        End With
        With SqlInsertCommand1
            .Parameters("@blast_no").Value = Me.blastNo
        End With

        adpSql.Fill(MarvinDs1, "d_blast_survey")

        'try inserting a record......
        Dim dr As DataRow = MarvinDs1.Tables("d_blast_survey").NewRow()
        dr(0) = "TEST2"
        ' leaving column 1 out as this is what is sposed to be auto
inserted
        dr(2) = 1
        etc etc etc

        MarvinDs1.Tables("d_blast_survey").Rows.Add(dr) 'falls over on
this line due to column 1 containing a null value

       'the insert statement i am using has been auto gen by VS:
        Me.SqlInsertCommand1.CommandText = "INSERT INTO
d_blasthole(hole_no, blast_no, easting) VALUES (@hole_no, @blast_no,
@easting); SELECT hole_no, blast_no, easting FROM d_blasthole WHERE
(hole_no = @hole_no)"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@hole_no",
System.Data.SqlDbType.VarChar, 8, "hole_no"))
        Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@blast_no",
System.Data.SqlDbType.Int, 4, "blast_no"))
        Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@easting",
System.Data.SqlDbType.Real, 4, "easting"))

Any help is greatly appreciated
Paul

19 Nov 2005 10:19 AM

Cor Ligthert [MVP]

PJ,

I have given you an answer on this in another newsgroup.

Cor