Trouble With If Command

Nov 6, 2007

 Ok, what I am tryin to do is quite simple i do believe.  Basically I have an aspx page with a textbox, a button and a datagrid linked to my sql db.  What I am wanting is for a user to enter in their Social Security Number in the text box and click the button.  If the SSN matches a record in the DB then it binds to the datagrid and shows it, IF no match is found then it displays a link to click on to continue onto the next page.  This is to stop duplicate entries.  I can get one to work by itself but not both of them at the same time.  Here is my code.
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">

    Sub btnSearch_OnClick(sender as Object, e as EventArgs)
        Dim objConnection As SqlConnection
        Dim objCommand    As SqlCommand
        Dim objAdapter    As SqlDataAdapter
        Dim objDataSet    As DataSet
        Dim strSearch     As String
        Dim strSQLQuery As String
       

        ' Get Search
        strSearch = txtSearch.Text
       
        ' If there's nothing to search for then don't search
        ' o/w build our SQL Query and execute it.
        If Len(Trim(strSearch)) > 0 Then                                                      OK, Here is my first IF, this works.
            ' Set up our connection.
            objConnection = New SqlConnection("Data Source=BRADBLACKBUR-PCMSSMLBIZ;" _
    & "Initial Catalog=Victorypoint;Integrated Security=True;")

           
            ' Set up our SQL query text.
            strSQLQuery = "SELECT FirstName, LastName, SSN " _
    & "FROM Applicants " _
    & "WHERE SSN LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
    & "ORDER BY SSN;"

            ' Create new command object passing it our SQL query
            ' and telling it which connection to use.
            objCommand = New SqlCommand(strSQLQuery, objConnection)

            ' Get a DataSet to bind the DataGrid to
            objAdapter = New SqlDataAdapter(objCommand)
            objDataSet = New DataSet()
            objAdapter.Fill(objDataSet)
           
           
            If objDataSet.Tables.Count < 1 Then                     Right here is where I am having the problem, I need to count the Rows not the Tables, but I dont know what code i should use here.
                Button1.Visible = True
            Else
                ' DataBind DG to DS
                dgSearch.DataSource = objDataSet
                dgSearch.DataBind()

                objConnection.Close()
           
            End If
        End If
       
       
    End Sub

View 2 Replies


ADVERTISEMENT

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 Replies View Related

Using A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..

Nov 4, 2006

Hi All,

i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

enterName - String packageLevel (will store the name I enter)

myVar - String packageLevel. (to store the query)

I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"

Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

Can Someone guide me whr am going wrong?

myVar variable, i have set the ExecuteAsExpression Property to true too.

Please let me know where am going wrong?

Thanks in advance.








View 12 Replies View Related

Do Somebody Know How Long (in Chars) Script(command) Can Be Solved By SQL Command?

Aug 30, 2004

Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks

View 1 Replies View Related

What Command Is Used To Get Back The Privileges Offered By The GRANT Command?

Mar 10, 2007

reply.

View 1 Replies View Related

Command Text Was Not Set For The Command Object Error

Sep 19, 2006

Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:

Description: An OLE DB Error has occured. Error code: 0x80040E0C.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".

.........

Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".

This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:

private bool BuildPackage()

{




// Create the package object

oPackage = new Package();

// Create connections for the Foxpro and SQL Server data

Connections oPkgConns = oPackage.Connections;

// Foxpro Connection

ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");

oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere

oFoxConn.Name = "SourceConnectionOLEDB";

oFoxConn.Description = "OLEDB Connection For Foxpro Database";

// SQL Server Connection

ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");

oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere

oSQLConn.Name = "DestinationConnectionOLEDB";

oSQLConn.Description = "OLEDB Connection For SQL Server Database";

// Add Prepare SQL Task

Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");

TaskHost thSQLTask = exSQLTask as TaskHost;

thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");

thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);

thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);

thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");

thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);

thSQLTask.FailPackageOnFailure = true;



// Add Data Flow Tasks. Create a separate task for each table.

// Get a list of tables from the source folder

arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");

for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)

{


// Get the name of the file from the array

sDataFile = Path.GetFileName(arFiles[iCount].ToString());

sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);

oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

oDataFlow.AutoGenerateIDForNewObjects = true;



// Create the source component

IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();

oSource.Name = (sDataFile + "Src");

oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper srcDesignTime = oSource.Instantiate();

srcDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oSource.RuntimeConnectionCollection.Count > 0)

{


oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;

oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);

}

// Set Custom Properties

srcDesignTime.SetComponentProperty("AccessMode", 0);

srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);

srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);

// Re-initialize metadata

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();

// Create Destination component

IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();

oDestination.Name = (sDataFile + "Dest");

oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper destDesignTime = oDestination.Instantiate();

destDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oDestination.RuntimeConnectionCollection.Count > 0)

{


oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;

oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);

}

// Set custom properties

destDesignTime.SetComponentProperty("AccessMode", 2);

destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);

destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");



// Create the path to link the source and destination components of the dataflow

IDTSPath90 dfPath = oDataFlow.PathCollection.New();

dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);

// Iterate through the inputs of the component.

foreach (IDTSInput90 input in oDestination.InputCollection)

{


// Get the virtual input column collection

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the column collection

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{


// Call the SetUsageType method of the design time instance of the component.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

}

//Map external metadata to the inputcolumn

foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)

{


IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();

externalColumn.Name = inputColumn.Name;

externalColumn.Precision = inputColumn.Precision;

externalColumn.Length = inputColumn.Length;

externalColumn.DataType = inputColumn.DataType;

externalColumn.Scale = inputColumn.Scale;

// Map the external column to the input column.

inputColumn.ExternalMetadataColumnID = externalColumn.ID;

}

}

}

// Add precedence constraints to the package executables

PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);

pcTasks.Value = DTSExecResult.Success;

for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)

{


pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);

pcTasks.Value = DTSExecResult.Success;

}

// Validate the package

DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);

// Check if the package was successfully executed

if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))

{


string sErrorMessage = "";

foreach (DtsError pkgError in oPackage.Errors)

{


sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";

sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";

sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";

sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";

sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";

sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";

sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";

sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;

}

MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);

return false;

}

// return a successful result

return true;
}

View 2 Replies View Related

Boy Am In Big Trouble!!!

Feb 16, 2004

I just got finished developing the company intranet site and thinking that everything was working I boasted about how good it was by getting my boss to login and submit a new job to the db (new job, its a work management app) while i did the same, the pland was to hit the submit button at the same time. He would send one to be read by me and I would send one to be read by him. We both hit submit and the following happened.

The db has somehow fused the two into one. I thought maybe we were to accurate in hitting the submit button together. But I even gave a five second delay between and for some reason the job is being overriden by one user tor the other. In other words we are both sharing the same jobid. I thought this could never happen with sql server supposing that it would lock one request until another was completed and vice versa. But I'm so new to this that I'm just so naieve to think that the db would do this for you. Problem is I'm about to move on and I can't leave the app in this state. Can anyone point to some articles or give some suggestions has to my situation. Most desperately in need!!

Thanks in advance

View 11 Replies View Related

Trouble Getting My Sp Done

May 20, 2006

hello

i'm having touble getting my sp done. the problem is as follow..i've found an sp tokenize (which works fine) with the following signature:

CREATE PROCEDURE TOKENIZE (
S VARCHAR(10000),
DELIM CHAR(1))
RETURNS (
ID INTEGER,
TKN VARCHAR(10000))
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE LEN INTEGER;
DECLARE VARIABLE FIRSTCHAR CHAR(1);
DECLARE VARIABLE S2 VARCHAR(10000);
begin
...
SUSPEND;
end^

then i build myself another sp (which also works fine) with this signature:

CREATE PROCEDURE GET_DICENTRIES_BY_ASDSKRPT (
ASDSKRPTINPUT VARCHAR(15))
RETURNS (
ID BIGINT)
AS
begin
...
suspend;

end^

now, what i'm trying to do is write another sp (get_dicentries_by_all_asdskrpts) that accepts a '.'-tokenized string as a parameter (e.g. 'bla.bli.blo.blu'; number of tokens NOT fixed at 4!) and returns the intersection of

