Re: Help me in developing stored - Database Discussion Boards

Welcome.

Regards,
Arun Kumar.A

i have a Query that is workng well
select * from property
where
substring(func_key,1,5) = 'GEOSS' And substring(func_key,6,8)>= 3

it Display all the records that has 'GEOSS' and has a functional key that is equal to 3 and greater than 3. the Query is working.

NB:* The 'GEOSS' Records are Subtyptes of PVOOOOOO Records. It means that the main Key will be like '022100550.PV000000' and the Subtypes will be
'022100550.GEOSS001
'022100550.GEOSS002
'022100550.GEOSS003
Those are subtypes of 022100550.PV000000. above SQL statement searches them and Exclude the main key, i need a separate key that will show all main keys of the subkeys. that means all the PV000000 that has Sub keys GEOSS.

Please Help

where the

Vuyiswa Maseko Junior Programmer that will never stop learning form Code Project

Why don't you break all this information in to separate columns? At the moment it looks like it is breaking the first normal form (Keep separate data in separate columns). Wouldn't it make it much easier and more efficient to query?

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

This is a large Database that holds the propery of whole Province, i was built i can create my own fields i have to search for what they want and Fix what they want.

Vuyiswa

Can you rephrase that. I didn't understand it.

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

I have created the login page of my project,I want create a stored procedure in SQL 2005for the user login page how to compare the two text boxes, please any body help me with the stored procedure and how to call the that stored procedure from server page in c#.

Thank you
Bilal Ahmed Wani.
Software Traniee.

Bilal wani wrote:
how to compare the two text boxes

That is not a function of SQL Server. That is a function of your application.

Bilal wani wrote:
please any body help me with the stored procedure and how to call the that stored procedure from server page in c

If you are asking that level of question then perhaps a tutorial on accessing SQL Server from a .NET application would be more useful to you. Search results[^]

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Dear Friend ,
I have created the login page, you just go through the code of that page below:

Client End:(ASP.NET 2005)
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" %>

<title>Metalearn Login Page

// <!CDATA[

// ]]>

<asp:panel id="Panel1" runat="server" backcolor="MidnightBlue" height="236px" style="z-index: 100;
left: 429px; position: absolute; top: 176px; border-left-style:inset; border-bottom-color:Aqua; border-right-color:Aqua" width="425px" forecolor="Aqua">
<asp:label id="Label2" runat="server" height="23px" style="z-index: 100; left: 36px;
position: absolute; top: 53px" text="User Name" width="129px">

<asp:button id="Button2" runat="server" style="z-index: 101; left: 183px; position: absolute;
top: 160px" text="Sign In" validationgroup="SignIn" onclick="Button2_Click" backcolor="CornflowerBlue" bordercolor="CornflowerBlue" width="77px">
<asp:label id="Label3" runat="server" height="25px" style="z-index: 102; left: 35px;
position: absolute; top: 111px" text="Password" width="96px">
<asp:textbox id="txtuname" runat="server" style="z-index: 103; left: 210px; position: absolute;
top: 55px" backcolor="Beige" validationgroup="SignIn">
<asp:textbox id="passwrd" runat="server" style="z-index: 104; left: 211px; position: absolute;
top: 108px" textmode="Password" width="149px" backcolor="LightGoldenrodYellow" validationgroup="SignIn">

<asp:requiredfieldvalidator id="RequiredFieldValidator2" errormessage="* Please enter your Password" controltovalidate="TextBox1" validationgroup="SignIn" runat="server" style="z-index: 105; left: 209px; position: absolute; top: 137px" font-size="XX-Small" width="187px">
<asp:button id="Button1" runat="server" validationgroup="SignIn" style="z-index: 106; left: 270px; position: absolute;
top: 159px" text="Reset" onclick="Button1_Click" backcolor="CornflowerBlue" bordercolor="CornflowerBlue" width="104px">
<asp:requiredfieldvalidator id="RequiredFieldValidator1" errormessage="* Please enter your User Name" controltovalidate="TextBox1" validationgroup="SignIn" runat="server" style="z-index: 107; left: 208px; position: absolute; top: 84px" font-size="XX-Small" width="201px">

