I am using asp.net, VB and sql 2005 data base. there is a text box by which the PNUMB is searched and is displayed in text box below. when i try to update and press submit button the following error comes.
Index and length must refer to a location within the string.
Parameter name: length
my code is
Imports System.Data.SqlClient
Imports System.Data
Partial Class coledit
Inherits System.Web.UI.Page
Dim Str As String = ConfigurationManager.ConnectionStrings("MS16ConnectionString").ConnectionString
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim NO As String = persno.Text
Dim con As New SqlConnection
con.ConnectionString = ConfigurationManager.ConnectionStrings("MS16ConnectionString").ConnectionString
con.Open()
Dim qstr As String
qstr = "select * from COL_DATA where PNUMB='" + persno.Text + "'"
Dim sqladp As New SqlDataAdapter(qstr, con)
Dim dt As New DataSet
dt.Clear()
sqladp.Fill(dt, "COL_DATA")
notxt.Text = dt.Tables("COL_DATA").Rows(0).Item("PNUMB")
sufxtxt.Text = dt.Tables("COL_DATA").Rows(0).Item("SUFX")
ranktxt.Text = dt.Tables("COL_DATA").Rows(0).Item("RANK")
nametxt.Text = dt.Tables("COL_DATA").Rows(0).Item("Name")
dtsentxt.Text = dt.Tables("COL_DATA").Rows(0).Item("DTSEN")
unittxt.Text = dt.Tables("COL_DATA").Rows(0).Item("UNIT")
tostxt.Text = dt.Tables("COL_DATA").Rows(0).Item("TOS")
specitxt.Text = dt.Tables("COL_DATA").Rows(0).Item("SPECI")
remarktxt.Text = dt.Tables("COL_DATA").Rows(0).Item("REMARK")
End Sub
Private Function updateunit(ByVal unit As String, ByVal tos As String, ByVal speci As String, ByVal remark As Integer) As Integer
Dim con As New SqlConnection(Str)
Dim cmd As New SqlCommand("UPDATE_UNIT", con)
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@unit", unit)
If tostxt.Text = "" Then
cmd.Parameters.Add("@tos", SqlDbType.DateTime).Value = DBNull.Value
Else
cmd.Parameters.AddWithValue("@tos", tos)
End If
cmd.Parameters.AddWithValue("@speci", speci)
cmd.Parameters.AddWithValue("@remark", remark)
Dim status As Integer
Try
con.Open()
status = cmd.ExecuteNonQuery()
Return status
Catch ex As Exception
Response.Write(Err.Description)
Finally
con.Close()
End Try
End Function
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
If Page.IsPostBack = True Then
Dim unit As String = unittxt.Text
Dim tos As Date
If Not tostxt.Text = "" Then
tos = ChangeDateFormat(tostxt.Text)
End If
Dim speci As String = specitxt.Text
Dim remark As Integer = remarktxt.Text
updateunit(unit, tos, speci, remark)
End If
End Sub
Public Function FixNull(ByVal dbvalue) As String
If dbvalue Is DBNull.Value Then
Return ""
Else
Return dbvalue.ToString
End If
End Function
Private Function ChangeDateFormat(ByVal tempDate As String) As Date
Dim dd As String = tempDate.Substring(0, 2)
Dim mm As String = tempDate.Substring(3, 2)
Dim yyyy As String = tempDate.Substring(6, 4)
Dim dt As String = mm + "/" + dd + "/" + yyyy
Dim rt_date As Date = CType(dt, Date)
Return rt_date
End Function
Private Function ChangeDateFormatIndia(ByVal tempDate As String) As String
Dim dd As String = tempDate.Substring(0, 2)
Dim mm As String = tempDate.Substring(3, 2)
If Len(dd) < 2 Then
dd = "0" + dd
End If
If Len(mm) < 2 Then
mm = "0" + mm
End If
Dim yyyy As String = tempDate.Substring(6, 4)
Dim dt As String = dd + "/" + mm + "/" + yyyy
' Dim rt_date As Date = CType(dt, Date)
Return dt
End Function
End Class
i changed the code a little. now there is no error but the data is not updated. my code is as under
Imports System.Data.SqlClient
Imports System.Data
Partial Class coledit
Inherits System.Web.UI.Page
Dim Str As String = ConfigurationManager.ConnectionStrings("MS16ConnectionString").ConnectionString
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim NO As String = persno.Text
Dim con As New SqlConnection
con.ConnectionString = ConfigurationManager.ConnectionStrings("MS16ConnectionString").ConnectionString
con.Open()
Dim qstr As String
qstr = "select * from COL_DATA where PNUMB='" + persno.Text + "'"
Dim sqladp As New SqlDataAdapter(qstr, con)
Dim dt As New DataSet
dt.Clear()
sqladp.Fill(dt, "COL_DATA")
notxt.Text = dt.Tables("COL_DATA").Rows(0).Item("PNUMB")
sufxtxt.Text = dt.Tables("COL_DATA").Rows(0).Item("SUFX")
ranktxt.Text = dt.Tables("COL_DATA").Rows(0).Item("RANK")
nametxt.Text = dt.Tables("COL_DATA").Rows(0).Item("Name")
dtsentxt.Text = dt.Tables("COL_DATA").Rows(0).Item("DTSEN")
unittxt.Text = dt.Tables("COL_DATA").Rows(0).Item("UNIT")
tostxt.Text = dt.Tables("COL_DATA").Rows(0).Item("TOS")
specitxt.Text = dt.Tables("COL_DATA").Rows(0).Item("SPECI")
remarktxt.Text = dt.Tables("COL_DATA").Rows(0).Item("REMARK")
End Sub
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
If Page.IsPostBack = False Then Exit Sub
Dim unit As String = unittxt.Text
'Dim tos As Date
'If Not tostxt.Text = "" Then
' tos = ChangeDateFormat(tostxt.Text)
'End If
Dim tos As Date = tostxt.Text
Dim speci As String = specitxt.Text
Dim remark As String = remarktxt.Text
Dim con As New SqlConnection(Str)
Dim cmd As New SqlCommand("UPDATE_UNIT", con)
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@unit", unit)
If tostxt.Text = "" Then
cmd.Parameters.Add("@tos", SqlDbType.DateTime).Value = DBNull.Value
Else
cmd.Parameters.AddWithValue("@tos", tos)
End If
cmd.Parameters.AddWithValue("@speci", speci)
cmd.Parameters.AddWithValue("@remark", remark)
End Sub
Public Function FixNull(ByVal dbvalue) As String
If dbvalue Is DBNull.Value Then
Return ""
Else
Return dbvalue.ToString
End If
End Function
End Class
You have not perform your update operations!try this:
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
If Page.IsPostBack = False Then Exit Sub
Dim unit As String = unittxt.Text
'Dim tos As Date
'If Not tostxt.Text = "" Then
' tos = ChangeDateFormat(tostxt.Text)
'End If
Dim tos As Date = tostxt.Text
Dim speci As String = specitxt.Text
Dim remark As String = remarktxt.Text
Dim con As New SqlConnection(Str)
Dim cmd As New SqlCommand("UPDATE_UNIT", con)
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@unit", unit)
If tostxt.Text = "" Then
cmd.Parameters.Add("@tos", SqlDbType.DateTime).Value = DBNull.Value
Else
cmd.Parameters.AddWithValue("@tos", tos)
End If
cmd.Parameters.AddWithValue("@speci", speci)
cmd.Parameters.AddWithValue("@remark", remark)
Try
con.Open()
cmd.ExecuteNonQuery()
lblMessage.Text = "Record updated successfully"
Catch ex As Exception
Throw ex
Finally
con.Close()
con.Dispose()
End Try
End Sub
Please mark the replies as answers if they help or unmark if not.
Feedback to us
Invalid object name 'Unit'.
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.Data.SqlClient.SqlException: Invalid object name 'Unit'.
Source Error:
Line 53: lblMessage.Text = "Record updated successfully"
Line 54: Catch ex As Exception
Line 55: Throw ex
Line 56: Finally
Line 57: con.Close()
Source File: K:\MS16_COLS\coledit.aspx.vb Line: 55
Stack Trace:
AND I COMMENT OUT LINE THAT STILL DATA IS NOT GOT INSERTED
Code
isno
problem,
butI
noticeyou areusingstored
procedures:"UPDATE_UNIT" .Pleaserefer to the link below,It
is alsousing the
storedprocedure to
update the database records:
Baiju EP
Member
178 Points
439 Posts
Updating data through text box (asp.net,VB,sql 2005)
May 13, 2012 05:49 AM|LINK
I am using asp.net, VB and sql 2005 data base. there is a text box by which the PNUMB is searched and is displayed in text box below. when i try to update and press submit button the following error comes.
Index and length must refer to a location within the string.
Parameter name: length
my code is
Imports System.Data.SqlClient Imports System.Data Partial Class coledit Inherits System.Web.UI.Page Dim Str As String = ConfigurationManager.ConnectionStrings("MS16ConnectionString").ConnectionString Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim NO As String = persno.Text Dim con As New SqlConnection con.ConnectionString = ConfigurationManager.ConnectionStrings("MS16ConnectionString").ConnectionString con.Open() Dim qstr As String qstr = "select * from COL_DATA where PNUMB='" + persno.Text + "'" Dim sqladp As New SqlDataAdapter(qstr, con) Dim dt As New DataSet dt.Clear() sqladp.Fill(dt, "COL_DATA") notxt.Text = dt.Tables("COL_DATA").Rows(0).Item("PNUMB") sufxtxt.Text = dt.Tables("COL_DATA").Rows(0).Item("SUFX") ranktxt.Text = dt.Tables("COL_DATA").Rows(0).Item("RANK") nametxt.Text = dt.Tables("COL_DATA").Rows(0).Item("Name") dtsentxt.Text = dt.Tables("COL_DATA").Rows(0).Item("DTSEN") unittxt.Text = dt.Tables("COL_DATA").Rows(0).Item("UNIT") tostxt.Text = dt.Tables("COL_DATA").Rows(0).Item("TOS") specitxt.Text = dt.Tables("COL_DATA").Rows(0).Item("SPECI") remarktxt.Text = dt.Tables("COL_DATA").Rows(0).Item("REMARK") End Sub Private Function updateunit(ByVal unit As String, ByVal tos As String, ByVal speci As String, ByVal remark As Integer) As Integer Dim con As New SqlConnection(Str) Dim cmd As New SqlCommand("UPDATE_UNIT", con) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@unit", unit) If tostxt.Text = "" Then cmd.Parameters.Add("@tos", SqlDbType.DateTime).Value = DBNull.Value Else cmd.Parameters.AddWithValue("@tos", tos) End If cmd.Parameters.AddWithValue("@speci", speci) cmd.Parameters.AddWithValue("@remark", remark) Dim status As Integer Try con.Open() status = cmd.ExecuteNonQuery() Return status Catch ex As Exception Response.Write(Err.Description) Finally con.Close() End Try End Function Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click If Page.IsPostBack = True Then Dim unit As String = unittxt.Text Dim tos As Date If Not tostxt.Text = "" Then tos = ChangeDateFormat(tostxt.Text) End If Dim speci As String = specitxt.Text Dim remark As Integer = remarktxt.Text updateunit(unit, tos, speci, remark) End If End Sub Public Function FixNull(ByVal dbvalue) As String If dbvalue Is DBNull.Value Then Return "" Else Return dbvalue.ToString End If End Function Private Function ChangeDateFormat(ByVal tempDate As String) As Date Dim dd As String = tempDate.Substring(0, 2) Dim mm As String = tempDate.Substring(3, 2) Dim yyyy As String = tempDate.Substring(6, 4) Dim dt As String = mm + "/" + dd + "/" + yyyy Dim rt_date As Date = CType(dt, Date) Return rt_date End Function Private Function ChangeDateFormatIndia(ByVal tempDate As String) As String Dim dd As String = tempDate.Substring(0, 2) Dim mm As String = tempDate.Substring(3, 2) If Len(dd) < 2 Then dd = "0" + dd End If If Len(mm) < 2 Then mm = "0" + mm End If Dim yyyy As String = tempDate.Substring(6, 4) Dim dt As String = dd + "/" + mm + "/" + yyyy ' Dim rt_date As Date = CType(dt, Date) Return dt End Function End ClassAmalO.Abdull...
Contributor
3116 Points
764 Posts
Re: Updating data through text box (asp.net,VB,sql 2005)
May 13, 2012 10:15 AM|LINK
in which line please?
shafiqkr
Member
455 Points
387 Posts
Re: Updating data through text box (asp.net,VB,sql 2005)
May 13, 2012 11:49 AM|LINK
Can you please confirm your passing parametrs? OR give us the correct line number,we need clarification of ur question
Baiju EP
Member
178 Points
439 Posts
Re: Updating data through text box (asp.net,VB,sql 2005)
May 13, 2012 02:48 PM|LINK
I want to update Unit,TOS,SPECI,Remarks
while updating following error comes
Index and length must refer to a location within the string.
Parameter name: length
Baiju EP
Member
178 Points
439 Posts
Re: Updating data through text box (asp.net,VB,sql 2005)
May 13, 2012 02:57 PM|LINK
now the erro is in
Line 75: Dim yyyy As String = tempDate.Substring(6, 4)Baiju EP
Member
178 Points
439 Posts
Re: Updating data through text box (asp.net,VB,sql 2005)
May 13, 2012 03:20 PM|LINK
i changed the code a little. now there is no error but the data is not updated. my code is as under
Imports System.Data.SqlClient Imports System.Data Partial Class coledit Inherits System.Web.UI.Page Dim Str As String = ConfigurationManager.ConnectionStrings("MS16ConnectionString").ConnectionString Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim NO As String = persno.Text Dim con As New SqlConnection con.ConnectionString = ConfigurationManager.ConnectionStrings("MS16ConnectionString").ConnectionString con.Open() Dim qstr As String qstr = "select * from COL_DATA where PNUMB='" + persno.Text + "'" Dim sqladp As New SqlDataAdapter(qstr, con) Dim dt As New DataSet dt.Clear() sqladp.Fill(dt, "COL_DATA") notxt.Text = dt.Tables("COL_DATA").Rows(0).Item("PNUMB") sufxtxt.Text = dt.Tables("COL_DATA").Rows(0).Item("SUFX") ranktxt.Text = dt.Tables("COL_DATA").Rows(0).Item("RANK") nametxt.Text = dt.Tables("COL_DATA").Rows(0).Item("Name") dtsentxt.Text = dt.Tables("COL_DATA").Rows(0).Item("DTSEN") unittxt.Text = dt.Tables("COL_DATA").Rows(0).Item("UNIT") tostxt.Text = dt.Tables("COL_DATA").Rows(0).Item("TOS") specitxt.Text = dt.Tables("COL_DATA").Rows(0).Item("SPECI") remarktxt.Text = dt.Tables("COL_DATA").Rows(0).Item("REMARK") End Sub Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click If Page.IsPostBack = False Then Exit Sub Dim unit As String = unittxt.Text 'Dim tos As Date 'If Not tostxt.Text = "" Then ' tos = ChangeDateFormat(tostxt.Text) 'End If Dim tos As Date = tostxt.Text Dim speci As String = specitxt.Text Dim remark As String = remarktxt.Text Dim con As New SqlConnection(Str) Dim cmd As New SqlCommand("UPDATE_UNIT", con) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@unit", unit) If tostxt.Text = "" Then cmd.Parameters.Add("@tos", SqlDbType.DateTime).Value = DBNull.Value Else cmd.Parameters.AddWithValue("@tos", tos) End If cmd.Parameters.AddWithValue("@speci", speci) cmd.Parameters.AddWithValue("@remark", remark) End Sub Public Function FixNull(ByVal dbvalue) As String If dbvalue Is DBNull.Value Then Return "" Else Return dbvalue.ToString End If End Function End Classi want to update Unit,TOS,SPECI, Remarks
Frank Jiang ...
All-Star
16006 Points
1728 Posts
Microsoft
Re: Updating data through text box (asp.net,VB,sql 2005)
May 15, 2012 03:48 AM|LINK
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click If Page.IsPostBack = False Then Exit Sub Dim unit As String = unittxt.Text 'Dim tos As Date 'If Not tostxt.Text = "" Then ' tos = ChangeDateFormat(tostxt.Text) 'End If Dim tos As Date = tostxt.Text Dim speci As String = specitxt.Text Dim remark As String = remarktxt.Text Dim con As New SqlConnection(Str) Dim cmd As New SqlCommand("UPDATE_UNIT", con) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@unit", unit) If tostxt.Text = "" Then cmd.Parameters.Add("@tos", SqlDbType.DateTime).Value = DBNull.Value Else cmd.Parameters.AddWithValue("@tos", tos) End If cmd.Parameters.AddWithValue("@speci", speci) cmd.Parameters.AddWithValue("@remark", remark) Try con.Open() cmd.ExecuteNonQuery() lblMessage.Text = "Record updated successfully" Catch ex As Exception Throw ex Finally con.Close() con.Dispose() End Try End SubFeedback to us
Develop and promote your apps in Windows Store
Baiju EP
Member
178 Points
439 Posts
Re: Updating data through text box (asp.net,VB,sql 2005)
May 15, 2012 01:37 PM|LINK
i TRIED IT BUT THERE IS AN ERROR
AND I COMMENT OUT LINE THAT STILL DATA IS NOT GOT INSERTED
Frank Jiang ...
All-Star
16006 Points
1728 Posts
Microsoft
Re: Updating data through text box (asp.net,VB,sql 2005)
May 16, 2012 02:52 AM|LINK
Code is no problem, but I notice you are using stored procedures: "UPDATE_UNIT" .Please refer to the link below, It is also using the stored procedure to update the database records:
http://www.aspsnippets.com/Articles/Calling-Update-SQL-Server-Stored-Procedures-using-ADO.Net.aspx
Feedback to us
Develop and promote your apps in Windows Store
Baiju EP
Member
178 Points
439 Posts
Re: Updating data through text box (asp.net,VB,sql 2005)
May 19, 2012 04:45 PM|LINK
problem stll not solved