homeshopper
asked on
How can I edit/update data in any table in a given sql database?
I have a dropdown that selects a sql database.
a second dropdown that selects a table in a given database.
The code I have so far only works if I select table 'tblItems' in database 'Cams2'
If I select a different table it gives an error.
How can I edit/update data in any table in a given sql database?
The error is caused by the following code:
line 555 Me.DGrid.DataKeyField = "ItemID"
line 571 m_Key = DGrid.DataKeys(e.Item.Item Index)
line 575 Dim SQL As String = "Update " & strTable & " Set Item='" & tbox.Text & "' Where ItemID=" & m_Key
How can I re-write the code to accept DataKeyField from different tables etc?
Thanks in advance for any help given, Ian.
for clarity below is the code so far:
Private Sub LoadData()
Try
Dim m_DBCon As New OleDbConnection()
Dim Cstring As String = HttpContext.Current.Sessio n("strConn ection")
If m_DBCon.State <> ConnectionState.Open Then
m_DBCon.ConnectionString = Cstring
m_DBCon.Open()
End If
Dim SQL As String
Dim strTable As String = HttpContext.Current.Sessio n("strTabl e")
SQL = "Select * From " & strTable
Dim DA As OleDb.OleDbDataAdapter
Dim DS As DataSet
DA = New OleDb.OleDbDataAdapter(SQL , m_DBCon)
DS = New DataSet()
DA.Fill(DS)
Me.DGrid.DataSource = DS
Me.DGrid.DataKeyField = "ItemID"
Me.DGrid.DataBind()
Catch ex As Exception
ErrorLabel.Text = "=1:Error:558:LoadData:" & "Session=" & HttpContext.Current.Sessio n("sqlCams Admin.Admi nUser") & "# " & "strConnection=" & HttpContext.Current.Sessio n("strConn ection") & "#<br/>" & ex.ToString()
End Try
End Sub
Private Sub DGrid_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls. DataGridCo mmandEvent Args) Handles DGrid.UpdateCommand
Response.Write("<div style='position:absolute;w idth:530px ;height:16 6px;left:9 px;top:87p x;font-siz e:xx-small ;overflow: auto;borde r:1px solid #722F90;' id='DIV17'>")
Try
Dim m_DBCon As New OleDbConnection()
Dim Cstring As String = HttpContext.Current.Sessio n("strConn ection")
If m_DBCon.State <> ConnectionState.Open Then
m_DBCon.ConnectionString = Cstring
m_DBCon.Open()
End If
Dim m_Key As Long
m_Key = DGrid.DataKeys(e.Item.Item Index)
Dim tbox As TextBox
tbox = e.Item.FindControl("txtIte m")
Dim strTable As String = HttpContext.Current.Sessio n("strTabl e")
Dim SQL As String = "Update " & strTable & " Set Item='" & tbox.Text & "' Where ItemID=" & m_Key
Response.Write("state:576: Update:" & m_DBCon.State() & "#<br/>")
Response.Write("str:577:" & Cstring & " " & strTable & "#<br/>")
Response.Write("SQL:578:" & SQL & "#<br/>")
Dim CMD As New OleDb.OleDbCommand(SQL, m_DBCon)
CMD.ExecuteNonQuery()
Me.DGrid.EditItemIndex = -1
LoadData()
Catch ex As Exception
ErrorLabel.Text = "=1:Error:584:Update:" & "Session=" & HttpContext.Current.Sessio n("sqlCams Admin.Admi nUser") & "# " & "strConnection=" & HttpContext.Current.Sessio n("strConn ection") & "#<br/>" & ex.ToString()
End Try
Response.Write("</div>")
End Sub
a second dropdown that selects a table in a given database.
The code I have so far only works if I select table 'tblItems' in database 'Cams2'
If I select a different table it gives an error.
How can I edit/update data in any table in a given sql database?
The error is caused by the following code:
line 555 Me.DGrid.DataKeyField = "ItemID"
line 571 m_Key = DGrid.DataKeys(e.Item.Item
line 575 Dim SQL As String = "Update " & strTable & " Set Item='" & tbox.Text & "' Where ItemID=" & m_Key
How can I re-write the code to accept DataKeyField from different tables etc?
Thanks in advance for any help given, Ian.
for clarity below is the code so far:
Private Sub LoadData()
Try
Dim m_DBCon As New OleDbConnection()
Dim Cstring As String = HttpContext.Current.Sessio
If m_DBCon.State <> ConnectionState.Open Then
m_DBCon.ConnectionString = Cstring
m_DBCon.Open()
End If
Dim SQL As String
Dim strTable As String = HttpContext.Current.Sessio
SQL = "Select * From " & strTable
Dim DA As OleDb.OleDbDataAdapter
Dim DS As DataSet
DA = New OleDb.OleDbDataAdapter(SQL
DS = New DataSet()
DA.Fill(DS)
Me.DGrid.DataSource = DS
Me.DGrid.DataKeyField = "ItemID"
Me.DGrid.DataBind()
Catch ex As Exception
ErrorLabel.Text = "=1:Error:558:LoadData:" & "Session=" & HttpContext.Current.Sessio
End Try
End Sub
Private Sub DGrid_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.
Response.Write("<div style='position:absolute;w
Try
Dim m_DBCon As New OleDbConnection()
Dim Cstring As String = HttpContext.Current.Sessio
If m_DBCon.State <> ConnectionState.Open Then
m_DBCon.ConnectionString = Cstring
m_DBCon.Open()
End If
Dim m_Key As Long
m_Key = DGrid.DataKeys(e.Item.Item
Dim tbox As TextBox
tbox = e.Item.FindControl("txtIte
Dim strTable As String = HttpContext.Current.Sessio
Dim SQL As String = "Update " & strTable & " Set Item='" & tbox.Text & "' Where ItemID=" & m_Key
Response.Write("state:576:
Response.Write("str:577:" & Cstring & " " & strTable & "#<br/>")
Response.Write("SQL:578:" & SQL & "#<br/>")
Dim CMD As New OleDb.OleDbCommand(SQL, m_DBCon)
CMD.ExecuteNonQuery()
Me.DGrid.EditItemIndex = -1
LoadData()
Catch ex As Exception
ErrorLabel.Text = "=1:Error:584:Update:" & "Session=" & HttpContext.Current.Sessio
End Try
Response.Write("</div>")
End Sub
And what's the error? I think the issue might be that you have to specify the database in your connection string (or use "User dbname") so you are unable to update other databases.
ASKER
Hi CodeCruiser,
If I selected say database 'Cams2' and table 'tblItems' no error.
if I selected a different table say 'ProductCategories' error as follows:
label:=1:Error:558:LoadDat a:Session= # strConnection=Provider=SQL OLEDB;Data Source=fuertemon4;User ID=sa;Password=sasa; Initial Catalog=Cams2;#
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ItemID'. at System.Web.UI.DataBinder.G etProperty Value(Obje ct container, String propName) at System.Web.UI.WebControls. DataGrid.C reateContr olHierarch y(Boolean useDataSource) at System.Web.UI.WebControls. BaseDataLi st.OnDataB inding(Eve ntArgs e) at System.Web.UI.WebControls. BaseDataLi st.DataBin d() at scriptFrame_tempScript2.Lo adData() in C:\Projects\cams00CsVbDev\ sqlScriptV bData\scri ptFrame\te mpScript2. aspx.vb:li ne 556
If I selected say database 'Cams2' and table 'tblItems' no error.
if I selected a different table say 'ProductCategories' error as follows:
label:=1:Error:558:LoadDat
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ItemID'. at System.Web.UI.DataBinder.G
You have to change the datakeyname to the one which exists in the table.
ASKER
Thankyou for your comments.
Is it possible to edit any selected table from a given database?
(either) without using datakeynames.
(or) programmically change the datakeyname to a field name that existing in the given table.
Hence, original question: How can I edit/update data in any table in a given sql database?
Thanks in advance for any help given.
Is it possible to edit any selected table from a given database?
(either) without using datakeynames.
(or) programmically change the datakeyname to a field name that existing in the given table.
Hence, original question: How can I edit/update data in any table in a given sql database?
Thanks in advance for any help given.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry, got waylaid on another project.
getting back to the matter in hand now.
I am going to use another dropdown for Columns as you suggested,
this will be poulated from the dropdown of the list of tables.
Thankyou for the help, I'll let you know who I get on later.
getting back to the matter in hand now.
I am going to use another dropdown for Columns as you suggested,
this will be poulated from the dropdown of the list of tables.
Thankyou for the help, I'll let you know who I get on later.