Link to home
Start Free TrialLog in
Avatar of Steven O'Neill
Steven O'NeillFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Executing a Stored Procedure

I am using Visual Web Developer Express to develop a form in ASP.NET. I have a connection to a SQL 2000 database and within that database I have a Stored Procedure that performs a basic insert into my database (should I be using an SP for something as simply as this? There could be about 30 fields to be inserted).

How do I execute the SP within the aspx page? I have created a Sub that is culled upon a button click to execute the query but I just need a little assistance with the actual creation of the query. My connection string is defined in my web.config file and is called psybt_webInternal. Do I use this for setting up a connection to the database rather than creating a new conection?

Any assistance on this matter is greatly appreciated.
Avatar of Collindsouza
Collindsouza
Flag of United Kingdom of Great Britain and Northern Ireland image

in your web.config file add the following line within configuration node

<appSettings>
         <add key="AppConnection" value="CONNECTION STRING GOES HERE"/>
</appSettings>


on button click lets add a method called Execute_SP

protected void btnSave_Click(object sender, EventArgs e)
    {
        Execute_SP()
     }

protected void Execute_SP()
    {

           SqlConnection myConnection = new SqlConnection("ConfigurationSettings.AppSettings["AppConnection"]"); //Create Connection
            SqlCommand myCommand = myConnection.CreateCommand(); // Declare a Command
            myCommand.CommandText = "Stored Proc Name Goes here"; //Name the Stored proc
            myCommand.CommandTimeout = 120; // Connection Timeout

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

    1.        SqlParameter parameterName = new SqlParameter("@[Stored proc INPUT VARABLE]", SqlDbType.[DB TYPE Ex. INt, Varchar], [LENGTH:- 50 ]);
    2.        parameterName .Value = [Value for the Stored proc INPUT VARABLE] ;
    3.        myCommand.Parameters.Add(parameterName );
           
//Copy lines 1-3 for the number of input parameters

//Execure the Stored proc
 try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();

            }
            finally
            {
                myConnection.Close();
            }


