Steven O'Neill
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.
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.
Here you can find more details like Collindsouza provided.
http://sqljunkies.com/Article/4EC83A05-37CA-479D-94D9-04EAFE7705F9.scuk
Cheers!!!
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
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...
both of your links are pointing to the same article...
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=PASSWOR D" 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.SqlConnecti on(psybt_w ebInternal )
Dim cmdTest As New Data.SqlClient.SqlCommand( "18-30Upda teMainTabl e", cnBKTest)
cmdTest.CommandType = Data.CommandType.StoredPro cedure
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@EventI D", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@Event ID").Value = "1"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@intTit le", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@intTi tle").Valu e = litTitle
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strFor ename", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strFo rename").V alue = litFirstName
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strSur name", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strSu rname").Va lue = litSecondName
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitPri maryContac t", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitPr imaryConta ct").Value = "1"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@dteDoB ", Data.SqlDbType.DateTime, 8))
cmdTest.Parameters("@dteDo B").Value = litDoB
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitDoN otContact" , Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitDo NotContact ").Value = "0"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitCan HoldSensDa te", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitCa nHoldSensD ate").Valu e = "1"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitDir ectMailOpt Out", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitDi rectMailOp tOut").Val ue = "0"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitEma ilOptOut", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitEm ailOptOut" ).Value = "0"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strPre ferredComm s", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@strPr eferredCom ms").Value = litCommMethod
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strJob Title", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strJo bTitle").V alue = litTitle
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strAdd ress01", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strAd dress01"). Value = litAddress01
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strAdd ress02", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strAd dress02"). Value = litAddress02
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strAdd ress03", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strAd dress03"). Value = litAddress03
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strTow n", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strTo wn").Value = litTown
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strPos tcode", Data.SqlDbType.VarChar, 10))
cmdTest.Parameters("@strPo stcode").V alue = litPostcode
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strCou ntry", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strCo untry").Va lue = "GBR"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strBus inessNo", Data.SqlDbType.VarChar, 11))
cmdTest.Parameters("@strBu sinessNo") .Value = litWorkPhone
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strMob ileNo", Data.SqlDbType.VarChar, 11))
cmdTest.Parameters("@strMo bileNo").V alue = litMobile
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strFax No", Data.SqlDbType.VarChar, 11))
cmdTest.Parameters("@strFa xNo").Valu e = "11"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strEma il", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strEm ail").Valu e = litEmail
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strCom panyName", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strCo mpanyName" ).Value = litCompanyName
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strWeb site", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strWe bsite").Va lue = "www"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitCan ShareData" , Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitCa nShareData ").Value = "1"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitGen der", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitGe nder").Val ue = litSex
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitPar tnership", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitPa rtnership" ).Value = litRadio_Partnership
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strBus inessIdea" , Data.SqlDbType.VarChar, 250))
cmdTest.Parameters("@strBu sinessIdea ").Value = litBusinessIdea
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strSec tor", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@strSe ctor").Val ue = litIndustry
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strEmp loyStatus" , Data.SqlDbType.Int, 4))
cmdTest.Parameters("@strEm ployStatus ").Value = litEmploy
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitCur rentlyRunn ing", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@bitCu rrentlyRun ning").Val ue = litRUinBusiness
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strEth nicity", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strEt hnicity"). Value = litRad_Ethnicity
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitDis abled", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitDi sabled").V alue = litDisability
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strHow Heard", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strHo wHeard").V alue = "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
<appSettings/>
<connectionStrings>
<add name="psybt_webInternal" connectionString="Data Source=SERVER;Initial Catalog=database;Persist Security Info=True;User ID=IDHERE;Password=PASSWOR
</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.SqlConnecti
Dim cmdTest As New Data.SqlClient.SqlCommand(
cmdTest.CommandType = Data.CommandType.StoredPro
cmdTest.Parameters.Add(New
cmdTest.Parameters("@Event
cmdTest.Parameters.Add(New
cmdTest.Parameters("@intTi
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strFo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strSu
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitPr
cmdTest.Parameters.Add(New
cmdTest.Parameters("@dteDo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitDo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitCa
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitDi
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitEm
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strPr
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strJo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strAd
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strAd
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strAd
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strTo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strPo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strCo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strBu
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strMo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strFa
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strEm
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strCo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strWe
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitCa
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitGe
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitPa
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strBu
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strSe
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strEm
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitCu
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strEt
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitDi
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strHo
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.Confi gurationSe ttings.App Settings(" psybt_webI nternal")
So u r connection code will be like this:
Dim cnBKTest As New Data.SqlClient.SqlConnecti on(System. Configurat ion.Config urationSet tings.AppS ettings("p sybt_webIn ternal"))
All the best,
Raju P S
U can not directly use the Web.config file key as a parameter, instead u have to use
System.Configuration.Confi
So u r connection code will be like this:
Dim cnBKTest As New Data.SqlClient.SqlConnecti
All the best,
Raju P S
U1014440
change line Dim cnBKTest As New Data.SqlClient.SqlConnecti on(psybt_w ebInternal )
TO
SqlConnection myConnection = new SqlConnection("Configurati onSettings .AppSettin gs["psybt_ webInterna l"]"); //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
change line Dim cnBKTest As New Data.SqlClient.SqlConnecti
TO
SqlConnection myConnection = new SqlConnection("Configurati
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.Confi guration.C onfigurati onSettings .AppSettin gs("psybt_ webInterna l"))
also make sure that right at the top of the page you have used the SolClient namespace as follows:
Import System.Data.SqlClient
this is the correct syntax
Dim cnBKTest As SqlConnection(System.Confi
also make sure that right at the top of the page you have used the SolClient namespace as follows:
Import System.Data.SqlClient
ASKER
Thanx for this guys. I've used the following for my connection string:
Dim cnBKTest As New Data.SqlClient.SqlConnecti on(System. Configurat ion.Config urationMan ager.AppSe ttings("ps ybt_webInt ernal"))
So my code looks like:
Dim cnBKTest As New Data.SqlClient.SqlConnecti on(System. Configurat ion.Config urationMan ager.AppSe ttings("ps ybt_webInt ernal"))
Dim cmdTest As New Data.SqlClient.SqlCommand( "18-30Upda teMainTabl e", cnBKTest)
cmdTest.CommandType = Data.CommandType.StoredPro cedure
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strHow Heard", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strHo wHeard").V alue = "1"
cnBKTest.Open()
cmdTest.ExecuteNonQuery()
cnBKTest.Close()
However I'm now receiving this error:
System.InvalidOperationExc eption: 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.SqlParamete r("@strHow Heard", 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?
Dim cnBKTest As New Data.SqlClient.SqlConnecti
So my code looks like:
Dim cnBKTest As New Data.SqlClient.SqlConnecti
Dim cmdTest As New Data.SqlClient.SqlCommand(
cmdTest.CommandType = Data.CommandType.StoredPro
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strHo
cnBKTest.Open()
cmdTest.ExecuteNonQuery()
cnBKTest.Close()
However I'm now receiving this error:
System.InvalidOperationExc
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
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.ConnectionStr ing or what returns System.Configuration.Confi gurationSe ttings.App Settings(" psybt_webI nternal")
See what contains MyConnection.ConnectionStr
ASKER
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.SqlConnecti on("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?
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.SqlConnecti
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
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
ASKER
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(Configuratio nManager.A ppSettings ("psybt_we bInternal" ))
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.InvalidCastExceptio n: 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(Configuratio nManager.A ppSettings ("psybt_we bInternal" ))
Dim cmdTest As New SqlCommand("18-30UpdateMai nTable", cnBKTest)
cmdTest.CommandType = Data.CommandType.StoredPro cedure
cmdTest.Parameters.Add(New SqlParameter("@EventID", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@Event ID").Value = 1
cmdTest.Parameters.Add(New SqlParameter("@intTitle", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@intTi tle").Valu e = litTitle
cmdTest.Parameters.Add(New SqlParameter("@strForename ", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strFo rename").V alue = litFirstName
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strSur name", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strSu rname").Va lue = litSecondName
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitPri maryContac t", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitPr imaryConta ct").Value = True
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@dteDoB ", Data.SqlDbType.DateTime, 8))
cmdTest.Parameters("@dteDo B").Value = litDoB
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitDoN otContact" , Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitDo NotContact ").Value = False
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitCan HoldSensDa te", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitCa nHoldSensD ate").Valu e = True
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitDir ectMailOpt Out", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitDi rectMailOp tOut").Val ue = False
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitEma ilOptOut", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitEm ailOptOut" ).Value = False
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@intPre ferredComm s", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@intPr eferredCom ms").Value = litCommMethod
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strJob Title", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strJo bTitle").V alue = litTitle
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strAdd ress01", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strAd dress01"). Value = litAddress01
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strAdd ress02", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strAd dress02"). Value = litAddress02
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strAdd ress03", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strAd dress03"). Value = litAddress03
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strTow n", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strTo wn").Value = litTown
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strPos tcode", Data.SqlDbType.VarChar, 10))
cmdTest.Parameters("@strPo stcode").V alue = litPostcode
'cmdTest.Parameters.Add(Ne w Data.SqlClient.SqlParamete r("@strCou ntry", Data.SqlDbType.VarChar, 50))
'cmdTest.Parameters("@strC ountry").V alue = "GBR"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strPho neNo", Data.SqlDbType.VarChar, 11))
cmdTest.Parameters("@strPh oneNo").Va lue = litPhone
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strBus inessNo", Data.SqlDbType.VarChar, 11))
cmdTest.Parameters("@strBu sinessNo") .Value = litWorkPhone
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strMob ileNo", Data.SqlDbType.VarChar, 11))
cmdTest.Parameters("@strMo bileNo").V alue = litMobile
'cmdTest.Parameters.Add(Ne w Data.SqlClient.SqlParamete r("@strFax No", Data.SqlDbType.VarChar, 11))
'cmdTest.Parameters("@strF axNo").Val ue = "11"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strEma il", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strEm ail").Valu e = litEmail
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strCom panyName", Data.SqlDbType.VarChar, 100))
cmdTest.Parameters("@strCo mpanyName" ).Value = litCompanyName
'cmdTest.Parameters.Add(Ne w Data.SqlClient.SqlParamete r("@strWeb site", Data.SqlDbType.VarChar, 100))
'cmdTest.Parameters("@strW ebsite").V alue = "www"
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitCan ShareData" , Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitCa nShareData ").Value = True
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitGen der", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitGe nder").Val ue = litSex
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitPar tnership", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitPa rtnership" ).Value = litRadio_Partnership
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strBus inessIdea" , Data.SqlDbType.VarChar, 250))
cmdTest.Parameters("@strBu sinessIdea ").Value = litBusinessIdea
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@intSec tor", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@intSe ctor").Val ue = litIndustry
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@intEmp loyStatus" , Data.SqlDbType.Int, 4))
cmdTest.Parameters("@intEm ployStatus ").Value = litEmploy
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitCur rentlyRunn ing", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitCu rrentlyRun ning").Val ue = litRUinBusiness
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@strEth nicity", Data.SqlDbType.VarChar, 50))
cmdTest.Parameters("@strEt hnicity"). Value = litRad_Ethnicity
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@bitDis abled", Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitDi sabled").V alue = litDisability
cmdTest.Parameters.Add(New Data.SqlClient.SqlParamete r("@intHow Heard", Data.SqlDbType.Int, 4))
cmdTest.Parameters("@intHo wHeard").V alue = 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.SqlParamete r("@bitCan ShareData" , Data.SqlDbType.Bit, 1))
cmdTest.Parameters("@bitCa nShareData ").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.
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(Configuratio
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.InvalidCastExceptio
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(Configuratio
Dim cmdTest As New SqlCommand("18-30UpdateMai
cmdTest.CommandType = Data.CommandType.StoredPro
cmdTest.Parameters.Add(New
cmdTest.Parameters("@Event
cmdTest.Parameters.Add(New
cmdTest.Parameters("@intTi
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strFo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strSu
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitPr
cmdTest.Parameters.Add(New
cmdTest.Parameters("@dteDo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitDo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitCa
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitDi
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitEm
cmdTest.Parameters.Add(New
cmdTest.Parameters("@intPr
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strJo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strAd
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strAd
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strAd
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strTo
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strPo
'cmdTest.Parameters.Add(Ne
'cmdTest.Parameters("@strC
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strPh
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strBu
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strMo
'cmdTest.Parameters.Add(Ne
'cmdTest.Parameters("@strF
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strEm
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strCo
'cmdTest.Parameters.Add(Ne
'cmdTest.Parameters("@strW
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitCa
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitGe
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitPa
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strBu
cmdTest.Parameters.Add(New
cmdTest.Parameters("@intSe
cmdTest.Parameters.Add(New
cmdTest.Parameters("@intEm
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitCu
cmdTest.Parameters.Add(New
cmdTest.Parameters("@strEt
cmdTest.Parameters.Add(New
cmdTest.Parameters("@bitDi
cmdTest.Parameters.Add(New
cmdTest.Parameters("@intHo
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
cmdTest.Parameters("@bitCa
So if the Value is TRUE it would appear as a 1 or checked in the database?
Thanx for bearing with me thru this.
ASKER
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("@Event ID").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("@strFo rename").V alue = litFirstName
Do I also enclose these in some way as the value (litFirstName) is the id of the Literal?
Thanx
cmdTest.Parameters.Add(New
cmdTest.Parameters("@Event
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
cmdTest.Parameters("@strFo
Do I also enclose these in some way as the value (litFirstName) is the id of the Literal?
Thanx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
<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("Configurati
SqlCommand myCommand = myConnection.CreateCommand
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.StoredProcedur
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(p
//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("Configurati
SqlCommand myCommand = new SqlCommand("usp_AddNewCont
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedur
myCommand.CommandTimeout = 120;
// Add Parameters to SPROC
SqlParameter parameterContactID = new SqlParameter("@ContactID",
parameterContactID.Value = contactid;
myCommand.Parameters.Add(p
// Add Parameters to SPROC
SqlParameter parameterFirstName = new SqlParameter("@FirstName",
parameterFirstName.Value = textBoxFirstName.Text;
myCommand.Parameters.Add(p
try
{
myConnection.Open();
myCommand.ExecuteNonQuery(
}
finally
{
if (myConnection.State != ConnectionState.Closed)
myConnection.Close();
}