SQL and VB.NET identity insert problem.

aqzman_

Hi guys, I hope this is the right section, but as it's more of a SQL problem, it most likly is. Anyway, my problem is that I get this error: "DEFAULT or NULL are not allowed as explicit identity values." Everytime I try to run my code, but of all the searhing I've done on Google and Google code search I really can't find anything amiss. I'll post my source (What I think is needed) at the bottem. If there are any important lines of code that I've left out ask and I'll promptly post them.

    Dim mySelectQuery As String = "SELECT \* FROM tblCallers SET IDENTITY\_INSERT tblCallers ON"
    Dim myInsertQuery As String = "INSERT INTO tblCallers (CallerID, Name, Address, PostalCode, City, PhoneNumber, EmailAddress) VALUES(DEFAULT, '" & Name & "', '" & Address & "', '" & PostalCode & "', '" & City & "', '" & PhoneNum & "', '" & EmailAddress & "')"
    Dim myConnection As New Data.SqlClient.SqlConnection(myConnString)
    Dim myCommand As New Data.SqlClient.SqlCommand(mySelectQuery, myConnection)
    Dim myCommand2 As New Data.SqlClient.SqlCommand(myInsertQuery, myConnection)
    Dim retvalue As Integer
    myConnection.Open()
    retvalue = myCommand2.ExecuteNonQuery()
    Console.WriteLine(retvalue)
    Dim myReader As Data.SqlClient.SqlDataReader
    myReader = myCommand.ExecuteReader()
    myReader.Close()
    myConnection.Close()

I know that the code works because I use took it from another program I wrote and added the "SET IDENTITY_INSERT tblCallers ON" line. Also, the CallerID field is an Identity field. Thanks, aqzman

Colin Angus Mackay

aqzman_ wrote:

I know that the code works because I use took it from another program

Uh-huh...

aqzman_ wrote:

Dim myInsertQuery As String = "INSERT INTO tblCallers (CallerID, Name, Address, PostalCode, City, PhoneNumber, EmailAddress) VALUES(DEFAULT, '" & Name & "', '" & Address & "', '" & PostalCode & "', '" & City & "', '" & PhoneNum & "', '" & EmailAddress & "')"

You may wish to read about SQL Injection Attacks and how to prevent them[^] becuase your code is vulnerable right now. My suggestion, don't include the column that will automatically receive the identity in the list of columns you are inserting.

Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website

Giorgi Dalakishvili

If CallerID is auto-increment column then you don't need to specify it's value

#region signature my articles #endregion

aqzman_

Thanks a lot for the reply! I know my code might be a bit shakey, but I only started learning SQL, ASP.NET 2.0, and Visual Basic .NET last week. So sorry for any newbie mistakes, heh. Thanks for the advice about SQL Injection, I've only ever heard of it. Never read about it myself. This program will only be going up on a in-office intranet though, so I'm not too worried about attackers. But I'll probably fix it up once I read the links you posted. Never too early to start learning good programming habits. As for not including the column which will recieve the identity, that did the trick! Thanks a lot for your help! aqzman

Colin Angus Mackay

aqzman_ wrote:

This program will only be going up on a in-office intranet though, so I'm not too worried about attackers.

Statistically, most attacks are from people within the organisation.

Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... "I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless." Ready to Give up - Your help will be much appreciated. My website

sidbaruah

true very true!!! :)

I was born dumb!! :laugh:Programming made me laugh:laugh:!!! --sid--