[ASP.Net] Insert into gridview
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
Code:
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
Re: [ASP.Net] Insert into gridview
Code:
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..
Re: [ASP.Net] Insert into gridview
Yes but ow do I insert???
Re: [ASP.Net] Insert into gridview
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:
Code:
'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:
Code:
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:
Code:
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
Re: [ASP.Net] Insert into gridview
I modified my codes based on your example. I get no errors but the inserted fields do now show up in the gridview2
Code:
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
Re: [ASP.Net] Insert into gridview
can we see the markup code you've written?
Re: [ASP.Net] Insert into gridview
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
Code:
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
Re: [ASP.Net] Insert into gridview
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.
