insertin and updating using parameter

Can Anybody help me
I m trying to insert and update candidate info and retriving the doc file , where insrting is happyning properly but while updating i m getting error

how ever i m getting eroor"The number name 'Can_CodeSystem.Web.UI.WebControls' contains more than the maximum number of prefixes. The maximum is 3."

Imports System
Imports System.Data
Imports System.Data.SqlClient

Partial Class CandidateInfo
Inherits System.Web.UI.Page
Dim intEditMode As Integer

Protected Sub but_Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles but_Submit.Click
Dim strcon As String = "Data source=(local);Initial catalog=SaveImage; user id=sa;password=sa;"
Dim sql As String
Dim len As Integer = up_cv.PostedFile.ContentLength
Dim obj As Byte() = New Byte(len - 1) {}
up_cv.PostedFile.InputStream.Read(obj, 0, len)

Dim con As New SqlConnection(strcon)

Dim strGender As String
If RB_male.Checked = False Then
strGender = "Male"
Else
strGender = "Female"
End If

If Session("Can_Code") = Nothing Then

'sql = "Insert into CandidateInfo(Can_AppDate,Can_FName,Can_Mname,Can_Lname,Can_Address,Can_Phone,Can_Mobile,Can_EmailId,Can_BirthDate,Can_Gender,cReportType,Doc) values('" & Trim(strApplDate) & "','" & Trim(TB_FName.Text) & "','" & Trim(TB_Mname.Text) & "','" & Trim(TB_LName.Text) & "','" & Trim(TB_Address.Text) & "','" & Trim(TB_Phone.Text) & "','" & Trim(TB_Mobile.Text) & "','" & Trim(TB_EmailId.Text) & "','" & Trim(strBirthDate) & "','" & Trim(strGender) & "','" & Trim(TB_CV.Text) & "','" & Trim(obj.ToString()) & "')"
sql = "insert into CandidateInfo(Can_AppDate,Can_FName,Can_Mname,Can_Lname,Can_Address,Can_Phone,Can_Mobile,Can_EmailId,Can_BirthDate,Can_Gender,cReportType,Doc)values (@Can_AppDate,@Can_FName,@Can_Mname,@Can_Lname,@Can_Address,@Can_Phone,@Can_Mobile,@Can_EmailId,@Can_BirthDate,@Can_Gender,@cReportType,@Doc)"
con.Open()
Dim cmd1 As New SqlCommand(sql, con)
cmd1.Parameters.Add("@Can_FName", SqlDbType.NVarChar).Value = TB_FName.Text
cmd1.Parameters.Add("@Can_Mname", SqlDbType.NVarChar).Value = TB_Mname.Text
cmd1.Parameters.Add("@Can_Lname", SqlDbType.NVarChar).Value = TB_LName.Text
cmd1.Parameters.Add("@Can_Address", SqlDbType.NVarChar).Value = TB_Address.Text
cmd1.Parameters.Add("@Can_Phone", SqlDbType.NVarChar).Value = TB_Phone.Text
cmd1.Parameters.Add("@Can_Mobile", SqlDbType.NVarChar).Value = TB_Mobile.Text
cmd1.Parameters.Add("@Can_EmailId", SqlDbType.NVarChar).Value = TB_EmailId.Text
cmd1.Parameters.Add("@Can_Gender", SqlDbType.NVarChar).Value = strGender
cmd1.Parameters.Add("@cReportType", SqlDbType.NVarChar).Value = TB_CV.Text
cmd1.Parameters.Add("@Doc", obj)

cmd1.Connection = con
cmd1.ExecuteNonQuery()
con.Close()

intEditMode = 1
Else
'sql = "Update CandidateInfo set Can_AppDate='" & Trim(strApplDate) & "',Can_FName='" & Trim(TB_FName.Text) & "',Can_Mname='" & Trim(TB_Mname.Text) & "',Can_Lname='" & Trim(TB_LName.Text) & "',Can_Address='" & Trim(TB_Address.Text) & "',Can_Phone='" & Trim(TB_Phone.Text) & "',Can_Mobile='" & Trim(TB_Mobile.Text) & "',Can_EmailId='" & Trim(TB_EmailId.Text) & "',Can_BirthDate='" & Trim(strBirthDate) & "',Can_Gender='" & Trim(strGender) & "',cReportType='" & Trim(TB_CV.Text) & "',Doc='" & Trim(obj.ToString()) & "' where Can_Code= " & (Session("Can_Code"))
'sql = "Update CandidateInfo set Can_FName='" & Trim(TB_FName.Text) & "',Can_Mname='" & Trim(TB_Mname.Text) & "',Can_Lname='" & Trim(TB_LName.Text) & "',Can_Address='" & Trim(TB_Address.Text) & "',Can_Phone='" & Trim(TB_Phone.Text) & "',Can_Mobile='" & Trim(TB_Mobile.Text) & "',Can_EmailId='" & Trim(TB_EmailId.Text) & "',Can_Gender='" & Trim(strGender) & "',cReportType='" & Trim(TB_CV.Text) & "' where Can_Code= " & Session("Can_Code")
sql = "Update CandidateInfo set Can_FName=@Can_FName,Can_Mname=@Can_Mname,Can_Lname=@Can_Lname,Can_Address=@Can_Address,Can_Phone=@Can_Phone,Can_Mobile=@Can_Mobile,Can_EmailId=@Can_EmailId,Can_Gender=@Can_Gender,cReportType=@cReportType where Can_Code" & Session("Can_Code")
con.Open()
Dim cmd As New SqlCommand(sql, con)
cmd.Parameters.Add("@Can_FName", SqlDbType.NVarChar).Value = TB_FName.Text
cmd.Parameters.Add("@Can_Mname", SqlDbType.NVarChar).Value = TB_Mname.Text
cmd.Parameters.Add("@Can_Lname", SqlDbType.NVarChar).Value = TB_LName.Text
cmd.Parameters.Add("@Can_Address", SqlDbType.NVarChar).Value = TB_Address.Text
cmd.Parameters.Add("@Can_Phone", SqlDbType.NVarChar).Value = TB_Phone.Text
cmd.Parameters.Add("@Can_Mobile", SqlDbType.NVarChar).Value = TB_Mobile.Text
cmd.Parameters.Add("@Can_EmailId", SqlDbType.NVarChar).Value = TB_EmailId.Text
cmd.Parameters.Add("@Can_Gender", SqlDbType.NVarChar).Value = strGender
cmd.Parameters.Add("@cReportType", SqlDbType.NVarChar).Value = TB_CV.Text

