INSERT INTo Command

[dr223]

Hi,

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

What am I doing wrong please?

 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

[dunfiddlin]

What am I doing wrong please?

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

[Shaggy Hiker]

Are you getting errors, or just getting nothing?

[dr223]

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

[dunfiddlin]

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

[dr223]

where should I put it please

[dunfiddlin]

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

cmd.ExecuteNonQuery

conn.Close()

[dr223]

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

   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

  <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

[dunfiddlin]

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.!

[dr223]

Ok Changed to:

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

[dunfiddlin]

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 .....

[techgnome]

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

[Hack]

Moved

[sapator]

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