Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > General .NET
|
General .NET For general discussion of MICROSOFT .NET topics that don't fall within any of the other .NET forum subcategories or .NET language forums.  If your question is specific to a language (C# or Visual Basic) or type of application (Windows Forms or ASP.Net) try an applicable forum category. ** PLEASE BE SPECIFIC WITH YOUR QUESTION ** When posting here, provide details regarding the Microsoft .NET language you are using and/or what type of application (Windows/Web Forms, etc) you are working in, if applicable to the question. This will help others answer the question without having to ask.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the General .NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 16th, 2004, 06:35 AM
Authorized User
 
Join Date: Mar 2004
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problems with Sqlcommand.Connection.Open()

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>
                                        &nbsp;<asp:textbox id="Addr1" runat="server" />
                                        <asp:RequiredFieldValidator id="Addr1ReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="Addr1">
                                        &nbsp;* </asp:RequiredFieldValidator>
                                    </td>
                                </tr>

                                <tr>
                                    <td nowrap="nowrap">
                                        City</td>
                                    <td>
                                        &nbsp;<asp:textbox id="CityCd" runat="server" />
                                        <asp:RequiredFieldValidator id="CityCdReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="CityCd">
                                        &nbsp;* </asp:RequiredFieldValidator>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        State</td>
                                    <td>
                                        &nbsp;<asp:textbox id="StateCd" runat="server" />
                                        <asp:RequiredFieldValidator id="StateCdReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="StateCd">
                                        &nbsp;* </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">
                                        &nbsp;* </asp:RequiredFieldValidator>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        Zip/Postal Code</td>
                                    <td>
                                        &nbsp;<asp:textbox id="PostCd" runat="server" />
                                        <asp:RequiredFieldValidator id="PostCdReqVal" runat="server" Font-Size="12" Font-Name="Verdana" Display="Static" ControlToValidate="PostCd">
                                        &nbsp;* </asp:RequiredFieldValidator>
                                    </td>
                                </tr>
                            </tbody>
                        </table>
                    </td>
                    <td>
                        &nbsp;&nbsp;&nbsp;</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>&nbsp;
                                        </td>
                                </tr>
                                <tr>
                                    <td>
                                        Residence</td>
                                    <td>
                                        &nbsp;<asp:textbox id="Phone" runat="server" /></td>
                                </tr>
                                <tr>
                                    <td>
                                        Mobile</td>
                                    <td>
                                        &nbsp;<asp:textbox id="Mobile1" runat="server" /></td>
                                </tr>
                                <tr>
                                    <td>
                                        Fax</td>
                                    <td>
                                        &nbsp;<asp:textbox id="Fax" runat="server" /></td>
                                </tr>
                                <tr>
                                    <td>
                                        E-mail</td>
                                    <td>
                                        &nbsp;<asp:textbox id="Email" runat="server" /></td>
                                </tr>
                                <tr>
                                    <td>
                                        Others</td>
                                    <td>
                                        &nbsp;<asp:textbox id="Other" runat="server" /></td>
                                </tr>
                                <tr>
                                    <td>
                                    </td>
                                    <td>
                                        &nbsp; <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>
 
Old March 16th, 2004, 12:57 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

You are trying to reference an object that doesn't exist.

It would seem that the page is not seeing the "Message" object. I don't see why this is however.

I'm concerned that you are missing a vital aspect of ASP.NET webform design. Is there a reason you are not using the standard asp webcontrols for your form elements? You are using old HTML input elements and making them runat=server. I suspect this might be part of the problem. Instead of the <span> you should be using <asp:label...> and similar for all the other page controls.

Peter
------------------------------------------------------
Work smarter, not harder.
 
Old March 16th, 2004, 09:28 PM
Authorized User
 
Join Date: Mar 2004
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, Peter. I'm really appreciate with your help and advice. But now i face another problem. I can't add the data into the database. can you help me? Thanks...

 
Old March 17th, 2004, 01:30 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Are you getting an error? I don't know how to help if I don't know what the problem is.
 
Old March 18th, 2004, 12:41 AM
Authorized User
 
Join Date: Mar 2004
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your concerned. I've settle this problem. The reason why I can't add data into the database is because I've put an invalid data into it and it comes out error. Anyway, thanks....






Similar Threads
Thread Thread Starter Forum Replies Last Post
open connection problem MunishBhatia SQL Server 2005 0 October 5th, 2007 04:24 AM
Regarding SqlCommand and.............. param99 SQL Server 2000 1 September 19th, 2006 07:29 AM
Regarding SqlCommand and.............. param99 SQL Language 0 September 19th, 2006 07:23 AM
open connection in another connection adityamadisetty SQL Server 2000 1 May 4th, 2006 04:56 AM
Problems with ADODB.Open zippo BOOK: Beginning ASP 3.0 1 April 22nd, 2005 05:16 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.