SQL INSERT Problem in ASP.NET - Does not give me any Error either

[BilalAKhan]

Hi,

I have a problem with asp.net insertion data in SQL data base. I am using SQL Express with ASP.NET 4.0. I had written the code correctly and it was working fine as well, but now it does not insert the data in the database. It started behaving abnormally when I made the some changes in ASP.NET form. When I run the application it shows me that it has inserted the data entered but when I look into the database, there is nothing there.

The code is given below:

I

mports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Linq
Imports System.Text
Imports System.Web.UI.WebControls.RadioButtonList

Partial Class Registration
    Inherits System.Web.UI.Page

    Protected Sub RegisterButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RegisterButton.Click

        If FirstNameText.Text = "" Or LastNameText.Text = "" Or EmailAddressText.Text = "" GenderList.SelectedIndex = False Then
            EntryMissingWarningLabel.Text = "Please fill in all the fields above."
        End If

        Dim connectionstring As String = WebConfigurationManager.ConnectionStrings("Register").ConnectionString

        Dim insertcommand As String

        insertcommand = "insert into Medpoint_Register values (@FirstName, @LastName, @Email, @Gender)"

        Dim reg_connection As New SqlConnection(connectionstring)
        Dim reg_command As New SqlCommand(insertcommand, reg_connection)

        reg_command.Parameters.AddWithValue("@FirstName", FirstNameText.Text)
        reg_command.Parameters.AddWithValue("@LastName", LastNameText.Text)
        reg_command.Parameters.AddWithValue("@Email", EmailAddressText.Text)
        reg_command.Parameters.AddWithValue("@Gender", GenderList.SelectedIndex)
       

        Dim added As Integer = 0
        Try
            reg_connection.Open()
            added = reg_command.ExecuteNonQuery()
            AddedLabel.Text = added.ToString() & "Record Added"
        Catch err As SqlException
            Error1Label.Text = "Problem in inserting the data. Please try again"
        Finally
            reg_connection.Close()

        End Try
        '  reg_connection.Close()
        Response.Redirect("RegistrationRedirect.aspx")
    End Sub

End Class

Please help me with this... I have checked the code so many timesm but it seems ok to me.

Thank you in anticipation.

Regards,

Bilal A. Khan

[stbuchok]

Just out of curiosity, what happens if FirstName.Text equals the following:

'', '', '', '') Go Drop Table Medpoint_Register --

Please use Stored Procedures, it will make it easier to make changes to your code without breaking your SQL, it also protects you from SQL injections.

Also your validation at the top that checks for empty strings should test against String.Empty, not to mention that even if there is empty strings, the rest of your code still executes (have an else with that if).

Also you can use SQL Profiler when inserting into the database (not just for inserts though, extremely useful tool) to see what is going on. Express version doesn't come with it however, there is a free version here: http://sites.google.com/site/sqlprofiler/

[bmcmickle]

Hi,

I have a problem with asp.net insertion data in SQL data base. I am using SQL Express with ASP.NET 4.0. I had written the code correctly and it was working fine as well, but now it does not insert the data in the database. It started behaving abnormally when I made the some changes in ASP.NET form. When I run the application it shows me that it has inserted the data entered but when I look into the database, there is nothing there.

The code is given below:

I

mports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Linq
Imports System.Text
Imports System.Web.UI.WebControls.RadioButtonList

Partial Class Registration
    Inherits System.Web.UI.Page

    Protected Sub RegisterButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RegisterButton.Click

        If FirstNameText.Text = "" Or LastNameText.Text = "" Or EmailAddressText.Text = "" GenderList.SelectedIndex = False Then
            EntryMissingWarningLabel.Text = "Please fill in all the fields above."
        End If

        Dim connectionstring As String = WebConfigurationManager.ConnectionStrings("Register").ConnectionString

        Dim insertcommand As String

        insertcommand = "insert into Medpoint_Register values (@FirstName, @LastName, @Email, @Gender)"

        Dim reg_connection As New SqlConnection(connectionstring)
        Dim reg_command As New SqlCommand(insertcommand, reg_connection)

        reg_command.Parameters.AddWithValue("@FirstName", FirstNameText.Text)
        reg_command.Parameters.AddWithValue("@LastName", LastNameText.Text)
        reg_command.Parameters.AddWithValue("@Email", EmailAddressText.Text)
        reg_command.Parameters.AddWithValue("@Gender", GenderList.SelectedIndex)
       

        Dim added As Integer = 0
        Try
            reg_connection.Open()
            added = reg_command.ExecuteNonQuery()
            AddedLabel.Text = added.ToString() & "Record Added"
        Catch err As SqlException
            Error1Label.Text = "Problem in inserting the data. Please try again"
        Finally
            reg_connection.Close()

        End Try
        '  reg_connection.Close()
        Response.Redirect("RegistrationRedirect.aspx")
    End Sub