FOR EXAMPLE:

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection("ConfigurationSettings.AppSettings["AppConnection"]"); //Created Connection
            SqlCommand myCommand = new SqlCommand("usp_AddNewContact", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandTimeout = 120;

            // Add Parameters to SPROC

            SqlParameter parameterContactID = new SqlParameter("@ContactID", SqlDbType.Int, 4);
            parameterContactID.Value = contactid;
            myCommand.Parameters.Add(parameterContactID);

             // Add Parameters to SPROC
            SqlParameter parameterFirstName = new SqlParameter("@FirstName", SqlDbType.NVarChar,255);
            parameterFirstName.Value = textBoxFirstName.Text;
            myCommand.Parameters.Add(parameterFirstName);


            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            finally
            {
                if (myConnection.State != ConnectionState.Closed)
                    myConnection.Close();
            }

 
Here you can find more details like Collindsouza provided.

http://sqljunkies.com/Article/4EC83A05-37CA-479D-94D9-04EAFE7705F9.scuk

Cheers!!!
A complete code to execute stored procedure: Including passing parameter and more ...

http://dotnetjunkies.com/Article/9AE62C44-3841-4687-B906-2F6D4A5A4622.dcik

Regards,
Mukesh
Hey sandip132, prajapati84

both of your links are pointing to the same article...
Avatar of Steven O'Neill

ASKER

Thanx for this guys. I'd found an article before I posted here which is in a similar style but I need some help in getting it sorted. In my web.config file I have the following connection info:

    <appSettings/>
    <connectionStrings>
        <add name="psybt_webInternal" connectionString="Data Source=SERVER;Initial Catalog=database;Persist Security Info=True;User ID=IDHERE;Password=PASSWORD" providerName="System.Data.SqlClient" />
    </connectionStrings>

Now I'm using an idea supplied by Collindsouza in that I've split my actual SPROC into a seperate Sub and it goes like this:

    Protected Sub Execute_SP()
        Dim cnBKTest As New Data.SqlClient.SqlConnection(psybt_webInternal)
        Dim cmdTest As New Data.SqlClient.SqlCommand("18-30UpdateMainTable", cnBKTest)

        cmdTest.CommandType = Data.CommandType.StoredProcedure

        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@EventID", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@EventID").Value = "1"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@intTitle", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@intTitle").Value = litTitle
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strForename", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strForename").Value = litFirstName
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strSurname", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strSurname").Value = litSecondName
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitPrimaryContact", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitPrimaryContact").Value = "1"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@dteDoB", Data.SqlDbType.DateTime, 8))
        cmdTest.Parameters("@dteDoB").Value = litDoB
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitDoNotContact", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitDoNotContact").Value = "0"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitCanHoldSensDate", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitCanHoldSensDate").Value = "1"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitDirectMailOptOut", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitDirectMailOptOut").Value = "0"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitEmailOptOut", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitEmailOptOut").Value = "0"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strPreferredComms", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@strPreferredComms").Value = litCommMethod
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strJobTitle", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strJobTitle").Value = litTitle
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strAddress01", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strAddress01").Value = litAddress01
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strAddress02", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strAddress02").Value = litAddress02
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strAddress03", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strAddress03").Value = litAddress03
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strTown", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strTown").Value = litTown
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strPostcode", Data.SqlDbType.VarChar, 10))
        cmdTest.Parameters("@strPostcode").Value = litPostcode
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strCountry", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strCountry").Value = "GBR"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strBusinessNo", Data.SqlDbType.VarChar, 11))
        cmdTest.Parameters("@strBusinessNo").Value = litWorkPhone
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strMobileNo", Data.SqlDbType.VarChar, 11))
        cmdTest.Parameters("@strMobileNo").Value = litMobile
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strFaxNo", Data.SqlDbType.VarChar, 11))
        cmdTest.Parameters("@strFaxNo").Value = "11"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strEmail", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strEmail").Value = litEmail
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strCompanyName", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strCompanyName").Value = litCompanyName
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strWebsite", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strWebsite").Value = "www"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitCanShareData", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitCanShareData").Value = "1"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitGender", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitGender").Value = litSex
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitPartnership", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitPartnership").Value = litRadio_Partnership
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strBusinessIdea", Data.SqlDbType.VarChar, 250))
        cmdTest.Parameters("@strBusinessIdea").Value = litBusinessIdea
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strSector", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@strSector").Value = litIndustry
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strEmployStatus", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@strEmployStatus").Value = litEmploy
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitCurrentlyRunning", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@bitCurrentlyRunning").Value = litRUinBusiness
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strEthnicity", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strEthnicity").Value = litRad_Ethnicity

        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitDisabled", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitDisabled").Value = litDisability
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strHowHeard", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strHowHeard").Value = "1"
       
        cnBKTest.Open()
        cmdTest.ExecuteNonQuery()
        cnBKTest.Close()
    End Sub

Now I'm getting an error @ the first line of the sub as it cannot handle the connection passed from my web.config file (BTW I'm writing this in VB.NET). How would I pass the correct info to my SPROC? I also have a full SPROC created in my database so do I need to use the Parameters.Add for each parameter entry? Can I simply use the parameters as I've already created the SPROC?

Thanx
Hi,
U can not directly use the Web.config file key as a parameter, instead u have to use

System.Configuration.ConfigurationSettings.AppSettings("psybt_webInternal")

So u r connection code will be like this:
Dim cnBKTest As New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("psybt_webInternal"))


All the best,

Raju P S
U1014440

change line  Dim cnBKTest As New Data.SqlClient.SqlConnection(psybt_webInternal)

TO

SqlConnection myConnection = new SqlConnection("ConfigurationSettings.AppSettings["psybt_webInternal"]"); //Create Connection

also make sure that right at the top of the page you have used the SolClient namespace
as follows

Import System.Data.SqlClient

oops!!! sorry that was in C#. since your code is in VB.NET

this is the correct syntax

Dim cnBKTest As SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("psybt_webInternal"))

also make sure that right at the top of the page you have used the SolClient namespace as follows:

Import System.Data.SqlClient


Thanx for this guys. I've used the following for my connection string:

        Dim cnBKTest As New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings("psybt_webInternal"))

