ASP.Net Error: Conversion failed when converting the nvarchar value Test33 to data type int

lingers

SOLVED CLOSED ACTIVE ANSWERED
User: lingers
Posted: on Jul 20, 2021 07:16 AM
Forum: Data Controls
Answer: 1
Views: 2409
Sample Code: Download

Please i want to embid from job table. From the code below it only displays the id and pid, but what i want is to select the id, pid, embid so that i can use the embid in the next query.

Please note that the id of the embom table that has the embid is the embid in he job table i.e. id.embom = embid.job

My code

    <asp:TextBox id="txtSearch1" class="autosuggest1" runat="server" Width="99%"></asp:TextBox> 
    <asp:TextBox id="TextBox70" class="autosuggest31" runat="server" Width="99%"></asp:TextBox>
    $(function () {
        SearchText();     
    });
    function SearchText() {
        $(".autosuggest").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "Order.aspx/GetAutoCompleteData",
                    data: "{ 'pid': '" + request.term + "'}",
                    dataType: "json",
                    success: function (data) {
                        if (data.d.length > 0) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('/')[0],
                                    val: item.split('/')[1]
                                }
                            }));
                        }
                        else {
                            response([{ label: 'No Records Found', val: -1}]);
                        }
                    },
                    error: function (result) {
                        alert("Error");
                    }
                });
            },
            select: function (event, ui) {
                if (ui.item.val == -1) {
                    return false;
                }
                $('[id*=hfPId]').val(ui.item.val);
            }
        });
    }
    $(function () {
        SearchText31();
    });
    function SearchText31() {

        $(".autosuggest31").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "Order.aspx/GetAutoCompleteData31",
                    data: "{ 'embid': '" + request.term + "', id: '" + $(".autosuggest").val() + "'}",
                    dataType: "json",
                    success: function (data) {
                        if (data.d.length > 0) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('/')[0],
                                    val: item.split('/')[1]
                                }
                            }));
                        }
                        else {
                            response([{ label: 'No Records Found', val: -1}]);
                        }
                    },
                    error: function (result) {
                        alert(result.responseText);
                    }
                });
            },
            select: function (event, ui) {
                if (ui.item.val == -1) {
                    return false;
                }
            }
        });
    }

please help

[WebMethod]
public static List<string> GetAutoCompleteData(string pid)
{
    List<string> result = new List<string>();
    using (SqlConnection con = new SqlConnection("Data Source=NERO-SIGBENU\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=arpackaging;"))
    {
        using (SqlCommand cmd = new SqlCommand("select id,pid from job where pid LIKE '%'+@SearchText+'%'", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SearchText", pid);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(string.Format("{0}/{1}", dr["pid"], dr["id"]));
            }
            return result;
        }
    }
}   

[WebMethod]
public static List<string> GetAutoCompleteData31(string embid, string id)
{
         List<string> result = new List<string>();
         using (SqlConnection con = new SqlConnection("Data Source=NERO-SIGBENU\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=arpackaging;"))
         {
             using (SqlCommand cmd = new SqlCommand("select id,embid from job_emboss where  id=@embid ", con))
             {
                 con.Open();
                 cmd.Parameters.AddWithValue("@SearchText31", embid);
                 cmd.Parameters.AddWithValue("@id", embid);
                 SqlDataReader dr = cmd.ExecuteReader();
                 while (dr.Read())
                 {
                     result.Add(string.Format("{0}/{1}", dr["embid"], dr["id"]));
                 }
                 return result;
             }
         }
}

My Error

{"Message":"Conversion failed when converting the nvarchar value \u0027Test33\u0027 to data type int.","StackTrace":" at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean\u0026 dataReady)\r\n at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean\u0026 moreRows)\r\n at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean\u0026 more)\r\n at System.Data.SqlClient.SqlDataReader.Read()\r\n at Order.GetAutoCompleteData31(String embid, String id) in c:\\Users\\hp\\Documents\\Visual Studio 2010\\WebSites\\WebSite6\\Order.aspx.cs:line 173","ExceptionType":"System.Data.SqlClient.SqlException"}

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 5

Reply
ANSWER
User: dharmendr
Replied: on Jul 21, 2021 01:13 AM
Modified: on Jul 21, 2021 07:21 AM
Report

Hi lingers,

Refer the modified code.

HTML