End Class

Please help me with this... I have checked the code so many timesm but it seems ok to me.

Thank you in anticipation.

Regards,

Bilal A. Khan

Please look at your Insert statement. I think you need to define the field the values will go into like insert into Medpoint_Register(firstname,lastname,email,gender) values (@FirstName, @LastName, @Email, @Gender)" I was just guessing what your db field names are.

[aspproject]

try query like
insert into Medpoint_Register (firstname,lastname,email,gender) values (@FirstName, @LastName, @Email, @Gender)

[nandey]

Hi

Use this code

Protected Sub RegisterButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RegisterButton.Click

If FirstNameText.Text = "" Or LastNameText.Text = "" Or EmailAddressText.Text = "" Or GenderList.SelectedIndex = False Then
Error1Label.Text = "Please fill in all the fields above."
End If

Dim connectionstring As String = WebConfigurationManager.ConnectionStrings("Register").ConnectionString

Dim insertcommand As String

insertcommand = "insert into Medpoint_Register(FirstName, LastName, Email, Gender) values (@FirstName, @LastName, @Email, @Gender)"

Dim reg_connection As New SqlConnection(connectionstring)
Dim reg_command As New SqlCommand(insertcommand, reg_connection)
reg_command.CommandType = CommandType.Text
reg_command.Parameters.AddWithValue("@FirstName", SqlDbType.VarChar).Value = FirstNameText.Text
reg_command.Parameters.AddWithValue("@LastName", SqlDbType.VarChar).Value = LastNameText.Text
reg_command.Parameters.AddWithValue("@Email", SqlDbType.VarChar).Value = EmailAddressText.Text
reg_command.Parameters.AddWithValue("@Gender", SqlDbType.Int).Value = GenderList.SelectedIndex

Dim added As Integer = 0
Try
reg_connection.Open()
added = reg_command.ExecuteNonQuery()
If added > 0 Then
Error1Label.Text = added.ToString() & "Record Added"
Else
Error1Label.Text = added.ToString() & "Not Record Addrf"
End If
Catch err As SqlException
Error1Label.Text = "Problem in inserting the data. Please try again"
Finally
reg_connection.Close()

End Try
' reg_connection.Close()
Response.Redirect("RegistrationRedirect.aspx")
End Sub

anil nandey

[nandey]

Protected Sub RegisterButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles RegisterButton.Click

        If FirstNameText.Text = "" Or LastNameText.Text = "" Or EmailAddressText.Text = "" Or GenderList.SelectedIndex = False Then
            Error1Label.Text = "Please fill in all the fields above."
        End If

        Dim connectionstring As String = WebConfigurationManager.ConnectionStrings("Register").ConnectionString

        Dim insertcommand As String

        insertcommand = "insert into Medpoint_Register(FirstName, LastName, Email, Gender) values (@FirstName, @LastName, @Email, @Gender)"

        Dim reg_connection As New SqlConnection(connectionstring)
        Dim reg_command As New SqlCommand(insertcommand, reg_connection)
        reg_command.CommandType = CommandType.Text
        reg_command.Parameters.AddWithValue("@FirstName", SqlDbType.VarChar).Value = FirstNameText.Text
        reg_command.Parameters.AddWithValue("@LastName", SqlDbType.VarChar).Value = LastNameText.Text
        reg_command.Parameters.AddWithValue("@Email", SqlDbType.VarChar).Value = EmailAddressText.Text
        reg_command.Parameters.AddWithValue("@Gender", SqlDbType.Int).Value = GenderList.SelectedIndex


        Dim added As Integer = 0
        Try
            reg_connection.Open()
            added = reg_command.ExecuteNonQuery()
            If added > 0 Then
                Error1Label.Text = added.ToString() & "Record Added"
            Else
                Error1Label.Text = added.ToString() & "Not Record Addrf"
            End If
        Catch err As SqlException
            Error1Label.Text = "Problem in inserting the data. Please try again"
        Finally
            reg_connection.Close()

        End Try
        '  reg_connection.Close()
        Response.Redirect("RegistrationRedirect.aspx")
    End Sub