We help IT Professionals succeed at work.

sqldatasource

Mayank_Agarwal
on
Hi Guys

I am trying to insert a row in tot he table. the code is given below. I have create a sqldatasource using the asp.net wizard. when i try to insert the row i get the error in the * line as below.

Thanks in advance


INSERT INTO [Sites] ([SiteName], [SiteID]) VALUES (@SiteName, @SiteID)
....
            <InsertParameters>
                <asp:Parameter Name="SiteName" Type="String" />
                <asp:Parameter Name="SiteID" Type="Int32" />
            </InsertParameters>
......
 
    Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreate.Click
 
        dsGetSiteNames.InsertParameters.Add("@SiteID", 3)
        dsGetSiteNames.InsertParameters.Add("@SiteName", "txtSiteName")
        dsGetSiteNames.Insert()*******
    End Sub
Cannot insert the value NULL into column 'SiteName', table 'ECC_Web_Site.dbo.Sites'; column does not allow nulls. INSERT fails.
The statement has been terminated

Open in new window

Comment
Watch Question

Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
Hi Mayank_Agarwal,

You only have those two fields on the table (SiteName and SiteID) ?

jpaulino
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
Also "txtSiteName" shouldn't be the textbox ? If the txtSiteName.Text is empty you will receive that error.

jpaulino

Author

Commented:
HI

I only have these tow fields in the table,. i have tried to put txtSiteName.text as well but that did'nt work, so i tried to hardcode it to test it. I am also trying

       Using myConn As New SqlConnection(connStr)
            Dim sql As String = "INSERT INTO [Sites] ([SiteName], [SiteID]) VALUES (@SiteName, @SiteID)"
            Dim myCommand As New SqlCommand(sql, myConn)
            'Dim myDataSet As New DataSet
            myCommand.Parameters.AddWithValue("@siteID", 3)
            myCommand.Parameters.AddWithValue("@SiteName", txtSiteName.Text)
            Dim myAdapter As New SqlDataAdapter(myCommand)
            myAdapter.InsertCommand = myCommand
            myConn.Close()
        End Using
which doesnt work either, this doesnt give my any error but the row is not inserted

Author

Commented:
This seems to be working, i am sure there is an easier way to do this.

        Using myConn As New SqlConnection(connStr)
            Dim sql As String = "INSERT INTO [Sites] ([SiteName], [SiteID]) VALUES (@SiteName, @SiteID)"
            Dim myCommand As New SqlCommand(sql, myConn)
            'Dim myDataSet As New DataSet
            myCommand.Parameters.AddWithValue("@siteID", 3)
            myCommand.Parameters.AddWithValue("@SiteName", txtSiteName.Text)
            myCommand.Connection.Open()
            myCommand.ExecuteNonQuery()
            myCommand.Connection.Close()
            myConn.Close()
        End Using
IT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008
Commented:
Add this

myAdapter.InsertCommand = myCommand
Dim x As Integer = myCommand.ExecuteNonQuery()

Author

Commented:
Thanks Mate I managed to find the solution, some what using your technique.
Jorge PaulinoIT Pro/Developer
CERTIFIED EXPERT
Top Expert 2008

Commented:
You already have post it.  You don't need to open the connection again. If you define x as integer you can check if there is any affected record.

If x > 0 Then
 ' x records inserted
Else
 ' 0 records inserted
End If

Explore More ContentExplore courses, solutions, and other research materials related to this topic.