GET_DICENTRIES_BY_ASDSKRPT('bla'), GET_DICENTRIES_BY_ASDSKRPT('bli'), GET_DICENTRIES_BY_ASDSKRPT('blo') AND GET_DICENTRIES_BY_ASDSKRPT('blu')

does any of you have an idea how to go about?

thanx,

martin

View 1 Replies View Related

Sql Trouble

Jul 23, 2005

Could someone help me get the following SQL statement working?SELECTstandardgame.gamename,standardgame.rowteamname,standardgame.colteamname,standardgame.dollarvalue,standardgame.gameid,standardgame.cutoffdatetime,standardgame.gametype,standardgame.gameowner,(100-COUNT(purchasedsquares.gameid)) AS squaresremainingFROM standardgameLEFT OUTER JOINpurchasedsquares ON standardgame.gameid = purchasedsquares.gameidwhere gametype='$gametype' and dollarvalue = '$dollarvalue' andgameowner = 'GROUP BY standardgame.gameidorder byCASE squaresremaining WHEN 0 THEN 1 ELSE 0 END ASC,squaresremaining ASCThe problem is... MySQL doesn't seem to want to let me usesquaresremaining in that case statement since it's not an officialcolumn name. Any idea how I can reference squaresremaining in the casestatement?

View 2 Replies View Related

Trouble With LIKE

Mar 13, 2007

I have a smalldatetime field in SQL.

 

For the query of my report, I need any transaction that is like 09/2006 (matching the month and year).

So I wrote something like this:

AND (DATE LIKE '%2006%')

That correctly returns all of the 2006 transactions.

Now why won't this work:

AND (DATE LIKE '%2006-09%')

Or how about this:

AND (DATE LIKE '%09%2006%')

 

What is the correct syntax??

View 3 Replies View Related

I'm In Trouble!!

Nov 2, 2007

am an ASP.net developer and i've stucked in a C# windows application ... and i am the linking part ... between the Database and my application ... in ASP i have a wizard that handles getting the data from the controls ... but is it the same in C# windows application ? am using VS2005 TeamSystem... and widows Vista(framework 3.0) .... and this query is getting executed and return 1 ... and the intellecence is telling me to insert 4 strings


int rowseffected = loginNamesTableAdapter.InsertQuery(textBox1.Text.ToString(), textBox2.Text.ToString(), textBox3.Text.ToString(), textBox4.Text.ToString());
MessageBox.Show(rowseffected.ToString());
i don't know how to link the parameters with the controls ... please show me how ... thx... please help with images ... as i previously mentioned... this is a new experment in C# thx.


__________________
Ahmed Reda

View 1 Replies View Related

Trouble With My 1st Connection

Jul 24, 2006

 Using Vs2005 sqlServer 2005 When i try to connect i get this error: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Connection string: <connectionStrings> <add name="pubsConnectionString1" connectionString="Data Source=.;Initial Catalog=pubs;User=mk;pw=x" providerName="System.Data.SqlClient" /> </connectionStrings> Calling code : try { using (SqlConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings[0].ConnectionString)) { cn.Open(); } } catch (Exception ex) { Response.Write(ex.Message); }

View 1 Replies View Related

Having Trouble With LIKE In Query

Mar 2, 2007

I have a table adapter query with a like clause that I can't get to work.  The field is "Type", so I have "LIKE '%@Type%'".  When I click the Execute Query button to test, not only does nothing get returned, I don't get the chance to enter the parameter.  If I change LIKE '%@Type%' to say, LIKE '%book%', the appropriate records are returned.  I actually need to check two parameters.  If I ad the second parameter, the where clause becomes(Type LIKE '%@Type%') AND (SendState = @SendState)When I test the query, a screen pops up to let me enter the state, but not the Type.  I can't see anything wrong with the query, but something must be. Diane 

View 4 Replies View Related

Trouble Connectiong

Jun 6, 2007

