Insert record using parameters

[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 when inserting the
record 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
Cheers
Paul

 

[Cor Ligthert [MVP]]

PJ,

I am not sure, because this can have a lot of reasons, however can you start
with trying setting the Missingschemaaction before your fill mostly is that
the easiest one to resolve this kind of problems.

http://msdn.microsoft.com/library/d...ndataadapterclassmissingschemaactiontopic.asp

and than with the addwithkey

http://msdn.microsoft.com/library/d...fsystemdatamissingschemaactionclasstopic.aspI hope this helps,Cor

 

[Guest]

Can you set up the Blast_No field to accept null values then after inserting
the row, fill in the blast no.

 

[Paul Craig]

Thank you both for your responses. Cor, i have tried putting both lines
you suggested in before my fill:
adpSQL.MissingSchemaAction = MissingSchemaAction.Error
adpSQL.MissingSchemaAction = MissingSchemaAction.AddWithKey
but I am still getting the same error when I try and add the row to the
dataset. The problem must relate to my parameter for my insert not
setting the blast_no value for me because if i remove the insert
parameter value setting and give blast_no a value when adding the row
(ie dr(1) = Me.blastNo), the insert works perfectly.

Dennis, unfortunatly I cant change the database schema because if the
database is accessed outside of my application via queries or an
external source (which is likely), then all sorts of problems could
arise with inconsistent and incomplete data. It would have solved my
problem though

Cheers
Paul