'Session("Can_Code") = Nothing

cmd.Connection = con
cmd.ExecuteNonQuery()
con.Close()

intEditMode = 2
End If

'Dim cmd1 As New SqlCommand(sql, con)
'con.Open()
'cmd1.Connection = con
'cmd1.ExecuteNonQuery()
'con.Close()

Response.Write("Record inserted succesfully")
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
FillGrid()

End If

End Sub

Public Sub FillGrid()
Dim strcon As String = "Data source=(local);Initial catalog=SaveImage; user id=sa;password=sa;"
Dim con As New SqlConnection(strcon)
Dim sql As String
sql = "select * from Candidateinfo"
Dim da As New SqlDataAdapter(sql, con)
Dim ds As New DataSet()
da.Fill(ds)
DG.DataSource = ds
DG.DataBind()

End Sub

Protected Sub DG_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DG.ItemCommand

If e.CommandName = "Edit" Then
Session("Can_Code") = e.Item.Cells(1).ToString()

TB_FName.Text = e.Item.Cells(3).Text.ToString()
If TB_FName.Text = "nbsp;" Then
TB_FName.Text = ""
End If

TB_Mname.Text = e.Item.Cells(4).Text.ToString()
If TB_Mname.Text = "nbsp;" Then
TB_Mname.Text = ""
End If

TB_LName.Text = e.Item.Cells(5).Text.ToString()
If TB_LName.Text = "nbsp;" Then
TB_LName.Text = ""
End If

TB_Address.Text = e.Item.Cells(6).Text.ToString()
If TB_Address.Text = "nsp;" Then
TB_Address.Text = ""
End If

TB_Phone.Text = e.Item.Cells(7).Text.ToString()
If TB_Phone.Text = "nbsp;" Then
TB_Phone.Text = ""
End If

TB_Mobile.Text = e.Item.Cells(8).Text.ToString()
If TB_Mobile.Text = "nbsp;" Then
TB_Mobile.Text = ""
End If

TB_EmailId.Text = e.Item.Cells(9).Text.ToString()
If TB_EmailId.Text = "nbsp;" Then
TB_EmailId.Text = ""
End If

TB_CV.Text = e.Item.Cells(12).Text.ToString()
If TB_CV.Text = "nbsp;" Then
TB_CV.Text = ""
End If

ElseIf e.CommandName = "Delete" Then
Dim sql As String
Dim strcon As String = "Data source=(local);Initial catalog=SaveImage; user id=sa;password=sa;"
Session("Req_DelCode") = e.Item.Cells(1).Text
Dim con As New SqlConnection(strcon)
sql = "Delete from CandidateInfo where Can_Code=" & Session("Req_DelCode")
Dim cmd As New SqlCommand(sql)
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
con.Close()

ElseIf e.CommandName = "view" Then
Dim stream As New IO.MemoryStream()

Dim sql As String
Dim strcon As String = "Data source=(local);Initial catalog=SaveImage; user id=sa;password=sa;"
Dim con As New SqlConnection(strcon)
Session("Req_SeCode") = e.Item.Cells(1).Text
con.Open()
sql = "select Doc from CandidateInfo where Can_Code=" & Session("Req_SeCode")
Dim cmd As New SqlCommand(sql, con)

'cmd.Parameters.Add("@cReportType", SqlDbType.VarChar)
'cmd.Parameters("@cReportType").Value = TB_CV.Text

Dim obj As Byte() = CType(cmd.ExecuteScalar(), Byte())
Response.ContentType = "Application/msword"
Response.BinaryWrite(CType(cmd.ExecuteScalar(), Byte()))
Response.[End]()
con.Close()
End If

End Sub

Protected Sub DG_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DG.ItemDataBound

End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

End Sub
End Class

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Arshad18y ago
Accepted answer
Hi, first of all i will suggest you that if ur using the ExecuteNonQuery then try to avoid the SqlParameter, try to use only for Storedprocedure and you are assigning to the session for geting the Id, you can use the HiddenField or ViewState that will be better. and one more thing like ur just getting the TextBox values in to the String every time, jsut Directly use the TextBox.Text.ToString(), that will be better.so now as per your requirement, you just Debugg your Application and check the UpdateString and run in the SQLServer then you will get to know that what is the Problem, coz i dont have your DB here so its tuff for us to do that.
afroz khan18y ago
Thank u dear