I am having a most frustrating time getting this working. I have two machines, one named 'REMOTE' and it contains my SQL Server (2005 developer edition). Then I have my main PC, named 'DOUGAL' The server instance on the REMOTE computer is called 'SQLSERVER' and there is only one instance. The server is showing as running etc and I can log in locally on the REMOTE computer easily via the Microsoft SQL Server Management Studio etc. I have two logins that are working fine, sa and a login called 'db_user'.Now I am trying to connect in the server explorer in visual studio. When I try and connection i get the following error; An Error has occured while establishing a connection to the server. When connectiong to SQL Server 2005 this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provder: Named Pipes Provider, error: 40 - Count not open connection to SQL Server).  I have tried connection to 'REMOTE/SQLSERVER' 'REMOTE' and with its IP address to - without any luck. So, to fix this problem I have checked that remote connections are allowed (and restarted the system). I have also checked that named pipes and tcp/ip connections are enabled. I have searched around with the aid of my best friend, Google. However, I've only found suggestions that involve trying the fixes I already tried. 

View 4 Replies View Related

Trouble With A Where Clause

May 5, 2008

I've tried to post this somewhere else, but I haven't gotten it figured out yet:
I'm passing variables through a URL, and the page that accepts the variables has a where clause like this:"WHERE (name1 LIKE  @name1 + '%') and (property_add like @property_add + '%') and (ctlmap  = @ctlmap )"On the search page where the variables are entered, the user may not know all the information that goes into each input field.  (If the user just knew the name, they would search by name.  If the user just knew the address, they would search by address...etc.) Everything works great before I add the ctlmap variable.  When the ctlmap variable is added, no search results will turn up unless there is an entry made to the ctlmap field.  I know it has to have something to do with the ctlmap being set to equals rather than like, but I can't find a way to make the search work with a null value for ctlmap.  Thanks, everyone. Additional Info:        <SelectParameters>            <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="name1"                 QueryStringField="name" Type="String" />            <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="property_add"                 QueryStringField="address" />            <asp:QueryStringParameter ConvertEmptyStringToNull="False" Name="ctlmap"                 QueryStringField="ctlmap" />        </SelectParameters>
Here's another type of where clause that I tried but had no luck with:
WHERE (name1 LIKE  @name1 + '%') and (property_add like @property_add + '%') and (@ctlmap IS NULL or @ctlmap = ctlmap)
This is how I would try to do it in php/mysql, but I don't know how to make it work in asp.net/sql:
  if ($ctlmap != "")      {            $whereClause = $whereClause." and ctlmp = '".strtoupper($ctlmap)."' ";      }                     if ($whereClause != "")      {            $whereClause = " where ".$whereClause;      }Here's the MYSQL counterpart:$sqlString = "Select distinct district, map, bill_group, ctlmap, bill_parcel, propertyid, special, property_add, name1 from MUR_bill_master".$whereClause";
 
Can anyone help?
 

View 11 Replies View Related

Trouble Connecting To SQL DB

Jun 9, 2004

Greetings,

I am having difficulty connecting to a SQL DB in my ASP page. Each time I run my application I receive the following error, "Login failed for user 'CX259ASPNET'". This is obviously something to do with permissions. I am all out of ideas but I will list the things I have already attempted to see if I either missed something or somebody can add to what I have already done.

1. In SQL Server I right-clicked the db -> properties -> security tab -> selected the option button to "SQL Server and Windows" instead of "Windows Only".
2. I installed SP3 and changed the sa password.
3. I created a new user under my db using the ASPNET account listed. I then changed the permissions to allow selecting, update, insert and delete access.
4. I also created a new user under my db using the IUSR account listed. I changed the permissions to the same settings as above.

I did a search on google which provided with all of the information above, but I am still stuck and hoping that this is a common problem and will be easy to fix.

Below is the code which I am attempting to use.


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim strConnString As String = _
"Data Source=(local);" & _
"Initial Catalog=Names;" & _
"Integrated Security=SSPI;"
Dim objConn As New SqlConnection(strConnString)
Dim strSQL As String = "SELECT * FROM [Names]"
Dim objCmd As New SqlCommand(strSQL, objConn)

objConn.Open()
MyDataGrid.DataSource = objCmd.ExecuteReader
MyDataGrid.DataBind()
objConn.Close()
End If
End Sub


Regards,
Corey

