Printable View
I have put together a simple table and wish to update my SQL database. All the Parameters are matching and data types set i.e. SqlDataType.datetime or NVarChar field lenghths also match the database's Parameters.
My Question when I Add a new record the SQLException handler returnes a value of 207 nothing I do can rectify this I am verry frustrated. & :confused:
I would check your query over....
Error 207 If I remember correctly has to do with an invalid column name or somthing to that affect.
You could list your query and what you have your db and I could look it over, if it is not too large.
Yeh right but my code looks ok.
Is it posible with SQL Server to update just one cell with code in ASP.NET it would make debuging so much easier.
db is tiny 3 records max
will post code when back at work 14/6 ta
<%@ Import Namespace="System.Data" %>
<HTML>
<script language="VB" runat="server">
Dim MyConnection As SqlConnection
Sub Page_Load(Sender As Object, E As EventArgs)
MyConnection = New SqlConnection("server=localhost;database=model;Trusted_Connection=yes")
If Not (IsPostBack)
BindGrid()
End If
End Sub
Sub AddAuthor_Click(Sender As Object, E As EventArgs)
Dim DS As DataSet
Dim MyCommand As SqlCommand
If Loan_ID.Value = "" Or Porduct_Id.Value = "" Or booked_Date.Value = "" Or Username.Value = ""
Message.InnerHtml = "ERROR: Null values not allowed for Author ID, Name or Phone"
Message.Style("color") = "red"
BindGrid()
End If
Dim InsertCmd As String = "insert into Results (Loan_ID, Porduct_Id, booked_Time, Booked_Date, Username, Request_Date, Returned_by, Returned_Date, Available) values (@id, @lname, @Time, @bDate, @Nm, @rDate, @By, @Date, @Avail)"
MyCommand = New SqlCommand(InsertCmd, MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@id", SqlDbType.NVarChar, 4))
MyCommand.Parameters("@id").Value = Loan_ID.Value
MyCommand.Parameters.Add(New SqlParameter("@lName", SqlDbType.Int, 4))
MyCommand.Parameters("@lName").Value = Porduct_Id.Value
MyCommand.Parameters.Add(New SqlParameter("@Time", SqlDbType.datetime, 8))
MyCommand.Parameters("@Time").Value = booked_Time.Value
MyCommand.Parameters.Add(New SqlParameter("@bDate", SqlDbType.datetime, 8))
MyCommand.Parameters("@bDate").Value = Booked_Date.Value
MyCommand.Parameters.Add(New SqlParameter("@Nm", SqlDbType.NVarChar, 50))
MyCommand.Parameters("@Nm").Value = Username.Value
MyCommand.Parameters.Add(New SqlParameter("@rDate", SqlDbType.datetime, 8))
MyCommand.Parameters("@rDate").Value = Request_Date.Value
MyCommand.Parameters.Add(New SqlParameter("@By", SqlDbType.NVarChar, 50))
MyCommand.Parameters("@By").Value = Returned_by.Value
MyCommand.Parameters.Add(New SqlParameter("@Date", SqlDbType.datetime, 8))
MyCommand.Parameters("@Date").Value = Returned_Date.Value
MyCommand.Parameters.Add(New SqlParameter("@Avail", SqlDbType.NVarChar,1))
MyCommand.Parameters("@Avail").Value = Available.Value
MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()
Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
textbox1.text = "The Error = "& Exp.Number
End If
Message.Style("color") = "red"
End Try
MyCommand.Connection.Close()
BindGrid()
End Sub
Sub BindGrid()
Dim MyCommand As SqlDataAdapter = new SqlDataAdapter("select * from results", MyConnection)
Dim DS As DataSet = new DataSet()
MyCommand.Fill(DS, "model")
MyDataGrid.DataSource=DS.Tables("model").DefaultView
MyDataGrid.DataBind()
End Sub
</script>
<body style="FONT: 10pt verdana">
<form runat="server" ID="Form1">
<h3>
<font face="Verdana">Tracy's Kinky Boots ohooooo</font>
</h3>
<table width="95%">
<tr>
<td valign="top">
<ASP:DataGrid id="MyDataGrid" runat="server" Width="700" BackColor="#ccccff" BorderColor="black" ShowFooter="false" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" EnableViewState="false" />
</td>
<td valign="top">
<table style="FONT: 8pt verdana">
<tr>
<td colspan="2" bgcolor="#aaaadd" style="FONT:10pt verdana">
Add a New Request:
</td>
</tr>
<tr>
<td nowrap>
ID:
</td>
<td>
<input type="text" id="Loan_ID" value="Int" runat="server" NAME="Loan_ID">
</td>
</tr>
<tr>
<td nowrap>
Prod Num:
</td>
<td>
<input type="text" id="Porduct_Id" value="Int" runat="server" NAME="Porduct_Id">
</td>
</tr>
<tr>
<td>
Booking Date:
</td>
<td>
<input type="text" id="booked_Date" value="Date" runat="server" NAME="booked_Date">
</td>
</tr>
<tr>
<td>
User Name:
</td>
<td>
<input type="text" id="Username" value="Text" runat="server" NAME="Username">
</td>
</tr>
<tr>
<td>
Req Date:
</td>
<td>
<input type="text" id="Request_Date" value="Date" runat="server" NAME="Request_Date">
</td>
</tr>
<tr>
<td>
Ret By:
</td>
<td>
<input type="text" id="Returned_by" value="Text" runat="server" NAME="Returned_by">
</td>
</tr>
<tr>
<td>
Ret Date:
</td>
<td>
<input type="text" id="Returned_Date" value="Date" runat="server" NAME="Returned_Date">
</td>
</tr>
<tr>
<td>
Available:
</td>
<tr>
<td>
Ret Date:
</td>
<td>
<input type="text" id="booked_Time" value="Date" runat="server" NAME="booked_Time">
</td>
</tr>
</tr>
<tr>
<td>
Available:
</td>
<td>
<select id="Available" runat="server" NAME="Available">
<option value="0" selected>
False</option>
<option value="1">
True</option>
</select>
</td>
</tr>
<tr>
<td>
</td>
<td style="PADDING-TOP:15px">
<input type="submit" OnServerClick="AddAuthor_Click" value="Add Author" runat="server" ID="Submit1" NAME="Submit1">
</td>
</tr>
<tr>
<td colspan="2" style="PADDING-TOP:15px" align="middle">
<span id="Message" EnableViewState="false" runat="server"></span>
</td>
</tr>
</table>
</td>
</tr>
</table>
<asp:TextBox id="TextBox1" runat="server"></asp:TextBox>
</form>
</body>
</HTML>