locked
help me please why error in Red RRS feed

  • Question

  • User-1778279641 posted

    Imports System.Data.SqlClient

    Public Class delicious
    Inherits System.Web.UI.Page
    Dim conn As New SqlConnection(" Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\delicious.mdf;Integrated Security=True")
    Dim dd As New SqlCommand

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
    Dim insertQuery As String = "insert inti delicious(Id,name,flavour,cone_size)" &
    "values('" & txtName.Text & "','" & txtFlavour.Text & "','" & txtSize.Text & "')"
    Dim comm As New SqlCommand(insertQuery, conn)
    executeMyQuery(comm, "Done Insert")
    views()
    End Sub

    Sub executeMyQuery(comm As SqlCommand, msg As String)
    conn.Open()
    If comm.ExecuteNonQuery = 1 Then
    MsgBox(msg)
    Else
    MsgBox("Query not execute")
    End If
    conn.Close()
    End Sub

    Sub views()
    Dim viewAll As String = "Select * from delicious"
    Dim comm As New SqlCommand(viewAll, conn)
    Dim adp As New SqlDataAdapter(comm)
    Dim dt As New DataTable
    adp.Fill(dt)
    Dim ds As New DataSet
    GridView1.DataBind()
    End Sub
    End Class

    Saturday, June 2, 2018 8:33 AM

All replies

  • User-1716253493 posted

    Recheck query, there is no "INSERT INTI" command in sql

    Saturday, June 2, 2018 2:54 PM
  • User-1778279641 posted
    I've fixed it but it's still wrong
    Saturday, June 2, 2018 2:58 PM
  • User-1716253493 posted

    if id is autonumber, id should not included in insert query.

    You have try to insert 4 columns value, but you only provide 3 values

    Saturday, June 2, 2018 3:21 PM
  • User283571144 posted

    Hi norakliqa,

    As the oned_gk says, if the delicious table's id is the identity(auto index).

    There is no need to add the id as the parameter name in the sql query.

    But, I don't suggest you directly combine the sql query with the '&'.

    Like this:

            comm.Parameters.AddWithValue("@name", txtName.Text)
    

    This will cause the sercurity issue.

    I suggesst you could consider using sqlparameter with passing the value to sql database.

    Besides, I found you doesn't add the girdview1's datasource in the view method.

    I suggest you could set it as below:

        Sub views()
            Dim viewAll As String = "Select * from delicious"
            Dim comm As New SqlCommand(viewAll, conn)
            Dim adp As New SqlDataAdapter(comm)
            Dim dt As New DataTable
            adp.Fill(dt)
            GridView1.DataSource = dt
            GridView1.DataBind()
        End Sub

    More details, you could refer to below codes:

    ASPX:

    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="SqlQueryVB.aspx.vb" Inherits="VBWebform.SqlQueryVB" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                <asp:TextBox ID="txtFlavour" runat="server"></asp:TextBox>
                <asp:TextBox ID="txtSize" runat="server"></asp:TextBox>
                <asp:Button ID="btnInsert" runat="server" Text="Button" OnClick="btnInsert_Click" />
            <br />
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            </div>
        </form>
    </body>
    </html>
    

    Code-behind

    Imports System.Data.SqlClient
    
    Public Class SqlQueryVB
        Inherits System.Web.UI.Page
    
        Dim conn As New SqlConnection("Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-CoreWithIdentity-CA8318F3-E2EA-4BEE-BE25-F0678BBA6D81;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False")
        Dim dd As New SqlCommand
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
        End Sub
    
        Protected Sub btnInsert_Click(sender As Object, e As EventArgs) Handles btnInsert.Click
            'Dim insertQuery As String = "insert into delicious(name,flavour,cone_size)" &
            '"values('" & txtName.Text & "','" & txtFlavour.Text & "','" & txtSize.Text & "')"
    
            Dim insertQuery As String = "insert into delicious(name,flavour,cone_size)" &
            "values(@name,@flavour,@cone_size)"
            Dim comm As New SqlCommand(insertQuery, conn)
            comm.Parameters.AddWithValue("@name", txtName.Text)
            comm.Parameters.AddWithValue("@flavour", txtName.Text)
            comm.Parameters.AddWithValue("@cone_size", txtName.Text)
            executeMyQuery(comm, "Done Insert")
            views()
        End Sub
    
        Sub executeMyQuery(comm As SqlCommand, msg As String)
            conn.Open()
            If comm.ExecuteNonQuery = 1 Then
                MsgBox(msg)
            Else
                MsgBox("Query not execute")
            End If
            conn.Close()
        End Sub
    
        Sub views()
            Dim viewAll As String = "Select * from delicious"
            Dim comm As New SqlCommand(viewAll, conn)
            Dim adp As New SqlDataAdapter(comm)
            Dim dt As New DataTable
            adp.Fill(dt)
            GridView1.DataSource = dt
            GridView1.DataBind()
        End Sub
    End Class

    Result:

    Best Regards,

    Brando

    Thursday, June 14, 2018 8:04 AM