DB Insert with SQL syntax

Question

User1883103525 posted

Hello. 

I've been trying to make this code work but I keep on getting all sorts of errors. 

Basically, I'm looking to take the values from text boxes - 

<form action='a_Tickets.aspx' method='post'>

<td> <asp:TextBox width="100%" ID="TicketID" > </asp:TextBox></td> 

<td> <asp:TextBox width="100%" ID="OrderID" > </asp:TextBox></td>

<td> <asp:TextBox width="100%" ID="FirstName" > </asp:TextBox></td>

<td> <asp:TextBox width="100%" ID="WorkDetails" > </asp:TextBox></td>

<td> <asp:TextBox width="100%" ID="CircuitInternalID" > </asp:TextBox></td>

<td> <asp:Button width="100%" ID="btnSubmit2" Text="Submit" /></td>

<form action='a_Tickets.aspx' method='post'>

<td> <asp:TextBox width="100%" ID="TicketID" > </asp:TextBox></td> 

<td> <asp:TextBox width="100%" ID="OrderID" > </asp:TextBox></td>

<td> <asp:TextBox width="100%" ID="FirstName" > </asp:TextBox></td>

<td> <asp:TextBox width="100%" ID="WorkDetails" > </asp:TextBox></td>

<td> <asp:TextBox width="100%" ID="CircuitInternalID" > </asp:TextBox></td>

<td> <asp:Button width="100%" ID="btnInsertTicket" Text="Submit" /></td>

and use an SQL statement to insert them into my database. 

<script language="vbscript" runat="server">
    sub btnInsertTicket_Click
    
    dim dbconn,sql2,dbcomm2,dbread2
    dim myPath As String
    
    myPath = Server.MapPath("DATABASE.accdb")
    
    dbconn=New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; data source=" & 

myPath & ";")
    dbconn.Open()
    sql2="INSERT INTO Tickets (ID, OrderID, WorkDetails, CircuitInternalID) VALUES ("' & 

TicketID.Text & '","' & OrderID.Text & '","' & WorkDetails & '", "' & CircuitInternalID & 

'")" 
    
    btnInsertTicket("Tickets")=sql2.GetRows
    sql2.Close
    
    dbread2.Close()
    dbconn.Close()

    end sub

</script>

I'm pretty sure thats not the way to do it, but I have no more ideas how to make it work. 

I'm very new to asp, please help! 

Answer 1

User1324895001 posted

 If you are using access database, add a accessdatasource to the page.. then configure it to use your form fields.

This will show you how to configure the control, but its pretty easy, drop the control on the page, select to configure and select your db file. Then it walks you thru the rest of the setup.

http://msdn.microsoft.com/en-us/library/ms178273(v=VS.80).aspx

 

 

Answer 2

User-1590642642 posted

 a couple of things wrong:

WorkDetails.text  & CircuitInternalID.text - you need to insert the .text properties.

you need one more object, the SQLCommand.   Set SQL2 to be its command property, dbConn as its Connection property, and then fire its ExecuteNonQuery() method.

I assume that's what you intended to use dbcomm2 for?

Answer 3

User-632894676 posted

Hey man, give this a shot!

    Public Sub btnInsertTicket_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim dbPath As String = Server.MapPath("DATABASE.accdb")
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; data source=" & dbPath
        Dim insertSQL = "INSERT INTO Tickets (ID, OrderID, WorkDetails, CircuitInternalID) VALUES (?,?,?,?)"
        
        Using db As New OleDbConnection(connectionString)
            Using cmd As OleDbCommand = db.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = insertSQL
                cmd.Parameters.AddWithValue("ID", Integer.Parse(TicketID.Text))
                cmd.Parameters.AddWithValue("OrderID", Integer.Parse(OrderID.Text))
                cmd.Parameters.AddWithValue("WorkDetails", WorkDetails.Text)
                cmd.Parameters.AddWithValue("CircuitInternalID", Integer.Parse(CircuitInternalID.Text))
                
                db.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
            
    End Sub

 

Answer 4

User1883103525 posted

Thank you, the guide helped.