[ASP.Net] Insert into gridview

[Shodan]

Hi all,
I created a connection at the page load event and then dragged a gridview on the page and when I ran it, it worked just fine. Now I want to take the values of textbox1,2, & 3 and a checkbox and insert into the gridview or access db. Can you tell me what I'm doing wrong with the following codes

Partial Class Page1
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim xconn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\HP_Owner\My Documents\Visual Studio 2005\MyFinalWebSite/db1.mdb")
        ' creates a connection, althought I don't understand why "data.oledb" is needed        Dim xcmd As New Data.OleDb.OleDbCommand("Select * from Table1", xconn)
        Dim xda As New Data.OleDb.OleDbDataAdapter(xcmd)

        Dim ds As New Data.DataSet

        xconn.Open()

        xda.Fill(ds, "Table1")

        GridView1.DataSource = ds.Tables("Table1")
        GridView1.DataBind()
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xconn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\HP_Owner\My Documents\Visual Studio 2005\MyFinalWebSite/db1.mdb")
        ' Tries to insert into Table1 my access table
        Dim xcmd As New Data.OleDb.OleDbCommand("INSERT INTO [Table1] Set [LName] = textbox1.text,[FName] = textbox2.text, [Student] = checkbox1.text, [Known] = textbox3.text", xconn)
        Dim xda As New Data.OleDb.OleDbDataAdapter(xcmd)

        Dim ds As New Data.DataSet

        xconn.Open()

        xda.Fill(ds, "Table1")


        GridView1.DataSource = ds.Tables("Table1")
        GridView1.DataBind()
    End Sub
End Class

[ZeBula8]

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

if not IsPostBack() then
        Dim xconn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\HP_Owner\My Documents\Visual Studio 2005\MyFinalWebSite/db1.mdb")
        ' creates a connection, althought I don't understand why "data.oledb" is needed        Dim xcmd As New Data.OleDb.OleDbCommand("Select * from Table1", xconn)
        Dim xda As New Data.OleDb.OleDbDataAdapter(xcmd)

        Dim ds As New Data.DataSet

        xconn.Open()

        xda.Fill(ds, "Table1")

        GridView1.DataSource = ds.Tables("Table1")
        GridView1.DataBind()
end if
    End Sub

if you're using Access, you can also use the AccessDataSource class instead of the OleDbDataAdapter... it's easier to use and is really a wrapper for the Ole..

[Shodan]

Yes but ow do I insert???

[HoraShadow]

Hello Shodan,

You have a syntax error in your query and the concatenation of values its not done properly. Also, you are trying to populate a DataSet instead of execute the INSERT query.

It should look like this:

'I'm taking the query out for visual ease.
Dim SQL as String = String.Empty

SQL = "INSERT INTO [Table1] Set [LName] = '" & textbox1.text & "',[FName] = '" & textbox2.text & "', [Student] = '" & checkbox1.text & "', [Known] = '" & textbox3.text & "' "

Dim xcmd As New Data.OleDb.OleDbCommand(SQL, xconn)
xcmd.ExecuteNonQuery() 'This command will execute the INSERT query.

Note 1: Concatenation
To concatenate a value that's referenced to an object, in this case a property of a control, you have to exit the string with double quotes ( " ) and concatenate the desired previously mentioned value with either + or & .

ie:

Dim var1 as String = "Hello World!"

Dim var2 as String = "The value is: " & var1

Response.Write(var2)

Note 2: SQL value types syntax
Different values have different syntax in SQL. For example, if the parameter of a field is a string, you have to enclose that parameter in single quotes ( ' ).
If the value would be a date, you would hvae to enclose it with ( # ).
If the value is numeric, there's no need of enclosure.

ie:

Dim SQL as String = "INSERT INTO table1 (FirstName, LastName) VALUES ('Thomas', 'Anderson') "

For the future, one of the many ways of finding out why a query is not working, is to place a break point on the line that concatenates the query, run the web app, once it has to run that line, the break point will pause the execution, and you can move forward with F11 in Visual Studio GUI. After the value is concatenated, go to the immediate window and query the result of the variable, for example ?SQL would output the value stored inside the variable SQL. With that value, go to Access and run the query directly against the Access database, which will give you most of the time an error legend that's easy to understand, or at least more clues of what went wrong.

Note 3: ADO.NET
With ADO.NET you have the SQL Command object. That object has pretty much 3 basic functionalities. To read from a database and to execute in a database.

INSERT is an execute query.
UPDATE is an execute query.
DELETE is an execute query.
SELECT is a read only query.

If it's an execute query, you have to use the ExecuteNonQuery() method of the SQL Command object.
If it's a read query, you can get the first value of the first row with ExecuteScalar(), or you can get the entire result with ExecuteReader(), which will return a DataReader. A data Reader is a Read Only, Forward Only object. The big pro the Data Reader has, it's that's very fast and straight forward. Alternatively, you can use a DataSet to retrieve values, like you were using in your code.

HTH,
HoraShadow

[Shodan]

I modified my codes based on your example. I get no errors but the inserted fields do now show up in the gridview2

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim iconn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\HP_Owner\My Documents\Visual Studio 2005\MyFinalWebSite/db1.mdb")
        Dim SQL As String = ""
        SQL = ("INSERT INTO [Table1] Set [LName] = '" & TextBox1.Text & "',[FName] = '" & TextBox2.Text & "', [Known] = '" & TextBox3.Text & "' ")

        Dim xcmd As New Data.OleDb.OleDbCommand(SQL, iconn)
        Dim ida As New Data.OleDb.OleDbDataAdapter(SQL, iconn)

        Dim ds As New Data.DataSet

        iconn.Open()

        ida.InsertCommand = xcmd


        GridView2.DataSource = ds.Tables("Table1")
        GridView2.DataBind()

    End Sub

[ZeBula8]

can we see the markup code you've written?

[Shodan]

I have a gridview2 which has FName, LName, Student & Known fieldnames. Student is a yes/no checkbox but all others are text.

I'm still having a problem with the SQL statement and I'm not quite sure if I need the "ExecuteNonQuery()" or where to place it in the code. I'm told that a checkbox's selectedvalue has to be evaluated first and I guess if true then place "true" in the text property of the checkbox?

Right now to make it really simple for me I'm just trying to insert textbox1, 2 and 3 into LName, FName and Known fields in the access DB that the gridview2 is tied to. My previous example got no errors but did not update the gridview....here's my latest version

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim iconn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\HP_Owner\My Documents\Visual Studio 2005\MyFinalWebSite/db1.mdb")
        Dim SQL As String = ""
        'SQL = ("INSERT INTO [Table1] Set [LName] = '" & TextBox1.Text & "',[FName] = '" & TextBox2.Text & "', [Known] = '" & TextBox3.Text & "' ")

        Dim xcmd As New Data.OleDb.OleDbCommand(SQL, iconn)

        Dim ida As New Data.OleDb.OleDbDataAdapter(SQL, iconn)

        Dim ds As New Data.DataSet

        iconn.Open()

        ida.InsertCommand = xcmd
        xcmd.ExecuteNonQuery()


        GridView2.DataSource = ds.Tables("Table1")
        GridView2.DataBind()


    End Sub

[mendhak]

You can insert into the database, good. Once you do that, retrieve data from the database into your dataset. Remember the DataAdapter.Fill() method? Use that to fill the dataset. Then clear the datasource of the GridView and assign it to the dataset. right now, you are creating a new dataset, which obviously contains nothing.