This is the programme that i'm doing...(amended)
<%@ Page Language="
vb" debug="true"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Dim conn As SqlConnection
Dim dbComm AS SQLDataAdapter
Dim SQL AS String
Dim ds AS New DataSet
Dim SQLserver AS String = "user id=sa;password=;database=myprogramme;server=localh ost;Connect Timeout=15;"
Sub Page_Load(myList AS Object,E as EventArgs)
If NOT Page.IsPostBack()
SQL = "SELECT * FROM tblRefCountry ORDER BY Country"
conn = New SQLConnection(SQLserver)
dbComm = New SQLDataAdapter(SQL,conn)
dbComm.Fill(ds,"Country")
countryCd.DataSource = ds.Tables("Country").DefaultView
countryCd.DataBind()
countryCd.Items.Insert(0,"- Select Country -")
End If
End Sub
Sub AddContact_Click(Sender As Object, E As EventArgs)
message.text = ""
If (Page.IsValid)
Dim DS As DataSet
Dim MyCommand As SqlCommand
Dim InsertCmd As String
InsertCmd = "insert into tblCrContactDet (Addr1, CityCd, StateCd, CountryCd, "
InsertCmd += "PostCd, Phone, Mobile1, Fax, Email, Other) values (@Addr1, @CityCd, @StateCd, @CountryCd,"
InsertCmd += "@PostCd, @Phone, @Mobile1, @Fax, @Email, @Other)"
conn = New SQLConnection(SQLserver)
MyCommand = New SqlCommand(InsertCmd, conn)
MyCommand.Parameters.Add(New SqlParameter("@Addr1", SqlDbType.NVarChar, 80))
MyCommand.Parameters("@Addr1").Value = Addr1.Text
MyCommand.Parameters.Add(New SqlParameter("@CityCd", SqlDbType.NChar, 6))
MyCommand.Parameters("@CityCd").Value = CityCd.Text
MyCommand.Parameters.Add(New SqlParameter("@StateCd", SqlDbType.NChar, 2))
MyCommand.Parameters("@StateCd").Value = StateCd.Text
MyCommand.Parameters.Add(New SqlParameter("@CountryCd", SqlDbType.NChar, 3))
MyCommand.Parameters("@CountryCd").Value = CountryCd.SelectedValue
MyCommand.Parameters.Add(New SqlParameter("@PostCd", SqlDbType.NVarChar, 10))
MyCommand.Parameters("@PostCd").Value = PostCd.Text
MyCommand.Parameters.Add(New SqlParameter("@Phone", SqlDbType.NVarChar, 20))
MyCommand.Parameters("@Phone").Value = Phone.Text
MyCommand.Parameters.Add(New SqlParameter("@Mobile1", SqlDbType.NVarChar, 20))
MyCommand.Parameters("@Mobile1").Value = Mobile1.Text
MyCommand.Parameters.Add(New SqlParameter("@Fax", SqlDbType.NVarChar,20))
MyCommand.Parameters("@Fax").Value = Fax.Text
MyCommand.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar,30))
MyCommand.Parameters("@Email").Value = Email.Text
MyCommand.Parameters.Add(New SqlParameter("@Other", SqlDbType.NVarChar,20))
MyCommand.Parameters("@Other").Value = Other.Text
MyCommand.Connection.Open()
Try
MyCommand.ExecuteNonQuery()
Message.text = "<b>Record Added</b><br>" & InsertCmd.ToString()
Catch Exp As SQLException
If Exp.Number = 2627
message.text = "ERROR: A record already exists with the same primary key"
Else
message.text = "ERROR: Could not add record, please ensure the fields are correctly filled out"
End If
Message.Style("color") = "red"
End Try
MyCommand.Connection.Close()
End If
End Sub
</script>
<html>
<head>
</head>
<body style="FONT: 8pt verdana">
<form runat="server">
<table style="WIDTH: 556px; HEIGHT: 189px">
<tbody>
<tr>
<td valign="top">
<table style="FONT: 8pt verdana; WIDTH: 279px; HEIGHT: 167px">
<tbody>
<tr rowspan="2">
<td nowrap="nowrap">
Home Address</td>
<td>
<asp:textbox id="Addr1" runat="server" />
<asp:RequiredFieldValidator id="Addr1ReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="Addr1">
* </asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td nowrap="nowrap">
City</td>
<td>
<asp:textbox id="CityCd" runat="server" />
<asp:RequiredFieldValidator id="CityCdReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="CityCd">
* </asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
State</td>
<td>
<asp:textbox id="StateCd" runat="server" />
<asp:RequiredFieldValidator id="StateCdReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="StateCd">
* </asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
Country</td>
<td>
<asp:dropDownList
id="CountryCd"
runat="server"
DataTextField="Country"
DataValueField = "CountryCd"
AutoPostBack = "True" />
<asp:RequiredFieldValidator id="CountryCdReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="CountryCd">
* </asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
Zip/Postal Code</td>
<td>
<asp:textbox id="PostCd" runat="server" />
<asp:RequiredFieldValidator id="PostCdReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="PostCd">
* </asp:RequiredFieldValidator>
</td>
</tr>
</tbody>
</table>
</td>
<td>
</td>
<td valign="top">
<table style="FONT: 8pt verdana; WIDTH: 245px; HEIGHT: 169px">
<tbody>
<tr>
<td nowrap="nowrap">
<p style="FONT-WEIGHT: bold" align="left">
Contact No.
</p>
</td>
<td>
</td>
</tr>
<tr>
<td>
Residence</td>
<td>
<asp:textbox id="Phone" runat="server" /></td>
</tr>
<tr>
<td>
Mobile</td>
<td>
<asp:textbox id="Mobile1" runat="server" /></td>
</tr>
<tr>
<td>
Fax</td>
<td>
<asp:textbox id="Fax" runat="server" /></td>
</tr>
<tr>
<td>
E-mail</td>
<td>
<asp:textbox id="Email" runat="server" /></td>
</tr>
<tr>
<td>
Others</td>
<td>
<asp:textbox id="Other" runat="server" /></td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="AddButton"
Text="Save"
OnClick="AddContact_Click"
runat="server"/>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td style="PADDING-TOP: 15px" align="middle" colspan="3">
<asp:label id="Message" runat="server" enableviewstate="false"/>
<asp:RegularExpressionValidator id="PostCdValidator" runat="server" Font-Size="X-Small" Font-Name="Arial" Display="Dynamic" ControlToValidate="PostCd" ValidationExpression="[0-9]{5}" Font-Names="Arial">
* Zip/Postal Code must has 5 numeric digits <br />
</asp:RegularExpressionValidator>
</td>
</tr>
</tbody>
</table>
</form>
</body>
</html>
Problems with Sqlcommand.Connection.Open()