<asp:label id="Label1" runat="server" height="19px" style="z-index: 101; left: 460px;
position: absolute; top: 194px" text=" Login In Here" width="247px" forecolor="Cyan">
<asp:panel id="Panel2" runat="server" backcolor="White" forecolor="MintCream"
="" height="428px" style="z-index: 99; left: 146px; position: absolute; top: 81px; border-style:outset;
border-bottom-color:Lime; border-left-color:Lime; border-right-color:Lime; border-top-color:Lime" width="742px">

<asp:label id="lblerrormsg" runat="server" forecolor="Red" style="z-index: 101; left: 310px;
position: absolute; top: 346px" width="282px">

Server end:(C#)

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Banking.Encryption;
public partial class Login : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// Put user code to initialize the page here //info.dll DATA =G5HQ4RcM3shUNn5uaYHTztba/UuiBuDG

}

protected void Button2_Click(object sender, EventArgs e)
{
EncryptDecrypt objEncrypt = new EncryptDecrypt();
if (txtuname.Text = "bank" & passwrd.Text = "bn")
{
Response.Redirect("Mainmenu.aspx", false);
}
else
{
lblerrormsg.Text = "Invalid Login";
//Button2.Attributes.Add("onclick", "return validations();");
txtuname.Text = "";
passwrd.Text = "";
txtuname.Focus();
}
}

protected void Button1_Click(object sender, EventArgs e)
{
lblerrormsg.Text = "";
txtuname.Text = "";
passwrd.Text = "";
txtuname.Focus();
}
}

Now the thing I have created a database table in SQL which can store userlogins now I want to know how to write the code in c# which can verify userlogins how to pass the parameters to compare user logins.

Thanks
Bilal

I think youre a genuine beginner at this stage. What you need to do here is to access the database from ur application and in this code sample here there is no sign of any DataAdapter or a query used.

First get some knowledge abt the DataSets, TableAdapters, ADO .NET and DataAdapters,

you could do it like this.

string sql =@"Select UserName From UsersTable Where UserName LIKE '"+textBoxUserName.Text+"' AND Password = '"+textBoxpass.Text+"';
//(Caution: password is case insensitive in this case. Convert it to Varbinary for making it case sensitve)
SqlCommand cmd = new SqlCommand(sql, sqlConnection1);
if(cmd.ExecuteNonQuery() == 1)
{
// login successful
}
else
{
login failed
}

Its always a better idea not to store the actual text for passwords in Db. Its better to use an encoding scheme. But for now, u have a long road ahead of u

Thanks for your help I will try.
Bye!

Dearfriend I changed the coding as u told me...
plz make the correction of the below code..

