5 Replies - 648 Views - Last Post: 22 January 2015 - 03:02 AM Rate Topic: -----

#1 Amy1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 07-November 14

how to get table name when tables are created manually in code?

Posted 21 January 2015 - 06:22 AM

my project is for construction in that I have to add multiple companies and have to add multiple projects under single company.

In Project form.jpg image I am adding a new project under selected company.

I have taken combo box for company its fetching data from database.

below code is for my projectform(see project form.jpg)






'Imports System.Web
Imports System.Data.SqlClient
Public Class FormAddProjects
    Dim s1 As New Connect
    Dim ds As DataSet
    'Dim Company As String = CmbCompName.Text
    Dim GetId As String = 0
    Private Sub FormAddProjects_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FormStyle(Me)
        ClearText(Panel1)
        CmbCompName.Focus()
    End Sub
    Private Sub CmbCompName_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmbCompName.GotFocus
        CmbCompName.DataSource = Nothing
        ds = s1.exesearch("select * from Company_Info")
        CmbCompName.DataSource = ds.Tables(0)
        CmbCompName.ValueMember = "id"
        CmbCompName.DisplayMember = "Company_Name"
        CmbCompName.ResetText()
    End Sub

    Private Sub TxtToken_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtToken.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub TxtDownPay_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtDownPay.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub TxtInstall_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtInstall.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub BtnSize_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSize.Click
        Dim obj As New FormSize
        obj.StringPassCompName = CmbCompName.Text
        obj.StringPassProjName = TxtProjName.Text
        obj.Show()
    End Sub

    Private Sub TxtRate_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtRate.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub TxtRate_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TxtRate.TextChanged
        Dim r As DataRow
        ds = s1.exesearch("select * from " & CmbCompName.Text + "_" + TxtProjName.Text & " where Project_Name1 = '" + CmbCompName.Text + "_" + TxtProjName.Text + "'")
        For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
            For Each r In ds.Tables(0).Rows
                TxtTotPay.Text = ds.Tables(0).Rows(i).Item("Vaar").ToString * Val(TxtRate.Text)
            Next
        Next
    End Sub

    Private Sub TxtTotPay_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtTotPay.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub TxtRemainAmt_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtRemainAmt.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub BtnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click

        If (TxtProjName.Text <> "" And TxtToken.Text <> "" And TxtDownPay.Text <> "" And TxtInstall.Text <> "" And TxtRate.Text <> "" And TxtTotPay.Text <> "" And TxtSlipNo.Text <> "" And TxtRemainAmt.Text <> "") Then

            's1.execommand("create table " & CmbCompName.Text + "_" + TxtProjName.Text & "(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, Plot_No int, Project_Name nvarchar(50), Customer_Name nvarchar(50), Cus_Contact_No int, Broker_Name nvarchar(50), Brok_Contact_No int, Token money, Down_Pay money, Installment money, Size int, Rate money, Total_Pay money, Last_Pay_Date nvarchar(50), Slip_No nvarchar(50), Remain_Amt money, Next_Pay_Date nvarchar(50), Project_Name1 nvarchar(50))")

            Dim cnt As Integer
            ds = s1.exesearch("select * from " & CmbCompName.Text + "_" + TxtProjName.Text & " where id='" + GetId.ToString + "'")
            cnt = ds.Tables(0).Rows.Count

            If cnt = 0 Then

                ''''Insert Record'''''''
                s1.execommand("update " & CmbCompName.Text + "_" + TxtProjName.Text & " set Project_Name = '" + TxtProjName.Text + "', Token = '" + TxtToken.Text + "', Down_Pay = '" + TxtDownPay.Text + "', Installment = '" + TxtInstall.Text + "', Rate = '" + TxtRate.Text + "', Total_Pay ='" + TxtTotPay.Text + "', Last_Pay_Date = '" + Date.Now() + "', Slip_No = '" + TxtSlipNo.Text + "', Remain_Amt = '" + TxtRemainAmt.Text + "', Next_Pay_Date = '" + Date.Now() + "' where Project_Name1 ='" + CmbCompName.Text + "_" + TxtProjName.Text + "'")
                s1.execommand("insert into Projects_Info(Project_Name, Company_Name, Detailed_Name)" & " values('" + TxtProjName.Text + "','" + CmbCompName.Text + "','" + CmbCompName.Text + "_" + TxtProjName.Text + "')")
                MsgBox("Record Succsessfully Saved......!")
                ClearText(Panel1)
                TxtProjName.Focus()

            End If
            'HttpContext.Current.Session("CompanyName") =
            'HttpContext.Current.Session.Add("CompanyName", Company)
        Else
            MsgBox("Please Enter All Details...!")
        End If
    End Sub

    Private Sub BtnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear.Click
        ClearText(Panel1)
    End Sub

    Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
        Me.Close()
    End Sub
    Public Sub New()
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        frmset(Me)
        LblHeader.Font = New Font("Times New Roman", 12, FontStyle.Bold)
        LblHeader.ForeColor = Color.White
        LblHeader.BackColor = Color.FromArgb(84, 153, 170)
        ' Add any initialization after the InitializeComponent() call.
    End Sub
End Class












now i'm passing company name and project name through public variable to another form i.e my size form. (see file.jpg).

after entering company name, project name, token, down payment, installment we have to click on size of plots button on project form (i.e.Project form.jpg).

after clicking on that button size form will load and on starting of form in disabled lable we can see the company name and project name(see file.jpg).

after clicking click to start button it creates the table using that public variable i.e stringpasscompname and stringpassprojname








in project form passing value into public variable see below code(I have paste it from above project form code)







    Private Sub BtnSize_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSize.Click
        Dim obj As New FormSize
        obj.StringPassCompName = CmbCompName.Text
        obj.StringPassProjName = TxtProjName.Text
        obj.Show()
    End Sub







creating public variable in Size form(pasting from below code)






 Public Property StringPassCompName As String
 Public Property StringPassProjName As String











assigning value to public variables in size form(pasting from below code)








        Label5.Text = StringPassCompName
        Label6.Text = StringPassProjName
        Label8.Text = StringPassCompName + "_" + StringPassProjName

        Dim tablename As String = Convert.ToString(Label8.Text)










below is the code of size form









Imports System.Data.SqlClient
Public Class FormSize
    Dim s1 As New Connect
    Dim ds As DataSet
    Dim Table As New DataTable
    Public Property StringPassCompName As String
    Public Property StringPassProjName As String
    'Dim Company As String = HttpContext.Current.Session("CompanyName")
    Dim GetId As String = 0

    Private Sub FormSize_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FormStyle(Me)
        ClearText(Panel1)
        Label5.Text = StringPassCompName
        Label6.Text = StringPassProjName
        Label8.Text = StringPassCompName + "_" + StringPassProjName
        TxtPlotNo.Visible = False
        TxtFrom.Visible = False
        TxtTo.Visible = False
        TxtSize1.Visible = False
        TxtSize2.Visible = False
        BtnSubmit.Visible = False
        BtnClear.Visible = False
        Label1.Visible = False
        Label2.Visible = False
        Label3.Visible = False
        Label4.Visible = False
        PictureBox1.Visible = False
    End Sub

    Private Sub BtnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnStart.Click
        TxtPlotNo.Visible = True
        TxtFrom.Visible = True
        TxtTo.Visible = True
        TxtSize1.Visible = True
        TxtSize2.Visible = True
        BtnSubmit.Visible = True
        BtnClear.Visible = True
        Label1.Visible = True
        Label2.Visible = True
        Label3.Visible = True
        Label4.Visible = True
        PictureBox1.Visible = True
        s1.execommand("create table " & StringPassCompName + "_" + StringPassProjName & "(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, Plot_No int, Project_Name nvarchar(50), Customer_Name nvarchar(50), Cus_Contact_No int, Broker_Name nvarchar(50), Brok_Contact_No int, Token money, Down_Pay money, Installment money, Size nvarchar(50), Vaar numeric(18,2), Rate money, Total_Pay money, Last_Pay_Date nvarchar(50), Slip_No nvarchar(50), Remain_Amt money, Next_Pay_Date nvarchar(50), Project_Name1 nvarchar(50))")
        TxtPlotNo.Focus()
    End Sub

    Private Sub TxtPlotNo_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtPlotNo.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub TxtFrom_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtFrom.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub TxtTo_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtTo.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub TxtSize1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtSize1.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub TxtSize2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtSize2.KeyPress
        e.Handled = s1.OnlyNum(Asc(e.KeyChar))
    End Sub

    Private Sub BtnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click
        Dim val1 As Integer = Convert.ToInt32(TxtPlotNo.Text)
        Dim val2 As Integer = Convert.ToInt32(TxtFrom.Text)
        Dim val3 As Integer = Convert.ToInt32(TxtTo.Text)
        Dim i As Integer
        Dim vaar As Integer
        Dim size1 As Integer
        Dim size2 As Integer
        Dim tablename As String = Convert.ToString(Label8.Text)

        ds = s1.exesearch("Select * from " & StringPassCompName + "_" + StringPassProjName & " where id='" + GetId.ToString + "'")
  
        If (val1 >= val2 And val1 >= val3) Then

            MsgBox("No. of plots " + TxtFrom.Text + " to " + TxtTo.Text + " are submitted")
            size1 = TxtSize1.Text
            size2 = TxtSize2.Text
            Dim cnt As Integer

            ds = s1.exesearch("select * from " & StringPassCompName + "_" + StringPassProjName & " where id='" + GetId.ToString + "'")
            cnt = ds.Tables(0).Rows.Count

            For i = val2 To val3
                vaar = size1 * size2 / 9
                Label7.Text = vaar
                If (cnt = 0) Then
                    s1.execommand("insert into " & StringPassCompName + "_" + StringPassProjName & " (Plot_No, Size, Vaar, Project_Name1)" & " values('" + TxtFrom.Text + "','" + TxtSize1.Text + "X" + TxtSize2.Text + "','" + Label7.Text + "','" + StringPassCompName + "_" + StringPassProjName + "')")
                    TxtFrom.Text = Val(TxtFrom.Text + 1)
                End If

            Next
            MsgBox("Record Inserted")

            TxtFrom.Text = ""
            TxtTo.Text = ""
            TxtSize1.Text = ""
            TxtSize2.Text = ""
            TxtPlotNo.Focus()

            'For Each row As DataRow In ds.Tables(0).Rows
            '    If (row.Item("Plot_No") = val2 And row.Item("Plot_No") = val3) Then
            '        MsgBox("Plots " + TxtFrom.Text + " are Exist")
            '    End If
            'Next row

        Else
            MsgBox("You can not enter more than " + TxtPlotNo.Text + " Plots")
            TxtFrom.Text = ""
            TxtTo.Text = ""
            TxtFrom.Focus()

        End If

    End Sub

    Private Sub BtnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClear.Click
        ClearText(Panel1)
    End Sub

    Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
        Me.Close()
    End Sub
    Public Sub New()
        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        frmset(Me)
        LblHeader.Font = New Font("Times New Roman", 12, FontStyle.Bold)
        LblHeader.ForeColor = Color.White
        LblHeader.BackColor = Color.FromArgb(84, 153, 170)
        ' Add any initialization after the InitializeComponent() call.
    End Sub
End Class











now in above code when i'm trying to looping through in database table (I have paste that for loop below from above code)









      Dim value1 As Double
        For v As Integer = 0 To ds.Tables(tablename).Rows.Count
            value1 += ds.Tables(tablename).Rows(v).Item("Plot_No").ToString

            If (val2 = value1 And val3 = value1) Then

                MsgBox("Plots " + TxtFrom.Text + " are Exist")
                TxtFrom.Text = ""
                TxtTo.Text = ""
            End If
        Next













when I'm trying to use table name in above for loop then it throws an error "object reference not set to an instance of an object"(see error.jpg)
I'm creating table manually using combo box text, text box text








        Label5.Text = StringPassCompName
        Label6.Text = StringPassProjName
        Label8.Text = StringPassCompName + "_" + StringPassProjName

        Dim tablename As String = Convert.ToString(Label8.Text)











n see my connect.vb file (it is for stop using same code on and on) so you can understand my code easily











Imports System.Data.SqlClient
Public Class Connect
    Dim cn As New SqlConnection("Data Source=BHAVESH-PC;Initial Catalog=ConstructERP;Integrated Security=True;Pooling=False")
    'Dim cn As New SqlConnection("Data Source=BHAVESH-PC;Initial Catalog=LessERP;User ID=sa;Password=sql")
    'Dim cn As New SqlConnection("Data Source=VihalCreation;Initial Catalog=LessERP;User ID=sa;Password=duma")
    Dim cm As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim ds As DataSet
    Friend ConfigurationManager As Object

    Public Sub execommand(ByVal st As String)
        cm = New SqlCommand(st, cn)
        cn.Open()
        cm.ExecuteNonQuery()
        cn.Close()
    End Sub
    Public Sub execINSERT(ByVal table As String, ByVal params As String, ByVal values As String)
        Try
            Dim param() As String = params.Split(New String("|").ToCharArray)
            Dim value() As String = values.Split(New String("|").ToCharArray)
            Dim st As String
            st = "insert into  " + table + "  values ("
            For i As Integer = 0 To param.Length - 1
                If i = param.Length - 1 Then
                    st += param(i) + ")"
                Else
                    st += param(i) + ","
                End If
            Next
            Dim cm As New SqlCommand(st, cn)
            For i As Integer = 0 To param.Length - 1
                Dim p As New SqlParameter
                p.ParameterName = param(i)
                p.Value = value(i)
                'If i = 0 Then p.DbType = DbType.Int16
                cm.Parameters.Add(p)
                cm.Parameters.AddWithValue(param(i), value(i))
            Next
            cn.Open()
            'MsgBox(cm.CommandText)
            'MsgBox(cn.ConnectionString)
            cm.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try
    End Sub
    Public Sub execDELETE(ByVal yourtable As String, ByVal yourfield As String, ByVal yourvalue As String)
        Dim st As String
        st = "delete from " + yourtable + " where " + yourfield + " = '" + yourvalue + "' "
        Dim cm As New SqlCommand(st, cn)
        cn.Open()
        'MsgBox(cm.CommandText)
        cm.ExecuteNonQuery()
        cn.Close()
    End Sub
    Public Function exesearch(ByVal st As String) As DataSet
        cm = New SqlCommand(st, cn)
        da = New SqlDataAdapter(cm)
        ds = New DataSet
        da.Fill(ds)
        Return ds
    End Function
    Public Function OnlyNum(ByVal Kcode As String) As Boolean
        If (Kcode >= 48 And Kcode <= 57) Or Kcode = 8 Then
            OnlyNum = False
        Else
            OnlyNum = True
        End If
    End Function
    Public Function Onlychar(ByVal Kcode As String) As Boolean
        If (Kcode >= 48 And Kcode <= 57) Or Kcode = 8 Then
            Onlychar = False
        Else
            Onlychar = True
        End If
    End Function
End Class




now tell me how to get tablename which is stored in object
I have tried it using convert.tostring(object), ctype(object, string) but its not working.
please help me.

Attached image(s)

  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image

This post has been edited by andrewsw: 21 January 2015 - 06:55 AM
Reason for edit:: fixed code tags


Is This A Good Question/Topic? 0
  • +

Replies To: how to get table name when tables are created manually in code?

#2 andrewsw   User is offline

  • Bouncy!
  • member icon

Reputation: 6563
  • View blog
  • Posts: 26,615
  • Joined: 12-December 12

Re: how to get table name when tables are created manually in code?

Posted 21 January 2015 - 06:53 AM

ds = s1.exesearch("select * from " & CmbCompName.Text + "_" + TxtProjName.Text & " where id='" + GetId.ToString + "'")

You need to surround this CmbCompName.Text + "_" + TxtProjName.Text with apostrophes. Much better would be to use parameterized queries.

(If the id is a number then it doesn't need apostrophes around it.)

Note that the closing code tag is [ /code ] not [ \code ] (without the extra spaces).

Please also avoid large areas of empty space in your post, it makes it more awkward to read.
Was This Post Helpful? 0
  • +
  • -

#3 Amy1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 07-November 14

Re: how to get table name when tables are created manually in code?

Posted 22 January 2015 - 02:49 AM

@andrewsw If I write it in apostrophes then it will not going to search table..because I have created project tables using combo box text and text box text...so If I write it in apostrophes it throws an error...give me some better solution for it....sorry for the empty spaces in post..now tell me how to edit my post....
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • Bouncy!
  • member icon

Reputation: 6563
  • View blog
  • Posts: 26,615
  • Joined: 12-December 12

Re: how to get table name when tables are created manually in code?

Posted 22 January 2015 - 02:54 AM

Sorry, my mistake, the table-name doesn't need apostrophes.

You cannot edit your posts yet.

This post has been edited by andrewsw: 22 January 2015 - 02:56 AM

Was This Post Helpful? 0
  • +
  • -

#5 Amy1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 07-November 14

Re: how to get table name when tables are created manually in code?

Posted 22 January 2015 - 02:57 AM

nope i can not edit my post and profile too...
Was This Post Helpful? 0
  • +
  • -

#6 Amy1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 07-November 14

Re: how to get table name when tables are created manually in code?

Posted 22 January 2015 - 03:02 AM

table name will be in apostrophes like :

dim qry as string = "Select * from MyTable"



but I'm trying to create my table using objects text(combobox, textbox)
1
ds = s1.exesearch("select * from " & CmbCompName.Text + "_" + TxtProjName.Text & " where id='" + GetId.ToString + "'")



I'm using "&" to get object text as my table name
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1