Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting Table from SQL Server using Datagrid

Status
Not open for further replies.

toddw607

Programmer
Joined
Apr 2, 2007
Messages
55
Location
US
Hi Everyone!

I have a ASP.NET webform that brings data in from SQL Server 2000 and displays it on a page. I want to update the webpage while in IE using the following code:



<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<script language="VB" runat="server">
Dim MyConnection As SqlConnection

Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)

MyConnection = New SqlConnection("Data Source=SQLSERVER;Initial Catalog=upsizedCandidate;Integrated Security=True")

If Not (IsPostBack) Then
BindGrid()
End If

End Sub

Sub MyDataGrid_Edit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub

Sub MyDataGrid_Cancel(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub

Sub MyDataGrid_Update(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)

Dim DS As DataSet
Dim MyCommand As SqlCommand

Dim UpdateCmd As String = "UPDATE ToddsTable1 SET myNewCol1 = @myNewCol1, myNewCol2 = @myNewCol2"

MyCommand = New SqlCommand(UpdateCmd, MyConnection)

MyCommand.Parameters.Add(New SqlParameter("@myNewCol1", SqlDbType.NVarChar, 40))
MyCommand.Parameters.Add(New SqlParameter("@myNewCol2", SqlDbType.NVarChar, 20))

MyCommand.Parameters("@Id").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))

Dim Cols As String() = {"@myNewCol1", "@myNewCol2"}

Dim NumCols As Integer = E.Item.Cells.Count

Dim I As Integer
For I = 2 To NumCols - 2 'skip first, second and last column

Dim CurrentTextBox As TextBox
CurrentTextBox = E.Item.Cells(I).Controls(0)
Dim ColValue As String = CurrentTextBox.Text

MyCommand.Parameters(Cols(I - 1)).Value = Server.HtmlEncode(ColValue)
Next

MyCommand.Connection.Open()

Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Updated</b><br>" & UpdateCmd.ToString()
MyDataGrid.EditItemIndex = -1
Catch Exp As SQLException
If Exp.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not update record, please ensure the fields are correctly filled out"
End If
Message.Style("color") = "red"
End Try

MyCommand.Connection.Close()

BindGrid()
End Sub


