7 Replies - 845 Views - Last Post: 01 January 2013 - 01:10 PM Rate Topic: -----

#1 NejcZ  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-December 12

String in SQL query c#

Posted 01 January 2013 - 01:27 AM

I'm having some troubles getting this SQL sentance to work.
I get label3.Text from other form, which is users login name.
I now must get users id with using that label.Text. What am i doing wrong? I'm getting SqlException was unhandled error - Incorect sysntax near 'MyUsername'.

What am I doing wrong? How to get queries bellow to work properly?

Thanks.


private void btnPrihod_Click(object sender, EventArgs e)
        {
           
           SqlConnection con = Program.GetConnection;
           string dobiID = "SELECT id_zaposlenega FROM tabelaZaposlenih WHERE up_ime='" + label3.Text + "'";
           string prihodSql = "INSERT INTO delavniki (id_zaposlenega, datum_ura_prihod, status_zaposlenega) VALUES ('" + dobiID + "', CURRENT_TIMESTAMP, 'na_delu')";
           SqlCommand cmd = new SqlCommand(prihodSql, con);
           cmd.ExecuteNonQuery();
           MessageBox.Show("Prijavljeni ste na delovno mesto!");
           MessageBox.Show(dobiID);



        }



Is This A Good Question/Topic? 0
  • +

Replies To: String in SQL query c#

#2 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: String in SQL query c#

Posted 01 January 2013 - 02:00 AM

I don't see 'MyUsername' in that code anywhere. And use parameters or god will kill a kitten.
Was This Post Helpful? 0
  • +
  • -

#3 NejcZ  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-December 12

Re: String in SQL query c#

Posted 01 January 2013 - 02:12 AM

I get username in public Form() with:

label3.Text = Program.UserLoginName;


Ehe error is:
Incorect sysntax near 'NejcZ'

..when i'm logged in.
Was This Post Helpful? 0
  • +
  • -

#4 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: String in SQL query c#

Posted 01 January 2013 - 04:58 AM

Put a breakpoint in your code at line 6 and when it breaks look at the SQL statement in the line above. Does it look right? What is it, exactly? What is the column up_ime defined as?
Was This Post Helpful? 0
  • +
  • -

#5 NejcZ  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-December 12

Re: String in SQL query c#

Posted 01 January 2013 - 05:20 AM

To get you better idea, here is my login form:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace program
{
    public partial class Login : Form
    {
        public Login()
        {
            InitializeComponent();
            
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            SqlConnection con = Program.GetConnection;
            SqlDataReader dr = null;

            try
            {
                SqlCommand cmd = new SqlCommand("SELECT id_zaposlenega, up_ime, geslo FROM tabelaZaposlenih WHERE up_ime='" +
                    txtName.Text + "' AND geslo='" + txtpassword.Text + "'", con);
                dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    Program.UserLoginName = dr.GetString(1);
                    this.Close();
                    dr.Close();
                    new Form1().Show();


                }
                else
                    MessageBox.Show("Nepravilno uporabniško ime ali geslo!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }
        }

    }
}



I'm using
string dobiID = "SELECT id_zaposlenega FROM tabelaZaposlenih WHERE up_ime='" + label3.Text + "'";


to get user's id (id_zaposlenega), since i don't know any other way to do that, and the i use

string prihodSql = "INSERT INTO delavniki (id_zaposlenega, datum_ura_prihod, status_zaposlenega) VALUES (" + dobiID + ", CURRENT_TIMESTAMP, 'na_delu')";


to put data in table, where id_zaposlenega should be id_zaposlenega from dobiID string. Do you know what i mean?

Thanks.
Was This Post Helpful? 0
  • +
  • -

#6 pcaddict  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 45
  • Joined: 11-February 09

Re: String in SQL query c#

Posted 01 January 2013 - 08:56 AM

The best solution is to implement the concepts in the tutorial Parameterizing your SQL Queries: The RIGHT way to query a database
Was This Post Helpful? 0
  • +
  • -

#7 NejcZ  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-December 12

Re: String in SQL query c#

Posted 01 January 2013 - 10:42 AM

I understand that parameters are much better solution, but i'm only in phase of testing this little project, so i will stick with that code design that I'm familiar with. for now :) So, is there any chance to join two queries:

string dobiID = "SELECT id_zaposlenega FROM tabelaZaposlenih WHERE up_ime='" + label3.Text + "'";



string prihodSql = "INSERT INTO delavniki (id_zaposlenega, datum_ura_prihod, status_zaposlenega) VALUES (" + dobiID + ", CURRENT_TIMESTAMP, 'na_delu')";

Basicly what I need to do is get ID somehow from SQL and then use it in INSERT statement as a value. Is that even possible? In select statement i'm using label3.Text as a parameter value, becouse that is the only info I have of current user.
Was This Post Helpful? 0
  • +
  • -

#8 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3578
  • View blog
  • Posts: 11,130
  • Joined: 05-May 12

Re: String in SQL query c#

Posted 01 January 2013 - 01:10 PM

The brute force solution is to first execute your SELECT query to get back the ID from your id_zaposlenega column. Once you have that value, use it in a following your INSERT query.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1