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!

Register Log in
  • 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

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!
 
Sort by date Sort by votes
try changing this to

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

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

End Sub


 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
I looked at my data on the server and nothing is happening yet..I'll trace through and see where it is. THanks
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Makumbi256
  • Locked
  • Question Question
How to make days vertical and grid view editable using asp.net vb
Replies
0
Views
1K
Makumbi256
Makumbi256
G12Consult
  • Locked
  • Question Question
Javascript from Content Pages
Replies
0
Views
985
G12Consult
G12Consult
G12Consult
  • Locked
  • Question Question
Retrieve data from JSON
Replies
1
Views
1K
gmmastros
gmmastros
G12Consult
  • Locked
  • Question Question
Importing Excel to SQL Server using EPPlus
Replies
3
Views
852
jbenson001
jbenson001
engsalah2018
  • Locked
  • Question Question
How to get label text from table reference on database based on TableName and FieldName
Replies
2
Views
1K
jbenson001
jbenson001

Part and Inventory Search

Sponsor

Back
Top