Solved

Executing a Stored Procedure

Posted on 2006-11-30
18
330 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Steven O'Neill
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
18 Comments
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18050334
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();
            }

 
0
 
LVL 12

Expert Comment

by:sandip132
ID: 18050573
Here you can find more details like Collindsouza provided.

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

Cheers!!!
0
 
LVL 5

Expert Comment

by:prajapati84
ID: 18051461
A complete code to execute stored procedure: Including passing parameter and more ...

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

Regards,
Mukesh
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:Collindsouza
ID: 18051497
Hey sandip132, prajapati84

both of your links are pointing to the same article...
0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 18051908
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
0
 
LVL 5

Expert Comment

by:rajups
ID: 18052236
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
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18052862
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

0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18052873
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


0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 18057894
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?
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18058746
guess that you are passing an empty connection string.
See what contains MyConnection.ConnectionString or what returns System.Configuration.ConfigurationSettings.AppSettings("psybt_webInternal")
0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 18062768
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?
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18063044
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
0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 18063212
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.
0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 18063367
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
0
 
LVL 5

Accepted Solution

by:
Collindsouza earned 500 total points
ID: 18063914
>>The page gives me this error:

>>System.InvalidCastException: Object must implement IConvertible

Based on my experience, this exception means that there is a conversion problem and the SqlClient managed provider does not support IConvertable in order to perform the conversion. So, what this error indicates is that when the query was executed one (or more) parameter has failed to bind with the provided data type. To narrow down the problem, I suggest that you can create a new Stored Procedure without any parameter and see if it works. And then you can add the parameters one by one to the Stored Procedure to check which one causes the problem.

>>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

NO.. you dont put the values in Quotes.. remember that @EventID is declared as a parameter of type int..

all SqlParameter datatpyes that are declared as Vrachar.. must be supplied with string values only..
 so make sure you do that..

hence for SqlParameters of type string make sure that you passs string data types only..

0
 
LVL 2

Author Comment

by:Steven O'Neill
ID: 18080529
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...
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18080558
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...
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question