So my code looks like:

        Dim cnBKTest As New Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings("psybt_webInternal"))
        Dim cmdTest As New Data.SqlClient.SqlCommand("18-30UpdateMainTable", cnBKTest)

        cmdTest.CommandType = Data.CommandType.StoredProcedure
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strHowHeard", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strHowHeard").Value = "1"
       
        cnBKTest.Open()
        cmdTest.ExecuteNonQuery()
        cnBKTest.Close()

However I'm now receiving this error:

System.InvalidOperationException: The ConnectionString property has not been initialized.

This error is pointing to the cnBKTest.Open() line. I'm not sure why. Can anyone advise?

Can anyone also tell me if I need the line:

        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strHowHeard", Data.SqlDbType.VarChar, 50))

I ask as I already have the SPROC created in the database and to me this code looks as though it's actually trying to create a parameter for the SPROC?
guess that you are passing an empty connection string.
See what contains MyConnection.ConnectionString or what returns System.Configuration.ConfigurationSettings.AppSettings("psybt_webInternal")
Sorry Collindsouza but I don't understand.

I would normally do a reponse.write(connstr) but I cannot do that. How do I check what's in the connectionstring within asp.net (remember I'm using VB?

I've also tried adding the connectionstring like this:

        Dim cnBKTest As New Data.SqlClient.SqlConnection("Data source=Data Source=server;Initial Catalog=database;Persist Security Info=True;User ID=user;Password=password")

but this hasn't worked either, in fact I get a message like this on screen:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I'm using a SQL 2000 database so I don't know why I'm getting this?
please try and do the following

add a new aspx page to the project

lets say Default2.aspx

then go to the design view of the page and... drag and drop SqlDatasource onto the web page ... right click on it and select Configure data source

Click new connection and in server name type (local).. and then select 'Use SQL Server Authentication' type username as 'sa' and leave password blank

then select pubs database from dropdown and see if your able to select it from the dropdown.. if you can then click test connection and then click ok...
then go back to the aspx page and select SqlDatasource and click on properties and see the connectionstring property and replace this  connectionstring property
with the one above

if your unable to see the pubs database in the dropdown when you select select 'Use SQL Server Authentication' then select 'Use Windows Authentication'
then select pubs database from dropdown and see if your able to select it from the dropdown.. if you can then click test connection and then click ok...
then go back to the aspx page and select SqlDatasource and click on properties and see the connectionstring property and replace this  connectionstring property
with the one above
Collinsouza

I built my connectionstring using this method already and if I remove all references to my SPROC and access the page via my browser then the lookups work fine (I have a number of dropdowns that lookup data from other tables in the same database). So I know my connection is working okay...just cannot figure out why it cannot do an update. In fact I've simplified the code to:

        Dim cnBKTest As New SqlConnection(ConfigurationManager.AppSettings("psybt_webInternal"))
        cnBKTest.Open()
        cnBKTest.Close()

So it should simply try opening the database and the close it (no requests for data or SPROCs) but it still fails at the open command.

I've put the pages up to my hosts site and it does get past this problem (and I really need it working there for public access so I'm happy with that at the moment), however the page is failing at:

        cmdTest.ExecuteNonQuery()

The page gives me this error:

System.InvalidCastException: Object must implement IConvertible

