Solved

Executing a Stored Procedure

Posted on 2006-11-30
18
323 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
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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

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

Author Comment

by:Steven O'Neill
ID: 18080648
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now