A
Adam_Cogswell
Hello everyone,
I've been beating my head on this for two days and it's driving me
crazy. First things first, I'm learning ASP.NET and SQL integration
as I go here so please be kind.
I've done other web projects, but
this is the most complicated one I've attempted so far. This forum
has been a great resource and has saved me tons of time and energy but
I'm stuck...
A little background: I have a SQL 2000 db with a few related tables.
I am trying to create a asp.net front end to manage them. I'm fine as
long as the tables I'm updating don't have relatsionships, but the
items in one table in particular that has several foreign key
relationships. Ever time I try to insert a record to this table I get
the "Error Converting Data Type nvarchar to numeric". I think this is
happening because asp is trying to pass the ID field from one of the
drop down lists (asp:listbox) through as text and SQL is expecting a
numeric but I don't know how to fix it. Here's the code for my page
(again, please be kind ):
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" Runat="server">
Dim strConn as string =
ConfigurationManager.ConnectionStrings("EATData").ConnectionString
Sub Page_Load(Source as Object, E as EventArgs)
Dim GetListBoxValues as New SQLConnection(strConn)
GetListBoxValues.Open()
'Populate the drop down lists
Dim SolutionListSQL as string = "SELECT * FROM tblSolutions ORDER
BY Solution"
Dim CSPListSQL as string = "SELECT * FROM tblCodeLevels ORDER BY
CodeLevel"
Dim PackageTypeListSQL as string = "SELECT * FROM tblPackageTypes
ORDER BY PackageType"
Dim getSolutionList as New SQLCommand (SolutionListSQL,
GetListBoxValues)
Dim getCSPList as New SQLCommand (CSPListSQL, GetListBoxValues)
Dim getPackageTypeList as New SQLCommand (PackageTypeListSQL,
GetListBoxValues)
'Bind the listbox controls to the appropriate data reader
Dim objDR1 as SQLDataReader
objDR1 = getSolutionList.ExecuteReader()
lstSolutions.DataSource = objDR1
lstSolutions.DataBind()
objDR1.Close()
Dim objDR2 as SQLDataReader
objDR2 = getCSPList.ExecuteReader()
lstCSP.DataSource = objDR2
lstCSP.DataBind()
objDR2.Close()
Dim objDR3 as SQLDataReader
objDR3 = getPackageTypeList.ExecuteReader()
lstPackageTypes.DataSource = objDR3
lstPackageTypes.DataBind()
objDR3.Close()
GetListBoxValues.Close()
'Populate the list of current values
If not Page.IsPostBack then
getentries
end if
End Sub
Sub doInsert(Source as Object, E as EventArgs)
'Insert a new record
Dim InsertSQL as string = "Insert INTO tblPackages (PackageNum,
Description, SolutionID) VALUES (@PackageNum, @Description,
@lstSolutions)"
Dim InsertConn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand (InsertSQL, InsertConn)
cmd.Parameters.Add(New SQLParameter("@PackageNum",
txtPackageNum.text))
cmd.Parameters.Add(New SQLParameter("@Description",
txtDescription.text))
cmd.Parameters.Add(New SQLParameter("@lstSolutions",
lstSolutions.datavaluefield))
InsertConn.Open()
Cmd.ExecuteNonQuery()
InsertConn.Close()
'Update the list of current entries
getEntries
End Sub
Sub getEntries()
Dim MySQL as string = "SELECT * FROM tblPackages"
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader
MyDataGrid.DataSource = objDR
MyDataGrid.DataBind()
MyConn.Close()
End Sub
</script>
<html>
<head>
</head>
<body class="sidelights" onload="preloadImages();">
<p>
<form id="frmManageIssues" runat="server">
<div style="float:left">
<table width="100%">
<tr>
<td width="15%">Package Number:</td>
<td align="left"><asp:TextBox id="txtPackageNum" runat="server"/></
td>
</tr>
<tr>
<td width="15%">Description:</td>
<td><asp:TextBox id="txtDescription" runat="server"/></td>
</tr>
<tr>
<td width="15%">Solution:</td>
<td><asp:ListBox id="lstSolutions" rows="1"
datatextfield="Solution" datavaluefield="SolutionID" runat="server"></
asp:ListBox></td>
</tr>
</table>
<br>
<br>
<asp:Button id="InsertIssue" text="Insert" onclick="doInsert"
runat="server"/>
<br>
<br>
<asp:GridView runat="server"
id="MyDataGrid"/>
</asp:GridView>
</div>
</form>
</p>
</body>
</html>
I've been back and forth through the forum looking for a solution and
I'm sure I'm doing something stupid it but any help would be greatly
appreciated. Also, if this is the wrong forum, I apologize in
advance. Thanks!
I've been beating my head on this for two days and it's driving me
crazy. First things first, I'm learning ASP.NET and SQL integration
as I go here so please be kind.
I've done other web projects, butthis is the most complicated one I've attempted so far. This forum
has been a great resource and has saved me tons of time and energy but
I'm stuck...
A little background: I have a SQL 2000 db with a few related tables.
I am trying to create a asp.net front end to manage them. I'm fine as
long as the tables I'm updating don't have relatsionships, but the
items in one table in particular that has several foreign key
relationships. Ever time I try to insert a record to this table I get
the "Error Converting Data Type nvarchar to numeric". I think this is
happening because asp is trying to pass the ID field from one of the
drop down lists (asp:listbox) through as text and SQL is expecting a
numeric but I don't know how to fix it. Here's the code for my page
(again, please be kind
):<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" Runat="server">
Dim strConn as string =
ConfigurationManager.ConnectionStrings("EATData").ConnectionString
Sub Page_Load(Source as Object, E as EventArgs)
Dim GetListBoxValues as New SQLConnection(strConn)
GetListBoxValues.Open()
'Populate the drop down lists
Dim SolutionListSQL as string = "SELECT * FROM tblSolutions ORDER
BY Solution"
Dim CSPListSQL as string = "SELECT * FROM tblCodeLevels ORDER BY
CodeLevel"
Dim PackageTypeListSQL as string = "SELECT * FROM tblPackageTypes
ORDER BY PackageType"
Dim getSolutionList as New SQLCommand (SolutionListSQL,
GetListBoxValues)
Dim getCSPList as New SQLCommand (CSPListSQL, GetListBoxValues)
Dim getPackageTypeList as New SQLCommand (PackageTypeListSQL,
GetListBoxValues)
'Bind the listbox controls to the appropriate data reader
Dim objDR1 as SQLDataReader
objDR1 = getSolutionList.ExecuteReader()
lstSolutions.DataSource = objDR1
lstSolutions.DataBind()
objDR1.Close()
Dim objDR2 as SQLDataReader
objDR2 = getCSPList.ExecuteReader()
lstCSP.DataSource = objDR2
lstCSP.DataBind()
objDR2.Close()
Dim objDR3 as SQLDataReader
objDR3 = getPackageTypeList.ExecuteReader()
lstPackageTypes.DataSource = objDR3
lstPackageTypes.DataBind()
objDR3.Close()
GetListBoxValues.Close()
'Populate the list of current values
If not Page.IsPostBack then
getentries
end if
End Sub
Sub doInsert(Source as Object, E as EventArgs)
'Insert a new record
Dim InsertSQL as string = "Insert INTO tblPackages (PackageNum,
Description, SolutionID) VALUES (@PackageNum, @Description,
@lstSolutions)"
Dim InsertConn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand (InsertSQL, InsertConn)
cmd.Parameters.Add(New SQLParameter("@PackageNum",
txtPackageNum.text))
cmd.Parameters.Add(New SQLParameter("@Description",
txtDescription.text))
cmd.Parameters.Add(New SQLParameter("@lstSolutions",
lstSolutions.datavaluefield))
InsertConn.Open()
Cmd.ExecuteNonQuery()
InsertConn.Close()
'Update the list of current entries
getEntries
End Sub
Sub getEntries()
Dim MySQL as string = "SELECT * FROM tblPackages"
Dim MyConn as New SQLConnection(strConn)
Dim objDR as SQLDataReader
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
objDR=Cmd.ExecuteReader
MyDataGrid.DataSource = objDR
MyDataGrid.DataBind()
MyConn.Close()
End Sub
</script>
<html>
<head>
</head>
<body class="sidelights" onload="preloadImages();">
<p>
<form id="frmManageIssues" runat="server">
<div style="float:left">
<table width="100%">
<tr>
<td width="15%">Package Number:</td>
<td align="left"><asp:TextBox id="txtPackageNum" runat="server"/></
td>
</tr>
<tr>
<td width="15%">Description:</td>
<td><asp:TextBox id="txtDescription" runat="server"/></td>
</tr>
<tr>
<td width="15%">Solution:</td>
<td><asp:ListBox id="lstSolutions" rows="1"
datatextfield="Solution" datavaluefield="SolutionID" runat="server"></
asp:ListBox></td>
</tr>
</table>
<br>
<br>
<asp:Button id="InsertIssue" text="Insert" onclick="doInsert"
runat="server"/>
<br>
<br>
<asp:GridView runat="server"
id="MyDataGrid"/>
</asp:GridView>
</div>
</form>
</p>
</body>
</html>
I've been back and forth through the forum looking for a solution and
I'm sure I'm doing something stupid it but any help would be greatly
appreciated. Also, if this is the wrong forum, I apologize in
advance. Thanks!