So I now assume my SPROC has the wrong parameter type within it or something. My complete SPROC code is:

        Dim cnBKTest As New SqlConnection(ConfigurationManager.AppSettings("psybt_webInternal"))
        Dim cmdTest As New SqlCommand("18-30UpdateMainTable", cnBKTest)

        cmdTest.CommandType = Data.CommandType.StoredProcedure

        cmdTest.Parameters.Add(New SqlParameter("@EventID", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@EventID").Value = 1
       
        cmdTest.Parameters.Add(New SqlParameter("@intTitle", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@intTitle").Value = litTitle
       
        cmdTest.Parameters.Add(New SqlParameter("@strForename", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strForename").Value = litFirstName
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strSurname", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strSurname").Value = litSecondName
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitPrimaryContact", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitPrimaryContact").Value = True
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@dteDoB", Data.SqlDbType.DateTime, 8))
        cmdTest.Parameters("@dteDoB").Value = litDoB
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitDoNotContact", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitDoNotContact").Value = False
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitCanHoldSensDate", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitCanHoldSensDate").Value = True
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitDirectMailOptOut", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitDirectMailOptOut").Value = False
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitEmailOptOut", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitEmailOptOut").Value = False
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@intPreferredComms", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@intPreferredComms").Value = litCommMethod
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strJobTitle", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strJobTitle").Value = litTitle
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strAddress01", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strAddress01").Value = litAddress01
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strAddress02", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strAddress02").Value = litAddress02
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strAddress03", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strAddress03").Value = litAddress03
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strTown", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strTown").Value = litTown
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strPostcode", Data.SqlDbType.VarChar, 10))
        cmdTest.Parameters("@strPostcode").Value = litPostcode
       
        'cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strCountry", Data.SqlDbType.VarChar, 50))
        'cmdTest.Parameters("@strCountry").Value = "GBR"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strPhoneNo", Data.SqlDbType.VarChar, 11))
        cmdTest.Parameters("@strPhoneNo").Value = litPhone

        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strBusinessNo", Data.SqlDbType.VarChar, 11))
        cmdTest.Parameters("@strBusinessNo").Value = litWorkPhone
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strMobileNo", Data.SqlDbType.VarChar, 11))
        cmdTest.Parameters("@strMobileNo").Value = litMobile
       
        'cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strFaxNo", Data.SqlDbType.VarChar, 11))
        'cmdTest.Parameters("@strFaxNo").Value = "11"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strEmail", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strEmail").Value = litEmail
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strCompanyName", Data.SqlDbType.VarChar, 100))
        cmdTest.Parameters("@strCompanyName").Value = litCompanyName
       
        'cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strWebsite", Data.SqlDbType.VarChar, 100))
        'cmdTest.Parameters("@strWebsite").Value = "www"
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitCanShareData", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitCanShareData").Value = True
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitGender", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitGender").Value = litSex
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitPartnership", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitPartnership").Value = litRadio_Partnership
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strBusinessIdea", Data.SqlDbType.VarChar, 250))
        cmdTest.Parameters("@strBusinessIdea").Value = litBusinessIdea
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@intSector", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@intSector").Value = litIndustry
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@intEmployStatus", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@intEmployStatus").Value = litEmploy
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitCurrentlyRunning", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitCurrentlyRunning").Value = litRUinBusiness
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@strEthnicity", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strEthnicity").Value = litRad_Ethnicity

        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitDisabled", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitDisabled").Value = litDisability
       
        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@intHowHeard", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@intHowHeard").Value = 1
       
        cnBKTest.Open()
        cmdTest.ExecuteNonQuery()
        cnBKTest.Close()

I have a number of bit fields here and was wondering if it was falling over at them. Should my parameter be made up like so:

        cmdTest.Parameters.Add(New Data.SqlClient.SqlParameter("@bitCanShareData", Data.SqlDbType.Bit, 1))
        cmdTest.Parameters("@bitCanShareData").Value = True

So if the Value is TRUE it would appear as a 1 or checked in the database?

Thanx for bearing with me thru this.
Okay I've figured out the Bit parts but the others may be cauing me a problem. I have all the data being passed as Literals at the end of my form input and they are displayed on screen during my construction phase so I can see what values are being passed but if I have the following:

        cmdTest.Parameters.Add(New SqlParameter("@EventID", Data.SqlDbType.Int, 4))
        cmdTest.Parameters("@EventID").Value = 1

This is obviously an integar but how do I pass the value to the parameter? Do I enclode it in any way (say with quotes)?

Similarly I have strings (varchars) as well:

        cmdTest.Parameters.Add(New SqlParameter("@strForename", Data.SqlDbType.VarChar, 50))
        cmdTest.Parameters("@strForename").Value = litFirstName

Do I also enclose these in some way as the value (litFirstName) is the id of the Literal?

Thanx
ASKER CERTIFIED SOLUTION
Avatar of Collindsouza
Collindsouza
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had to go thtu my query one value at a time and it was because I was trying to pass the literals to the query rather than the expected values. All working now thanx guys...however I have a new problem that I'm going to put on for another 500 points so anyone who can help with some codebehind problems...
Thank you for the points....

post your question and alsp post the link to that question here..so that i can look at it right away..  we are here to help...