Inserting data into SQL Server database using VB.Net

Question

I am currently using HDI Membership provider and the design looks as shown below:

enter image description here

Now I am trying to create a new user and insert those values into the database as shown below:

Try
   Dim connectionString As String = "Data Source=.\sqlexpress;Initial Catalog=HDIMembershipProvider;Integrated Security=True"
   Using cn As New SqlConnection(connectionString)
      cn.Open()
      Dim cmd As New SqlCommand()
      cmd.CommandText = "INSERT INTO Users VALUES(@Username,@Password,@Email,@PasswordQuestion,@PasswordAnswer)"

      Dim param1 As New SqlParameter()
      param1.ParameterName = "@Username"
      param1.Value = txtUsername.Text.Trim()
      cmd.Parameters.Add(param1)

      Dim param2 As New SqlParameter()
      param2.ParameterName = "@Password"
      param2.Value = txtPassword.Text.Trim()
      cmd.Parameters.Add(param2)

      Dim param3 As New SqlParameter()
      param3.ParameterName = "@Email"
      param3.Value = txtEmail.Text.Trim()
      cmd.Parameters.Add(param3)

      Dim param4 As New SqlParameter()
      param4.ParameterName = "@PasswordQuestion"
      param4.Value = txtSecurityQuestion.Text.Trim()
      cmd.Parameters.Add(param4)

      Dim param5 As New SqlParameter()
      param5.ParameterName = "@PasswordAnswer"
      param5.Value = txtSecurityAnswer.Text.Trim()
      cmd.Parameters.Add(param5)

      cmd.Connection = cn
      cmd.ExecuteNonQuery()
      cn.Close()
   End Using
   Successlbl.show
   Successlbl.show.Text = "Regisration Success."
Catch
   Errolbl.Show()
   Errolbl.Text = "Your account was not created.Please try again."
End Try

Now the problem is the data is not inserting to the database. I would like to know If anyone can point me where I'm going wrong?

You are swallowing any exceptions - you need to log the exception or at least find out what it is before any help can be given. Don't do a Catch without at least figuring out the exception. — Oded
– Oded, Commented Jan 1, 2012 at 9:52
@Oded-I'm getting this exception:Column name or number of supplied values does not match table definition. — coder
– coder, Commented Jan 1, 2012 at 9:54

Oded · Accepted Answer · 2012-01-01 09:56:38Z

2

Your insert statement is incorrect - since you are not specifying any field names you should be supplying values for all columns.

The fix is to supply the names of the columns you are insert into.

The screenshot also shows that there is a required ApplicationName column, so unless it has a DEFAULT defined, you will need to supply that as well.

Assuming you have a DEFAULT defined on ApplicationName:

cmd.CommandText = "INSERT INTO Users ( Username, Password, Email, PasswordQuestion, PasswordAnswer) VALUES(@Username,@Password,@Email,@PasswordQuestion,@PasswordAnswer)"

answered Jan 1, 2012 at 9:56

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

coder Over a year ago

@Oded-Now I have a problem that you only can solve this so now how do I mention the Application path as In web.config we have a membership tag within that we an mention Application name but in app.config how do I mention that?As I have asked now in this question stackoverflow.com/questions/8692694/… you please help me out with this.

Oded Over a year ago

@Kiran - First, off, I am not the only one that can help you. And it looks like you already got answers on it.

Collectives™ on Stack Overflow

Inserting data into SQL Server database using VB.Net

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related