Job:
<asp:TextBox ID="txtSearch1" class="autosuggest1" runat="server" Width="99%"></asp:TextBox>
<br />
Emboss:
<asp:TextBox ID="TextBox70" class="autosuggest31" runat="server" Width="99%"></asp:TextBox>
<br />
<asp:Button Text="Save" runat="server" OnClick="OnSave" />
<asp:HiddenField ID="hfId" runat="server" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js"></script>
<script type="text/javascript">
    $(function () {
        SearchText();
        SearchText31();
    });

    function SearchText() {
        $(".autosuggest1").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "Default.aspx/GetAutoCompleteData",
                    data: "{ 'pid': '" + request.term + "'}",
                    dataType: "json",
                    success: function (data) {
                        if (data.d.length > 0) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('/')[0],
                                    val: item.split('/')[1]
                                }
                            }));
                        }
                        else {
                            response([{ label: 'No Records Found', val: -1 }]);
                        }
                    },
                    error: function (result) {
                        alert(result.responseText);
                    }
                });
            },
            select: function (event, ui) {
                if (ui.item.val == -1) {
                    return false;
                }
                $('[id*=hfId]').val(ui.item.val);
            }
        });
    }

    function SearchText31() {
        $(".autosuggest31").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "Default.aspx/GetAutoCompleteData31",
                    data: "{ 'pid': '" + request.term + "', embid: '" + $('[id*=hfId]').val() + "'}",
                    dataType: "json",
                    success: function (data) {
                        if (data.d.length > 0) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('/')[0],
                                    val: item.split('/')[1]
                                }
                            }));
                        }
                        else {
                            response([{ label: 'No Records Found', val: -1 }]);
                        }
                    },
                    error: function (result) {
                        alert(result.responseText);
                    }
                });
            },
            select: function (event, ui) {
                if (ui.item.val == -1) {
                    return false;
                }
            }
        });
    }
</script>

Namespaces

using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Data.SqlClient
Imports System.Web.Services

Code

[WebMethod]
public static List<string> GetAutoCompleteData(string pid)
{
    List<string> result = new List<string>();
    using (SqlConnection con = new SqlConnection("Data Source=NERE\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=kaging;"))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT embid,pid FROM job2 WHERE pid LIKE '%'+@SearchText+'%'", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SearchText", pid);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(string.Format("{0}/{1}", dr["pid"], dr["embid"]));
            }

            return result;
        }
    }
}

[WebMethod]
public static List<string> GetAutoCompleteData31(string pid, string embid)
{
    List<string> result = new List<string>();
    using (SqlConnection con = new SqlConnection("Data Source=NERE\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=kaging;"))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT id,embid FROM emboss2 WHERE embid LIKE '%'+@SearchText31+'%' AND id=@Id", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SearchText31", pid);
            cmd.Parameters.AddWithValue("@Id", embid);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(string.Format("{0}/{1}", dr["embid"], dr["id"]));
            }

            return result;
        }
    }
}

protected void OnSave(object sender, EventArgs e)
{
       
}

VB.Net

<WebMethod>
Public Shared Function GetAutoCompleteData(ByVal pid As String) As List(Of String)
    Dim result As List(Of String) = New List(Of String)()
    Using con As SqlConnection = New SqlConnection("Data Source=NERE\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=kaging;")
        Using cmd As SqlCommand = New SqlCommand("SELECT embid,pid FROM job2 WHERE pid LIKE '%'+@SearchText+'%'", con)
            con.Open()
            cmd.Parameters.AddWithValue("@SearchText", pid)
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            While dr.Read()
                result.Add(String.Format("{0}/{1}", dr("pid"), dr("embid")))
            End While

            Return result
        End Using
    End Using
End Function

<WebMethod>
Public Shared Function GetAutoCompleteData31(ByVal pid As String, ByVal embid As String) As List(Of String)
    Dim result As List(Of String) = New List(Of String)()
    Using con As SqlConnection = New SqlConnection("Data Source=NERE\\SQLEXPRESS01;Integrated Security=true;Initial Catalog=kaging;")
        Using cmd As SqlCommand = New SqlCommand("SELECT id,embid FROM emboss2 WHERE embid LIKE '%'+@SearchText31+'%' AND id=@Id", con)
            con.Open()
            cmd.Parameters.AddWithValue("@SearchText31", pid)
            cmd.Parameters.AddWithValue("@Id", embid)
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            While dr.Read()
                result.Add(String.Format("{0}/{1}", dr("embid"), dr("id")))
            End While

            Return result
        End Using
    End Using
End Function

Protected Sub OnSave(ByVal sender As Object, ByVal e As EventArgs)
End Sub

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

ASP.Net Error: Conversion failed when converting the nvarchar value Test33 to data type int

Company

Explore

Follow us

Contact Us