protected void Button2_Click(object sender, EventArgs e)
{
string sql =@"Select [Username],[Password] From [Login] Where UserName = '"+Textbox1.Text+"' AND Password ='"+Textbox2.Text+"'";
SqlConnection con = new SqlConnection("Data Source=BILAL\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
SqlCommand cmd = new SqlCommand(sql, con);
//cmd.Parameters.AddWithValue("@Username", Textbox1.Text);
//cmd.Parameters.AddWithValue("@Password", Textbox2.Text);
con.Open();
//cmd.ExecuteNonQuery();
if(cmd.ExecuteNonQuery()==0)
{
// login successful
Response.Redirect("Mainmenu.aspx", false);
}
else
{
lblerrormsg.Text = "Invalid Login";
//Button2.Attributes.Add("onclick", "return validations();");
Textbox1.Text = "";
Textbox2.Text = "";
Textbox1.Focus();
}
con.Close();
}
I have done Insertion Deletion Updation but I am not able to create the logic for to compare the input with a sql table , please modify the coding completely

thank You.
With Regards
Bilal
-- modified at 1:42 Thursday 26th April, 2007

Hi.

I'm writing an application able to work with Access, MS SQL Server 2005 or MySql database, depending on the user's will.

I want to use the classes of the System.Data.OleDb namespace for all three providers.

It's working with Access so for and now I'm trying to use the other two.

What connection strings must be specified for MySql and MS SQL 2005?

(Remote connection must be possible).

Thanks.

For all your connection string needs[^]

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

blackjack2150 wrote:
I want to use the classes of the System.Data.OleDb namespace for all three providers.

Why? This will be inefficient when there are native drivers available. Why not take a look at the Factory pattern that MS used with .NET 2? This allows you to work in a reasonably database agnostic fashion. Take a look at the classes DbConnection and DbCommand in System.Data.Common to get an idea as to how it all works.

Deja View - the feeling that you've seen this post before.

Also, look up dataprovider factory on msdn. This will give you some good info.

_____________________________________________________________________

Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.

The Enlightenment Project (paraphrased comment)

Visit Me at GISDevCafe

Hi,

I am getting the exception " Procedure or function SummerizeInsert has too many arguments specified." But so far my coding is correct. also i noticed this error comes when the loop is going through for the second time.

regards
ruwandi

rkherath

rkherath wrote:
I am getting the exception " Procedure or function SummerizeInsert has too many arguments specified." But so far my coding is correct. also i noticed this error comes when the loop is going through for the second time.

Would you care to show the code so that we can verify this?

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Hi,

this is the stored procedure

ALTER PROCEDURE dbo.SummerizeInsert
@cYear char(4),
@cMonth char(2),
@cLocationCode nchar(3),
@cAlias char(3),
@cLocationName char(10),
@cSKU char(10),
@cCategoryCode char(10),
@cCategory char(10),
@cDescs char(10),
@nOpeningBalance float(8),
@nReceipts float(8),
@nIssues float(8),
@nStkAdj float(8),
@nClosingBalance float(8),
@nCOS float(8),
@nSales float(8),
@nSalesRefunds float(8),
@nNetSales float(8),
@nPromotionsDiscount float(8),
@nOtherDiscount float(8),
@nPGPrs float(8),
@nPGP float(8),
@nAGPrs float(8),
@nAGP float(8),
@nStockDays float(8)
AS
insert into tblStock_Sales_Monthly
(cYear,cMonth,cLocationCode,cAlias,cLocationName,cSKU,cCategoryCode,cCategory,cDescs,nOpeningBalance,nReceipts,nIssues,nStkAdj,nClosingBalance,nCOS,nSales,nSalesRefunds,nNetSales,nPromotionsDiscount,nOtherDiscount,nPGPrs,nPGP,nAGPrs,nAGP,nStockDays)
Values
(@cYear,@cMonth,@cLocationCode,@cAlias,@cLocationName,@cSKU,@cCategoryCode,@cCategory,@cDescs,@nOpeningBalance,@nReceipts,@nIssues,@nStkAdj,@nClosingBalance,@nCOS,@nSales,@nSalesRefunds,@nNetSales,@nPromotionsDiscount,@nOtherDiscount,@nPGPrs,@nPGP,@nAGPrs,@nAGP,@nStockDays)
RETURN

this is the coding

public void insert()
{

DataTable dt = SelectFields().Tables[0];

foreach (DataRow dr in dt.Rows)
{
if (dr["OpeningBal"].ToString() == "")
dr["OpeningBal"] = 0.0;
if (dr["Reciepts"].ToString() == "")
dr["Reciepts"] = 0.0;
if (dr["Issues"].ToString() == "")
dr["Issues"] = 0.0;
if (dr["ClosingBal"].ToString() == "")
dr["ClosingBal"] = 0.0;
if (dr["Sales"].ToString() == "")
dr["Sales"] = 0.0;
if (dr["SalesReturn"].ToString() == "")
dr["SalesReturn"] = 0.0;
if (dr["PromDiscounts"].ToString() == "")
dr["PromDiscounts"] = 0.0;
if (dr["OtherDiscount"].ToString() == "")
dr["OtherDiscount"] = 0.0;
if (dr["Adj"].ToString() == "")
dr["Adj"] = 0.0;
if (dr["ReFrom"].ToString() == "")
dr["ReFrom"] = 0.0;
if (dr["ReTo"].ToString() == "")
dr["ReTo"] = 0.0;

StkAdj = (double)dr["Adj"] - (double)dr["ReFrom"] + (double)dr["ReTo"];
COS = (double)dr["OpeningBal"] + (double)dr["Reciepts"] - (double)dr["Issues"] - (double)dr["ClosingBal"] + StkAdj;
netSales = (double)dr["Sales"] - (double)dr["SalesReturn"];
nPGPrs = (netSales - COS) + (double)dr["PromDiscounts"] + (double)dr["OtherDiscount"];

double DevideBy=(netSales + (double)dr["PromDiscounts"] + (double)dr["OtherDiscount"]);
if (nPGPrs != 0.0 & DevideBy!=0.0)
{
nPGP = (nPGPrs / DevideBy) * 100;
}
else
{
nPGP = 0.0;
}

nAGPrs = nPGPrs + StkAdj - (double)dr["OtherDiscount"];
if (nAGPrs != 0.0 & DevideBy!=0.0)
{
nAGP = (nAGPrs / DevideBy) * 100;
}
else
{
nAGP = 0.0;
}
if ((double)dr["ClosingBal"] != 0.0 & COS != 0.0)
{
StockDays = ((double)dr["ClosingBal"] / COS) * 100;
}
else
{
StockDays=0.0;
}

InsertCom.CommandText = "dbo.SummerizeInsert";
InsertCom.CommandType = CommandType.StoredProcedure;
InsertCom.Parameters.Add(new SqlParameter("@cYear", year));
InsertCom.Parameters.Add(new SqlParameter("@cMonth", Month));
InsertCom.Parameters.Add(new SqlParameter("@cLocationCode", dr["Code"]));
InsertCom.Parameters.Add(new SqlParameter("@cAlias", dr["Alias"]));
InsertCom.Parameters.Add(new SqlParameter("@cLocationName", dr["LocationName"]));
InsertCom.Parameters.Add(new SqlParameter("@cSKU", dr["SKU"]));
InsertCom.Parameters.Add(new SqlParameter("@cCategoryCode", dr["CategoryCode"]));
InsertCom.Parameters.Add(new SqlParameter("@cCategory", dr["Category"]));
InsertCom.Parameters.Add(new SqlParameter("@cDescs", dr["CategoryName"]));
InsertCom.Parameters.Add(new SqlParameter("@nOpeningBalance", dr["OpeningBal"]));
InsertCom.Parameters.Add(new SqlParameter("@nReceipts", dr["Reciepts"]));
InsertCom.Parameters.Add(new SqlParameter("@nIssues", dr["Issues"]));
InsertCom.Parameters.Add(new SqlParameter("@nStkAdj", StkAdj));
InsertCom.Parameters.Add(new SqlParameter("@nClosingBalance", dr["ClosingBal"]));
InsertCom.Parameters.Add(new SqlParameter("@nCOS", COS));
InsertCom.Parameters.Add(new SqlParameter("@nSales", dr["Sales"]));
InsertCom.Parameters.Add(new SqlParameter("@nSalesRefunds", dr["SalesReturn"]));
InsertCom.Parameters.Add(new SqlParameter("@nNetSales", netSales));
InsertCom.Parameters.Add(new SqlParameter("@nPromotionsDiscount", dr["PromDiscounts"]));
InsertCom.Parameters.Add(new SqlParameter("@nOtherDiscount", dr["OtherDiscount"]));
InsertCom.Parameters.Add(new SqlParameter("@nPGPrs", nPGPrs));
InsertCom.Parameters.Add(new SqlParameter("@nPGP", nPGP));
InsertCom.Parameters.Add(new SqlParameter("@nAGPrs", nAGPrs));
InsertCom.Parameters.Add(new SqlParameter("@nAGP", nAGP));
InsertCom.Parameters.Add(new SqlParameter("@nStockDays", StockDays));

InsertCom.Connection = sqlconnection;
if (sqlconnection.State == ConnectionState.Closed)
{
sqlconnection.Open();
}
InsertCom.ExecuteNonQuery();

}

regards
Ruwandi

rkherath

It appears your insert command exists prior to the loop. The loop adds the necessary parameters. On the second iteration it adds the parameters again to the command. So there are now two sets of parameters in the command. (Hence the too many parameters exception)

You need to either
* setup the parameters once (before the loop) and assign the values only on each loop.
Or
* remove all the parameters in the command at the start of the loop.

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Thanks, I will try on this.

Regards
Ruwandi

rkherath

Hey Thanks It worked . Thanks a lot.

rkherath

It seems, the connection is not closed, so you are passing into stored procedure
number of ther required parameters multpileid by number of rows in your DataTable.
I am not sure, this was your intend

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

AS THE ERROR INDICATES YOU HAVE PASSED MORE VALUES TO THE PROCEDURE/FUNCTION THAN REQUIRED.

Gautham

You may wish to fix your caps lock key - it appears to be broken.

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

if you can see i have have have the exact number of parameters sent as required.

Regards
Ruwandi

rkherath