• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

INSERT INTO with sqlCommand object not working

When I use the following code:

                strSQL = "INSERT INTO  tblCookTimes(ItemNum, KettleSize, Cooktime) VALUES ('8085001013', '650', '60' )"
                cmdUpdateTime.CommandText = strSQL
                cmdUpdateTime.ExecuteNonQuery()

I get an error saying that the field "RecID" in the table "tblCookTimes" cannot be null.  

I know that I set RecID as an Identity set to increment by 1 and that if I use a straight SQL statement in SQL Query Analyzer it will work and just insert the record and increment the RecID.  But in code it fails.  

How do I get around this? Thanks in advance.
0
mrbass78
Asked:
mrbass78
  • 4
  • 3
  • 2
  • +1
1 Solution
 
wguerramCommented:
What Command object are you using?

OleDbCommand or SqlCommand
0
 
mrbass78Author Commented:
SqlCommand
0
 
Timbo87Commented:
Is it also a primary key?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mrbass78Author Commented:
Yes
0
 
DotNetLover_BaanCommented:
Are there any other fields in "tblCookTimes" table, except these four ?
-Baan
0
 
wguerramCommented:
Remove the primary key attribute of the column and let it allow nulls.

and try your code.
0
 
mrbass78Author Commented:
Baan:  No just those four fields are in the table.  
wguerram: I want it to be the primary key and I want it to have unique values, and I don't want to manage that through code.
0
 
wguerramCommented:
I just want to see what happens...

I understand you need it as a primary.
0
 
DotNetLover_BaanCommented:
ok, then don't specify the field name while inserting..
strSQL = "INSERT INTO  tblCookTimes VALUES ('8085001013', '650', '60' )"   'Use this.

-Baan
0
 
wguerramCommented:
I Tried inserting values in my sqlserver and didn't problems.
What is you framework version? Sql Sever Version?

Can you post your code starting from the point when you create your connection and connection string also.
'--------------------------------------------


This a Work around:


Create a stored procedure in your SQLServer DB:

CREATE PROCEDURE proc_Insert_tblCookTimes (@ItemNum char(10), @KettleSize char(10), @Cooktime char(10))  AS

INSERT INTO tblCookTimes (ItemNum, KettleSize, Cooktime) VALUES (@ItemNum, @KettleSize, @Cooktime)
GO
'End of store procedure

'---------------------------------
In you VB Code


Dim cmd As New System.Data.SqlClient.SqlCommand("proc_Insert_tblCookTimes", cn)
       
        With cmdUpdateTime
            .CommandText = "proc_Insert_tblCookTimes"
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@ItemNum", Data.SqlDbType.Char, 10)).Value = "8085001013"
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@KettleSize", Data.SqlDbType.Char, 10)).Value = "650"
            .Parameters.Add(New System.Data.SqlClient.SqlParameter("@Cooktime", Data.SqlDbType.Char, 10)).Value = "60"
            .CommandType = Data.CommandType.StoredProcedure
           
            .ExecuteNonQuery()
        End With
               
        cmdUpdateTime.Parameters.Clear()
        cmdUpdateTime.Dispose()


If you have problems, let me know it.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now