View 11 Replies View Related

CURSOR Trouble (:

Dec 24, 2004

Hi All,

What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @Where. (I tried with DEBUGGING option of Query Analyzer also).

=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"

if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"

DECLARE curs1 CURSOR SCROLL
FOR SELECT
*
FROM
school
WHERE
school ='ABC' + @where
=============================================

It gives me all the Records inside the SCHOOL='ABC' ...

Please check my SQL Above and Could somebody tell me , how can I attach the VARIABLE with CURSOR sql statement ?

Please advice me..(:

Thanks !

View 4 Replies View Related

Trouble With DatePart

Feb 22, 2005

I'm trying to get just the day part of the date - 2/22/2005 (getdate()) but instead of returning '22', it's returning '2'. Can someone please tell me what I'm doing wrong?

Thanks!
Lynnette

Here's the code

declare @thisDay varchar
set @thisDay = Convert(varchar, Datepart(day, getdate()))

View 3 Replies View Related

Trouble With SqlDataReader

Jan 29, 2006

hi..i am kind of new to asp.net n having trouble with the SqlException error message.. n this code works as 1st page pass the id to second page and  the second page took the id to execute the query..i dun know the wer the error occurs..can give a help..Thanks.
private void Page_Load(object sender, System.EventArgs e)
{

SqlConnection connection = null;
SqlCommand command = null;
string sqlConnection = null;
string sql = null;
string ab = null;
sqlConnection = ConfigurationSettings.AppSettings["MSQLConnectionString"];
connection = new SqlConnection(sqlConnection);
if (!Page.IsPostBack)
{
try
{
if (Request.QueryString["categoryID"] == null)
{
}
else
{
ab= Request.QueryString["categoryID"].ToString(); //getting the id from page that pass this values


sql = "Select groupname, categoryid, description from groups where groups.categoryid=?"; // can this query execute?
command = new SqlCommand(sql, connection);
connection.Open();
command.Parameters.Add(new SqlParameter("categoryid", ab));
reader = command.ExecuteReader();  // error on here "SqlException"
while (reader.Read())
{
group.InnerText = reader["groupname"].ToString();
desc.InnerText = reader["description"].ToString();


}
}
}
finally
{
if (reader != null)
{
reader.Close();
}

if (connection != null)
{
connection.Close();
}
}
}

View 1 Replies View Related

SQL Mail Trouble

Jun 21, 2001

Hi everybody!
I´m trying to start SQL Mail in SQL7. I configure the exchange client, test the Mailbox, but when i try to start Mail, Console show me the following error:
Error 22030 - A MAPI Error - MapiLogonEx Failed due to MAPI Error 273

NOTE: The Exchange server is in another domain.

I think that i'm forgetting something (i´m new on this)...
Thanks in advance

View 2 Replies View Related

Trouble With Xp_readmail

Nov 17, 2000

Hello,
I've got some trouble when using xp_readmail.I check the email count and saved the attached files in c:winnt.
The problem is when the attached files are saved cause the file name is cut in dos format (8 characters + . + 3 characters) but my file's name is like toto.tutu.titi_tata.txt so is there a way to keep the name unchanged when saving attached files ?
Thanks for your answers

View 1 Replies View Related

Big Trouble With Date And Sql 7

Jan 13, 2000

I've been having this huge problem with date and SQL 7, like lot of people. I use asp with the sql and trying to do this query where startdate should equal or smaller the date today, here are to queries that worked, but not anymore.

SELECT * FROM tbl_date WHERE convert(varchar(10), startdate, 101) = convert(varchar(10), getdate(), 101)

this query worked until the year 2000, the normal y2k bug
then this one, wich i saw here on messageboard:

select * from tbl_date where startdate =< '" & date & "'"
well this worked until today, when it just stop working

so does anybody know what to do?

hope to get answer on this

View 1 Replies View Related

Replication Trouble

Sep 20, 2000

Replication problem:

Server A is Publisher/Distributor SQL7
Server B is Subscriber SQL7

Successfully set up a publication for table on Server A

Synchronisation fails with error that 'The network name cannot be found' and the following file could not be created:
The last action is 'Server AC$MSSQLRepldataunc<pub name><date string>ablename.sch'

... which is odd because the file IS created and contains a correct looking table definition.


So, manually sync data on this one table and try again, now it can't connect with my server, which is weird cos I
can connect through EM.

Any thoughts???
G.

View 2 Replies View Related

IF...ELSE Statement Trouble

Apr 17, 2000

Take a look at this code:

CREATE PROCEDURE sp_addCustomer

@fnamechar(25),
@lnamechar(25),
@companyNamechar(30),
@addressvarchar(50),
@zipvarchar(10),
@citychar(20),
@countrychar(20),
@emailchar(30)

AS

IF @email = (SELECT email FROM customers WHERE @email = email)
PRINT 'This customer is already in the database'
ELSE
PRINT 'New Customer Added'
INSERT INTO customers
VALUES (@fname,@lname, @companyName, @address, @zip, @city, @country, @email)

Looks pretty straightfoward, however when I add a duplicate customer, I get the 'This customer is already in the database' message and then the record is added. I don't know why this is happening. Can anyone help?

Thank you,
Nathan

View 2 Replies View Related

Trouble With DTS Package

May 6, 2003

Hello All,

I am attempting to port a database from one server to another, I am using the DTS wizard to so this, the Db copies over to the other server but it drops the views, usernames, stored procs etc. Is there another way to do this? possibly with T-SQL? Any suggestions would be great.

Thanks

Tony

View 1 Replies View Related

Trouble With Distinccount..

Sep 15, 2005

Hello,

I have a cube with a lot of dimensions. Two of them are ItemNo and LocationCode. I need to have two measures "Locations Shipped To" and "Item Count". For those, I need to have a count of different locations/items matching current dimension filters. So I tried adding a calculated member:

distinctcount({[Item].Members})

but it only shows 0 or 1 :(

Am I missing something here?

thanks.

View 2 Replies View Related

Trouble With Blobs

Aug 3, 1998

Help! Where building a document mgmt system that will store MSword documents, etc in SQL Server.
When we BCP a table which contains image data to another copy of the table,
the image data seems to get clobbered and is not longer valid. Has anyone had this problem

View 1 Replies View Related

Trouble With View

Feb 26, 2007

Hi i'm new here so be kind.

Here is what I have.
Windows 2000 server, Sql server 2000 standard, Solomon 5.5

I have a view that is used by crystal reports. We had a company come in and they designed a custom report. The view uses three other views. The report works perfectly until two people try to run the report at the same time. This is when four of the columns in the report double the values.

We ran the report for a year or two with no problem. Now that we are starting to use Solomon Desktop for people out in the field we are finding this problem.

Is there a way that the view can be setup not to cause this problem?

View 10 Replies View Related

Sa Login In Trouble

May 14, 2002

Hi!
When I double click on “sa” account in the Logins folder under Security I’m getting the error message:

“ Error 21776: [SQL - DMO] The name ‘dbo’ was not found in the users collection. If the name is a qualified name, use [ ] to separate various parts of the name, and try again.”

What does it mean? What is corrupted?
Thanks.
Elena.

View 1 Replies View Related

Having Trouble Connecting To SQL

Jul 17, 2006

ok, we have a development server in our office and it has SQL running on it. If i remote over to that machine i can open the database administrator and create tables and things just fine. I use the windows login.

now when i try to goto my machine and open up visual studio or dreamweaver to try to make a dataset connection it will not connect to the server. I get a message in visual studio saying it is probly because the default settings prohibit remote connections to the DB. I'm also doing the wondows login mode from the remote computer.

So is there a way i can change it so i can have remote access? i dont see how else they expect anyone to be able to do any development from a remote machine elsewise...

View 6 Replies View Related

Trouble With Sum() Function

Aug 20, 2004

I have a pretty nasty situation in where I have two tables that hold quantity values for an interval of time. One table has an hourly date format while the other is every 5 minutes. I'm using the below t-sql to pull back the summed quantities I need in an HOURLY format; however, it is doubling the MarketQty field becuase it's not summing the qty field from the HE (henwood_extract) table correctly:

Here's a sample of what I'm trying to do.....

imo_meter_data table has records like this


Code:

ID END_DATE QTY
2 2004/08/18 00:05 2
2 2004/08/18 00:10 2.987
2 2004/08/18 00:15 3.1



henwood_extract is like this


Code:

ID END_DT QTY
132 2004/08/18 00:00 6.087
133 2004/08/18 00:00 1



I'm building a comparison tool, so I want the data from the query to return like this:


Code:

MyFDate MarketQty HenwoodQty
2004/08/18 00:00 7.087 7.087



Which would be correct if the sums worked in the query, but what it's looking like is this:


Code:

MyFDate MarketQty HenwoodQty
2004/08/18 00:00 14.174 14.174



Here's the query that I'm using:

Code:

select imo.meter_id, Convert(datetime, cast(datepart(mm, imo.end_date) as varchar)
+ '/' + cast(datepart(dd, imo.end_date) as varchar)
+ '/' + cast(datepart(yyyy, imo.end_date) as varchar)
+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
+ ':00', 120) MyFDate,
sum(imo.qty) MarketQty, sum(he.qty) as HenwoodQty
from imo_meter_data imo, henwood_extract he where
convert(datetime, imo.end_date, 120) between
convert(datetime, '08/18/2004', 120) and
convert(datetime, '08/19/2004', 120) and
convert(datetime, he.end_dt, 120) between
convert(datetime, '08/18/2004', 120) and
convert(datetime, '08/19/2004', 120) and
convert(datetime, he.end_dt, 120) = Convert(datetime, cast(datepart(mm, imo.end_date) as varchar)
+ '/' + cast(datepart(dd, imo.end_date) as varchar)
+ '/' + cast(datepart(yyyy, imo.end_date) as varchar)
+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
+ ':00', 120) and
he.plant_name = 'Brighton Beach' and
he.meter_type = 'ENERGY'
group by imo.meter_id, convert(datetime, cast(datepart(mm, imo.end_date) as varchar)
+ '/' + cast(datepart(dd, imo.end_date) as varchar)
+ '/' + cast(datepart(yyyy, imo.end_date) as varchar)
+ ' ' + cast(datepart(hh, imo.end_date) as varchar)
+ ':00', 120)
order by MyFDate



Any and all help is greatly appreciated.

View 3 Replies View Related

Trouble With 'TOP' Statement

Jun 12, 2006

Background:

I am using standard ASP to connect and select records from a SQL Server 2000 database. My goal is to select the 3 most recent records that match my criteria.

Problem: When I run my query and display the records without the 'TOP 3' statment all records matching my criteria are predictably returned. However, when I add the 'Top 3' statement only one record is returned.

Here is the SQL code I am using to select my records:


Code:

strSql = "SELECT TOP 3 GroupImageName, groupImageDate "
strSql= strSql & "FROM groupImages "
strSql= strSql & "WHERE groups_id='" & groups_id & "' "
strSql= strSql & "ORDER BY groupImageDate"

set rs=conn.execute(strSql)



and here is the code I use to display the records. There isn't any formatting yet as I am attempting to get the expected results before adding any formatting to the page:


Code:

if not rs.eof then
do while not rs.eof
response.Write(rs("GroupImageName"))
rs.movenext
loop
end if



Even if I remove the 'ORDER BY' statment I am still only seeing 1 record. If I remove the 'top 3' statement 9 records are returned!

Any help would be appreciated. Thanks.

View 1 Replies View Related

DTS Transaction Trouble

Apr 10, 2006

Hi Guys,

I have a DTS Routine which archives a large amount of data into archive tables in the same database(Personally I would rather archive into a separate db but the paperwork for the customer involved in doing this is ridiculous).

The routine seems to archive all tables except for one (the largest one) However if I split the routine and run the rest of it first then the problem table then the table archives ok. To me it smells of transaction size or something but does anyone have any other ideas.. If it is transaction size, is there a way that I can break the routine down into multiple transactions?

Any help is as always greatly appreciated.

Cheers,

Chris
----------------------------------------------------------------
If we were meant to count in Hex, why do we only have X fingers?
----------------------------------------------------------------

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved