INSERT INTo Command

Hi,

I am working with asp.net and VB.Net;

What am I doing wrong please?

Code:

---

Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click


        Dim strConn As String
        strConn = ConfigurationManager.ConnectionStrings("CCMSConnectionString").ConnectionString

        Dim conn As New SqlConnection(strConn)

        conn.Open()

        Dim strSql As String

        strSql = "INSERT INTO dbo.TblChangeControlDet (RequestBy, CreatedBy, ChangeType, TechnicalPerson, Req, BusImp,ChangeRaisedDate, PeerRvw) " & _
                                            "VALUES (@RequestBy, @CreatedBy, @ChangeType, @TechnicalPerson, @Req, @BusImp,@ChangeRaisedDate, @PeerRvw )"

        Dim cmd As New SqlCommand(strSql, conn)

        cmd.Parameters.AddWithValue("@RequestBy", Me.cmbchngreq.Text)
        cmd.Parameters.AddWithValue("@CreatedBy", Me.cmbchngcrt.Text)
        cmd.Parameters.AddWithValue("@ChangeType", Me.cmbchngtyp.Text)
        cmd.Parameters.AddWithValue("@TechnicalPerson", Me.cmbTechname.Text)
        cmd.Parameters.AddWithValue("@Req", Me.txtreq.Text)
        cmd.Parameters.AddWithValue("@BusImp", Me.txtbusimp.Text)
        cmd.Parameters.AddWithValue("@ChangeRaisedDate", Me.txtdate.Text)
        cmd.Parameters.AddWithValue("@PeerRvw", Me.cmbpeername.Text)

        conn.Close()

    End Sub

---

Database is not updated even if data in inserted

Many many thanks

Quote:

---

What am I doing wrong please?

---

Asking non-questions for a start. What's the actual problem? Are you getting errors?

Are you getting errors, or just getting nothing?

No Errors - but no data is added to the database..
Whe th ebutton is CLICKED..

Probably because you never actually execute the command. You're missing a cmd.ExecuteNonQuery

where should I put it please

cmd.Parameters.AddWithValue("@PeerRvw", Me.cmbpeername.Text)

cmd.ExecuteNonQuery

conn.Close()

Thnks; Ok this inserts data into the database i.e., the connection is Fine.

Code:

---

  Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click


        Dim strConn As String

        strConn = ConfigurationManager.ConnectionStrings("CCMSConnectionString").ConnectionString

        Dim conn As New SqlConnection(strConn)

        conn.Open()

        Dim strSql As String

        strSql = "INSERT INTO dbo.TblChangeControlDet (RequestBy, CreatedBy, ChangeType, TechnicalPerson, Req, BusImp,ChangeRaisedDate, PeerRvw) VALUES (@RequestBy, @CreatedBy, @ChangeType, @TechnicalPerson, @Req, @BusImp,@ChangeRaisedDate, @PeerRvw )"

        Dim cmd As New SqlCommand(strSql, conn)

        With cmd.Parameters
            .AddWithValue("@RequestBy", Me.cmbchngreq.Text)
            .AddWithValue("@CreatedBy", Me.cmbchngcrt.Text)
            .AddWithValue("@ChangeType", Me.cmbchngtyp.Text)
            .AddWithValue("@TechnicalPerson", Me.cmbTechname.Text)
            .AddWithValue("@Req", Me.txtreq.Text)
            .AddWithValue("@BusImp", Me.txtbusimp.Text)
            .AddWithValue("@ChangeRaisedDate", Me.txtdate.Text)
            .AddWithValue("@PeerRvw", Me.cmbpeername.Text)
        End With

        cmd.ExecuteNonQuery()

        conn.Close()

    End Sub

---

However, the data is incorrect..

RequestBy = -1 (Expected and item selected which are usually names e.g., John Smith etc)
CreatedBy = -1 (Expected and item selected which are usually names e.g., John Smith etc)
ChangeType = -1 (Expected and item selected which are usually natypes e.g websites)
TechnicalPerson = -1 (Expected and item selected which are usually names e.g., John Smith etc)
Req = Empty (eventhough I typed data before clicking the button)
BusImp = Empty (eventhough I typed data before clicking the button)
ChangeRaisedDate = 18/10/12 (this was the Only correct data)
PeerRvw = -1

Why is it behaving like so.

Note:

My first field is set as Please Select set in asp as

Code:

---

  <asp:ListItem Value="-1">Please Select</asp:ListItem>

---

Please help me.

Secondly when I click submit button it refreshes all the fields, why? Nothing in the click event states so...

Many thanks

Assuming that this is a combobox ...

Me.cmbchngreq.Text

..... you should be using .SelectedItem.ToString not .Text

As for the empty values, are the controls actually on the same form as this code? If not, then it can't be Me.!

Ok Changed to:

Code:

---

With cmd.Parameters
            .AddWithValue("@RequestBy", Me.cmbchngreq.SelectedItem.ToString)
            .AddWithValue("@CreatedBy", Me.cmbchngcrt.SelectedItem.ToString)
            .AddWithValue("@ChangeType", Me.cmbchngtyp.SelectedItem.ToString)
            .AddWithValue("@TechnicalPerson", Me.cmbTechname.SelectedItem.ToString)
            .AddWithValue("@Req", txtreq.Text)
            .AddWithValue("@BusImp", txtbusimp.Text)
            .AddWithValue("@ChangeRaisedDate", Me.txtdate.Text)
            .AddWithValue("@PeerRvw", Me.cmbpeername.SelectedItem.ToString)
        End With

---

RequestBy = Please Select
CreatedBy = Please Select
ChangeType = Please Select
Technical Person = Please Select
Req = Empty
BusImp = Empty
PeerRvw = Please Select

Even, when I select a different value on the combo boxes and click the button. It still takes the value Please Select and for the emoty textboxes I cant even explain

Any ideas..

Many thanks

It's going to be in the way that you're linking the controls from ASP which I guess means looking at your design as well as the code. Can you zip your project and attach it here? I can't promise that I'll get to look at it immediately but somewhen between now and the end of the world .....

I suspect that your button is autoposting... so it's causing a refresh and when the code is running, the data hasn't been set in the form elements yet, so you're getting the default values.

I'd also suggest this get moved to the ASP.NET forums, as it's a problem with the ASP.NET setup and not about the database (at least not any more). I'll ask as mod to move it.

-tg

Moved

I'm close to TG opinion but i suspect your dropdownlist is autoposting.Show your markup.