I am unable to set the value of my SqlParameter unless it is to a string.sqlCmd.Parameters.Add('@myParameter', SqlDbType.Bit).Value := false;
Setting it to false or 0 results in error: "Incompatible types: 'Object' and 'Boolean' (or 'Integer')"
I've tried to cast them as objects and that didn't work. I don't understand why something like 'false' (as a string) will.
FYI: I'm using Delphi.NET and hating it.
I'm using parameters to record into database like:
SqlParameter paramId_empresa = new SqlParameter("@Id_empresa", SqlDbType.Int, 4);
paramId_empresa.Value = idEmpresa;
myCommand.Parameters.Add(paramId_empresa);
And set sql server field to allow null values and default value as (null)
Trouble is I'm getting an Input string was not in a correct format. when the field is blank
Line 182:Line 183: myConnection.Open();Line 184: myCommand.ExecuteNonQuery();Line 185: myConnection.Close();Line 186: }
Thanks a lot for any help
Im trying to execute the following:mySqlCmd.Parameters.Add("@Parent_ID", SqlDbType.Int).Value = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : myParentID); However i get an error that Null cant be converted to Int. Is there any way i can uyse the SqlParameters approach but pass in a Null value for the field or must i wrap the entire connection within a IF/ELSE statement one containing a hard-coded NULL within the query and the other as a standard parameter with a proper int value? Thanks
View 1 Replies View RelatedHi I am writing an app in flash which needs to hook up to MS SQL via asp.I need the code below to pass the var (ptodaysDate) to the sql statement. I can hard code it in and it works great but I really need to pass the var from flash.Pulling my hair out here, not much left to go.Any help greatly appreciated.---------------------------------------------- [WebMethod] public Schedule[] getSchedule(int ptodaysDate) { SqlDataAdapter adpt = new SqlDataAdapter("SELECT scheduleID, roomName, eventType,unitName,groupName,staffName,staffName2,theDate,theEnd FROM tb_schedule Where theDate >= @rtodaysDate", connString); SqlParameter rtodaysDate = new SqlParameter("@rtodaysDate", ptodaysDate); DataSet ds = new DataSet(); ArrayList al = new ArrayList(); adpt.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) { Schedule obj = new Schedule(); obj.scheduleID = (int)row["scheduleID"]; obj.roomName = (string)row["roomName"]; obj.eventType = (string)row["eventType"]; obj.unitName = (string)row["unitName"]; obj.groupName = (string)row["groupName"]; obj.staffName = (string)row["staffName"]; obj.staffName2 = (string)row["staffName2"]; obj.theDate = (string)row["theDate"]; obj.theEnd = (string)row["theEnd"]; al.Add(obj); } Schedule[] outArray = (Schedule[])al.ToArray(typeof(Schedule)); return outArray; } public class Schedule { public int scheduleID; public string roomName; public string eventType; public string unitName; public string groupName; public string staffName; public string staffName2; public string theDate; public string theEnd; }
View 2 Replies View RelatedThe FULL System.Data.SqlDbType reference seems unnecessary? Is there something I am doing wrong or something that should be added to Web.Config? If I try ONLY ",SqlDbType," it will not compile?
MY CODE: SqlParameter AName;
AName = myCommand.Parameters.Add("@AName",System.Data.SqlDbType.VarChar,50);
Hi,
I am getting the following error:
The SqlParameter with ParameterName '@pk' is already contained by another SqlParameterCollection
If I could work out what code to post I would, but I can say that I am managing my Sql data in my code by caching small arrays of SqlParameter objects as pulled from the database. If I need to update the DB I change the cached SqlParameter and re-insert it. If I perform a SELECT I check the cache first to see if I already have the SqlParameter.
However, currently, I am experiencing the above error when performing a select, then later an update, followed by another update.
Would I be correct in saying that a SqlParameter object can only be used once for a database operation and should be discarded? Would I be correct if I said that the SqlCommand object should be discarded? I am barking up the wrong tree entirely?
Distressed in DBLand,
Matt.
Is it possible to have a SqlParameter setup as an Output, and get it to return @@Identity without using a stored procedure?
I have an INSERT statement, written as TEXT in my code (at the time being, I cannot create stored procedures). I'm trying to find out how to return the created IDENTITY that was generated.
Can someone please explain to me how this works?
Thanks.
hi
I am creating a stroed procedure which suppose to update an image column of the given row. I have created the same procedures before which worked fine with texts and numbers but not with Images.
this procedure get table name,row Id, column name of the image, and image data. like:
Create PROCEDURE [dbo].[spImageUpd]
(
@TableName varchar(100),
@Id int,
@ImageColName nvarchar(100),
@ImageData image
)
begin
??????
end
I tryed to use
set @sql='update '+ @TableName+ ' set '+@ImageColName + '=' + @ImageDt' + ' where Id='+ cast(@Id as nvarchar(10))
EXEC sp_executesql
@query = @sql,
@params = N'@ImageDt">N'@ImageDt Image',
@ImageDt = @ImageData
but sp_executesql just accept text as params. is it possible to fix this problem?
thank you in advance
regards
I am just learning ASP.net, been reading over data grids but I am having a tough time understanding what the parameter line would be used for. I have read the MSDN regarding parameters, they seem to make sense. But the whole System.Byte(0) I cannot understand.
sqlUpdateCommand1 = new SqlCommand();
sqlUpdateCommand1.Parameters.Add(new SqlParameter("@Original_SSN", SqlDbType.VarChar, 11, ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "SSN", DataRowVersion.Original, null));
Any pointers or tutorials regarding this is much appreciated.
Thanks,
Rishi Dhupar
Below I have pasted the error message, hidden form fields, function code and even the stored procedure code. I'd be most grateful if anyone can show me the error of my ways!
Thanks,
theSpike
I'm calling a stored procedure and besides several varchar fields, I'm passing two integer values (employeeID and businessID). These values come from hidden fields on a form and it's the businessID that throws the format error. I call the same stored procedure for both insert and update (the SP evaluates employeeID to decide btwn insert/update) and the insert works perfectly, but the update throws the format exception error. The only difference is that the employeeID is 0 (zero) for an insert and > 0 when it's updating. I strikes me as particularly bizzare that the line that throws the exception is the businessID, not the employeeID because the businessID is the same value for both insert and update. It's the employeeID that is subject to change.
The error:
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 147: //businessID
Line 148: SqlParameter businessID = new SqlParameter("@businessID",SqlDbType.Int,4);
Line 149: businessID.Value = Convert.ToInt32(hdnBusinessID.Value);
Line 150: businessID.Direction = ParameterDirection.Input;
Line 151: theCommand.Parameters.Add(businessID);
I'm calling the SAME function for the insert and update, the same hiddent form fields exist
<input name="hdnBusinessID" id="hdnBusinessID" type="hidden" value="1" />
<input name="hdnEmployeeID" id="hdnEmployeeID" type="hidden" value="7" />
Here's the function that get's called:
void btnAddEmployee_Click(object sender, EventArgs e) {
int intNewEmployeeID;
string connectionstring = ConfigurationSettings.AppSettings["ConnectionString"];
SqlCommand theCommand = new SqlCommand();
theCommand.CommandText = "AMS_EmployeeSave";
theCommand.Connection = new SqlConnection(connectionstring);
theCommand.CommandType = CommandType.StoredProcedure;
// Add parameters to SqlCommand
//employeeID
SqlParameter empID = new SqlParameter("@employeeID",SqlDbType.Int,4);
empID.Value = Convert.ToInt32(hdnEmployeeID.Value);
empID.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(empID);
//businessID
SqlParameter businessID = new SqlParameter("@businessID",SqlDbType.Int,4);
businessID.Value = Convert.ToInt32(hdnBusinessID.Value);
businessID.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(businessID);
//title
SqlParameter title = new SqlParameter("@title",SqlDbType.Char,5);
title.Value = txtTitle.Text.ToString();
title.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(title);
//firstname
SqlParameter firstName = new SqlParameter("@firstName",SqlDbType.VarChar,25);
firstName.Value = txtFirstName.Text.ToString();
firstName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(firstName);
//middleName
SqlParameter middleName = new SqlParameter("@middleName",SqlDbType.VarChar,15);
middleName.Value = txtMiddleName.Text.ToString();
middleName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(middleName);
//LastName
SqlParameter lastName = new SqlParameter("@lastName",SqlDbType.VarChar,25);
lastName.Value = txtLastName.Text.ToString();
lastName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(lastName);
//suffix
SqlParameter suffix = new SqlParameter("@suffix",SqlDbType.Char,5);
suffix.Value = txtSuffix.Text.ToString();
suffix.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(suffix);
//email
SqlParameter email = new SqlParameter("@email",SqlDbType.VarChar,80);
email.Value = txtEmail.Text.ToString();
email.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(email);
//password
SqlParameter password = new SqlParameter("@password",SqlDbType.Char,20);
password.Value = txtPassword.Text.ToString();
password.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(password);
//newEmployeeID output parameter
SqlParameter newEmployeeID = new SqlParameter("@newEmployeeID",SqlDbType.Int,4);
newEmployeeID.Direction = ParameterDirection.Output;
theCommand.Parameters.Add(newEmployeeID);
theCommand.Connection.Open();
theCommand.ExecuteNonQuery();
intNewEmployeeID = Convert.ToInt16(theCommand.Parameters["@newEmployeeID"].Value);
theCommand.Connection.Close();
if(intNewEmployeeID == -1){
lblSaveResult.Text = "There was a problem saving the employee information";
}
else if(hdnEmployeeID.Value == "0"){
lblSaveResult.Text = "New employee successfully added";
}
else{
lblSaveResult.Text = "Employee successfully updated";
}
lblSaveResult.Visible = true;
}
STORED PROCEDURE
CREATE PROCEDURE [dbo].[xxxx]
(@employeeID int,
@businessID int,
@title char(5),
@firstName varchar(25),
@middleName varchar(15),
@lastName varchar(25),
@suffix char(5),
@email varchar(80),
@password char(20),
@newEmployeeID int output)
AS
declare @alreadyExists int
-- passed in ID = 0 means they're adding a new employee so do insert
if @employeeID = 0
begin
select @alreadyExists = count(*) from dbo.AMS_employee where emp_email = @email
if @alreadyExists = 0
begin
insert into AMS_employee (emp_businessID, emp_title, emp_firstName, emp_middleName, emp_lastName, emp_suffix, emp_email, emp_password)
values (@businessID, @title, @firstName, @middleName, @lastName, @suffix, @email, @password)
select @newEmployeeID = @@identity
end
else -- there is already an employee with that email address
select @newEmployeeID = -1
end
-- passed in ID > 0 means they're updating existing employee so do update
else if @employeeID > 0
begin
update AMS_employee set
emp_title = @title,
emp_firstName = @firstName,
emp_middleName = @middleName,
emp_lastName = @lastName,
emp_suffix = @suffix,
emp_email = @email
where emp_ID = @employeeID
select @newEmployeeID = @employeeID
-- only update the password if it one was provided
if len(rtrim(@password)) > 0
begin
update AMS_employee set emp_password = @password where emp_ID = @employeeID
end
end
GO
Hi,
i don't know if this is the right forum to post to, but here i go:
I'm having a problem...
I need to delete several records having as criteria their pk.
The deletion is made using SqlParameter in the code and a stored procedure on server.
as far as i know, i can do this, either using a IN list:
- build and pass the list of id's from code to the sp:
delete from tbl where attachment.id in (1,2,3)
OR
- execute a simple delete several times.
So far i tried first option, but i get errors in converting the list to ints; that is what the sql engine says.
I prefer this option because i think is faster and requires less roundtrips to the sql server.
My questions are:
1. how do i build a sane IN list?
2. is it worth to use a delete in a loop? the records that must be deleted are 5 at most, per job.
1 job can have [0..5] attachments.
Thank you,
Daniel
We've got an app that is in final testing so we can't go around forcing SqlDbType.VarChar. Is there any way to make SqlParameter default to varchar if you don't explicitly set a type for a string value? It's bad for our db index usage because they're all varchar and nvarchar forces a convert_implicit messing up performance.
View 1 Replies View Related Stored procedure:1 ALTER PROCEDURE [dbo].[insert_DagVerslag]
2 -- Add the parameters for the stored procedure here
3 @serverID int,
4 @datum datetime,
5 @ID int OUTPUT
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11 -- Insert statements for procedure here
12 BEGIN TRANSACTION
13 INSERT Log ( serverID, datum)
14 VALUES( @serverID, @datum)
15 COMMIT TRANSACTION
16 SET @ID = @@identity
17 END
18
Method who is calling the stored procedure and causes the error1 public void AddDagVerslag(Historiek historiek)
2 {
3 SqlConnection oConn = new SqlConnection(_connectionString);
4 string strSql = "insert_DagVerslag";
5 SqlCommand oCmd = new SqlCommand(strSql, oConn);
6 oCmd.CommandType = CommandType.StoredProcedure;
7 oCmd.Parameters.Add(new SqlParameter("@serverID", SqlDbType.Int)).Value = historiek.ServerID;
8 oCmd.Parameters.Add(new SqlParameter("@datum", SqlDbType.DateTime)).Value = historiek.Datum;
9 oCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
10
11 oCmd.Parameters["@ID"].Direction = ParameterDirection.Output;
12
13 try
14 {
15 oConn.Open();
16 int rowsAffected = oCmd.ExecuteNonQuery();
17 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek");
18 oCmd.Parameters.Clear();
19 historiek.HistoriekID = System.Convert.ToInt32(oCmd.Parameters["@ID"].Value);
20
21 foreach (HistoriekDetail hDetail in historiek.LstHistoriekDetails)
22 {
23 AddDagVerslagCategorie(historiek.HistoriekID, hDetail);
24 }
25 }
26 catch (Exception ex)
27 {
28 throw new ApplicationException("Fout toevoegen historiek : " + ex.Message);
29 }
30 finally
31 {
32 if (oConn.State == ConnectionState.Open) oConn.Close();
33 }
34 }
Whats going wrong, i've added the ID parameter my ParameterCollection... so why cant it be found
Hello All,
I'm having a problem tracking down why my app is not instantiating a parameter in a sqldatasource, when I'm expecting it to do so.
When the app fails, this info is displayed:
Server Error in '/' Application.--------------------------------------------------------------------------------
An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection.
Source Error:
Line 30: Private Sub SqlDataSource1_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.DeletingLine 31: e.Command.Parameters.Item("@createdby").Value = CType(Membership.GetUser.ProviderUserKey, Guid)Line 32: Line 33: End Sub
Source File: ...filename... Line: 31
Stack Trace:
[IndexOutOfRangeException: An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection.] System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName) +713105 System.Data.Common.DbParameterCollection.get_Item(String parameterName) +7 diabetes.reading.SqlDataSource1_Deleting(Object sender, SqlDataSourceCommandEventArgs e) in C:Documents and SettingsAlbertMy DocumentsVisual Studio 2005Projectsdiabetesdiabetesdiabetes
eading.aspx.vb:31 System.Web.UI.WebControls.SqlDataSourceView.OnDeleting(SqlDataSourceCommandEventArgs e) +114 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +682 System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +75 System.Web.UI.WebControls.FormView.HandleDelete(String commandArg) +839 System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +556 System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Here's the datasource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DiabetesOne %>" DeleteCommand="reading_delete" DeleteCommandType="StoredProcedure" > <DeleteParameters> <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" /> <asp:Parameter Name="rid" Type="Int32" /> <asp:Parameter Name="createdby" Type="Object" /> </DeleteParameters> </asp:SqlDataSource>
Here's the beginning of the stored procedure showing the parameters:
CREATE PROCEDURE [dbo].[reading_delete] @rid int,@createdby sql_variantASset nocount on;begin try...the procedural code...
Here's debugger capture, pointing to same issue:
System.IndexOutOfRangeException was unhandled by user code Message="An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection." Source="System.Data" StackTrace: at System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName) at System.Data.Common.DbParameterCollection.get_Item(String parameterName) at diabetes.reading.SqlDataSource1_Deleting(Object sender, SqlDataSourceCommandEventArgs e) in C:... at System.Web.UI.WebControls.SqlDataSourceView.OnDeleting(SqlDataSourceCommandEventArgs e) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) at System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback)
I've inspected the sqldatasourcecommandeventargs command.parameters in the deleting method during debugging and could not find the parameter in the array, although I "think" it should be because it's explicitly stated in the sqldatasource parameters declarations. I see two parameters in the array, @RETURN_VALUE and @RID, both of which are expected and coincide with the declaration in the stored procedure and the sqldatasource.
Hi,What I am trying to do is to get the new ID for every record is inserted into a table. I have a For...Each statement that does the loop and using the sqldatasource to so the insert. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ myConnectionString%>">
<InsertParameters>
<asp:Parameter Name="NewID" Type="int16" Direction="output" />
</InsertParameters>
</asp:SqlDataSource> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim sqlSelect As String
Session("Waste_Profile_Num") = 2
Session("Waste_Profile_Num2") = 5
sqlSelect = "SELECT Range, Concentration, Component_ID FROM Component_Profile WHERE (Waste_Profile_Num = " & Session("Waste_Profile_Num").ToString() & ")"
SqlDataSource1.SelectCommand = sqlSelect
Dim dv As Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView)
For Each dr As Data.DataRow In dv.Table.Rows
sqlSelect = "INSERT INTO Component_Profile (Waste_Profile_Num, Range, Concentration, Component_ID) "
sqlSelect &= "VALUES (" & Session("Waste_Profile_Num2").ToString() & ", @Range, @Concentration, @Component_ID); SELECT @NewID = @@Identity"
SqlDataSource1.InsertCommand = sqlSelect
'SqlDataSource1.InsertParameters.Add("Waste_Profile_Num", Session("Waste_Profile_Num2").ToString())
SqlDataSource1.InsertParameters.Add("Range", dr("Range").ToString())
SqlDataSource1.InsertParameters.Add("Concentration", dr("Concentration").ToString())
SqlDataSource1.InsertParameters.Add("Component_ID", dr("Component_ID").ToString())
SqlDataSource1.Insert()
SqlDataSource1.InsertParameters.Clear()
MsgBox(Session("NewID").ToString())
Next
End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
Session("NewID") = e.Command.Parameters("@NewID").Value.ToString
End Sub What I have done so far is to display the new id and I am going to use that return id later. However, the first time through the loop, I am able to get the new id but not the second time. So, I wonder, how do I every single new id each time the INSERT statement is executed?STAN
Error 2 'Text' is not a member of 'System.Data.SqlClient.SqlParameter'. I think this error has something to do with the lack of an Import command, anyone point me in the right direction?
View 4 Replies View RelatedDear All,
I have a problem while trying to update the content of my page to the database by the means of a stored procedure
string OcompConnection = ConfigurationManager.ConnectionStrings["GenUserCode"].ConnectionString;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(OcompConnection);
System.Data.SqlClient.SqlCommand ocmd = new System.Data.SqlClient.SqlCommand("Dealer_Insert", conn);ocmd.CommandType = CommandType.StoredProcedure;
ocmd.Parameters.Add(new SqlParameter("@UserCode"), SqlDbType.NVarChar);
ocmd.Parameters["@UserCode"] = TxtUserCode;
and also there is another error message saying that argument1: can not comvert from system.data.sqlclient.sqlparameter to string.
What am i Missing???
Eventually there is the try{open} and finally{close}
Many Thanks
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
View 1 Replies View RelatedHello, I am a SQL rookie. I have followed the tutorial and installed MSDN as it says. However, I am unable to create a database with WebMatrix. I keep getting an error that reads "SQL Server does not exist or access denied. ConnectionOpen (Connect())."
When I loaded the SQL, everything seemed to go well. I got all of the results that the tutorial said I should.
Please advise on my next steps. Thank you in advance.
JS
If you have a SQL server setup that isn’t using a SAN but just a normal raid array and you wanted to utilize a SAN now.
What are the steps, I'm guessing that you will need to start the services under a domain user correct?
Is it as easy as detaching the DB and then reattaching the DB on the SAN?
I'm assuming you need to map the data and log arrays from the server to the SAN correct?
I can’t seem to find any documentation on moving a server to a new SAN. Can someone post links if you have them?
Do you need ENT edition or is Standard OK .
Thanks for your help.
Hi
I'm trying to set up a new job to update a field in the table, I've managed to get the select syntax to work, but when I added an IF statement and Update syntax it didn't like it and the following error was shown:-
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'BEGIN'.
This is the syntax I'm trying to use for the job:-
USE EmployerEngagement
IF (SELECT On_Stop
FROM tblEmployer LEFT OUTER JOIN tblWP_Details
ON tblEmployer.Emp_ID = tblWP_Details.Emp_ID LEFT OUTER JOIN tblVetting
ON tblWP_Details.Record_ID = tblVetting.Record_ID
WHERE tblEmployer.On_Stop = 0
AND tblVetting.Next_Vett_Date <= GETDATE())
BEGIN
UPDATE tblEmployer
SET On_Stop = 1
END
Basically I just want to change the On_Stop value from 0 to 1 if the Next_Vett_Date is before or on todays date.
Am I using the wrong syntax for this?
Thanks
How do i do it? do i have to set up an ODBC?
View 20 Replies View RelatedHello,I've noticed on my DTS that it is connecting to the remote SQL Servervia Named Pipes. Not sure why I've been getting the following error:[DBNETLIB][ConnectionWrite (WrapperWrite()).] but I wanted to changethe DTS from using Named Pipes to use TCP/IP. How to do it?Also, if anyone has any clues on what this error means, please let meknow!Step Error Source: Microsoft Data Transformation Services (DTS) DataPumpStep Error Description:The number of failing rows exceeds the maximumspecified. (Microsoft OLE DB Provider for SQL Server (80004005):[DBNETLIB][ConnectionWrite (WrapperWrite()).]General network error.Check your network documentation.)Step Error code: 8004206AStep Error Help File:sqldts80.hlpStep Error Help Context ID:0Tnks!
View 1 Replies View RelatedI am trying to connect to my database in SQL SERVER EXPRESS via dreamweaver. I am trying to create DSN connection but when i click on sql server in the optionlist it lists about 100 options of the sql server i want to connect to. How do I find out which one is the one I have my tables in and the sql server express that I use.
Cheers
sry im an idiotbut how do u take like "SELECTbla FROM blar WHERE blam=blfda" and make the thing it "selects" into a variable thnx
View 5 Replies View RelatedHi, I have just started with ASP, and have a problem. I have created a stored procedure which looks to a specific tablename for information, based upon the users choice from a dropdown list.
The control works fine when executed from within visual web developer, and I manually enter the value that the variable expects. However I can not get the dropdown listbox value to be written to the SQL value. I have tried for days, traweled the net for answers, borrowed 3ft in height of SQL books! so either I am doing something fundamentally wrong, or I am missing something. My SP is:
ALTER Procedure GenericTableSelect
@tablename VarChar(20)
AS
Declare @SQL VarChar(1000)
SELECT @SQL = 'SELECT [base model] FROM '
SELECT @SQL = @SQL + @tablename
Exec ( @SQL)
and from the page the command to call it is:
SelectCommand=generictableselect></asp:SqlDataSource>
But this fails to compile and comes back with "@tablename not defined"
any pointers in the right direction would help.
The object of this is for two drop down boxes - the first is populated from one database of categories, the selection of which populates the second drop down list with items from within that category.
Cheers,
Richard
Hello all,
I've gotten approval at my work place to implement a new web server cluster. We currently have one web server running Windows 2003 and one SQL 2005 box. I've gotten approval to purchase 2 new web servers that will eventually become our primary servers.
I've browsed Microsoft's site but can't seem to find any FAQ's or How-Tos on clustering that are of any help. Is there anyone out there who has some info that could help me get started?
Thanks.
Richard M
I have been using MySQL as the back end of my .Net 2.0 applications for a while using a custom membership provider without any problems.Having now switched to a new webhost which supports both MySQL and MS SQL Server 2005 and also creating a new site thought I should set up the new site to use MS SQL..... I am having trouble however setting up the SQL server to act as the membership provider. Following the advice on here I tried using aspnet_regsql.exe... Firstly, when trying to get the list of databasses on the remote server I get the following error: Failed to query a list of database names from the SQL Server. Invalid object name 'Sysdatabases' If I then just type in the name of my database and go through to the end I get the following error:Exception:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 8152 and the SqlException message is: String or binary data would be truncated. I can connect to my MS SQL server remotely without any problem using the likes of MS SQL Server Management Studio Express. Is there any other way I can setup the databases needed for user and roles access control or is the usage of MS SQL much more complicated than what MySQL was?
View 4 Replies View RelatedI've received the error - Setup failed to configure the server. Refer to the server error logs and setup error logs
View 3 Replies View RelatedHello
my host required to have a fix IP in order to access his SQL Server database.
I got the fix IP, but I don't know where to define the port number while creating a SQL Server connection in Enterprise Manager.
Anyone knows ?
Thanks a lot
Johann
hey guys heres my problem iam trying to install SQL server2000 but i keep on running into problems....??
here is what iam having problems with
setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
it keeps saying the instance name specified is invalid there has to be an easyer way to run the program...??
i have all ready set up an instance name and the SAPWD.
any suggestions?? please help
I have the following to create a new table....
CREATE TABLE dbo.test (Id varchar(20) NOT NULL PRIMARY KEY, Name varchar(256) NOT NULL, visible bit NOT NULL )
I need to set the default value of the bit visible to 1.
How can this be done using a T-SQL statement?
Thanks,
Zath
Hi all:
How can I change the Default value of a column in a table using sql?
for instance I have a table called Phone and I have a field called AreaCode and the default now is '123' . I would like to change it to '456'.
Thanks,
Nisha