Sub MyDataGrid_ItemDataBound(ByVal Sender As Object, ByVal E As DataGridItemEventArgs)
If (E.Item.ItemType = ListItemType.EditItem) Then
Dim i As Integer
For i = 0 To E.Item.Controls.Count - 1
If (E.Item.Controls(i).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.TextBox") Then
Dim tb As TextBox
tb = E.Item.Controls(i).Controls(0)
tb.Text = Server.HtmlDecode(tb.Text)
End If
Next
End If
End Sub

Sub BindGrid()

Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = New SqlDataAdapter("select * from ToddsTable1", MyConnection)

DS = New DataSet()
MyCommand.Fill(DS, "ToddsTable1")


MyDataGrid.DataSource = DS.Tables("ToddsTable1").DefaultView
MyDataGrid.DataBind()

End Sub

</script>


<body style="font: 10pt verdana">

<form id="Form1" runat="server">

<h3><font face="Verdana">Updating ToddsTable1</font></h3>

<span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/><p>


<ASP:DataGrid id="MyDataGrid" runat="server"
Width="700"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
DataKeyField="myNewCol1"
OnItemDataBound="MyDataGrid_ItemDataBound"
EnableViewState="false"
>

<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false"/>
</Columns>

</ASP:DataGrid>

</form>
</body>
</html>



The table is only 2 columns just to get something working but when I click the edit link my title still shows up but the table disappears. Any thoughts on what I could do differently? Thanks!
 
try changing this to

Sub MyDataGrid_Edit(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = E.Item.ItemIndex
BindGrid()

End Sub


 
Thanks for your RE: dvannoy. I tried to do what you said and it comes out the same. I've also tried placing the IsPostBack in different placing. When I removed it from the Page_load function the edit box came up and I was able to edit but when I clicked the "update" link the info went back to the original data. Any other thoughts?
 
You'll have to trace through your code to see what the update statment is and check the syntax as well as if you are actually updating any data based on your criteria.
 
I looked at my data on the server and nothing is happening yet..I'll trace through and see where it is. THanks
 
You have to check the actual SQL statment.. not the data yet.. and see if an update will actually be performed..

Get your update statment and run it in Query Analyzer and see if any rows acutally update.
 
Hi jbenson,
Are you saying that in order to make the SQL Server table update I need a stored procedure in the database? I can't just accomplish this using strictly ASP.NET? Thanks
 
No, I don't think that is what he meant but using Stored Procedures is the recommended approach.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.
 
I've changed my code to work more efficiently. Here is my new code:

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<script runat="server">
Public Sub Page_Load(Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
BindData()
End If
End Sub

Public Sub DataGrid_Edit(ByVal Source As Object, _
ByVal E As DataGridCommandEventArgs)
myInfo.EditItemIndex = E.Item.ItemIndex
BindData()
End Sub

Public Sub DataGrid_Cancel(ByVal Source As Object, _
ByVal E As DataGridCommandEventArgs)
myInfo.EditItemIndex = -1
BindData()
End Sub

Public Sub DataGrid_Update(ByVal Source As Object, _
ByVal E As DataGridCommandEventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim txtmyNewCol1 As TextBox = E.Item.Cells(2).Controls(0)
Dim txtmyNewCol2 As TextBox = E.Item.Cells(3).Controls(0)
Dim strUpdateStmt As String

strUpdateStmt = "UPDATE ToddsTable1 SET " & _
"myNewCol1 = '" & txtmyNewCol1.Text & "', " & _
"myNewcol2 = '" & txtmyNewCol2.Text & "' "

myConnection = New SqlConnection( _
"server=SQLSERVER;database=upsizedCandidate;Integrated Security=True;")
myCommand = New SqlCommand(strUpdateStmt, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()

myInfo.EditItemIndex = -1
BindData()
End Sub

Public Sub BindData()
Dim myDataSet As New DataSet
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter( _
"SELECT * FROM ToddsTable1", _
"server=SQLSERVER;database=upsizedCandidate;Integrated Security=True")
mySqlDataAdapter.Fill(myDataSet, "ToddsTable1")
myInfo.DataSource = myDataSet.Tables("ToddsTable1")
myInfo.DataBind()
End Sub
</script>
</head>
<body>
<form id="Form1" runat="server" method="post">
<H3>Editing ToddsTable1 from SQL Server</H3>

<asp:DataGrid id="myInfo" runat="server"
AutoGenerateColumns="False"
OnEditCommand="DataGrid_Edit" Width="702px"
OnCancelCommand="DataGrid_Cancel"
OnUpdateCommand="DataGrid_Update">

<Columns>
<asp:EditCommandColumn
CancelText="Cancel"
EditText="Edit"
UpdateText="Update" />
<asp:BoundColumn
DataField="myNewCol1"
HeaderText="myNewCol1" />
<asp:BoundColumn
DataField="myNewCol2"
HeaderText="myNewCol2" />
</Columns>
</asp:DataGrid>


</form>
</body>
</html>

The table is being stored, the edit and cancel buttons work fine but when I edit the values in the table, it gives me the following error message:


Server Error in '/WebNewFileItems' Application.
Specified argument was out of the range of valid values.
Parameter name: index
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Parameter name: index

I'm not sure which index it is talking about. I attempted to comment out the index statement in the Update function but it came out the same. Any thoughts?
 
ok, in the following statements:
Dim txtmyNewCol1 As TextBox = E.Item.Cells(2).Controls(0)
Dim txtmyNewCol2 As TextBox = E.Item.Cells(3).Controls(0)

I switched the 2 to 1 and the 3 to 2 but now when I click "update" it changes the whole column to the value I place in the first cell, which is the only cell I wanted to ypdate, not all the cells in the column. hmmmm, still trying.
 
One of the reasons Stored Procedures (with parameters being used to pass the data) was recommended is that it will protect you against SQL Injection. Before you go any further I'd recommend you fix this first as at the moment a user could potentially drop your entire database!


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.
 
I am only attempting to input a string through ASP.NET from a webform displayed through Internet Explorer. The user would have to go through this just to add data into a table. For right now I'm just attempting to make the index input into the .aspx form only one cell at a time. I have the index for each column specified but I think I need to specify the index down the cell instead of the column as a whole, ie:only one cell at a time. Trying to think of a good way to do this. Thank you for your responses.
 
I see what you're saying now. I need to do some more research on my and attempt to fix it. Thanks for the advice!
 
Hi Everyone,
I just wanted to let anyone else know that when I placed a new column in my table to specify a unique int value and placed a where statement in the above SQL statement then everything worked fine. Also as stated earlier, I went ahead and placed some constrictions in my code to alleviate the SQL injection problem as given in the above link to Wikipedia. Thanks everyone for your help and I hope someone else will get something out of this post.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top